MySQL Performance

In my efforts as a server DevOps, understanding the programs a client is running is as important as understanding the Linux server itself. In most cases, the client is running a web app that requires a MySQL database connection.

// MAX CONNECTIONS
The max_user_connections set to “0” means it has unlimited connections and this can actually cause problems by overwhelming the database. this is something to keep an eye on.
max_user_connections=0
max_connections=60

This should be increased to:
max_connections=1024

// BUFFER POOL SIZE (DEPRECATED in 10.5+)
This provides a memory buffer to allow for database calls to be processed. This buffer must be setup with a very specific formula or it will automatically adjust itself to a value you may not want.

The formula is:
The buffer pool size must be equal to or a multiple of innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances. Changing those variable settings requires restarting the server.

Currently, the innodb_buffer_pool_size is:
innodb_buffer_pool_chunk_size=790626304
innodb_buffer_pool_instances=2
innodb_buffer_pool_size=1581252608

As you see in these numbers, the innodb_buffer_pool_size is slightly more than double the innodb_buffer_pool_chunk_size * the innodb_buffer_pool_instances

This is fine, but just know that changing just the innodb_buffer_pool_size will not deliver the results you may expect if you leave everything as is.

MySQL recommends values that are based on a 1024 byte increment. So I always recommend setting these up using either the MySQL formats like this:

M = 1048576
G = 1073741824

Or typing out the entire number in a 1024 format. For example, 1024 * 1024 = 1048576

So a good example, depending on the amount of memory available would be:
8G is a valid innodb_buffer_pool_size value because 8G is a multiple of innodb_buffer_pool_instances=16 * innodb_buffer_pool_chunk_size=128M, which is 2G.

innodb_buffer_pool_chunk_size=128M
innodb_buffer_pool_instances=16
innodb_buffer_pool_size=8G

In my experience, no one ever gets this right, and it’s a major reason why MySQL databases become slow or crash.

// JOIN BUFFER SIZE
This has the default setting of 256K, and is usually OK, but that all depends on the app developers.

The JOIN clause is often times written using large SQL statements that request all data.
Like this:

SELECT * FROM t1 LEFT JOIN (t2, t3, t4)
ON (t2.a = t1.a AND t3.b = t1.b AND t4.c = t1.c)

This JOIN request is wasteful and slow. This is often the major reason a database has slow queries.

Instead, the JOIN clause should look like this:

SELECT t1.accountID LEFT JOIN (t2, t3, t4)
ON (t2.a = t1.a AND t3.b = t1.b AND t4.c = t1.c)

This can take considerably longer for developers to rebuild all these requests, but in the long run, this makes everything faster for the end user.

// MAX DIGEST LENGTH
This is an area that no one ever sets correctly or understands how a MySQL database works.

If enabled, and it should be, all database calls are converted into a hash called a “digest”, and depending on the size of the database call, these digests will either be one string, or many many truncated strings that add up to the original database call. Think of these digests like a deck of cards. If the database calls are large (52 cards large), then the default “max_digest_length” will require a 52 separate cards to complete the request. That means I have to deal you one card at a time until you have all 52.

If the “max_digest_length” is set correctly, then all I do is hand you the entire deck.

Currently, the max_digest_length is set to:
max_digest_length=1024

This value determines the maximum number of bytes available per session for a database call and that’s not nearly enough for most database calls.

This is the default database setup and it needs to be upgraded to:
performance_schema=ON
max_digest_length=32768
performance_schema_max_digest_length=32768

Since “performance_schema” is currently set to OFF, none of these digests are being used.
Again, this is the default, but it should be enabled for significantly better database performance.

// DATABASE ENGINE
In most cases, developers will designate a database engine that’s different from the default. In my experience, they always select MyISAM even if the database default engine is InnoDB.

These two engines work very differently from one another, and out of the box, MySQL uses the InnoDB as the default engine. That’s what the database is using now. So it’s vital to keep the database engine consistent.

