Showing posts with label MySQL. Show all posts
Showing posts with label MySQL. Show all posts

Unbuffered data fetching from MySQL - mysql_free_result performance issues

When you run a SELECT statement through MySQL client library (or some driver implemented on top of it, such as Perl's DBD::mysql or PHP's mysql_query), by default the server buffers the complete query result and returns rows to the client only after the entire query has finished. This is undesirable if the result set is potentially huge and you want to obtain rows as soon as possible. Fortunately, there is a documented solution for this problem - you can configure your client library to use the API function mysql_use_result rather than mysql_store_result (which is equivalent to the --quick option in mysql command-line client).

With that option in place, let's say you wish to implement a sort of client-side LIMIT statement, based on some required client-side filtering of the sorted result set, and discard the rest of the rows as soon as you have found your matches. Unfortunately, your luck ends here because mysql_use_result will not easily satisfy this scenario. When you attempt to finish/release the statement handle after reading a couple rows (as you are required to do, e.g. by calling $sth->finish if you use Perl's DBI), under the hood the function mysql_free_result is called. The documentation makes it appear harmless, saying that it "frees the memory". Apparently, for unknown reasons, this function (or more precisely, cli_flush_use_result called from within it) does a lot more and takes ages to complete given a big result set. Your client may remain blocked for one minute or longer before it is given an opportunity to execute the next statement, all while causing a high CPU usage on the server. You really wanted to simply abort the query, but with MySQL you can't.

This regrettable behavior is even partly documented: you must retrieve all the rows even if you determine in mid-retrieval that you've found the information you were looking for, although the reason for it is not explained. The only workaround I found was to close the connection on which the incomplete query was issued and then reconnect immediately. Ironically, this brute-force way of "freeing memory" works instantly in comparison. (Of course, it is not acceptable if you're inside a transaction, which will be lost by dropping the connection.)

MySQL 5.6 memcached plugin performance benchmarks

MySQL 5.6 introduces a new feature - you can talk to the InnoDB enginge using the memcached protocol, which is supposed to be faster for simple get/set operations based on a key. For an introduction, refer to this MySQL via Memcache tutorial and the MySQL manual. (The basic idea is that you create a special database in which you describe your tables that should be "exported" to be accessible through the memcached plugin and then enable the plugin itself.)

I didn't find any ballpark figures about how much improvement the new memcached backend might actually offer for applications, so I ran a few benchmarks myself. I compared it to a straightforward way of using an InnoDB table as a key-value store via SQL (either with DELETE/INSERT or REPLACE) and also (just for fun) against a real memcached backend (which of course unlike MySQL offers no persistence). The versions I used specifically were InnoDB 5.6.14 and memcached 1.4.5. The benchmark client wrote about 270000 key-value pairs in random order to the test.demo_test table shipped with the plugin as an example. Each key consisted of 10 and each value of 176 bytes. I also ran another test with 4 such clients running in parallel (on a quad-core AMD Phenom 9650 machine).

