sub-select to find max values with a group

Several times recently I've needed to get the max or the min value within a group of values. For example, say I want the max id for a user each day or the max id for each user who has several entries in some kind of a history table.

 At first I struggled with how I was going to get that data but the solution was actually fairly simple with a sub-select and GROUP BY. Since I realized how easy this is to do, I've had to do a very similar thing at work quite often. It is nice to remember how useful GROUP BY can be. This is a similar query I wrote recently with a GROUP BY and a sub-select. 

This trick only works when the table has an auto-increment id. You also have to make an assumption for your data that the max auto increment id has the highest value you are looking for in the group. 

Might result in full index scans so it may not be the most optimal solution but it worked for what I needed it for:

    

    SET @day_number = 1;

    SELECT id

    FROM table_a

    WHERE date_created BETWEEN DATE_SUB(NOW(), INTERVAL @day_number DAY) AND NOW()

    AND value1 < (@day_number*86400)

    AND id IN (SELECT MAX(id) FROM table_a GROUP BY user_id);

Adding new MySQL users to an AWS MySQL instance and using an SSH tunnel

This may sound really stupid but I was creating users today on a MySQL instance that is hosted in AWS and couldn't figure out what hostname value to use for the new users in order to get them to work.

Whoever at my work setup the instance completely forgot the root password. They were managing this themselves and eventually it got thrown over the fence to my team.

This site has a great little tutorial on resetting the root password for MySQL when you have shell access to the box:
http://www.rackspace.com/knowledge_center/article/mysql-resetting-a-lost-mysql-root-password

Normally I work with MySQL instances in our own local data center. This MySQL instance was in AWS and couldn't be accessed without tunneling in via SSH. I wasn't sure what the host should for the MySQL users. At first I was limiting them to the IP range of my office. After several attempts to logon to the MySQL instances over the SSH tunnel it still wasn't working. At then it dawned on me that after connecting to the system via SSH you are localhost. So the mysql users have to be user_name@localhost. Worked fine after I realized that!

12 Steps to MySQL Query Tuning

I've been a fan of Solar Winds' 12 Steps to Query Performing Tuning Infographic which they published for Oracle and SQL Server. However, they didn't publish one for MySQL. Using the same 12 step model as in their infographic, I've written my own:

 12 Steps to MySQL Query Tuning

Step 1 - Examine the Execution Plan

Make sure you are working with tables, not views
Make Certain you know the row count
Review the Access Predicates (key_len, ref columns in EXPLAIN PLAN)
- Is all or part of composite index being used?
Are there full table scans? Are there full index scans?
Review the Extra column
- Do you see "Using temporary;" or "Using filesort;" (BAD)
- Do you see "Using Index" (GOOD)
Is the Possible keys column NULL?
Is the value in the Rows column high? (BAD)
Does the type column show RANGE?
- Is it having traverse large number of rows in the Index leaf nodes?
- You want to keep the scanned index range as small as possible
Examine the EXPLAIN EXTENDED
SHOW WARNINGS <- Run this right after EXPLAIN EXTENDED to review how the actual SQL will be executed

Step 2 - Review the filters, JOINS, WHERE predicates, ORDER BY, GROUP BY, HAVING BY, LIMIT

What is the filtered row count?
If there is a LIMIT clause? Is it occurring after or being the entire table is accessed?
Are there functions in the ORDER BY?
Review the data types to avoid implicit conversion
- truthy/falsey filtering in the Predicates (converting columns to Boolean)
- WHERE !TIMESTAMP (ignores index)
Don't wrap your indexes in expressions in the WHERE predicates
- WHERE DATE(TIMESTMAP) = '2010-10-10' (ignores index)
Should the query be using an INNER JOIN vs. LEFT JOIN?
Is the query unnecessarily filtering out duplicates? (UNION vs. UNION ALL)
Are there bind variables? Using prepared statements?

Step 3 - Know the Selectivity of the table data

Is the data skewed resulting in queries that cannot effectively use existing indexes?
Make Sure to work with the smallest possible logical set
Can additional WHERE predicates be added?
Know when the predicate is applied when dealing with multiple tables, you want the most SELECTIVE predicate earlier than later

Step 4 - Gather Table Information

Review Table definitions
Have the stats been updated? Get current stats.
Are there triggers on the table? If so, how often do they fire? What are they doing?
What is the storage engine? Does it allow for row level locking vs. table level locking?
Make sure that datatypes match for the columns that are being JOINED between two tables, including the CHARSET and COLLATION

