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.

Query to Audit the Permissions/Privileges for a MySQL instances

I found some nice queries on this blog (http://blog.devart.com/how-to-get-a-list-of-permissions-of-mysql-users.html) and I've been using them to do user audits on the MySQL databases I administer.  I decided to UNION all the queries together and also make a few additions (checking if the password is blank, hostname, database version). The result is an easy to read list of all permissions for all MySQL users on an instance. Additionally, I changed the syntax of the queries so that the results could be copied and pasted into a GRANT statement.

Because this super long query is "unioning" the results of the global priv, the database priv, table priv, column priv and routine priv, it might be a little confusing to read at first. The entries that show the password hash are the global entries followed by a "-" where the priv is not applicable. Next you will see database priv (if there any) and a "-" for each non applicable section and so on through tables, column, view, and routines.

If you want to generate a list for all the user on the instance set @username to %. If you only want to look for one user then change @username to that username. When I create users on production that have elevated priv such as for a DBA, I create them a "bang" account and a separate read only account. The DBAs will have a bang account with all priv that looks like '!username'@'10.%' and then a second account with just the ability to browse the database and run SELECT queries that looks like 'username'@'10.%'.  When I run this audit query, if I put 'username' into the value below it will also show me '!username'.


SET @username = '<user name>';
SELECT -- the list of global privileges
@@hostname as server_host,
SUBSTRING(@@version , 1, CHAR_LENGTH(@@version) - 4) as 'database_version',
mu.host `host`,
mu.user `user`,
(case when `mu`.`password` ='' then "**Yes**" ELSE 'No' end) as `Empty Password?`,
mu.password as 'password',
'-' as `Database`,
'-' as `Tables`,
'-' as `Views`,
'-' as `Tables Columns`,
'-' as `Views Columns`,
'-' as `Procedures`,
'-' as `Functions`,
TRIM(TRAILING ',' FROM(RTRIM(CONCAT(
IF(mu.Select_priv = 'Y', 'Select, ', ''),
IF(mu.Insert_priv = 'Y', 'Insert, ', ''),
IF(mu.Update_priv = 'Y', 'Update, ', ''),
IF(mu.Delete_priv = 'Y', 'Delete, ', ''),
IF(mu.Create_priv = 'Y', 'Create, ', ''),
IF(mu.Drop_priv = 'Y', 'Drop, ', ''),
IF(mu.Reload_priv = 'Y', 'Reload, ', ''),
IF(mu.Shutdown_priv = 'Y', 'Shutdown, ', ''),
IF(mu.Process_priv = 'Y', 'Process, ', ''),
IF(mu.File_priv = 'Y', 'File, ', ''),
IF(mu.Grant_priv = 'Y', 'Grant, ', ''),
IF(mu.References_priv = 'Y', 'References, ', ''),
IF(mu.Index_priv = 'Y', 'Index, ', ''),
IF(mu.Alter_priv = 'Y', 'Alter, ', ''),
IF(mu.Show_db_priv = 'Y', 'SHOW DATABASES, ', ''),
IF(mu.Super_priv = 'Y', 'Super, ', ''),
IF(mu.Create_tmp_table_priv = 'Y', 'CREATE TEMPORARY TABLES, ', ''),
IF(mu.Lock_tables_priv = 'Y', 'LOCK TABLES, ', ''),
IF(mu.Execute_priv = 'Y', 'Execute, ', ''),
IF(mu.Repl_slave_priv = 'Y', 'REPLICATION SLAVE, ', ''),
IF(mu.Repl_client_priv = 'Y', 'REPLICATION CLIENT, ', ''),
IF(mu.Create_view_priv = 'Y', 'CREATE VIEW, ', ''),
IF(mu.Show_view_priv = 'Y', 'SHOW VIEW, ', ''),
IF(mu.Create_routine_priv = 'Y', 'CREATE ROUTINE, ', ''),
IF(mu.Alter_routine_priv = 'Y', 'ALTER ROUTINE, ', ''),
IF(mu.Create_user_priv = 'Y', 'CREATE USER, ', ''),
IF(mu.Event_priv = 'Y', 'Event, ', ''),
IF(mu.Trigger_priv = 'Y', 'Trigger, ', '')
)))) AS `Privileges`
FROM
mysql.user mu
WHERE user LIKE CONCAT('%', @username)
UNION
SELECT -- the list of privileges for a database
@@hostname as server_host,
SUBSTRING(@@version , 1, CHAR_LENGTH(@@version) - 4) as 'database_version',
md.host `host`,
md.user `user`,
'-' as `Empty Password?`,
'-' as 'password',
md.db `Database`,
'-' as `Tables`,
'-' as `Views`,
'-' as `Tables Columns`,
'-' as `Views Columns`,
'-' as `Procedures`,
'-' as `Functions`,
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 user LIKE CONCAT('%', @username)
UNION
SELECT -- the list of privileges for tables
@@hostname as server_host,
SUBSTRING(@@version , 1, CHAR_LENGTH(@@version) - 4) as 'database_version',
mt.host `host`,
mt.user `user`,
'-' as `Empty Password?`,
'-' as 'password',
'-' as `Database`,
CONCAT(mt.Db, '.', mt.Table_name) `Tables`,
'-' as `Views`,
'-' as `Tables Columns`,
'-' as `Views Columns`,
'-' as `Procedures`,
'-' as `Functions`,
REPLACE(mt.Table_priv, ',', ', ') AS `Privileges`
FROM
mysql.tables_priv mt
WHERE
user LIKE CONCAT('%', @username)
AND
mt.Table_name IN
(SELECT
DISTINCT
t.table_name `tables`
FROM
information_schema.tables AS t
WHERE
t.table_type IN
('BASE TABLE', 'SYSTEM VIEW', 'TEMPORARY', '') OR
t.table_type <> 'VIEW' AND
t.create_options IS NOT NULL
)
UNION
SELECT -- the list of privileges for views
@@hostname as server_host,
SUBSTRING(@@version , 1, CHAR_LENGTH(@@version) - 4) as 'database_version',
mv.host `host`,
mv.user `user`,
'-' as `Empty Password?`,
'-' as 'password',
'-' as `Database`,
'-' as `Tables`,
CONCAT(mv.Db, '.', mv.Table_name) `Views`,
'-' as `Tables Columns`,
'-' as `Views Columns`,
'-' as `Procedures`,
'-' as `Functions`,
REPLACE(mv.Table_priv, ',', ', ') AS `Privileges`
FROM
mysql.tables_priv mv
WHERE
user LIKE CONCAT('%', @username)
AND
mv.Table_name IN
(SELECT
DISTINCT
v.table_name `views`
FROM
information_schema.views AS v
)
UNION
SELECT -- the list of privileges for table columns
@@hostname as server_host,
SUBSTRING(@@version , 1, CHAR_LENGTH(@@version) - 4) as 'database_version',
mtc.host `host`,
mtc.user `user`,
'-' as `Empty Password?`,
'-' as 'password',
'-' as `Database`,
'-' as `Tables`,
'-' as `Views`,
CONCAT(mtc.Db, '.', mtc.Table_name, '.', mtc.Column_name) `Tables Columns`,
'-' as `Views Columns`,
'-' as `Procedures`,
'-' as `Functions`,
REPLACE(mtc.Column_priv, ',', ', ') AS `Privileges`
FROM
mysql.columns_priv mtc
WHERE
user LIKE CONCAT('%', @username)
AND mtc.Table_name IN
(SELECT
DISTINCT
t.table_name `tables`
FROM
information_schema.tables AS t
WHERE
t.table_type IN
('BASE TABLE', 'SYSTEM VIEW', 'TEMPORARY', '') OR
t.table_type <> 'VIEW' AND
t.create_options IS NOT NULL
)
UNION
SELECT -- the list of privileges for view columns
@@hostname as server_host,
SUBSTRING(@@version , 1, CHAR_LENGTH(@@version) - 4) as 'database_version',
mvc.host `host`,
mvc.user `user`,
'-' as `Empty Password?`,
'-' as 'password',
'-' as `Database`,
'-' as `Tables`,
'-' as `Views`,
'-' as `Tables Columns`,
CONCAT(mvc.Db, '.', mvc.Table_name, '.', mvc.Column_name) `Views Columns`,
'-' as `Procedures`,
'-' as `Functions`,
REPLACE(mvc.Column_priv, ',', ', ') AS `Privileges`
FROM
mysql.columns_priv mvc
WHERE
user LIKE CONCAT('%', @username)
AND mvc.Table_name IN
(SELECT
DISTINCT
v.table_name `views`
FROM
information_schema.views AS v
)
UNION
SELECT -- the list of privileges for procedures
@@hostname as server_host,
SUBSTRING(@@version , 1, CHAR_LENGTH(@@version) - 4) as 'database_version',
mp.host `host`,
mp.user `user`,
'-' as `Empty Password?`,
'-' as 'password',
'-' as `Database`,
'-' as `Tables`,
'-' as `Views`,
'-' as `Tables Columns`,
'-' as `Views Columns`,
CONCAT(mp.Db, '.', mp.Routine_name) `Procedures`,
'-' as `Functions`,
REPLACE(mp.Proc_priv, ',', ', ') AS `Privileges`
FROM
mysql.procs_priv mp
WHERE
mp.Routine_type = 'PROCEDURE'
AND user LIKE CONCAT('%', @username)
UNION
SELECT -- the list of privileges for functions
@@hostname as server_host,
SUBSTRING(@@version , 1, CHAR_LENGTH(@@version) - 4) as 'database_version',
mf.host `host`,
mf.user `user`,
'-' as `Empty Password?`,
'-' as 'password',
'-' as `Database`,
'-' as `Tables`,
'-' as `Views`,
'-' as `Tables Columns`,
'-' as `Views Columns`,
'-' as `Procedures`,
CONCAT(mf.Db, '.', mf.Routine_name) `Functions`,
REPLACE(mf.Proc_priv, ',', ', ') AS `Privileges`
FROM
mysql.procs_priv mf
WHERE
mf.Routine_type = 'FUNCTION'
AND user LIKE CONCAT('%', @username);

Bang Account for MySQL

When I was a Windows admin my team referred to the special user we had for doing admin work as a bang account. This would be a separate account from your normal active directory account. My manager had a bang bang account which allowed him to administer the admins.

When I became a DBA I carried this concept into the creation of users on database systems. I do a lot of Database Engineering work and I'm constantly troubleshooting queries, writing queries, dropping databases, deleting data, etc. Our database accounts are not yet tied into LDAP or PAM or active directory or tied into roles or anything like that yet. They are managed independently of any other systems.

Even with the precautions of colored screens and warnings it is easy to mix up that I'm on a prod systems and not a dev systems and accidentally drop a database that wasn't supposed to be dropped. Because of this I create two accounts on production for DBAs. A bang account that has all priv and a read only account that only has Select, Process, Execute, REPLICATION CLIENT, Trigger. It allows me to browse the database without the risk of me accidentally making changes. If I do need to do admin work or make a change then I log on with the bang account and immediately close the connection when I'm done. I never saved the bang account credentials so that I don't accidentally logon unaware. There have been so many times when I thought I was on a dev system or a test system and issued a DDL change but I was actually on production, since I was logged onto my read only account, no changes were made.

To differentiate a bang account from a read only account I simply prefix them with an exclamation point. Some people have told me this is silliness and creates additional user management overhead. I feel it is worth it. Does anyone else do some thing similar?

I found a MySQL 5.6 bug

I setup a MySQL 5.6 instance and immediately granted table level permissions to a user. I then ran my audit query to confirm that the permissions were added. The query returned zero results. I thought maybe I had done something wrong. I confirmed that the permissions were actually there.

Here is an example of the table level portion of the audit query:

SELECT -- the list of privileges for tables
mt.host `host`,
mt.user `user`,
CONCAT(mt.Db, '.', mt.Table_name) `Tables`,
REPLACE(mt.Table_priv, ',', ', ') AS `Privileges`
FROM
mysql.tables_priv mt
WHERE
mt.Table_name IN
(SELECT
DISTINCT
t.table_name `tables`
FROM
information_schema.tables AS t
WHERE
t.table_type IN
('BASE TABLE', 'SYSTEM VIEW', 'TEMPORARY', '') OR
t.table_type <> 'VIEW' AND
t.create_options IS NOT NULL
)

The query has a SUB SELECT that checks to make sure tables with the name of grant actually exist. For some reason doing a sub-select on the information schema does not work on MySQL 5.6. There is a bug already reported for this:

http://bugs.mysql.com/bug.php?id=77191

The work around is to run this:

set optimizer_switch='semijoin=off';