I also tested two ways of setting the keys - either with one operation per key or with a batch operation set_multi (this is something offered by the Cache::Memcached::Fast client, not in the memcached protocol, as far as I'm aware).

Note that the DELETE/INSERT approach is not recommended as it is not only slow, but also causes errors and/or deadlocked transactions on collisions (I didn't bother implementing transaction retry).









1 client 4 clients
WRITE ms/op ops/client/s ms/op ops/client/s total ops/s
DELETE+INSERT 0.47875 2089 0.78324 1277 5107 (not safe)
REPLACE 0.28412 3520 0.55597 1799 7195
InnoDB set 0.26271 3806 0.51304 1949 7797
InnoDB set_multi 0.12020 8319 0.47455 2107 8429
memcached set 0.12068 8286 0.11235 8900 35602
memcached set_multi 0.04038 24766 0.14384 6952 27809







READ





SELECT 0.16261 6150 0.13342 7495 29981
InnoDB get 0.09920 10080 0.07527 13286 53142
memcached get 0.06140 16288 0.03447 29010 116040

My conclusion is that the new memcached backend can improve MySQL read performance quite a lot (77% more reads per second) and improve write performance a little (8%) compared to a pre-5.6 approach using SELECT/REPLACE. The percentual speedup for reads may seem big, but it is negligible in absolute terms (retrieving single rows through key lookup was already very fast). So switching to the new backend would only make sense for applications that experience really heavy read loads; it won't improve response time of an average application which is mostly sitting around idle. And of course if need no persistence nor SQL access to your cache tables (which sounds nice for performing bulk updates/deletes as may be), then the good old memcached is a clear winner.

Download my benchmark script and the results in spreadsheet format:

Uncorrelated subqueries treated as DEPENDENT by MySQL

Are you using the operator IN with subqueries in MySQL and wondering about poor performance? Have you tried EXPLAINing your queries and noticed that they are reported as "DEPENDENT SUBQUERY" despite your best intent to use an uncorrelated subquery? If so, read on:

This post describes a MySQL bug (some call it a feature) which has been open for many years and according to official documentation still exists in version 5.6 (note that I only checked 5.0.x and 5.1.x myself and didn't bother to look for info about 6.0, but I have no high hopes based on the bug's age and character).

Consider the following two SQL statements:

  1. SELECT id FROM t1 WHERE id IN (1, 2, 3)
  2. SELECT id FROM t1 WHERE id IN (SELECT id FROM t2)

In MySQL they have very different performance characteristics: the first one will work fast as expected, the second one will actually cause index lookups to be executed repeatedly for each row from t1 rather than just once for the entire statement. Why?

Blame the "optimizer", as explained in the manual. Our fine uncorrelated query becomes forcingly "optimized" into an EXISTS query and thus correlated (rougly speaking; it's a bit more complicated, but you can view the result by using EXPLAIN EXTENDED followed by SHOW WARNINGS). MySQL of course must handle correlated queries per row, leading to disastrous algorithmic performance in what one would assume is a very common case.

I have no clever workaround - you can't "convince" the MySQL optimizer to behave differently. What you can do is use temporary tables to simulate what you really want to happen. But if you do, beware that you may only refer to a temporary table once in a SELECT statement due to another hugely annoying limitation.

For further information see:

P.S. If you found a MySQL version that works in a smarter way, don't hesitate to drop a comment.

Web-based front-end to MySQL's EXPLAIN

If you are troubleshooting slow MySQL queries and are confused with the tabular output of the EXPLAIN command, you may wish to check out my little MySQL EXPLAINer service. You upload a CSV file with whatever output EXPLAIN SELECT has given you, and the query execution plan are is presented in form of a tree.

The CGI script is actually just a web front-end to the nice command-line tool mk-visual-explain, which does all the hard work of parsing EXPLAIN output. All due credit goes to the developers of Maatkit - read their documentation and slides referenced from their site if you are really interested in the technical details.

Solution for MySQL query slow, index does not help

Problem: a MySQL query containing many joined tables is extremely slow (more than 1 minute). Proper indexes are present and being used according to EXPLAIN. The number of processed and returned rows is small (the problem persists with a COUNT(*) query).

Diagnosis: you may be hitting a performance problem with the MySQL query optimizer, which computes an "optimal" execution plan before the query is executed. It is difficult to find out without instrumentation (see example below on how to measure the time spent in optimizer), but it is easy to try whether the recommended solution helps.

Solution: change the MySQL parameter optimizer_search_depth, e.g. SET optimizer_search_depth=0 in the session before executing the query.

How to measure time spent in the MySQL query optimizer

On platforms which support DTrace and with an appopriate version of MySQL (I tested with 5.1.46, OpenSolaris), you can use the following DTrace script:

#!/usr/sbin/dtrace -s
/*
 * Measures and shows time spent in the mysql query optimizer.
 *
 * Usage: ./mysql-optimizer-check.d -p `pgrep -x mysqld`
*/
#pragma D option quiet

pid$target:mysqld:*mysql_parse*:entry
{
    printf("Query: %s\n", copyinstr(arg1));
}

pid$target:mysqld:*JOIN*optimiz*:entry
{
    self->start = timestamp;
    printf("Query optimizer entered\n");
}

pid$target:mysqld:*JOIN*optimiz*:return
{
    this->elapsed = timestamp;
    this->nano = this->elapsed - self->start;
    this->time1 = (this->elapsed - self->start)/1000000000;
    this->time2 = ((this->nano % 1000000000) * 100) / 1000000000;
    printf("Time spent in optimizer: %d.%ds\n", this->time1, this->time2);
    this->elapsed = 0;
    self->start = 0;
}

To run this script in OpenSolaris:

pfexec ./mysql-optimizer-time.d -p `pgrep -x mysqld`

Sample output (first query executes instantly, second query hangs and is aborted by myself after 7.72s):

Query: select @@version_comment limit 1
Query optimizer entered
Time spent in optimizer: 0.0s
Query: SELECT
    DISTINCT appl_app_id_appl.appl as 'appl.app_id_foreign',
    appl.app_id as 'appl.app_id',
    appl_app_id_appl.app_id as 'appl_app_id_appl.app_id',
    appl_app_id_appl.mandant_id as 'appl_app_id_appl.mandant_id',
    appl_app_id_appl.appl as
Query optimizer entered
Query: KILL /*!50000 QUERY */ 9
Time spent in optimizer: 7.72s
^C

Grep through a MySQL database

Looking for a particular string in a set of locations is a common theme when debugging or reverse-engineering software. One of the interesting search locations might be a MySQL database. dbgrep.pl is a little Perl script that will select all data from all (or just the specified) tables and then attempt to find a string (or regular expression) match in any field. Needless to say, it is rather resource-intensive, but still fast enough to sift through moderately sized development databases (at a processing speed of 3-5 MB/s).

You can download dbgrep.pl from my web site.

This is dbgrep.pl, version 1.0, which scans for strings in a MySQL database.
Copyright (c) 2009 Jan Ploski (plosquare.com)
This software is licensed to you under Artistic License 2.0,
which is published at http://www.perlfoundation.org/artistic_license_2_0

Usage: dbgrep.pl [-u <db user>] [-h <db hostname>]
                 [-p] [-x] [-a] [-v] [-q]
                 <expr> <db name> [<table name> ...]
Switches: 
       -p a password is needed for authorization, read from console
       -x interpret <expr> as a Perl regular expression rather than string
       -a grep through all fields, not just text/(var)char/blob fields
       -v print matching values, not just table/field/primary key locations
       -q only print names of matching tables and fields

Some invocation examples:

./dbgrep.pl -u dbuser -h localhost -p somestring dbname
./dbgrep.pl -u root -xaq '^123' dbname

Exporting data from Microsoft Access to MySQL using a linked table, trigger, UDF

This article describes a simple, but effective integration solution for transferring data on user request from an MS Access database to a MySQL database hosted on a remote web server. The data transfer occurs over an encrypted connection through the Internet, which creates some performance challenges. On the receiving side, the data has to be post-processed, which is achieved using a MySQL trigger and a user-defined function to notify a background daemon process.

The solution is outlined below:

  1. First off, ensure ODBC connectivity between the MySQL Access database and the MySQL database on the remote server. Install MyEnTunnel on the Windows machine to tunnel port 3306 from the local machine to the remote server over an encrypted (ssh) link. Configure MyEnTunnel to keep the connection open at all times. Set up an ODBC data source on the Windows machine to connect to the appropriate database at localhost:3306 (now tunneled to the remote server), using appropriate credentials. Note: I had some trouble configuring MyEnTunnel as a Windows service on Vista (though it worked ok on Windows XP). Adding it as a normal autostart program was a viable workaround.
  2. Based on the connection from the previous step, you can already create a linked table in Access which is mapped to a pre-existing MySQL table. Inserting rows into that table will insert them into MySQL. However, if you try inserting >1000 rows through a DSL connection, you will notice that the whole setup is unbearably slow (several minutes). Same applies to deleting rows. Some suggest setting up pass-through queries to work around the problem. I have not seriously tried this. Instead, I chose to reduce the number of INSERTs necessary by using a single linked table which acts as a "mailbox" on the MySQL side for receiving a "message" (long string) from MS Access. This message is deciphered by a remote daemon process.
  3. A naive approach would be to have the mailbox table contain a single column of type "text" and accept a single row. This won't work, MS Access will truncate everything below 64 KB or even below. You have to split the message into chunks. I chose to have 32 KB chunks. The layout of the mailbox table is as follows:
    CREATE TABLE `mailbox` (
      `id` int(4) NOT NULL auto_increment,
      `msg` text,
      `_ts` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
      `last_row` int(1) default '0',
      PRIMARY KEY(`id`)
    )
    
    To transmit the chunked message, first a DELETE FROM mailbox is executed, then a series of rows, each containing up to 32 KB msg payload are inserted, and all but the last row have last_row=0. The chunking is done by a VBA macro, which basically executes a query in Access, concatenates the rows (and columns) into a string, finally divides up the string into appropriately sized chunks.
  4. The following steps will make the server do something in response to the received data. Generally, you can react to database changes using triggers (provided that your MySQL version is recent enough). What we'd like to do is to have a trigger wake up a daemon process, which can then read the mailbox table, stitch together the received message chunks, and process the message. For that, we'll to add three more pieces to the puzzle: a dummy table, a trigger, and a user-defined function (UDF).
  5. The dummy table looks as follows:
    CREATE TABLE `dbg_tmp_dummy` (
      `dummy` varchar(256) default NULL
    )
    
    It should contain a single row, value doesn't matter. It will become apparent next why the dummy table is required.
  6. In order to notify an external process, I chose to use the tried and true UNIX named pipe mechanism (signals could be used just as well, but you'd then have to run the daemon process under MySQL identity). MySQL is supposed to write a character to a FIFO file, which the daemon process is reading from. MySQL doesn't support writing to files out-of-the-box without overwriting the file (the built-in export function will complain if the target file already exists). To work around this, a little MySQL user-defined function has to be implemented in C, so that the following incantation is possible: SELECT writefile('/tmp/fifo.pipe', 'x'); with the effect of a single byte 'x' being appended to /tmp/fifo.pipe. Given that we are dealing with a named pipe, and that the daemon process might not be listening, the write has to be a non-blocking one. I will skip the implementation details here. Rest assured that the UDF is a simple one. If you are new to UDFs, read the MySQL manual and have a look at udf_example.c shipped with the MySQL source code. Essentially, you compile the C file into a .so library, place the library in a directory searched by MySQL, use CREATE FUNCTION to register the function in your database.
  7. Finally comes the trigger part. To define the trigger, connect to the target database using the command-line MySQL client and enter the following:
    delimiter |
    CREATE TRIGGER mailbox_trigger AFTER INSERT ON mailbox
    FOR EACH ROW BEGIN
    UPDATE mailbox SET dummy=writefile('/tmp/fifo.pipe', 'x') WHERE NEW.last_row=1;
    END;
    |
    
    The above code is quite self-explanatory. The important things to note is that you have to use the special delimiter, you can't just enter the trigger definition having the standard semicolon as the delimiter (you'd get a syntax error). It is now apparent why the dummy table is needed. It is impossible to execute a SELECT statement within a trigger which is not tied to an INSERT/UPDATE. Finally, note how the last_row attribute is used to only send the notification after the full message has been received.
  8. The daemon process might be a Perl script which reads from the pipe:
    sub wait_for_notification
    {
        if (! -p $pipe_path) # create pipe if necessary
        {
            unlink($pipe_path);
            my $prev_umask = umask(077); # rw for owner (mysql)
            system("mknod $pipe_path p", 1, 1) || die "mknod: $!";
            umask($prev_umask);
        }
        open(FL, "<$pipe_path") || die "open: $!";
        for (;;)
        {
            my $buf = '';
            my $ret = sysread(FL, $buf, 1);
        
            if (!defined($ret))
            {
                die "sysread $pipe_path: $!";
            }
            elsif (!$ret) # EOF: we read something
            {
                close(FL) || die "close $pipe_path: $!";
                return;
            }
        }
    }
    
    Beware that notifications will be lost if MySQL writes to the pipe while the daemon is not reading it. For my particular use case, this is not an issue, as the daemon can also periodically poll the database and the notification volume is very low. You could modify the solution to use shared memory with appropriate locking instead of pipes to make sure that no race conditions are possible.