Moving the bottleneck after adding indexes

A few weeks ago, I identified half a dozen indexes that were missing from database tables for an important application at work. There was no rush to get them added because we typically have all changes including database indexes go through dev, qa, alpha, beta and then to prod. Performance started to get so bad for this application that management approved adding the indexes directly to production.

Several of the queries I identified were were doing full table scans frequently and it was clear that these queries would benefit from the index. The application was seeing some slowness and sometimes hit max connections because these queries were not clearing quickly enough.

I got permission to have the indexes added. At night time I added the indexes, one of which I used Percona Online Schema Change.

The next day, the throughput on the server had increased so much that the application was severely taxing the resources on the database system with high CPU and 200~600 something threads running all of these well tuned queries. It was interesting to see what a huge change those indexes made but also that the bottleneck moved from slow queries to too many queries for that version of MySQL to realistically handle with the number of CPU/cores on the system.

pt-query digest

I've used pt-query digest ever since I became a MySQL database guy. It is an extremely useful tool, especially when you company refuses to buy profiling tools for you. One drawback is that it is heavy on processing. For example, if you want to process several hours worth of logs it can take over an hour and is heavy on the processing power of the system. I wouldn't ever recommend running it on the same server as a production database because of this.

Today was the first time I actually used it against binary logs. Previously I've only done it on slow query logs. After requesting a copy of the binary logs (still cannot get them for myself), I converted the binary logs to text using mysqlbinlog. 

Here is an example from their documentation:

https://www.percona.com/doc/percona-toolkit/2.2/pt-query-digest.html

mysqlbinlog mysql-bin.000441 > mysql-bin.000441.txt

pt-query-digest --type binlog mysql-bin.000441.txt

Here is how I ran it to get specific time ranges and to save the report to a file:

pt-query-digest --type=binlog --since='2015-10-27 11:22:36' --until='2015-10-27 16:40:49' --limit=100%  mysqllog_all_day.out > mysqllog_tuesday_all_day_digest.txt

There are a lot of options to change how the report is created. Seeing the number of times a query with a specific footprint is executed and a graph of the distribution based on timings is super useful. 

If you want to see more, here are a couple blog posts:

https://www.percona.com/blog/2011/12/29/identifying-the-load-with-the-help-of-pt-query-digest-and-percona-server/

https://www.percona.com/blog/2014/03/14/tools-and-tips-for-analysis-of-mysqls-slow-query-log/

Grouping by granular timestamp ranges - by day or by minute

I was asked to run some counts and make it granular to the minute. At first I was like, sure I will just do a group by on the MINUTE. That didn't exactly work as it was missing several hours, so I realized that since every hour follows the same pattern I'd have to add a group by for each hour of the day in addition to the minute...

I want to get a count up to the minute for the entire day:

SELECT timestamp, count(*) as count

FROM table_b

WHERE timestamp BETWEEN '2015-10-26 00:00:00' AND '2015-10-26 23:59:59'

GROUP BY HOUR(timestamp), MINUTE(timestamp);

If you don't add the HOUR as part of the GROUP BY, it will only give you one hour out of the day. 

I want to get a count up to the hour for the entire year:

SELECT timestamp, count(*) as count

FROM table_a

WHERE start BETWEEN '2015-01-01 00:00:00' AND '2015-12-31 23:59:59'

GROUP BY MONTH(timestamp), DAY(timestamp), HOUR(timestamp)

If you don't add the MONTH as part of the GROUP BY, it will only give you the results for a single month. 

I want to get a count up to the day that spans multiple years:

SELECT COUNT(*) as count, SUM(duration),  YEAR(`timestamp `) as year, MONTH(`timestamp `) as month, DAY(`timestamp `) as day
FROM table_a
WHERE
`start` BETWEEN '2013-11-20 00:00:00' AND '2015-12-31 23:59:59'
GROUP BY YEAR(`timestamp `) , MONTH(`start`), DAY(`timestamp `)
ORDER BY year, month, day

Initially it doesn't seem intuitive but once you think about it makes sense.

For all the purists out there, this is an illegal GROUP BY. However, MySQL extends the GROUP BY. Lifted from the manual (https://dev.mysql.com/doc/refman/5.6/en/group-by-handling.html):

MySQL extends the use of GROUP BY so that the select list can refer to nonaggregated columns not named in the GROUP BY clause. You can use this feature to get better performance by avoiding unnecessary column sorting and grouping. However, this is useful primarily when all values in each nonaggregated column not named in the GROUP BY are the same for each group. The server is free to choose any value from each group, so unless they are the same, the values chosen are indeterminate. 

Counting things that do not exist with a correlated subquery

Today I had a problem where I needed to find the difference between total and in-active row counts based on a FK value from a parent table. I had a developer explaining that she wanted to do a count for values that don't exist. She gave me a query like this:

SELECT leader_id, count(*) as count

FROM lists

WHERE active = 1

GROUP BY leader_id

HAVING count = 0

I told her that you cannot count something that doesn't exist. It took me a little bit to understand what was needed but I was able to do it with a correlated subquery. Take this example table and example data:

CREATE TABLE `lists` (

  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,

  `leader_id` int(10) unsigned NOT NULL,

  `active` tinyint(4) NOT NULL DEFAULT '1',

  `name` varchar(100) DEFAULT NULL,

  PRIMARY KEY (`id`),

  KEY `idx_fk_leader_id` (`leader_id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `lists` (`id`, `leader_id`, `active`, `name`) VALUES (NULL, '1', '1', 'one');

INSERT INTO `lists` (`id`, `leader_id`, `active`, `name`) VALUES (NULL, '2', '1', 'two');

INSERT INTO `lists` (`id`, `leader_id`, `active`, `name`) VALUES (NULL, '1', '0', 'One old');

INSERT INTO `lists` (`id`, `leader_id`, `active`, `name`) VALUES (NULL, '1', '1', 'One also old');

INSERT INTO `lists` (`id`, `leader_id`, `active`, `name`) VALUES (NULL, '3', '1', 'three');

INSERT INTO `lists` (`id`, `leader_id`, `active`, `name`) VALUES (NULL, '3', '0', 'three old');

INSERT INTO `lists` (`id`, `leader_id`, `active`, `name`) VALUES (NULL, '4', '0', 'four old');

I wrote this query to get the difference:

SELECT l1.leader_id,

count(*) as total_count,

l2.inactive_count,

count(*) - l2.inactive_count as difference

FROM lists l1

INNER JOIN

(SELECT leader_id, count(*) as inactive_count FROM lists l3 WHERE active = 0

GROUP BY leader_id) l2 ON l1.leader_id = l2.leader_id

GROUP BY l1.leader_id

Results look like this:

What we really wanted was just the value where total count was equal to the inactive count (difference = 0) so I added a HAVING clause also:

SELECT l1.leader_id,

count(*) as total_count,

l2.inactive_count,

count(*) - l2.inactive_count as difference

FROM lists l1

INNER JOIN

(SELECT leader_id, count(*) as inactive_count FROM lists l3 WHERE active = 0

GROUP BY leader_id) l2 ON l1.leader_id = l2.leader_id

GROUP BY l1.leader_id

HAVING difference = 0

Results look like this:

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.