Tuning MySQL 5.6 for Great Product Performance: FAQs
By Rebecca Hansen on Sep 20, 2013
“Will you expand the next webcast to 90 minutes? This one was too interesting to last only one hour” was one of the questions we got during the “Tuning MySQL for Great Product Performance: The Fundamentals, Updated for MySQL 5.6” webinar on August 27th. The engineers on Q&A got a lot of good (and more technical) questions during the webcast. For those of you who were unable to join us live, I’ve posted the questions and answers below, and you can listen to a recording of the webinar and get a .pdf of slides at this link.
The webinar was created specifically for the software and hardware vendors that include MySQL with their products, often “deeply embedded” so their customers do no separate installation, tuning, or admin. Their focus is great out of the box performance, but whatever your use case, you’ll get useful performance tips with an emphasis on some of the newer capabilities in MySQL 5.5 and 5.6.
Following are some related and new resources, and below that, the Q&A.
• "Developer and DBA Guide to What's New in MySQL 5.6" white paper
• “What’s New in MySQL Workbench” on-demand webinar
• “MySQL Workbench: Database Design. Development. Administration. Migration” white paper
And, don’t forget! MySQL Connect starts on tomorrow!
Q. What tool did you use to perform the benchmarks?
A. We primarily use Sysbench. Here are more details regarding the benchmarks: http://dev.mysql.com/tech-resources/articles/mysql-5.6.html
Q. Does MySQL 5.6 Community Edition include the performance gains shown in the benchmarks?
A. Yes, everything that John has been discussing is in both releases of MySQL: Community Edition and the Commercial Editions.
Q. Do these performance results hold for NDB as well?
A. These graphs are based upon InnoDB. To better understand the performance characteristics of NDB, please review this information: http://www.mysql.com/why-mysql/benchmarks/mysql-cluster/
Q. Buffer Pool % of Available Memory: Does this mean RAM or virtual memory or combination? For 32-bit MySQL, would the maximum be 3 to 4 GB?
A. We recommend basing this upon RAM, mapping to Virtual Memory might create some performance issues. Find more details here: http://dev.mysql.com/doc/refman/5.6/en/innodb-buffer-pool.html
For 32-bit MySQL, the max is (2^32 -1). Here are the details: http://dev.mysql.com/doc/refman/5.6/en/innodb-parameters.html#sysvar_innodb_buffer_pool_size
Q. What’s the benefit of having multiple buffer pools instead of one large one?
A. When the InnoDB buffer pool is large, many data requests can be satisfied by retrieving from memory. But, you might encounter bottlenecks from multiple threads trying to access the buffer pool at once.
Starting in InnoDB 1.1 and MySQL 5.5, you can enable multiple buffer pools to minimize this contention. Each page that is stored in or read from the buffer pool is assigned to one of the buffer pools randomly, using a hashing function. Each buffer pool manages its own free lists, flush lists, LRUs, and all other data structures connected to a buffer pool, and is protected by its own buffer pool mutex.
Q. How can I calculate the buffer pool size if we have 250 connections running simultaneously?
A. Ideally, you set the size of the buffer pool to as large a value as practical, leaving enough memory for other processes on the server to run without excessive paging. The larger the buffer pool, the more InnoDB acts like an in-memory database, reading data from disk once and then accessing the data from memory during subsequent reads. The buffer pool even caches data changed by insert and update operations, so that disk writes can be grouped together for better performance.
Q. When using JPA (e.g. EclipseLink) it has a cache for database objects; should that cache more or less replace the InnoDB pool?
A. Since every application's requirements are unique, we recommend that you architect your solution based upon your business objectives. As far as MySQL goes, I would still recommend scaling the size of the InnoDB buffer pool as large as possible. Application caching is very unique to your goals.
Q. How can I get the number of “hits”(query select/insert/update/delete) in a table in a day?
A. The Performance Schema in MySQL 5.6 will give you that insight. The counters can be reset at the start of the day and then examine the contents at the end of the day.
Here is the query that I use: SELECT object_schema.object_name, count_fetch AS SELECTS, sum_timer_fetch, count_insert AS INSERTS, sum_timer_insert, count_delete AS DELETES, sum_timer_delete, FROM performance_schema.table_io_waits_summary_by_table ORDER BY sum_table_wait DESC;
Q. How do you diagnose and analyze a Query (SELECT) statement that has a very short duration reported but very long fetch time reported. How do you find out the cause of a very slow fetch time? What is a slow fetch time a symptom of?
A. If the EXPLAIN statement doesn't help, then with MySQL 5.6 you can try using the Optimizer Trace for more details on the cost of the SELECT statement. Or, I would recommend using the Performance Schema. This allows you to look at stages of a query and understand exactly where time is spent. http://dev.mysql.com/doc/refman/5.6/en/performance-schema.html
Q. Can you please elaborate on performance schema usage?
A. Performance Schema is designed to provide you with deeper details into how the MySQL server performs. Details are located at: http://dev.mysql.com/doc/refman/5.6/en/performance-schema.html If you want to discuss it further, please contact your local Sales Technical Consultant and we can dive into the details.
Other MySQL Storage Engines (not InnoDB)
Q. Which of the MySQL storage engines support built-in procedures etc.?
A. The general new features such as views, stored procedures, triggers, INFORMATION_SCHEMA, precision math (DECIMAL column type), and the BIT column type, apply to all storage engines.
Q. Can index condition pushdown be used with NDB?
A. This optimization can be used only by the NDB storage engine. See: http://dev.mysql.com/doc/refman/5.6/en/condition-pushdown-optimization.html
Q. Is that index is being internally created by the server while executing any query using order by in internal temp table?
A. You must manually create the index; an index is not automatically created by a query using ORDER BY.
Q. Do you have any tuning suggestions or references for those of us who are heavily dependent on the Archive storage engine?
A. Since you can't index tables in the ARCHIVE engine, there isn't much you can do - see: http://dev.mysql.com/doc/refman/5.6/en/archive-storage-engine.html You might want to take a look at using a different storage engine if performance is an issue.
Q. What disk types should be used for an OLTP environment?
A. That depends on the number of transactions per second, size of each transaction, etc. But you should at least have disks with high speeds (10,000 RPM), high access rates and with a large disk cache. But SSD's would be the best choice - if available.