…to complete. Here is example how it can look: mysql> show processlist; …using Innodb tables and you’re not actively changing users, stored procedures etc (which are stored in MyISAM tables anyway) you can consider using –no-lock…
Post: Using Multiple Key Caches for MyISAM Scalability
November 26, 2008 By 8 Comments
…lock was held during IO duration, In MySQL 4.1 the lock is held only when key block (1KB to…locks instead of one. When you decide to use Multiple Key Caches the question is how many to use, what sizes to allocate and how to map tables to…
Post: How Percona does a MySQL Performance Audit
November 24, 2008 By 17 Comments
…to catch. Maybe they’re long-running because they’re in Locked status, which might be an indication that it’s time to convert MyISAM tables to…to use a patched binary, we can use MySQL Proxy, packet sniffing, or other techniques to…
Post: INSERT INTO ... SELECT Performance with Innodb tables.
July 12, 2006 By 21 Comments
…using Innodb tables probably got use to the fact Innodb tables perform non locking reads, meaning unless you use some modifiers such as LOCK IN…tables which is being read to be Innodb – even if writes are done in MyISAM table. So why was this done, being pretty bad for MySQL…
Post: How fast is FLUSH TABLES WITH READ LOCK?
April 24, 2010 By 28 Comments
…to chat with them about their MySQL backup product. One of the representatives told me that their backup product uses FLUSH TABLES WITH READ LOCK…TABLES WITH READ LOCK will complete quickly. In some cases, it is unavoidable. This includes backing up a mixture of MyISAM…
Post: Implementing efficient counters with MySQL
July 1, 2007 By 16 Comments
…in single transaction saving on log commits significantly. I mentioned you can use MyISAM or HEAP tables for this purpose, how do you deal with Table Locks in…
Post: Should you move from MyISAM to Innodb ?
January 12, 2009 By 53 Comments
…in the end for most users – you do not get to deal with recovering tables on the crash or partially executed statements. Table locks is no…MyISAM kills Innodb, such as copying binary tables between the servers. It is important the team understands Innodb and knows how to…in the MySQL server – in…
Post: Using LVM for MySQL Backup and Replication Setup
August 21, 2006 By 53 Comments
…MyISAM…to do atomic snapshot of many volumes. Lets speak a bit about how LVM and snapshotting in…to do to perform backup of MySQL Database (or create slave) using LVM2 on Linux. 1) Connect to MySQL and run FLUSH TABLES WITH READ LOCK…
Post: Hacking to make ALTER TABLE online for certain changes
October 29, 2007 By 26 Comments
…to remove auto_increment from 100G table. No matter if it’s InnoDB or MyISAM, you’d usually ALTER TABLE `huge_table…tables with read lock, (3) swap .frm files while keeping mysql suspended and (4) unlock the tables afterwards: — 1. mysql> CREATE TABLE `huge_table…
Post: Innodb Performance Optimization Basics
November 1, 2007 By 70 Comments
…in MySQL 4.1 and now stable enough to use. Also check if your application can run in…in locking in 5.0 and even more to come with MySQL…no OS changes and have no changes done to application which was written keeping MyISAM tables in…
