… >> ps.log sleep 60 done Check for Table Cache Related Allocations There are cases when MySQL will allocate a lot of memory… even go deeper to check which sessions have created which temporary tables (both in memory and not): mysql> select * from information_schema… Buffer pool size, bytes 128849002496 Free buffers 1 Database pages 8252672 Old database pages 3046376 Modified db pages 23419 I’m using…
Post: Best kept MySQLDump Secret
March 23, 2012 By 11 Comments
… Innodb tables without making database read only. In most cases… B | | C | +——————–+ 3 rows in set (0.00 sec) mysql> select count(*) from A; +———-+ | count(*) | +———-+ | 2359296…FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */; /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */; …
Post: InnoDB's gap locks
March 27, 2012 By 14 Comments
… replication. To accomplish that, row level locking databases also acquire gap locks. What is a… locks except for foreign-key constraint checking or duplicate-key checking. The most important difference between…1/en/innodb-locks-set.html. Conclusion MySQL uses REPEATABLE READ as the default isolation…
Post: How FLUSH TABLES WITH READ LOCK works with Innodb Tables
March 23, 2012 By 20 Comments
… used by mysql client, so you might get a feel you can’t connect to the database at all (use mysql -A in… query blocks any READ queries to execute on the table. mysql> show processlist; +——-+——+———–+———-+————+——+——————————+——————————+———–+—————+———–+ | Id | User | Host | db | Command | Time | State | Info…. As a workaround you can consider having scripts which would check for situation like above and either kill FLUSH TABLES WITH…
Comment: Too many connections? No problem!
March 15, 2012 By 28 Comments
… have my website develop in PHP with MYSQL, these are MYSQL values: datadir=/data/mysql socket=/data/mysql/mysql.sock max_connections = 600 wait_timeout…-bin=/data/mysql/drbd expire_logs_days = 5 max_binlog_size = 100M [client] socket=/data/mysql/mysql.sock When I start the database service… to the database increases reaching the maximum of connections and I have to re-restart the server. I’ve checked the security…
Post: How to Monitor MySQL with Percona's Nagios Plugins
February 22, 2012 By 8 Comments
… administration mistake to delete one of MySQL‘s log or data files from disk. The database server will continue to function, however… until a later date. For example, if a database isn’t owned by MySQL, you won’t have any problems until you… drop the database, it will fail. These things tend to happen at inconvenient times. The pmp-check-mysql-file-privs plugin checks whether MySQL owns…
Post: Checking for a live database connection considered harmful
May 5, 2010 By 38 Comments
… to look at a customer’s database and notice a lot of overhead from checking whether a database connection is active before sending… Overhead Checking whether the connection is active typically involves a call to the ‘ping’ or ‘statistics’ command at the MySQL protocol level… app framework is doing a network round-trip to the database to check the connection, then another network round-trip to run…
Post: Eventual Consistency in MySQL
November 18, 2011 By 8 Comments
… Control Queries We need to check for orphaned rows in every parent-child relationship in your database. That is, do an exclusion… run them at any time: $ cat generate_foreign_key_checks.sql | mysql -N | mysql -E *************************** 1. row *************************** test.Bar.ID: 42 DIY RI…
Post: Ultimate MySQL variable and status reference list
January 8, 2011 By 4 Comments
I am constantly referring to the amazing MySQL manual, especially the option and variable reference table. But just …
Post: Upgrading MySQL
January 5, 2010 By 23 Comments
…. MySQL always was very good maintaining on disk binary compatibility between version and so you can run MySQL 5.0 database with MySQL 5… different MySQL version, unless you have very detailed automated QA. So what you can do is get the backup of the database… will run SELECT queries on both new and old MySQL installations and check result set, explain plan and execution time, reporting you…
