…, and full query execution time to less than 50ms. …how far from optimal plans do we get by using a greedy search. From the same discussion we can learn how…in join I think faster execution plan computation would be more important than backward compatibility. In MySQL 5.6 things are likely to get…
Post: Troubleshooting MySQL Memory Usage
March 21, 2012 By 3 Comments
…in most cases you do not know where exactly memory is allocated. This was not much of the problem in MySQL…execution so it is usually easy to…TIME: NULL UPDATE_TIME: NULL 3 rows in set (0.00 sec) Innodb Memory Usage Finally it is often helpful to check how…
Post: How FLUSH TABLES WITH READ LOCK works with Innodb Tables
March 23, 2012 By 20 Comments
…to execute on the table. mysql> show processlist; +——-+——+———–+———-+————+——+——————————+——————————+———–+—————+———–+ | Id | User | Host | db | Command | Time…
Post: How to Identify Bad Queries in MySQL
January 10, 2011 By 16 Comments
…execution time. You can use Amdahl’s Law to…to the timestamp when the query executed, not merely the duration the query executed for, which has been available in MySQL…
Post: Modeling MySQL Capacity by Measuring Resource Consumptions
March 8, 2011 By 1 Comment
…which requires 1 CPU second and 1 IO to execute and we have 16 core system with …How to get CPU consumption per query ? You can take a look at procfs for MySQL process: root@ubuntu:/var/log/mysql… We can also get “BUSY TIME” here, subtracting CPU time from it we get “Wait Time” which is in this case …
Post: How to Monitor MySQL with Percona's Nagios Plugins
February 22, 2012 By 8 Comments
…to monitor in MySQL. Those are only suggestions; any given deployment might want to monitor only some of them, as appropriate, and might need to…times. The pmp-check-mysql-file-privs plugin checks whether MySQL owns every file and directory within its data directory. InnoDB is getting…
Post: How to debug long-running transactions in MySQL
March 8, 2011 By 6 Comments
…to convert this into a query log, and examine the log. In MySQL…Make sure the disk isn’t getting too full. avail=$(df -m -P /…times. Alas, there are some cases where it won’t work. If the offending transaction is idle, and not continuing to execute… am still stumped as to how a SELECT-only transaction …
Post: A micro-benchmark of stored routines in MySQL
August 16, 2009 By 7 Comments
…how fast stored routines are in MySQL? I just ran a quick micro-benchmark to…to force a bad execution plan. You should never use IN() subqueries the way I do, at least not in MySQL…row in set (1.00 sec) If we explain it, we get output similar to… times slower, consistently, and that’s all I wanted to…
Post: Managing Slave Lag with MySQL Replication
October 12, 2007 By 11 Comments
The question I often get is how far MySQL may fall behind and how to keep replication from lagging. The lag you will see will… this time. As MySQL Replication goes in single thread it is enough for one statement to get stuck to have all replication stalled. Long Queries – MySQL Replication executes…
Post: Troubleshooting MySQL Upgrade Performance Regressions
February 15, 2012 By Leave a Comment
…to get more accurate results. In recent Percona Server versions and MySQL 5.6 you can also store innodb stats in…to change your application by changing how query is written and provide the hints. Check Query Execution If query execution…
