I recently got an inquiry on how to receive an email every time a query was executed via the MySQL proxy. This is very simple and you can achieve it by simply piping the query to the *nix mail command. Here is the sample code (in a file caled send_mail.lua):

function read_query(packet)
  if string.byte(packet) == proxy.COM_QUERY then
    print("Hello world! Seen the query: " .. string.sub(packet, 2))
    print("Sending query to email:")
    os.execute("echo " .. string.sub(packet, 2) .. "| mail -s'Lua test' your.email.here@example.com ")
  end
end

To execute this, install proxy as per the instructions in the manual. Then simply run the proxy and point it at your script:

shell> bin/mysql-proxy --proxy-lua-script=send_email.lua &

Finally, connect to the proxy and issue a query:

…

The config files that come with MySQL server are generally not that good. They almost never work well for enterprise server loads and will leave most people wondering why the database needs someone to come fix it. In fact that might be why they roll those configs by default, to keep the consulting services alive and profitable. Personally I have no issues with consulting services, but everyone should start out with good configs to begin with. Well, no worries… here are config files for different system RAM configurations. Just choose the one that fits how much RAM your server has and make sure the cnf file is in place before you install the MySQL server RPM/tar/deb packages. Obviously you’ll want to remove the extension of the filename so it reads “my.cnf” and not “my.cnf_64GB” or the like. These cnfs have default settings in the comments so you know what is changed. There are also several equations listed so you know why I am choosing …

I thought I would upload a small script I use which I find quite useful. log_processlist.sh is normally run minutely from cron and collects statistics from any MySQL instance it finds. Data is kept for up to a week and then rotated. This allows you to check what happened over a period of time, for example if an instance crashes or seems to have a lot of load.

I have found it quite invaluable on the instances I’m monitoring and once setup it’s usually nice and silent. If you find this useful or have suggestions for improvements please let me know.

Given the popular response from my first lesson in improving scalability where I detailed simple ways to eliminate unnecessary SQL, let me share another common bottleneck with MySQL scalability that can be instantly overcome.

Analyzing the writes that occur on a system can expose obvious potential bottlenecks. The MySQL Binary Log is a wealth of information that can be mined. Simple DML Counts per table can be achieved by a single line command.

Let’s look at the following example output of a production system:

mysqlbinlog /path/to/mysql-bin.000999 |  \
   grep -i -e "^update" -e "^insert" -e "^delete" -e "^replace" -e "^alter"  | \
   cut -c1-100 | tr '[A-Z]' '[a-z]' |  \
   sed -e "s/\t/ /g;s/\`//g;s/(.*$//;s/ set …

While most people look at performance optimizations for SELECT statements, UPDATE and DELETE statements are often overlooked. These can benefit from the principles of analyzing the Query Execution Plan (QEP). You can only run an EXPLAIN on a SELECT statement, however it’s possible to rewrite an UPDATE or DELETE statement to perform like a SELECT statement.

To optimize an UPDATE, look at the WHERE clause. If you are using the PRIMARY KEY, no further analysis is necessary. If you are not, it is of benefit to rewrite your UPDATE statement as a SELECT statement and obtain a QEP as previously detailed to ensure optimal indexes are used. For example:

UPDATE t
SET     c1 = ‘x’, c2 = ‘y’, c3 = 100
WHERE c1 = ‘x’
AND     d = CURDATE()

You can rewrite this UPDATE statement as a SELECT statement for using EXPLAIN:

EXPLAIN SELECT c1, c2, c3 FROM  t WHERE c1 = ‘x’ AND    d = CURDATE()

You should …

It can be very easy to improve scalability with a MySQL server by a few simple rules. Here is one of them.

“The most efficient way to improve an SQL statement is to eliminate it”

There are numerous ways to eliminate SQL statements, however before I give a classic example that I’ve observed again with a client, let me explain the basic premise of why this improves scalability?

The MySQL kernel can only physically process a certain number of SQL statements for a given time period (e.g. per second). Regardless of the type of machine you have, there is a physical limit. If you eliminate SQL statements that are unwarranted and unnecessary, you automatically enable more important SQL statements to run. There are numerous other downstream affects, however this is the simple math. To run more SQL, reduce the number of SQL you need to run.

Here is the output of a small sample of analyzed TCP/IP …

You blink and there is a new version. I have not seen an Planet MySQL release as yet about this new version. Release Notes.

I’d like to say I installed it, but I downloaded the Linux – Generic 2.6 (x86, 64-bit), TAR file, only to find it contains 6 rpm files. #fail, I’m using Ubuntu.

You have to scroll to the bottom of the list (another stupid thing for a generic binary choice) to get Linux – Generic 2.6 (x86, 64-bit), Compressed TAR Archive. Double #fail

Beginning with MySQL 5.1 as an additional plugin and included by default in MySQL 5.5 the InnoDB plugin includes many performance improvements. To leverage the support of new file formats however a very important setting is necessary.

#my.cnf
[mysqld]
innodb_file_per_table

The use of innodb_file_per_table with an existing system or during an upgrade to 5.1 or 5.5 requires a complete reload of your database to use effectively. In summary.

• Backup all InnoDB tables via mysqldump
• Drop InnoDB tables
• Verify InnoDB not used
• Stop MySQL
• Enable innodb_file_per_table & simplified innodb_data_file_path (if applicable)
• Remove ibdata? files
• Start MySQL
• Create Tables
• Reload Data
• Verify InnoDB Operation

The primary reason is we are moving from using a …

Today I discovered an interesting upgrade problem with a client migrating from MySQL 5.0 to 5.5. The client who is undertaking the upgrade reported that MySQL 5.5 did not support the DECIMAL(18,5) data type. I easily confirmed this not to be the case:

mysql> drop table if exists x;
mysql> create table x (col1 DECIMAL(18,5));
Query OK, 0 rows affected (0.01 sec)

Delving more into the issue in question, I looked at the complete CREATE TABLE statement, recreating the syntax.

mysql> drop table if exists x;
mysql> create table x ( MinValue DECIMAL(18,5));
Query OK, 0 rows affected (0.00 sec)

No problem there.

mysql> drop table if exists x;
mysql> create table x (Department INT NOT NULL, MinValue DECIMAL(18,5) NULL, MaxValue DECIMAL(18,5) NULL);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near …

Many early startups that use a single server for all services or a single database server for their website talk about how they would like to achieve higher availability with MySQL. This is not possible without at least two database servers. Using MySQL replication you can then support higher availability in several varying capacities. An additional MySQL database server can satisfy several infrastructure needs including:

• A primary hot backup
• A datasource for performing backups
• Read scalability infrastructure
• A reporting server
• A benchmarking server
• A fail-over/fail back master environment

MySQL replication is very easy to configure and deploy, a task that takes < 30 minutes for an experienced MySQL DBA. However, altering your backup and recovery strategy, modifying your application to support read/write splitting or implementing a fail-over/fail back …