… would like to see MySQL memory consumption plotted. I use “VSZ” columns from “ps” output on Linux. It helps me to understand how this…’t get fancy graph quickly you can use this simple script: while true do date >> ps.log ps aux | grep mysqld >> ps.log… it is related to connections and identified application which causes excessive memory usage you might look at how it uses MySQL to identify potential…
Post: How to diagnose errors in the MySQL error log
March 26, 2012 By 4 Comments
… see questions about how to understand the nature of errors in MySQL‘s error log. Now, there is a lot of complexity to this — the… space left on device That’s the key to understanding what really happened. MySQL created a temporary table, using the MyISAM storage… to diagnose with that first. It’s often the easiest route to a solution. PS: You may wish to get a copy of MySQL Troubleshooting…
Post: InnoDB's gap locks
March 27, 2012 By 14 Comments
…insertion of data to other sessions. How to troubleshoot gap locks? Is possible to detect …, 3 row lock(s), undo log entries 1 MySQL thread id 3, OS thread…to avoid phantom reads and to get a consistent Statement based replication. If your application can deal with phantom reads and your binary log…
Post: Modeling MySQL Capacity by Measuring Resource Consumptions
March 8, 2011 By 1 Comment
… About Performance by Cary Millsap. So how we can use this data to estimate capacity of MySQL system ? We can look at CPU…_os_log_fsyncs for your disk IO estimation. When you can divide it per number of “Questions” or “Com_select” to get amount… 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…
Post: How to Monitor MySQL with Percona's Nagios Plugins
February 22, 2012 By 8 Comments
… up for alerts. Recently I spoke to one person who gets about thirty thousand alerts per week … to delete one of MySQL‘s log or data files from disk. The database server will continue to function,…server has been restarted recently. It is surprising how often a server crashes, perhaps even cycling …
Post: How to recover a single InnoDB table from a Full Backup
January 25, 2012 By 7 Comments
… be focus on how to recover a single tablespace using stock MySQL server. First, you must meet certain prerequisites to be able to restore a…. Then, our first step is to get a consistent backup. First we need to copy all the data to an output directory: The –export… –export /tmp/ Then apply the logs to get a consistent backup: # innobackupex –defaults-file=/etc/my.cnf –apply-log –export /tmp/2012-01-22…
Post: How Percona diagnoses MySQL server stalls
March 3, 2011 By 6 Comments
…. Finally, make sure that the script can log into MySQL (if necessary, put a password into .my…. If I get a few spare hours, I will put together some screencasts illustrating how to use these…our MySQL video/screencast blog site. Another interesting topic is how to examine a system that appears to be …
Post: How to recover deleted rows from an InnoDB Tablespace
February 20, 2012 By 3 Comments
… doesn’t work. How can I recover deleted rows? We’re going to follow the same … You can also get the table row format from the Information Schema: mysql (information_schema) > SELECT …with tables and indexes (with their IDs) to the error log. mysql (employees) > CREATE TABLE innodb_table_monitor (id…
Post: Can MySQL temporary tables be made safe for statement-based replication?
May 26, 2008 By 8 Comments
A while ago I wrote about how to make MySQL replication reliable, part of which is to eliminate temporary tables. The idea is this… appears to me that there’s no way to get the CREATE TEMPORARY TABLE statement to be logged inside the transaction. And when it comes to a replication slave, what’s logged — not…
Post: How to load large files safely into InnoDB with LOAD DATA INFILE
July 3, 2008 By 28 Comments
…way; I chose 1M because the rows were small). But how to do this without splitting the file? The answer lies … more lines. If you ‘cat’ the fifo file, you get a million lines at a time from it. The …[ -e /tmp/my-fifo ]; do time mysql -e “set foreign_key_checks=0; set sql_log_bin=0; set unique_checks=…
