"MySQL performance" in Kiev, 09/22/2009

Today was at the conference " Performance MySQL ." The speaker was Dmitry Kravchuk. Thank you maghamed , I already knew 60% of the conference.

The conference itself was interesting, in the chronographic order of origin of MySQL. Starting from 1995, when Monty and David gathered, until today, MySQL Perf version.

What did not like:

• Sun has an insider version of MySQL Perf (performance slightly higher than in 5.4), which is in no hurry to roll out.
• Almost the entire conference was heard "Sun is, Sun is."
• Cheated maatkit attention (maybe because the enemy development?)
• The listener was pleased with the company which had a “scalable system” - 1500 requests per page. At the same time their technide considers memcache crutches.
• There were no sandwiches :(

What you liked:

• Speaker :). Dmitry answered all questions, there was a lively discussion. At the end of the speech went hints, about which I had not heard before and had never seen it anywhere.
• The principle of "trust but verify." Dmitry did not believe anyone, so MySQL completely tested it in performance.
• In the hall was a man from Percona, who sometimes helped Dmitry with the answers.
• MySQL is evolving! Despite the purchase of Sun in recent years, much attention has been paid to performance, which led to the emergence of version 5.4.

A couple of hints for yourself not to forget:

• Each application is unique and the server must be customized for specific needs (your KO)
• Now there is a bug with innodb_max_dirty_pages_pct. This value is simply ignored. There is a patch, they have not yet been added to the main branch (I could be wrong)
• While there is a bug with innodb_max_dirty_pages_pct, you can influence the “dirty pages” flash through innodb_log_file (do not ask why, ask Dima)
• An interesting option, about which I have not heard before - innodb_flush_log_trx_commit. Takes values ​​0, 1, 2. 0 - flush every second (0 commits per second = 1 flush), 1 - flush every commit (10 thousand commits per second = 10 thousand flush), 2 - flush every second if there was a commit (10 thousand commits in sec = 1 flush). The best option for speed estessno 2
• innodb_io_capacity - should be set depending on the capabilities of the hard drive. Dmitry offered 2000
• Query cache more than 20mb - evil
• With double write buffer enabled, in some cases you can lose up to 30% performance.
• Redo log, bin log, Double Write buffer should be stored on different hard drives because of random read for the database itself
• Sometimes it is worth playing around with max_purge_log
• Dmitry's blog dimitrik.free.fr