…MyISAM Does Not Scale, or it does quite well – two main things stopping you is table locks and global mutex on the KeyCache. Table Locks…
Post: Working with large data sets in MySQL
July 5, 2007 By 12 Comments
…with large data things in MySQL is patience and careful planning….reporting queries because for MyISAM they will lock tables for very long time…you do not care about Table Locks or transactions). Besides these various…waiting for maintainance window or bringing the site down. If you have tiny 1GB table…
Post: MySQL MyISAM Active Active Clustering - looking for trouble ?
March 15, 2007 By 4 Comments
… saying you can use MySQL with Active Active Clustering and MyISAM tables if you follow certain rules like enabling external locking, disabling query cache… MySQL servers this way. High availability ? This is where the biggest gotcha is waiting for you. If other MySQL node crashes it leaves table…
Post: How Percona does a MySQL Performance Audit
November 24, 2008 By 17 Comments
…re in Locked status, which might be an indication that it’s time to convert MyISAM tables to InnoDB. …at, including checking for default users in the mysql.* tables, running mk-duplicate-key-checker to find redundant … or just confirm that it was received and wait for a reply. In most cases the client…
Comment: How much memory can MySQL use in the worst case?
February 19, 2009 By 17 Comments
… for sale and/or rent. We are currently using MySQL and MyISAM tables and we keep running out of memory so the server…(this increases steady as the server runs) Created_tmp_disk_tables 9,208 Select_full_join 1,074(the application is… indexes to join the tables so this puzzles me) Sort_merge_passes 2,602 Opened_tables 1,500 Table_locks_waited 74 hope this is…
Post: Test Drive of Solid
September 22, 2006 By 5 Comments
…Table before test: select * from test2; +—-+——–+ | id | names | +—-+——–+ | 1 | Mysql | | 2 | Solid | | 3 | MyISAM | +—-+——–+ 3 rows in set (0.00 sec) Session1: mysql… lock wait not a deadlock. Test 2: SELECT FOR UPDATE: mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql…
Post: The perils of InnoDB with Debian and startup scripts
January 28, 2009 By 13 Comments
… on a server with MyISAM tables, if there are enough tables, but it is far…waits 0, rounds 5023577, OS waits 24953 RW-shared spins 34364070, OS waits 33800501; RW-excl spins 5756394, OS waits 5297208 Everyone is waiting… declared inside InnoDB 500 mysql tables in use 7, locked 0 MySQL thread id 6424, …
Post: Hacking to make ALTER TABLE online for certain changes
October 29, 2007 By 26 Comments
… InnoDB or MyISAM, you’d usually ALTER TABLE `huge_table` CHANGE `id` `id` int(6) NOT NULL and then wait hours for table rebuild to…, (2) flush tables with read lock, (3) swap .frm files while keeping mysql suspended and (4) unlock the tables afterwards: — 1. mysql> CREATE TABLE `huge_table_new…
Post: Pitfalls of converting to InnoDB
February 25, 2007 By 16 Comments
…convert their current database from MyISAM tables to InnoDB. The transfer …(ER_LOCK_WAIT_TIMEOUT) Lock wait timeout expired. Transaction was rolled back. 1213 (ER_LOCK_…table $t” mysql -u$DBUSER -p$DBPWD -e “alter table $t type=InnoDB” $DBNAME; done There is the standard script mysql_convert_table…
Comment: Database problems in MySQL/PHP Applications
November 6, 2006 By 13 Comments
… = 9216 tmp_table_size=1000M sort_buffer_size = 100M read_buffer_size = 100M read_rnd_buffer_size = 100M myisam_sort_buffer_size…/hostname tmpdir =/mysql_tmp/:/tmp/ # Uncomment the following if you are using BDB tables #bdb_cache_size = 384M #bdb_max_lock = 100000 # Uncomment…_log_at_trx_commit = 1 #innodb_lock_wait_timeout = 50 [mysqldump] quick max_allowed_packet = 16M [mysql] no-auto-rehash # Remove the next…