Step 5 - Evaluate Existing Indexes

Look at Index Definitions
Are there redundant indexes?
Are there composite indexes?
Is there a primary key on the table?
Is the primary key an integer, char?
Is the Primary key unnecessarily large as in the case a large varchar primary key?
Are there far too many secondary indexes?
Know the Selectivity of the indexes
Is the cardinality for the indexes high/low?
Could a fulltext index be useful?
Is each column that could be JOINED indexed?
Check to see if covering indexes can be created (avoid duplication and overlapping of indexes)

Step 6 - Analyze Columns in the WHERE clause and SELECT

Look for SELECT * or scalar function (the more data brought back the less optimal it may be to use certain functions)
Look for CASE, CAST, CONVERT
Are there sub queries? Can the sub query be converted to a JOIN?

Step 7 - Run the Query and record baseline metrics

Gather average execution times
If you are using Master-Master, Master-Slave or some other type of cluster where each instance has the same data, test running the query on each replica to see if the execution time and/or execution plan changes

Step 8 - Adjust/Tune the query

Focus on the most expensive operations first (full table scans, index scans, file sort, temporary table)
Look for high cost steps to eliminate
Consider a covering index, which includes every column that satisfies the query
In the case of ORDER BY and GROUP BY, check to see if you can eliminate "Using temporary;" or "Using filesort;" by properly indexing the table
Try to eliminate the frequent counting of rows by utilizing summary tables
Rewrite sub queries as a join
Seek vs. Scans, which is more expensive?

NOTE: Out of date statistics can impact performance. Issue "ANAYLZE TABLE"
NOTE: Fragmented tables can impact performance - during a planned maintenance Issue "OPTIMIZE TABLE"

Step 9 - Re-run the query

Record the results and compare
Only make one change at a time

Step 10 - Consider adjusting indexes

Test using index hints to see if the optimizer is choosing poorly
Look to reduce logical I/O

Step 11 - Incremental changes

Continue to re-run query and record results after each incremental change
Have a peer review the query it with you to see if you are missing something

Step 12 - Engineer out the Stupid

Abuse of wildcards?
Use of views?
Scalar Functions in the query?
Can the query be cached? Does it make sense to be using MySQL query cache or a dedicated caching layer? Consider turning off MySQL query cache as the effort to check the cache can severely slow down your queries.
Is the query using a cursor in a stored procedure?
Does the server have enough RAM to keep indexes in memory?
Are the data files stored on fast drives?
Are all the tables in your query using the same storage engine?
Do all your tables have Primary Keys, proper secondary indexes?
Is your table normalized/de-normalized as appropriate?
Is your MySQL server so heavily overloaded that queries are performing poorly?
Has the query been tested on large and small data sets?
Is the query being used on a sharded database where different databases have the same tables but possibly very different data sets?
Is the query doing silly things like over use of the OR/UNION command when an IN clause may be more appropriate?
Are you forcing the query to do full table scan by putting wildcard characters (%) on the left side of an index?
Is the table definition using the right data types? Abuse of CHAR(255), VARCHAR(255), BIGINT?
Can a complex query with dependent sub queries be split into multiple queries and/or use transactions?

UPDATE April 2016:
Solar Winds finally came out with an infographic for MySQL:
http://www.solarwinds.com/assets/infographics/dpa-mysql-12-steps.aspx

MySQL Performance Tuning - 12 Step Guide from SolarWinds

time zone errors when replaying logs

I kept getting this annoying error on my test instances of MySQL when running pt-query-upgrade using logs I pulled from production:

DBD::mysql::st execute failed: Unknown or incorrect time zone: 'America/Los_Angeles' [for Statement "SET time_zone = 'America/Los_Angeles'"]

SET time_zone = 'America/Los_Angeles'

The solution was to install time zones onto the testing instance like this:

mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root mysql

Some of the timezones didn't install but it did install for America/Lost Angelese which made my error go away. 

pt-upgrade

I love the idea of pt-upgrade in the Percona Toolkit but have struggled to effectively use the tool.

I've taken a backup of all the databases of a database server and restored that backup onto two different hosts, one running MySQL 5.1 and one running MySQL 5.6. I want to find failing queries that work on MySQL 5.1 but don't work on MySQL 5.6.

For initial testing, I just want to find SELECT statements that return different results or fail.

I've read over the documentation and this is how I'm executing the tool:

