« Presentation Slides of Japanize, Mylingual, Pathtraq | Main | Memo: Binary Logging of MySQL from the viewpoint of storage engine »

Q4M - 0.6 release and benchmarks

Today I have uploaded Q4M (a Queue for MySQL) 0.6, which is basically a performance-improvement from previous releases. Instead of using pread's and a small user-level cache, Q4M (in default configuration) now uses mmap for reads with a reader/writer lock to improve concurrency.

I also noticed that it would be possible to consume a queued row in one SQL statement.

SELECT * FROM queue_table WHERE queue_wait('queue_table');

This statement actually does the same thing as,

if (SELECT queue_wait('queue_table') == 1) {
  SELECT * FROM queue_table;
}

But since the former style requires only one SQL statement (compared to two statements of the second one), it has much less overhead.

And combining these optimizations together, consumption speed of Q4M has nearly doubled from previous post (or trippled from 0.5.1) to over 57,000 rows per second.

[kazuho@dev32 q4m-0.6]$ ./configure --with-mysql=/home/kazuho/dev/mysql/51/64-bin-src --prefix=/home/kazuho/dev/mysql/51/64-bin --with-sync=no --with-delete=msync
(snip)
[kazuho@dev32 q4m-0.6]$ USE_C_CLIENT=1 MESSAGES=1000000 CONCURRENCY=10 DBI='dbi:mysql:test;mysql_socket=/tmp/mysql51.sock;user=root' t/05-multireader.t 
1..4
ok 1 - check number of messages
ok 2 - min value of received message
ok 3 - max value of received message
ok 4 - should have no rows in table


Multireader benchmark result:
    Number of messages: 1000000
    Number of readers:  10
    Elapsed:            17.261 seconds
    Throughput:         57934.239 mess./sec.

The benchmark script accepts several test options as environment variables.

USE_C_CLIENT
if set to 1, uses a C version of test client (default:0)
MESSAGES
number of messages to be transmitted (should be a multiple of CONCURRENCY, default:6400)
CONCURRENCY
number of clients to execute (default:32)
VAR_LENGTH
maximum size of blob column to be transmitted (in bytes. Average row size becomes half of the supplied value, default:0)

Also, other test senarios other than the t/multireader.t script (that tests pure consumption speed) has been added.

t/multirw.t
benchmark script that concurrently inserts and consumes from a single queue (throughput benchmark)
t/multiwait.t
benchmark script to check performance penalties when queue becomes empty

For example, if you want to estimate the throughput of a queue with average 512 bytes of data per row, the test would be:

[kazuho@dev32 q4m-0.6]$ USE_C_CLIENT=1 VAR_LENGTH=1024 MESSAGES=100000 CONCURRENCY=10 DBI='dbi:mysql:test;mysql_socket=/tmp/mysql51.sock;user=root' t/05-multirw.t 
1..4
ok 1 - check number of messages
ok 2 - min value of received message
ok 3 - max value of received message
ok 4 - should have no rows in table


Multi-reader-writer benchmark result:
    Number of messages: 100000
    Number of readers:  10
    Elapsed:            4.197 seconds
    Throughput:         23825.368 mess./sec.

Or if you would like to run the queue with fsync enabled so that there would be no data losses upon kernel panic, the configuration options and the test results would be like:

[kazuho@dev32 q4m-0.6]$ ./configure --with-mysql=/home/kazuho/dev/mysql/51/64-bin-src --prefix=/home/kazuho/dev/mysql/51/64-bin --with-sync=fdatasync --with-delete=pwrite
(snip)
[kazuho@dev32 q4m-0.6]$ USE_C_CLIENT=1 VAR_LENGTH=1024 MESSAGES=100000 CONCURRENCY=100 DBI='dbi:mysql:test;mysql_socket=/tmp/mysql51.sock;user=root' t/05-multirw.t 
1..4
ok 1 - check number of messages
ok 2 - min value of received message
ok 3 - max value of received message
ok 4 - should have no rows in table


Multi-reader-writer benchmark result:
    Number of messages: 100000
    Number of readers:  100
    Elapsed:            13.497 seconds
    Throughput:         7408.798 mess./sec.

Note that CONCURRENCY was increased from 10 in previous tests to 100 in this test in order to hide the slowness of the hard drive (though the use of group commits).

Achieving a >7,000 rows/sec. throughput with fsync enabled seems to me a pretty good result. Doesn't it?

Post a comment

(If you haven't left a comment here before, you may need to be approved by the site owner before your comment will appear. Until then, it won't appear on the entry. Thanks for waiting.)