Redis, Memcached, Tokyo Tyrant and MySQL comparision (rectification skip-name-resolve)

My previous post Redis, Memcache, Tokyp Tyrant, MySQL comparison had a flaw as pointed out by this comment. The MySQL was taking a huge time for doing a reverse DNS lookup.

I turned on the skip-name-resolve parameter in the my.cnf and the Throughput of MySQL grew considerably, almost more than double.

Here are the new results.

GET

SET

worksheet

MyISAM vs InnoDB

Nothing much has changed in the above test. Except for the fact InnoDB starts leading the way when there are high number of concurrent Inserts/Updates or Writes on the table. As seen from the “Set” graph InnoDB starts closing for MyISAM’s write efficiency around 30 concurrent requests and then by 60 concurrent requests its already ahead in throughput of writes – 1284/s against 825/s. Further I had put a watch on processlist and was watching the processess, there were times during MyISAM when the inserts took over 6seconds to finish, which also means that if you are in a need of an application which requires quicker response during heavy loads / heavy concurrency… You need to check the MyISAM vs. InnoDB scenario really closely. At low concurrency MyISAM is well ahead in writes, and in Reads, both MyISAM and InnoDB perform equally well.

Again you need to make sure that you check ur test conditions really well before just taking InnoDB for granted.

9 thoughts on “Redis, Memcached, Tokyo Tyrant and MySQL comparision (rectification skip-name-resolve)”

  1. Hi,

    It seems like you might want to consider using MEMORY tables in MySQL too. It would be more of an “apples with apples” comparison.

    I don’t think it’s much of a surprise that disk-based/transactional tables are slower than memcached, etc.

    Lachlan

  2. True, a HEAP based table should perform better, but its not what MySQL’s heart lies in. It lies in MyISAM and InnoDB or disk-based data.

    I know with Redis and Memcached in comparison its Apples vs. Oranges but this whole test is just a guide for the developer to logically categorize the Programs in his application.

    Eg. In cases where extremely high and quick througput its impossible to use just Disk-based DB and a look has to put on Redis or Memcached, however I’m building apps in which it is next to impossible to store all data on RAM. There has to be a thin line drawn.

  3. It looks like you also don’t take advantage of the thread_cache_size which can make a huge difference when making 1000′s of connects and disconnects.

    http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html#sysvar_thread_cache_size

    Also no increase in innodb_buffer_pool_size makes a huge negative impact.

    Those changes alone I think should push the throughput of MySQL substantially again. (possibly on the level with Tokyo Tyrant)

  4. buffer pool, transaction log, transaction log flushing, table cache, thread cache, innodb_thread_concurrency, etc – MySQL out of the box may not have good values for any of these.

    also, try testing with bigger arenas (like, 20-30G) with various distributions… ;-)

  5. :) well testing on 20G or 30G boxes is out of contention !

    And the current setup does actually point a scenario where a huge dB is being worked with servers of about 4G of RAM

  6. You complained about MyISAM concurrency. Did you set concurrent_inserts to 2 so that MyISAM would append instead of getting an exclusive lock to reuse empty space only? What happened then?

    You were using an md5 hash. What column type did you use for this? If you used utf-8 varchar you tripled the size of the InnoDB index and should have used latin1. But you should really pack it more tightly than that given the limited range of character values.

    You’re comparing against non-persistent storage methods. Did you have InnoDB set to maximum durability with innodb_flush_log_at_trx_commit=1 and innodb_support_xa=on or did you set it to the lower and more comparable durability settings of innodb_flush_log_at_trx_commit=0 and innodb_support_xa=off?

    With the limited innodb_buffer_pool_size you’re using there’s a good chance that you’re actually measuring the effectiveness of the operating system cache, not MySQL or InnoDB. That’s because the OS cache will be many times the default buffer pool size and InnoDB stores both index and row data in its buffer pool. MyISAM stores only index information in the key buffer and always uses the OS cache for data rows, so defaults would make InnoDB look artificially bad.

    For memcached did you allocate more storage space than you had RAM to cause it to swap, simulating it running with the low RAM constraint that you’re applying to InnoDB with the default buffer pool size? If not, you’re effectively comparing well-configured memcached and moderately misconfigured MyISAM with deliberately very misconfigured InnoDB.

    For MySQL did you set thread_cache_size to cover the number of concurrent threads or did you leave it at default and cause it to unnecessarily free and allocate threads a lot? Check with SHOW GLOBAL STATUS and look at Threads_Created, if it’s high then you’re not using a large enough thread cache.

    If the bottleneck is connect time, as it may be, did you increase back_log so that the OS would accept connections while still creating threads or did you leave it at the default of 50?

    One of the reasons for the tuning suggestions is that MySQL deliberately ships with low RAM use, high durability settings, not set up for high performance large use setups. I thin you may have accidentally created an example of how properly tuning MySQL matters. :)

  7. Sorry, I see that you used:

    CREATE TABLE `comp_dump` (
    `k` char(32) DEFAULT NULL,
    `v` char(32) DEFAULT NULL,
    KEY `ix_k` (`k`)
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1

    So much better than varchar and utf-8. But now look at the correct way to do it:

    CREATE TABLE comp_dump(
    k binary(20) DEFAULT NULL,
    v char(32) DEFAULT NULL,
    Primary key(`k`) ) ENGINE=InnoDB;

    set @ct:=1;
    insert into comp_dump select unhex(sha1(@ct:=@ct+1)), ‘aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa’ ;

    repeat the next query n time
    insert into comp_dump select unhex(sha1(@ct:=@ct+1)), ‘aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa’ from comp_dump;

    and the rest from http://mituzas.lt/2009/11/19/again-on-benchmarks/#comments . This stores the hash in the required number of bytes and compares without having to follow the rules for your chosen character set and collation. Much better hit rates and comparison speed than storing the hex version of the hash.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>