…how much memory they use). Memory Tables MEMORY tables can take memory. There are implicit MEMORY tables which are allocated for query…to check which sessions have created which temporary tables (both in memory and not): mysql…
Post: InnoDB's gap locks
March 27, 2012 By 14 Comments
…the insertion of data to other sessions. How to troubleshoot gap locks? Is possible to detect those …(s), undo log entries 1 MySQL thread id 3, OS thread handle 0x7f84a78ba700, query id 163 … locks except for foreign-key constraint checking or duplicate-key checking. The most important difference …
Post: How to Monitor MySQL with Percona's Nagios Plugins
February 22, 2012 By 8 Comments
…to deadlocks and timeouts, applications often don’t handle or log this error appropriately. The pmp-check-mysql-status plugin can be configured to…checking that the server is alive and responds to a query. The pmp-check-mysql-status is also very flexible and can be used to…
Post: How to convert MySQL's SHOW PROFILES into a real profile
February 20, 2012 By 10 Comments
…how much time MySQL spends in various phases of query…query consumed 0.18 seconds. Where did the time go? mysql> SHOW PROFILE FOR QUERY 1; +———————-+———-+ | Status | Duration | +———————-+———-+ | starting | 0.000032 | | checking…logging slow query…
Post: Slow Query Log analyzes tools
September 6, 2006 By 83 Comments
…Query Log – Especially after the changes to log all queries slow query log may be growing too rapidly to follow, so we implemented slow query log filter (based on parse_mysql…
Post: Flexviews - part 3 - improving query performance using materialized views
April 4, 2011 By 12 Comments
… to using mysql binary logs…to convert it. You can easily check that it parses by running the SELECT portion, adding LIMIT 0 to…to actually build the contents of the view, making it available for querying: mysql…how you can combine both types of views together. The complete method examples show how to…
Post: How to use tcpdump on very busy hosts
April 18, 2011 By 17 Comments
… mk-query-digest output was obviously bogus when compared to the host’s slow-query-log (for example, none of the top 5 slow queries reported by mkqd appeared in the actual slow log… to capture tcpdump data in seconds (1200 = 20 min) TMP=’/tmp’ LOCKFILE=”$self.lockfile” TMPfile=”$self.temp” RESfile=”$self.result” # check lock…
Post: Modeling MySQL Capacity by Measuring Resource Consumptions
March 8, 2011 By 1 Comment
… “Questions” or “Com_select” to get amount of IO per query or per Select. It is good to check it over certain intervals – some… a lot. How to get CPU consumption per query ? You can take a look at procfs for MySQL process: root@ubuntu:/var/log/mysql# cat /proc… Server, enabled full query logging and log_slow_verbosity=full you can also get great amount of related data from mk-query-digest report…
Post: How to load large files safely into InnoDB with LOAD DATA INFILE
July 3, 2008 By 28 Comments
… gets bloated and slow. Even simple SELECT queries might have to scan through lots of obsolete, but not…I chose 1M because the rows were small). But how to do this without splitting the file? The answer… ]; do time mysql -e “set foreign_key_checks=0; set sql_log_bin=0; set unique_checks=0; load data…
Post: Reasons for MySQL Replication Lag
July 29, 2011 By 6 Comments
…to analyze your replication traffic regularly with mk-query-digest (Percona Server marks Slave Thread in slow query log so it is easy to…query-digest is a great way to deal with it. Note you need not only to check the types and frequencies of queries but also how…