The InnoDB engine will lock each line in the table when writing to the database.
The MyISAM engine will lock the entire table when writing to the database.

In general, the MyISAM is good for databases that are mostly read only and the InnoDB is good for a variety of read/write processes.

// AVAILABLE DATABASE ENGINES
+——————–+———+—————————————————————-+————–+——+————+
| ENGINE | SUPPORT | COMMENT | TRANSACTIONS | XA | SAVEPOINTS |
+——————–+———+—————————————————————-+————–+——+————+
| FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
| InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
| MyISAM | YES | MyISAM storage engine | NO | NO | NO |
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |
| CSV | YES | CSV storage engine | NO | NO | NO |
| ARCHIVE | YES | Archive storage engine | NO | NO | NO |
+——————–+———+—————————————————————-+————–+——+————+

As you can see, there are several registered database engines in this instance of MySQL. I would recommend using the “MEMORY” database engine when writing temporary tables for such requests as user sessions.

Since user sessions are typically never saved, the MEMORY engine alleviates pressure on the rest of the database. Remember, the database is reading and writing to a disk drive, so anything that can be pushed to something other than this drive will increase performance.

Using Memcached is a great example. Try to avoid Redis because Memcached can be setup to run multiple instances on one server.

All major social networks, such as Twitter, run Memcached and not Redis.

// SLOW QUERY LOGGING
It’s vital to setup the slow query log. More importantly, it’s vital to setup the “long_query_time” to less than 10 seconds. The slow query log will not log anything unless the default threshhold of 10 seconds has been exceeded. Setting this to a lower number will produce more logs of the specific SQL statements that are taking a long time to execute. This way, you can debug the SQL statements in the app framework.

Currently, the slow query log is:
slow_query_log=OFF
slow_query_log_file=/rdsdbdata/log/slowquery/mysql-slowquery.log
long_query_time=10.000000

slow_query_log=ON
long_query_time=3

Once the slow query has been identified, this should be turned off once again. Any logging slows the database down.

// CHARACTER SETS AND COLLATION
MySQL [pmc]> show variables like ‘%character%’;
+————————–+——————————————————————+
| Variable_name | Value |
+————————–+——————————————————————+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | utf8mb4 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | utf8mb4 |
| character_set_system | utf8 |
| character_sets_dir | /rdsdbbin/oscar-8.0.mysql_aurora.3.01.0.0.9430.0/share/charsets/ |
+————————–+——————————————————————+
8 rows in set (0.023 sec)

MySQL [database]> show variables like ‘%collation%’;
+——————————-+——————–+
| Variable_name | Value |
+——————————-+——————–+
| collation_connection | utf8_general_ci |
| collation_database | utf8mb4_0900_ai_ci |
| collation_server | utf8mb4_0900_ai_ci |
| default_collation_for_utf8mb4 | utf8mb4_0900_ai_ci |
+——————————-+——————–+
4 rows in set (0.023 sec)

Current database collation for most databases: latin1_swedish_ci

As you can see, this is a conflict. The standard today is to use the character set “utf8mb4”.
While the server is currently using a collation of: latin1_swedish_ci

I would recommend changing all this to: utf8mb4_unicode_ci

Now the app framework may not be setup to use utf8mb4, and if that’s the case, then make sure to use:
character_set_client=utf8
collation_database=utf8_general_ci

The database can be setup to use utf8mb4 and it will be backwards compatible with utf8. So the issue right now is the latin1_swedish_ci collation for all the database tables. This should be either utf8mb4_unicode_ci or utf8_general_ci.

The best choice is the utf8mb4 character set and utf8mb4_unicode_ci collation.

When updating, just be sure everything is consistent and compatible with the app framework.

// UPDATES TO RDS
The RDS “parameters” will need to be adjusted to make the changes recommended above.

Just look at the menu on the left hand side in AWS and you’ll see the “parameters”.

This is where it all starts and then in a week or two, we look at the slow query log to see the results.