… >> ps.log sleep 60 done Check for Table Cache Related Allocations There are cases when MySQL will allocate a lot of memory… if you’re using large blobs. It is easy to check though. Run “FLUSH TABLES” and see whenever memory usage goes down… Percona Server you can do better as you can query temporary tables too: mysql> select sum(data_length+index_length) from information…
Post: InnoDB's gap locks
March 27, 2012 By 14 Comments
… to this gap lock, when you run the same query twice, you get the same result,…undo log entries 1 MySQL thread id 3, OS thread handle 0x7f84a78ba700, query id 163 localhost msandbox…gap locks except for foreign-key constraint checking or duplicate-key checking. The most important difference between…
Post: Best kept MySQLDump Secret
March 23, 2012 By 11 Comments
…DDLs are not transactional and as such the running transaction will not see the contents …before dump has completed) mysql> alter table C add i int not null; Query OK, 1 row …/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */; /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */; /*!40101 SET …
Post: Index Condition Pushdown in MySQL 5.6 and MariaDB 5.5 and its performance impact
March 12, 2012 By 12 Comments
… in preparation for the talk comparing the optimizer enhancements in MySQL 5.6 and MariaDB 5.5. We are taking a… optimizer enhancement Index Condition Pushdown (ICP). Its available in both MySQL 5.6 and MariaDB 5.5 Now let’s take…
Post: How FLUSH TABLES WITH READ LOCK works with Innodb Tables
March 23, 2012 By 20 Comments
…query blocks any READ queries to execute on the table. mysql> show processlist; …check for situation like above and either kill FLUSH TABLES WITH READ LOCK and fail backup or kill long running SELECT queries…
Comment: Should you move from MyISAM to Innodb ?
March 10, 2012 By 53 Comments
…in the MySQL community are snobbish about using MyISAM (not using it that…includes consistently faster query speeds, tables which are easily maintained, is more easily tuned and a system that will run even … certain usage patterns – if you go down this path check them out (many are not free though).
Post: Troubleshooting MySQL Upgrade Performance Regressions
February 15, 2012 By Leave a Comment
… new MySQL version. Once you have the queries you will most likely able to repeat the problem by running query on old and new MySQL… query which performs differently between MySQL Server versions you should: Check Query Plan Run EXPLAIN to see if plans for the query are the same. Changing Query… operation is different, you can also check SHOW PROFILE to see where differences are. Running query in the loop and getting o_profile…
Post: How to Monitor MySQL with Percona's Nagios Plugins
February 22, 2012 By 8 Comments
…and actual failure. The pmp-check-mysql-replication-delay plugin checks for excessive delay, and pmp-check-mysql-replication-running checks that replication is functioning correctly…. Nagios are suitable for checking that the server is alive and responds to a query. The pmp-check-mysql-status is also…
Post: Shard-Query EC2 images available
May 11, 2011 By 2 Comments
… from 1988 to 2010′; mysql> use ontime1; Database changed mysql> show table status like ‘ontime…10 04:26:14 Update_time: NULL Check_time: NULL Collation: latin1_swedish_ci … output from the ./run_query command, called pivot_results cd shard-query/ $ ./run_query < queries.sql | tee raw |./pivot_results…
Post: How Percona does a MySQL Performance Audit
November 24, 2008 By 17 Comments
… port, etc etc. Nothing unusual. Next I check what the MySQL version is, and I check (with the ‘file’ command) whether /usr/libexec… at the queries themselves. If the client has long-running queries, it can be pretty easy to catch. Maybe they’re long-running because… other things I’ll look at, including checking for default users in the mysql.* tables, running mk-duplicate-key-checker to find redundant…
