Why MySQL indices are so important? Here at Vevida, we like to help our customers as much as possible. Even with optimizing a MySQL database when they don’t ask for it, or when a customer doesn’t know performance can be improved. For example by adding an index because we spotted a slow query in our slow-query log.
Fix MySQL indices and slow queries
An index is used by MySQL to help find rows quickly. We want to make it as easy as possible for MySQL to find the relevant rows, the more precise or specific we are the less the number of rows MySQL has to fetch.
The other day I spotted the following in MySQL slow-query log, in which MySQL needed roughly 3 seconds to examine 8035 and send 872 rows. That looks like a good optimization candidate…
# Time: 140605 16:41:34
# User@Host: database[database] @ server-01.example.com [1.1.11.111]
# Thread_id: 4660034 Schema: database QC_hit: No
# Query_time: 3.010892 Lock_time: 0.000062 Rows_sent: 872 Rows_examined: 8035
use database;
SET timestamp=1401979294;
SELECT * FROM sContent WHERE sYear = '2014' AND sPublish = 1 and ('2014-06-05 16:41:30' Between sVisible_from And sVisible_untill or sVisible_unlimitedSo I started with examining the query:
Examining a query with MySQL EXPLAIN SELECT
The MySQL EXPLAIN statement is used to obtain a query execution plan. That is, an explanation of how MySQL would execute a query. This means I can use EXPLAIN SELECT to examine the query.
MariaDB [database]> explain SELECT * FROM sContent WHERE sYear = '2014' AND sPublish = 1 and ('2014-06-05 16:41:30' Between sVisible_from And sVisible_untill or sVisible_unlimited = 1) ORDER BY sDate_insert DESC;
+------+-------------+-------------+------+----------------------------------------+----------------+---------+-------+------+-----------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+-------------+------+----------------------------------------+----------------+---------+-------+------+-----------------------------+
| 1 | SIMPLE | sContent | ref | sPublish,sVisible_unlimited | sPublish | 2 | const | 7091 | Using where; Using filesort |
+------+-------------+-------------+------+----------------------------------------+----------------+---------+-------+------+-----------------------------+
1 row in set (0.03 sec)Here I see indices exist: sPublish, sVisible_unlimited, and only sPublish is used.
In the WHERE clause there is sYear = '2014', but sYear isn’t indexed. I use the statement DESCRIBE (or DESC) to describe the table definitions. This is an synonym of EXPLAIN, but mostly used for describing table definitions.
MariaDB [database]> desc sContent;
+--------------------------+--------------+------+-----+---------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+--------------------------+--------------+------+-----+---------------------+-----------------------------+
| cnt_id | int(11) | NO | PRI | NULL | auto_increment |
[...]
| sYear | varchar(4) | YES | | NULL | |
+--------------------------+--------------+------+-----+---------------------+-----------------------------+I added an index on sYear:
ALTER TABLE `sContent` ADD INDEX (`sYear`);and this gives a better result examining the query again with EXPLAIN:
MariaDB [database]> explain SELECT * FROM sContent WHERE sYear = '2014' AND sPublish = 1 and ('2014-06-05 16:41:30' Between sVisible_from And sVisible_untill or sVisible_unlimited = 1) ORDER BY sDate_insert DESC;
+------+-------------+-------------+------+-------------------------------------------------+----------+---------+-------+------+----------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+-------------+------+-------------------------------------------------+----------+---------+-------+------+----------------------------------------------------+
| 1 | SIMPLE | sContent | ref | sPublish,sVisible_unlimited,sYear | sYear | 7 | const | 1503 | Using index condition; Using where; Using filesort |
+------+-------------+-------------+------+-------------------------------------------------+----------+---------+-------+------+----------------------------------------------------+
1 row in set (0.00 sec)By adding an index on this one column sYear, MySQL doesn’t have to search through 7091 rows, but only 1503. This saves considerable, which will improve database and website performance.
MySQL index optimization
Update: 2-4-2015, Why is a MySQL index so important? Today, I noticed the following query in the MySQL slow-log of a server (EXPLAIN added for investigation):
MariaDB [db_name]> EXPLAIN SELECT * FROM tblproducts WHERE CampaignId=379 AND CampaignProductId='AB1005257';
+------+-------------+------------------+------+---------------+---------+---------+-------+--------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+------------------+------+---------------+---------+---------+-------+--------+-------------+
| 1 | SIMPLE | tblproducts | ref | Index_5 | Index_5 | 5 | const | 146104 | Using where |
+------+-------------+------------------+------+---------------+---------+---------+-------+--------+-------------+
1 row in set (0.00 sec)As you can see, there is an index present on the tblproducts table, called Index_5. But still MySQL has to plow through 146104 rows. Given the CampaignId field contains what appears to be an identifier, that makes a great option for an extra index!
I added the index: ALTER TABLE `tblproducts` ADD INDEX (`CampaignId`);
The added index optimized the query to only one result:
MariaDB [db_name]> EXPLAIN SELECT * FROM tblproducts WHERE CampaignId=379 AND CampaignProductId='AB1005257';
+------+-------------+------------------+------+--------------------+------------+---------+-------------+------+-----------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+------------------+------+--------------------+------------+---------+-------------+------+-----------------------+
| 1 | SIMPLE | tblproducts | ref | Index_5,CampaignId | CampaignId | 308 | const,const | 1 | Using index condition |
+------+-------------+------------------+------+--------------------+------------+---------+-------------+------+-----------------------+
1 row in set (0.00 sec)This is why MySQL index optimization is so important, even on the WordPress wp_options table.
.toolbelt-social-share{clear:both;font-size:1rem;display:grid;gap:calc(var(–toolbelt-spacing)/ 4);grid-template-columns:repeat(auto-fit,minmax(11rem,1fr));margin-bottom:calc(var(–toolbelt-spacing) * 2);margin-top:calc(var(–toolbelt-spacing) * 2)}.toolbelt-social-share .toolbelt_share-api{display:none}.toolbelt-social-share a{padding:calc(var(–toolbelt-spacing)/ 4) var(–toolbelt-spacing);color:var(–toolbelt-color-light);align-items:center;display:flex;text-decoration:none}.toolbelt-social-share a:hover{color:var(–toolbelt-color-light)}.toolbelt-social-share a:hover span{text-decoration:underline}.toolbelt-social-share-api-enabled .toolbelt-social-share .toolbelt_share-api{display:inline}.toolbelt-social-share-api-enabled .toolbelt-social-share a{display:none}.toolbelt-social-share svg{-webkit-margin-end:calc(var(–toolbelt-spacing)/ 2);margin-inline-end:calc(var(–toolbelt-spacing)/ 2);height:1.5rem;width:1.5rem;vertical-align:middle}.toolbelt-social-share svg *{stroke:none;fill:currentColor}@media (min-width:500px){.toolbelt-social-share .toolbelt_whatsapp{display:none}}
Are you running into MySQL load problems? Learn how to tune MySQL servers for a heavy InnoDB workload, by configuring
innodb_buffer_pool_instances. Dividing the InnoDB buffer pool into multiple instances improves Disk I/O. By doing so, you run your database and website more efficiently and faster. Here is a little help for you.(adsbygoogle = window.adsbygoogle || []).push({});
Tune MySQL InnoDB buffer pool instances & size for heavy workloads
All for more InnoDB Disk I/O performance on MySQL 5.5+.
Tuning MySQL servers is an ever ongoing process. Every new MySQL version brings new configuration settings you can use to improve its performance. As a MySQL DBA you want your database server and databases to perform better than well, don’t you?
MariaDB/MySQL 5.5.4 introduces new configuration settings for the InnoDB storage engine. This can greatly improve MySQL’s InnoDB performance, both in read and write operations.
One of those settings is innodb_buffer_pool_instances. The
innodb_buffer_pool_instancesdivides the InnoDB buffer pool into separate instances. Dividing your buffer pool into separate instances can improve concurrency, by reducing contention as different threads read and write to cached pages. Multiple buffer pool instances are configured using theinnodb_buffer_pool_instancesconfiguration option.You might also want to adjust the
innodb_buffer_pool_sizevalue:The larger the InnoDB buffer pool, the more InnoDB acts like an in-memory database. It reads data from disk once and then accesses the data from memory during subsequent reads. Buffer pool size is configured using the
innodb_buffer_pool_sizeconfiguration option.Back to increasing innodb_buffer_pool_instances.
The innodb_buffer_pool_instances divides the InnoDB buffer pool in a number of regions.
In MySQL versions prior to 5.5.4 this was not configurable and thus set to just one instance. Now you can increase innodb_buffer_pool_size, and you can divide the InnoDB buffer pool into multiple regions by setting innodb_buffer_pool_instances to 2, 3, 4 or 8. As long as innodb_buffer_pool_size is set high enough, and you have enough memory available in your MySQL database server. This increases InnoDB read/write threads.
To enable multiple buffer pool instances, set the innodb_buffer_pool_instances configuration option to a value greater than 1 (the default) up to 64 (the maximum).
How to restore single MySQL table from a full mysqldump backup file?
For example, you can set
innodb_buffer_pool_sizeto 6 GB andinnodb_buffer_pool_instancesto 4 in yourmy.cnfMySQL configuration file:.wp-block-code {
border: 0;
padding: 0;
}
.wp-block-code > div {
overflow: auto;
}
.shcb-language {
border: 0;
clip: rect(1px, 1px, 1px, 1px);
-webkit-clip-path: inset(50%);
clip-path: inset(50%);
height: 1px;
margin: -1px;
overflow: hidden;
padding: 0;
position: absolute;
width: 1px;
word-wrap: normal;
word-break: normal;
}
.hljs {
box-sizing: border-box;
}
.hljs.shcb-code-table {
display: table;
width: 100%;
}
.hljs.shcb-code-table > .shcb-loc {
color: inherit;
display: table-row;
width: 100%;
}
.hljs.shcb-code-table .shcb-loc > span {
display: table-cell;
}
.wp-block-code code.hljs:not(.shcb-wrap-lines) {
white-space: pre;
}
.wp-block-code code.hljs.shcb-wrap-lines {
white-space: pre-wrap;
}
.hljs.shcb-line-numbers {
border-spacing: 0;
counter-reset: line;
}
.hljs.shcb-line-numbers > .shcb-loc {
counter-increment: line;
}
.hljs.shcb-line-numbers .shcb-loc > span {
padding-left: 0.75em;
}
.hljs.shcb-line-numbers .shcb-loc::before {
border-right: 1px solid #ddd;
content: counter(line);
display: table-cell;
padding: 0 0.75em;
text-align: right;
-webkit-user-select: none;
-moz-user-select: none;
-ms-user-select: none;
user-select: none;
white-space: nowrap;
width: 1%;
}
; InnoDB buffer pool size in bytes. The primary value to adjust on a database server,; can be set up to 80% of the total memory in these environments
innodb_buffer_pool_size = 6000M
; If innodb_buffer_pool_size is set to more than 1GB, innodb_buffer_pool_instances
; divides the InnoDB buffer pool into this many instances.
innodb_buffer_pool_instances = 4Code language: TOML, also INI (ini)
In this example, I’ve used an
innodb_buffer_pool_sizeof 6000M (6 GB), so there is 1500M available per innodb_buffer_pool_instance, which is more than the minimum 1 GB. As a rule of thumb, set yourinnodb_buffer_pool_sizeto approximately 70 – 80% of the RAM available.Innodb_buffer_pool_instances defaults
Various MySQL versions have different innodb_buffer_pool_instances default values, here is an overview – listing – for you:
MySQL version# InnoDB buffer pool instancesNotesMySQL 5.5 (<= 5.5.4)1not configurableMySQL 5.51–MySQL 5.6 (<= 5.6.5)1–MySQL 5.6 (>= 5.6.6)8or 1 if
innodb_buffer_pool_size< 1GBMySQL 5.78or 1 if innodb_buffer_pool_size < 1GBMariaDB 10 (<= MariaDB 10.0.3)1–MariaDB 10 (>= MariaDB 10.0.4)8–InnoDB read and write I/O threads in MySQL
Besides innodb_buffer_pool_instances, you can also increase the number of InnoDB read I/O threads and write I/O threads. These are configured with innodb_write_io_threads and innodb_read_io_threads.
Both settings default to 4 threads. We can increase these to, for example, 8:
; Number of I/O threads for writesCode language: TOML, also INI (ini)innodb_write_io_threads = 8
; Number of I/O threads for reads
innodb_read_io_threads = 8
innodb_read_io_threads
innodb_write_io_threads
When should you increase the number of
innodb_read_io_threads? When you see more than 64 × innodb_read_io_threads pending read requests inSHOW ENGINE INNODB STATUS, you might gain by increasing the value of innodb_read_io_threads.Optimizing InnoDB Disk I/O
If you follow the best practices for database design and the tuning techniques for SQL operations, but your database is still slowed by heavy disk I/O activity, explore these low-level techniques related to disk I/O. If the Unix top tool or the Windows Task Manager shows that the CPU usage percentage with your workload is less than 70%, your workload is probably disk-bound, Optimizing InnoDB Disk I/O.
Starting from MariaDB 10.0, the default number of
innodb_buffer_pool_instancesis 8. This means you have to configure yourinnodb_buffer_pool_sizeto at least 8 GB, see the defaults above.Protip, don’t over optimize: never make too many configuration changes at once. After changing one or two settings, let the server run for a few days so you can learn the effects of the changes. Then, if necessary, make additional changes to the configuration.
Convert MyISAM to InnoDB tables for WordPress using a pluginFor WordPress, I created a plugin to convert MyISAM tables to InnoDB, that now is incorporated into the Vevida Optimizer WordPress plugin. The Vevida Optimizer plugin extends the automatic update feature already present in WordPress. WordPress core updates can be switched on or off, themes and translations can be automatically updated, and the plugin updates can be configured on a per-plugin basis.
Innodb_buffer_pool_instances deprecated in MariaDB 10.5.1+
Please note that in MariaDB 10.5.1 and up, innodb_buffer_pool_instances is deprecated and ignored (MDEV-15058). Oracle MySQL 8.0 still seem to support it though.
Extra tips for MySQL performance tuning
Besides optimizing InnoDB for a high-performance workload, there is more you can do to tune MySQL server and database performance. Here are some extra MySQL configuration tips for you. Some information might be outdated and obsolete but may hold valuable information for tuning your MySQL servers. Some settings may be ignored if you’re only using InnoDB as storage engine (tip: drop MyISAM, only use InnoDB!)
Note: this is a translation and rewrite of my older Dutch post “MySQL performance en optimalisatie tips“, which is now deleted and links to here. Just in case you were wondering why you arrived here instead of the Dutch post after clicking a link :-)
1: No two MySQL servers are the same
When optimizing MySQL database servers, keep in mind that no server is equal to another. Settings that work well on one server, may degrade performance on a second. If you manage multiple servers with its configuration under version control (e.g almost -or exactly- the same MySQL configuration for all servers), choose what works best on all servers.
To determine what you can improve, you first need to know how the server performs now. You can use some MySQL commands for this on your MySQL cli (data comes from my very old post).
mysql> SHOW STATUS LIKE '%key_read%';Code language: SQL (Structured Query Language) (sql)+-------------------+-------------+
| Variable_name | Value |
+-------------------+-------------+
| Key_read_requests | 11810240259 |
| Key_reads | 9260357 |
+-------------------+-------------+
These two variables and values relate to the configured
key_buffer_sizeIn this old example, the database server has 4 GB of RAM and a configured
key_buffer_sizeof 512 MB. The ratio (Key_read_requests / Key_reads) is approximately 1/1275, which is good but thekey_buffer_sizevalue may be increased to 768 MB. Even though this is not yet necessary.mysql> SHOW STATUS LIKE 'thread%';Code language: SQL (Structured Query Language) (sql)+-------------------+---------+
| Variable_name | Value |
+-------------------+---------+
| Threads_cached | 0 |
| Threads_connected | 76 |
| Threads_created | 6234040 |
| Threads_running | 2 |
+-------------------+---------+
These
Threads_*variable values show you there are currently 76 connected threads, of which only 2 are really running a thread (executing a statement). This means 74 connections are idle.Here you can also see that there is no “thread cache” set up for MySQL:
Threads_cached | 0You can use the MySQL Server System variable thread_cache_size to configure how many threads must be cached by MySQL. This is one of those configuration settings that, probably, provides the least performance gain, but still…
Don’t set this one too high, somewhere between 20 and 40 is often good enough:
thread_cache_size = 20Code language: TOML, also INI (ini)When you execute the previous statement again, the values will be:
mysql> SHOW STATUS LIKE 'thread%';Code language: SQL (Structured Query Language) (sql)+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| Threads_cached | 14 |
| Threads_connected | 98 |
| Threads_created | 2896 |
| Threads_running | 1 |
+-------------------+-------+
You now have 14 threads cached :)
2: Miscellaneous MySQL configuration settings
A few words on some miscellaneous configuration settings.
2.1: tmp_table_size and max_heap_table_sizeThe default
tmp_table_sizeandmax_heap_table_sizevalues are 16M. These two have to be equal in size! It sets the maximum size for internal in-memory tables, resulting in less creation of temporarily MyISAM tables on the file system. That in return, results in less disk I/O.https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_tmp_table_sizehttps://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_max_heap_table_size
2.2: join_buffer_sizeThe join_buffer_size sets a maximum buffer size for plain index scans, range index scans and joins without indices (and therefore perform full table scans). Keep this one low, 1M for example.
MySQL database optimization with indices
3. Use Diagnostics for improvements
It is important to frequently run diagnostics and/or look up diagnostic data (for example in your information_scheme table). Percona has a lot of information about some key metrics:
https://www.percona.com/doc/percona-server/8.0/index.html#diagnostics-improvements
MySQL tuning, the conclusion
Tuning MySQL and the InnoDB storage engine is an important step in further optimizing your hosting environment. Every new MySQL version brings new settings to improve your MySQL configuration, so be sure to read those changelogs.
In this article we went over InnoDB Buffer Pool Size and InnoDB Buffer Pool Instances. Setting these properly greatly improves your MySQL server’s performance!
But never (ever, ever) over-optimize! Please don’t make too many configuration changes at once. Make one or two and restart mysqld. After monitoring your system for a few days, running with the new configuration, you have data available to further optimize other MySQL settings.
With InnoDB being the default storage engine, you also have to make sure you make use of this storage engine in MySQL. Therefore it is important to convert old MyISAM tables to InnoDB.
Related PostsCheck, repair and optimize MySQL tables with mysqlcheckHow to fix System.Collections.Generic.KeyNotFoundException “The given key was not present in the dictionary” Exception with MySQL Connector/NET and utf8mb4 character setMySQL database optimization with indicesHow to compare MD5 and SHA1 hashes in MySQLShare this:Click to share on Twitter (Opens in new window)Click to share on Facebook (Opens in new window)Click to share on Pocket (Opens in new window)Click to share on LinkedIn (Opens in new window)Click to share on WhatsApp (Opens in new window)Click to share on Reddit (Opens in new window)Click to share on Tumblr (Opens in new window)Click to share on Pinterest (Opens in new window)Click to share on Telegram (Opens in new window)Click to share on Skype (Opens in new window)Click to email this to a friend (Opens in new window)Click to print (Opens in new window)var toolbelt_social_share_description = “Are you running into MySQL load problems? Learn how to tune MySQL servers for a heavy InnoDB workload, by configuring…”;Share Tweet this
Share this
Share this
Save this
Share this