How the MySQL query cache can kill performance on your database instance

A while back Percona did an audit for my company telling us that we probably ought to turn off the MySQL query cache on most of our servers because it was causing more overhead that it was providing value. Management had no interest in pursuing this recommendation so it never happened. 

Months later, one of our database servers had become so overloaded with queries that it was performing poorly. The queries themselves were pretty fast queries and tuned as much as I could get them. Management came to me asking how to make the server perform better. I told them more queries were running on the server than it could reasonably handle. The server has 24 CPU but the threads_running as evidenced from this graph taken from a profiling session with Jet Profiler shows that at certain times we had 100 threads running (this is the dark blue color in the graph below). I told them without giving me command level access to delve deeper, the only recommendations I can tell you is to get more powerful database servers and do better load balancing to spread some of the read-only queries onto slave servers.

Meanwhile, a co-worker on my team had noticed from his benchmarking efforts that if the query cache is turned on and he was running PERF UNIX utility (http://www.brendangregg.com/perf.html) he would see the following wait state as the number 1 or number 2 wait state from the mysqld daemon:

QUERY_CACHE:INSERT_INTO_FREE_MEMORY_SORTED_LIST

So he tested turning off the MySQL query cache and that wait state went away and MySQL performed much better on a test system.

With this evidence we took it to management to see if the System Engineers would run PERF on the database server (they won't give my team SSH access) and check to see if the above wait state was at the top of the list. When they checked, it sure was!

We got permission to change the query cache to type 2 (as in "on demand" so that a query would only use the query cache if it was instructed to do so). We ran these commands:

SET GLOBAL query_cache_type=2;
RESET query cache;
FLUSH query cache;

After doing this, the results were pretty good. In benchmarking we had seen a greater than 15% increase in transactions per second being server by the DB server. Threads_running went down to less than 10. Slowness went away and everyone was happy. Here is the graph after making the change:

Here are two graphs from a totally different MySQL server with a different load and similar results:


BEFORE (with query cache turned on):

AFTER (one day later with query cache turned off):

Using screen on Linux

One of tools I frequently use in linux tools is called "screen". If you are any kind of administrator (DBA/DevOps/System Engineer) and using Linux systems, screen is a tool you should be familiar with.

Once you've got it installed, screen allows you to keep a session running even after you disconnect from SSH. Why is this helpful? If you are running any command that has the potential to take longer than a few seconds you should run it inside of a screen session. For example here are a few instances where you would want to use screen:

1. Installing new programs on your linux system
2. Dumping data from your MySQL instance
3. Restoring data into a MySQL instance
4. Adding an index to a table that will take a while
5. Running a bash script that requires a lot of processing and time to run

Typically I run "screen -R my_screen_name" to attach to a new screen session before performing the work. 

You can run "screen -ls" to see all the screen sessions that are currently running and also to see which one you are attached to. 

You can also use "screen -R <name>" to re-attach to a screen session that you have disconnected from. 

If you are currently attached to a screen session and you type "exit" then it will close the screen session. Unless this is what you want then you shouldn't type exit.

I usually just close the window and let it keep running on the console. Also if I lose VPN access then it just keeps running and I have to logon with a new SSH session to re-attach to the screen session. You can also detach from a session (without it exiting) by running "screen -d <name>"

Frequently you want to log what you did the during the screen session. Using "-L" will log what is done during that session. Screen will save a file like "screenlog.0" in the directory where you ran the command to create the new screen session. 

Here is a good quick reference: http://aperiodic.net/screen/quick_reference

Also if your screen sessions die sometimes you get a funny error like this:
"Suddenly the Dungeon collapses!! – You die…"

Orphaned MySQL privileges

I have discovered that some of the MySQL instances which I administer have orphaned privileges for users that do not exist. The user no longer has an entry in MySQL user table but for whatever reason there are privileges in the other mysql.* tables for these non-existent user. It is not clear to me how the privileges got into this state. Maybe someone deleted the user directly from the MySQL user table and didn't use the DROP USER command.

Here is the query I wrote (partly based on the MySQL audit query) to find these orphaned  privileges.

(SELECT @@hostname as 'hostname', CONCAT("'",`user`,"'",'@',"'",`host`,"'") as 'credentials', 'database' as 'priv_level', db as 'object', TRIM(TRAILING ',' FROM(RTRIM(CONCAT(
IF(md.Select_priv = 'Y', 'Select, ', ''),
IF(md.Insert_priv = 'Y', 'Insert, ', ''),
IF(md.Update_priv = 'Y', 'Update, ', ''),
IF(md.Delete_priv = 'Y', 'Delete, ', ''),
IF(md.Create_priv = 'Y', 'Create, ', ''),
IF(md.Drop_priv = 'Y', 'Drop, ', ''),
IF(md.Grant_priv = 'Y', 'Grant, ', ''),
IF(md.References_priv = 'Y', 'References, ', ''),
IF(md.Index_priv = 'Y', 'Index, ', ''),
IF(md.Alter_priv = 'Y', 'Alter, ', ''),
IF(md.Create_tmp_table_priv = 'Y', 'CREATE TEMPORARY TABLES, ', ''),
IF(md.Lock_tables_priv = 'Y', 'LOCK TABLES, ', ''),
IF(md.Create_view_priv = 'Y', 'CREATE VIEW, ', ''),
IF(md.Show_view_priv = 'Y', 'SHOW VIEW, ', ''),
IF(md.Create_routine_priv = 'Y', 'CREATE ROUTINE, ', ''),
IF(md.Alter_routine_priv = 'Y', 'ALTER ROUTINE, ', ''),
IF(md.Execute_priv = 'Y', 'Execute, ', ''),
IF(md.Event_priv = 'Y', 'Event, ', ''),
IF(md.Trigger_priv = 'Y', 'Trigger, ', '')
)))) as 'Privileges'
FROM mysql.db md
WHERE CONCAT("'",`user`,"'",'@',"'",`host`,"'") NOT IN (SELECT DISTINCT CONCAT("'",`user`,"'",'@',"'",`host`,"'") FROM mysql.user)
)
UNION ALL
(SELECT @@hostname as 'hostname', CONCAT("'",`user`,"'",'@',"'",`host`,"'") as 'credentials', 'table' as 'priv_level', table_name as 'object', table_priv as 'Privileges'
FROM mysql.tables_priv mt
WHERE CONCAT("'",`user`,"'",'@',"'",`host`,"'") NOT IN (SELECT DISTINCT CONCAT("'",`user`,"'",'@',"'",`host`,"'") FROM mysql.user)
)
UNION ALL
(SELECT @@hostname as 'hostname', CONCAT("'",`user`,"'",'@',"'",`host`,"'") as 'credentials', 'column' as 'priv_level', column_name as 'object', column_priv as 'Privileges'
FROM mysql.columns_priv mvc
WHERE CONCAT("'",`user`,"'",'@',"'",`host`,"'") NOT IN (SELECT DISTINCT CONCAT("'",`user`,"'",'@',"'",`host`,"'") FROM mysql.user)
)
UNION ALL
(SELECT @@hostname as 'hostname', CONCAT("'",`user`,"'",'@',"'",`host`,"'") as 'credentials', 'procs' as 'priv_level', routine_name as 'object', proc_priv as 'Privileges'
FROM mysql.procs_priv mf
WHERE CONCAT("'",`user`,"'",'@',"'",`host`,"'") NOT IN (SELECT DISTINCT CONCAT("'",`user`,"'",'@',"'",`host`,"'") FROM mysql.user)
);

I've seen on other blogs queries like this:

SELECT host, db, user
FROM
     information_schema.SCHEMATA right join
     mysql.db ON (SCHEMATA.SCHEMA_NAME=db.Db)
WHERE SCHEMA_NAME is null;

That query will help to find privileges for databases that do not exist anymore or never existed but it doesn't work so well in my case. We use wildcards in the GRANT statements a lot because we have thousands of databases on each server with the database has the same prefix. When I run the above query it returns results for the wildcard entries but those are not actually orphaned privileges.

Setting up replication on vagrant systems with Fatal error on UUID

My work uses Vagrant VMs to quickly create the same developer environment for everyone in the engineering department. I was setting up Master-Slave replication with a developer today on two Percona 5.6 Vagrant VMs and I kept getting an error when starting the slave. This was the first time I had tried to setup replication with one of these Vagrant VMs. The error could be seen in the Last_IO_Error column of "SHOW SLAVE STATUS"

Fatal error: The slave I/O thread stops because master and slave have equal MySQL server UUIDs; these UUIDs must be different for replication to work.

I didn't know until today that MySQL 5.6 has a file called auto.cnf in the data directory with the UUID for that system. Since our Vagrant VMs are all copied from the same system then of course they will all have the same auto.cnf file. Just removing the file and restarting MySQL fixed the issue. MySQL automatically created a new auto.cnf after restart.

This UUID is totally separate from the server-id in the my.cnf file.