…a big part of optimization. A scientific optimization process can be simplified to “can anything be improved…from running. This could be due to internal locks, I/O starvation, or what have you. …query executed for, which has been available in MySQL for years. This is necessary for determining whether…
Post: Troubleshooting MySQL Memory Usage
March 21, 2012 By 5 Comments
…forgot to remove (or still use for processing). Of course you will see these …(509995888 + 2623331) File system 294352 (82672 + 211680) Lock system 318875832 (318747272 + 128560) Recovery system 0 (0… is where your MySQL Support contract can be handy. Conclusion Understanding where MySQL can allocate …
Post: How fast is FLUSH TABLES WITH READ LOCK?
April 24, 2010 By 30 Comments
…processing involved in the command. Requesting the lock The FLUSH TABLES WITH READ LOCK command immediately requests the global read lock…process is ongoing: mysql> show processlist; …
Post: MySQL Backup tools used by Percona Remote DBA for MySQL
March 13, 2013 By 8 Comments
… between myisam and innodb tables. Global read lock only held until myisam tables are dumped. …we have a guardian process that runs during a backup. It then kills any process that causes …, long term backups Decompressing Percona XtraBackup for MySQL in parallel using all your resources available …
Post: How much memory Innodb locks really take ?
July 13, 2006 By 11 Comments
… of lock structs in row lock hash table 6092 LIST OF TRANSACTIONS FOR EACH SESSION: —TRANSACTION 0 42306989, ACTIVE 195 sec, process no 10099, OS thread id 1878960 6093 lock struct(s), heap size 503104 MySQL thread id 7429, query…, ACTIVE 133 sec, process no 10099, OS thread id 1878960 441 lock struct(s), heap size 44352 So we locked over 100K rows…
Post: Choosing innodb_buffer_pool_size
November 3, 2007 By 39 Comments
… swapping out MySQL Process or other important processes to make room for file cache. OS may find it unfair there is MySQL process which consumes… have other settings to play with. Finally you can try locking MySQL in memory by using –memlock – just be careful as in… lies in the swap – there are portions of both MySQL Process and other processes which you do not need during normal operation so…
Post: DROP TABLE and stalls: Lazy Drop Table in Percona Server and the new fixes in MySQL
June 22, 2012 By 33 Comments
… entry for this page): Release the shared lock on the latch protecting the AHI Lock the page in exclusive mode Remove all… lock on the latch protecting the AHI again Release the shared lock on the latch protecting the AHI Drop Table in Oracle MySQL… LRU process when these pages age towards the tail of the LRU. The other good thing is that in Oracle MySQL‘s…
Post: How Percona does a MySQL Performance Audit
November 24, 2008 By 17 Comments
…=/var/db/mysql –user=mysql –pid-file=/var/run/mysqld/mysqld.pid –skip-external-locking –port=3306 –socket=/var/db/mysql/mysql.sock consult 28852… the greatest latency for the application. Again, it’s a process of finding outliers by some criteria. Both types of queries… start working on while I keep investigating.” We make our process as interactive and flexible as the situation calls for. Early…
Post: Percona XtraDB Cluster reference architecture with HaProxy
June 20, 2012 By 63 Comments
…-112-39-98 log_slave_updates innodb_locks_unsafe_for_binlog=1 innodb_autoinc_lock_mode=2 innodb_buffer_pool_size=400M… clustercheck user. mysql> grant process on *.* to ‘clustercheckuser’@'localhost’ identified by ‘clustercheckpassword!’; Query OK, 0 rows affected (0.00 sec) mysql> flush privileges…
Post: Resyncing table on MySQL Slave
June 29, 2008 By 11 Comments
… instead of locking – for many applications this works better anyway as having table locked causes connections to pile up and either MySQL runs out of connections or Apache out of children allowed to process request; So you can do following instead: mysql> rename…
