Finding Blocking in Mysql

I’m primarily a SQL Server guy, but I’ve been lucky enough to have a handful of MYSQL databases to manage, and outside of our NDB cluster, they’ve been designed very simple and therefore stable, so my interaction is limited.  As the company has grown we’ve acquired more companies based in MYSQL and although my team doesn’t manage these directly, we’re often brought in to help during crises modes.  Of course it’s not optimal to dust off your skillset during a crises mode, but a b-tree is a b-tree, and a nested loop, scan, and other fundamentals of a RMDB don't change from one system to another… mostly.  What is different are the tools at getting at the data.  In my latest experience I came across a DB riddled with locking, which of course was leading to blocking.  The problem was trying to find what calls were blocking what, and causing the issue.  Lucky the innodb engine has some great real-time tables for accessing intervals.  Below is a query I wrote to get a view of the blocking going on real-time.  I’m sure I’m not the first person to do something similar, but I just couldn’t find it. 

select T.trx_state as Blocking_trx_state,
T.trx_started as Blocking_trx_started,
T.trx_requested_lock_id as Blocking_trx_requested_lock_id,
T.trx_wait_started as Blocking_trx_wait_started,
T.trx_weight as Blocking_trx_weight,
T.trx_mysql_thread_id as Blocking_trx_mysql_thread_id,
T.trx_query as Blocking_trx_query,
T.trx_operation_state as Blocking_trx_operation_state,
T.trx_tables_in_use as Blocking_trx_tables_in_use,
T.trx_tables_locked as Blocking_trx_tables_locked,
T.trx_lock_structs as Blocking_trx_lock_structs,
T.trx_lock_memory_bytes as Blocking_trx_lock_memory_bytes,
T.trx_rows_locked as Blocking_trx_rows_locked,
T.trx_rows_modified as Blocking_trx_rows_modified,
T.trx_concurrency_tickets as Blocking_trx_concurrency_tickets,
T.trx_isolation_level as Blocking_trx_isolation_level,
T.trx_unique_checks as Blocking_trx_unique_checks,
T.trx_foreign_key_checks as Blocking_trx_foreign_key_checks,
T.trx_last_foreign_key_error as Blocking_trx_last_foreign_key_error,
T.trx_adaptive_hash_latched as Blocking_trx_adaptive_hash_latched,
T.trx_adaptive_hash_timeout as Blocking_trx_adaptive_hash_timeout,
T.trx_id as Blocking_trx_id,
Ta.trx_state as Blocked_trx_state,
Ta.trx_started as Blocked_trx_started,
Ta.trx_requested_lock_id as Blocked_trx_requested_lock_id,
Ta.trx_wait_started as Blocked_trx_wait_started,
Ta.trx_weight as Blocked_trx_weight,
Ta.trx_mysql_thread_id as Blocked_trx_mysql_thread_id,
Ta.trx_query as Blocked_trx_query,
Ta.trx_operation_state as Blocked_trx_operation_state,
Ta.trx_tables_in_use as Blocked_trx_tables_in_use,
Ta.trx_tables_locked as Blocked_trx_tables_locked,
Ta.trx_lock_structs as Blocked_trx_lock_structs,
Ta.trx_lock_memory_bytes as Blocked_trx_lock_memory_bytes,
Ta.trx_rows_locked as Blocked_trx_rows_locked,
Ta.trx_rows_modified as Blocked_trx_rows_modified,
Ta.trx_concurrency_tickets as Blocked_trx_concurrency_tickets,
Ta.trx_isolation_level as Blocked_trx_isolation_level,
Ta.trx_unique_checks as Blocked_trx_unique_checks,
Ta.trx_foreign_key_checks as Blocked_trx_foreign_key_checks,
Ta.trx_last_foreign_key_error as Blocked_trx_last_foreign_key_error,
Ta.trx_adaptive_hash_latched as Blocked_trx_adaptive_hash_latched,
Ta.trx_adaptive_hash_timeout as Blocked_trx_adaptive_hash_timeout,
Ta.trx_id as Blocked_trx_id               
from  Information_Schema.INNODB_LOCK_WAITS W INNER JOIN   
Information_Schema.INNODB_TRX T ON T.trx_id = W.blocking_trx_id 
inner join Information_Schema.INNODB_TRX Ta on Ta.trx_id = W.requesting_trx_id \G

Mysql slow query log Not filtering correctly

