Re: Extreme high load and low throughput using MySQL & Hybris on an HP DL380G5, quad-core Intel, 4GB RAM

[Timothy Karl]

Thanks for your replies and suggestions!
I've been away for a while working with the MySQL Enterprise support team on this issue.

Regarding Hybris, from my understanding, we don't have much control over the queries which Hybris issues to MySQL; however, we did add indexes to take care of all of the slow queries (we also started logging queries using no indexes). The improvement was noticable, going from about 275 to about 1800 queries per second running the same set of SELECT statements using JMeter.

We also have kept using InnoDB, one reason being row locking vs. table locking. InnoDB does not create the same extreme high load average as with MyISAM (possibly because of the broader locking in MyISAM?). InnoDB creates a much lower load average under heavy load. However, we are still showing table_locks_immediate increasing even only when selects are taking place. Why should this happen?

The MySQL engineers suggested that we try using the InnoDB plugin version 1.0.3 available at InnoDB.com. Since it isn't available as a binary for our OS and version of MySQL, I compiled it. It seems to perform a little bit better in our tests, but nothing to 'write home' about. We're going to try it on our production machine soon to see what difference there is in the live environment.

I also adjusted sort_buffer_size to 8M, join_buffer_size to 8M, read_rnd_buffer_size to 8M and max_connections to 160. I tested various sizes for the buffer sizes using our JMeter SELECT tests, going all the way down to 1M with worsening throughput performance at 1M.

innodb_concurrency_tickets were tested between 10000, 5000, and 500; innodb_thread_sleep_delay between 20000, 10000, and 5000; and innodb_thread_conncurrency between 8, 6, and 4 with 6 being giving the best throughput/performance for us.

The query cache, no matter how large I make it, or after tweaking the query_cache_min_res_unit to minimize fragmentation (and therefore pruning), I still can't get the query cache usage to perform well. The current Hit:Insert ratio is 0.16:1 and increasing the query cache size from 32M to 64M doesn't seem to have any effect on the percentage of hits.

In summary, the best throughput performance for the overall system was gained from adding/changing indexes on the some queries. The server setting adjustments did not provide any huge gains in throughput. Our query cache does not seem to perform better by giving it more memory or lessening fragmentation. Table locks are taking place on SELECTS in InnoDB which I don't have an answer for. We'll try our self-compiled MySQL version with the InnoDB v. 1.0.3 plugin to see if it provides better performance.