pt-upgrade h=SERVER1 -uUSERNAME -pPASSWORD  h=SERVER2 uUSERNAME -pPASSWORD --type='genlog' --max-class-size=2 --max-examples=2  --run-time=10m 'general_log_mysqld.log' 1>report.txt 2>err.txt &

I've chosen to use the general log in this example instead of the slow query log but I've tested with both. The slow query log is more bloated with useless info while the general log has more bang for your buck in terms of size. The big problem I have with this tool is that I have hundreds databases on my server. Inside the general log are lots of "use <database>" statements before each query. However, when I run the tool it complains about not being able to run the query because the tables are missing:


On both hosts:

DBD::mysql::st execute failed: No database selected [for Statement "....

I can't just select one database when I run the command, the tool needs to read the USE <database> from the log file.

I tried again and found a database server with only a few databases. Sometimes it was working and sometimes it was failing to use the correct databases. As a work around I created the same tables inside each database. This only worked if all the tables had unique names.  This way it would not matter which database the tool was running against. At this point, at least I got a report with useful results.

It isn't always feasible to do what I did by putting the same tables in every database...what am I doing wrong with this tool?

Issues I've had with Percona Upgrade:

1. Queries pulled from production can be too intensive for a test machine running with limited resources. Queries I was testing with  were taken from systems with 30+ cores and 64 GB of RAM, queries would time out after a few minutes and the tool would stop working, queries taken from prod should be run on a prod like database servers, also queries like this "SELECT SQL_NO_CACHE * FROM table_name" which came from a backup job seemed  to break the tool
2. Some queries broke the tool, this included not only the backup queries, but weird custom SQL that were written by developers internally
3. The same query would happen so frequently in the logs (100s of thousands of times) that the reports became useless
4. There were a lot of failed queries when running the SELECT statements because of missing tables, these were not true temporary tables but tables that are created for a short time and then dropped, these tables were not part of my dump and restore because I didn't know the code was creating these "transitory" tables
5. The tool would not database context switch for me, I don't know why but when the logs would issue  a "use database_name" it would ignore it and try to run a query that was meant for a different database. The tool would report a huge number of failed queries because of this. My work around was to create the same tables in every schema so I would not get those failed queries anymore

After engaging Percona consulting they told me to only use the SLOW LOG files. For my system they said I would need to massage the files with pt-query-digest before running pt-upgrade like this:

Step 1: Massage a slow query log for SELECT statements using pt-query-digest
pt-query-digest --filter '$event->{arg} =~ m/^select/i' --sample 5 --no-report --output slowlog my_slow.log > my_slow_massaged_for_select.log

Step 2: Next run pt-upgrade
pt-upgrade h=my_source_host.com -uUSER -pPASSWORD h=my_target_host.com -uUSER -pPASSWORD --type=slowlog --max-class-size=1 --max-examples=1 --run-time=1m 'slow_massaged_for_select.log' 1> report_1.txt 2> error_1.txt &

The above massaging worked well for SELECT statements but when testing DDL/DML I ran into more problems. I would still see a lot errors for tables that don't exist because they are tmp tables and come and go during a session.

Step 1: Massage a slow query log for SELECT/DDL/DML statements using pt-query-digest

pt-query-digest --filter '$event->{arg} =~ m/^[select|alter|create|drop|insert|replace|update|delete]/i' --sample 5 --no-report --output slowlog alpha_slow.log > my_slow_massaged_for_dml_ddl.log

Step 2: Clean up the log file to remove LOCKS

Because my slow log file had a number of LOCK statements, I used SED to remove all rows that had any references to LOCKS.

Step 3: Next run pt-upgrade

pt-upgrade h=my_source_host.com -uUSER -pPASSWORD h=my_target_host.com -uUSER -pPASSWORD --type=slowlog --max-class-size=1 --max-examples=1 --run-time=1m --no-read-only 'my_slow_massaged_for_dml_ddl.log' 1> report_2.txt 2> error_2.txt &

Even running all the DDL, I would still see a lot errors for tables that don't exist because they are tmp tables and come and go during a session. At this point, I was able to get more confidence that the upgrade was going to work. 

Why is the triggers table in the information_schema so slow?

We have a sharded MySQL infrastructure at work where we sometimes create new shards from a .sql file. Each shard has all the same tables/triggers/functions, etc but the data is unique to the customer for which that shard is assigned to. This file is created from an alpha environment which has different users than production. This started to result in a situation where we are getting definers for triggers/functions on production but for users that do not exist. I wrote a script to send alerts for these but manually fixing them was getting annoying. The permanent fix so that doesn't happen anymore is in the works but the bureaucracy at work is taking too long so I wrote a bash script to fix them automatically on production. A portion of my bash script was inspired from this site:

http://codersresource.com/news/dzone-snippets/change-ownership-of-definer-and-triggers

Here is the script from the above site:

#!/bin/sh 
host='localhost' 
user='root' 
port='3306' 
# following should be the root@localhost password 
password='root@123' 

# triggers backup 
mysqldump -h$host -u$user -p$password -P$port --all-databases -d --no-create-info > triggers.sql 
if [[ $? -ne 0 ]]; then exit 81; fi 

# stored procedure backup 
mysqldump -h$host -u$user -p$password -P$port --all-databases --no-create-info --no-data -R --skip-triggers > procedures.sql 
if [[ $? -ne 0 ]]; then exit 91; fi 

# triggers backup 
mysqldump -h$host -u$user -p$password -P$port --all-databases -d --no-create-info | sed -e 's/DEFINER[ ]*=[ ]*[^*]*\*/\*/' > triggers_backup.sql 
if [[ $? -ne 0 ]]; then exit 101; fi 

# drop current triggers 
mysql -h$host -u$user -p$password -P$port -Bse"select CONCAT('drop trigger ', TRIGGER_SCHEMA, '.', TRIGGER_NAME, ';') from information_schema.triggers" | mysql -h$host -u$user -p$password -P$port 
if [[ $? -ne 0 ]]; then exit 111; fi 

# Restore from file, use root@localhost credentials 
mysql -h$host -u$user -p$password -P$port < triggers_backup.sql 
if [[ $? -ne 0 ]]; then exit 121; fi 

# change all the definers of stored procedures to root@localhost 
mysqldump -h$host -u$user -p$password -P$port --all-databases --no-create-info --no-data -R --skip-triggers | sed -e 's/DEFINER=[^*]*\*/\*/' | mysql -h$host -u$user -p$password -P$port 
if [[ $? -ne 0 ]]; then exit 131; fi 

My script was different but the idea was the same. However, it blew up and this is a bad idea. I tested it several times on a non-prod and it was working pretty good. My testing environment only had about a dozen databases. The particular MySQL server that has over 1500 databases. For whatever reason that I haven't been able to pinpoint, running a query on the triggers table takes 20 minutes. That little portion in the above script that creates a DROP triggers query with the SELECT concat and then sends the results back into another mysql session is not a good idea! It will cause queries to get locked up and hold up replication. It created slave lag in our clustered environment which got really far behind. There are plenty of other problem with our MySQL infrastructure which are out of my control which also contributed to this situation.

Long story short is that I'll be re-writing my version of the script to use SHOW TRIGGERS/SHOW FUNCTION STATUS/SHOW PROCEDURE STATUS because those commands run much faster. However, I'll have to loop over every single database and limit my query to only that database.

/bin/rm: Argument list too long

I was doing some replication testing with master-master on Percona 5.6 today and I kept having problems with my test instances not starting or shutting down when I changed the my.cnf setting file to use the new relay log files. I had previously used theses boxes for master-slave testing and had left it for weeks un-attended and replication had broken and relay log files were building up like crazy.

I ran this:
STOP SLAVE;
RESET SLAVE;

cd into this directory:

/var/log/mysql/

Lots of relay files like this:

relay.137105

I thought that RESET SLAVE was suppose to delete and those. Maybe it is and it just taking a long time.

So I try to delete them:

rm /var/log/mysql/relay.*
-bash: /bin/rm: Argument list too long

rm /var/log/mysql/relay.1*

bash: /bin/rm: Argument list too long

I was able to run this:

rm /var/log/mysql/relay.12*
rm /var/log/mysql/relay.13*
rm /var/log/mysql/relay.14*
and on and on

I didn't want to spend all night doing this.

I tried this:

cd /var/log/mysql/

find . -name "relay.*" -print | xargs rm

Copied from here: http://itigloo.com/how-do-i/binrm-argument-list-too-long-error/

Still a no go. It just hangs. Seems to be too much for my underpowered VM to handle.

I tried it again with only one file:

find . -name "relay.097436" -print | xargs rm

It worked. 

How about a few more files:

find . -name "relay.3*" -print | xargs rm

That worked.

I tried this again:

find . -name "relay.*" -print | xargs rm

At last it worked!!