If you setup mysql's slow query log you might notice it not filtering on the value setup up for the global variable long_query_time. For example maybe you have this setup for a value of 2 seconds, but are seeing durations of 0 or 1 seconds being recorded. The most likely reason for this is the variable "log_queries_not_using_indexes" is turned on by default and this uses the same log file or log table as the slow_query_log.

Mysql Issues with Innodb and Statistics Create Poor Plans

We’ve recently roll out openx which is a web ad serving software. The application consists of 2 primary mysql components. The first is the backend where AD campaigns are uploaded into the system. The second is the front end where ads are served up through PHP. Mysql replication is used to move data from the backend to the front end. The frontend consists of 2 identical slaves for redundancy and load balancing with both containing the same data. The primary tables serving the ads are innodb, and quite small (at least for now).

Since its launch we’ve been seeing particularly high spiky CPU times. Sometimes it’s on one server sometimes on both, and then there are times when both are running smoothly. The spikiness was easy to figure out, in that it dealt with the query cache. Every time new data would be replicated out, it would flush the query cache and force mysql to rebuild rerun it queries, however it didn’t explain why they should be high in the first place. After running a long running queries trace I was able to determine it was being caused by a 6 table join that was scanning like crazy. My initial thought was this was due to the optimizer getting the wrong plan so I forced mysql to do an exhaustive optimization by setting optimizer_prune_level to 0. Unfortunately this didn’t do the job, but it told me the optimizer was getting the correct plan for information it was given. Through looking at the statistics of the indexes it was clear that innodb was off. After running an optimize table command against the bad tables this fixed the problems.

Trying to get row counts out of innodb is like playing roulette, you never know what number is going to come up. This is because unlike myisam that keeps the row count as part of the data structure, innodb calculates these on the fly. These same row counts are used in creating statistics for the optimizer. A long term solution could be to schedule out a job to run the optimize command regularly, which in reality just rebuilds the table and is not the best solution. A permanent solution is to change these tables to myisam.

For some further reading Peter from mysqlperfomanceblog has a great article.
Post

Definer in Mysql Stored Procedures and Routines

The definer is a mysql security tactic used for executing mysql stored procedures. If you’re coming from an SQL Server world it’s similar to the schema that owns the SP. In other words when a definer is set it gives the user executing the SP the same perms as the definer in the context of the SP. This is much easier to explain by example.

Suppose you have a sp that runs a select on Table1. The definer must have select permissions to table1 while the user executing doesn’t need it. When the user is executing the sp they inherit the permissions as the account specified as the definer. This allows your application security to be setup so that application users only need to have execute perms on the SP’s and not perms to underlying objects.

Often when developing a SP this option is left blank and by default it is set to the user that created the sp. The problem with this is that when the SP gets pushed to production, the developer account doesn’t exist, and the SP won’t execute.

There are two workarounds here, the first is to create a standard user with the perms required to access the underlying objects in delivery. Also have this user in Test/Dev/Stress (Different Password then in production) and have this user as the definer. This is of course is the optimal solution, but requires quite a bit a work in developing your security infrastructure. The hack which works while you’re developing this infrastructure is to run the following statement in delivery after release.

update proc set definer='Defineruser@localhost';

This changes the definer to an appropriate user.

Determine the Number of Connections Per Server in Mysql

Coming from the Dynamic View world of sql server 2005 the show commands of mysql take me back to the old days. The primarily issue with the “Show Commands” are they don’t return a result set you can work with. As a DBA one of the most common requests is finding all the processes connected to the server and how many connections each host has. Using show processlist requires capturing this as text then writing some script to parse and aggregate. To avoid this I use the information_schema database. Although not as robust as many rdbs’s there are some good tables to get information from. In this case I like to query the processlist table.


mysql> select host, count(*) as cnt from processlist group by host;
+----------------+-----+
| host | cnt |
+----------------+-----+
| localhost:4819 | 1 |
| localhost:4833 | 1 |
+----------------+-----+
2 rows in set (0.00 sec)

However the challenge here is that concatenated with the host name is the thread id, so you still can’t get a proper count on how many connections each server has. To solve this I used the ultra cool substring_index function of mysql. Here you pass it the string to be searched, the string to be found, and the count of occurrences you want to find.

In this example I was able to group by this function.

mysql> select substring_index(host,':',1) as hostname, count(*) as cnt from processlist group by substring_index(host,':',1);
+-----------+-----+
| hostname | cnt |
+-----------+-----+
| localhost | 2 |
+-----------+-----+
1 row in set (0.02 sec)

Here’s a link to mysql’s documentation

http://dev.mysql.com/doc/refman/5.1/en/string-functions.html#function_substring-index