June 18, 2013

Post: Using MyISAM in production

June 17, 2006 By 36 Comments

… items you need to keep into account while using MyISAM tables. Recovery. MySQL was running stable for us, giving us false sense…. MyISAM uses table locks and has concurrent inserts which can go concurrently with selects. This is sometimes presented as great concurrency for inserts… select statements but it has to be one insert at the time. Happily inserts in MyISAM are rather fast so it rarely is…

Filed Under: Insight for DBAs Tagged With: ,

Post: InnoDB Full-text Search in MySQL 5.6 (part 1)

February 26, 2013 By 1 Comment

…:17 dir_test_myisam.frm -rw-rw—-. 1 mysql mysql 155011048 Feb 19 17:17 dir_test_myisam.MYD -rw-rw—-. 1 mysql mysql 153956352 Feb…:11 seo_test_myisam.frm -rw-rw—-. 1 mysql mysql 21561096 Feb 20 16:11 seo_test_myisam.MYD -rw-rw—-. 1 mysql mysql 14766080 Feb… FTS_DOC_ID column, however, MySQL apparently has no problem with either of the following statements: mysql> insert into dir_test_innodb3 (fts…

Filed Under: Insight for DBAs, Insight for Developers, MySQL Tagged With: , , , , ,

Post: MySQL File System Fragmentation Benchmarks

March 21, 2008 By 28 Comments

… tables and does specified number of inserts going to random tables. I used default MySQL settings for MyISAM (table_cache=64) and set… be IO bound. As you can see from MyISAM results (above) the insert speeds does not degrade that badly until going from… tables causes data fragmentation and affects table scan performance nadly – MyISAM suffers worse than Innodb – Innodb extent allocation works (perhaps would…

Filed Under: Benchmarks Tagged With: ,

Post: MySQL Users Conference - Innodb

May 9, 2007 By 6 Comments

… will LOAD DATA INFILE be optimized same way as for MyISAM tables by separate phase of building Indexes ? Will be UNIQUE… due to statement level MySQL Replication which among other requirements, needs all auto-increment values in multiple value insert to be sequential…. There are probably similar problems in INSERT IGNORE and ON DUPLICATE KEY UPDATE cases. Now In MySQL 5.1+ it is also…

Filed Under: Events and Announcements Tagged With:

Post: MyISAM concurrent insert

June 13, 2006 By 12 Comments

… using range-queries and doing INSERT not often than disabling concurrent-insert with –skip-concurrent-insert can improve MyISAM performance. As I said the reason of bad scalability is rw-locks and currently MySQL…

Filed Under: Benchmarks, Insight for DBAs Tagged With: ,

Post: InnoDB Full-text Search in MySQL 5.6: Part 2, The Queries!

March 4, 2013 By 7 Comments

… are 543 stopwords in the default MyISAM list. What happens if we take the MyISAM stopwords, insert them into a table, and configure…, sounds easy enough: mysql: SHOW CREATE TABLE innodb_myisam_stopword\G *************************** 1. row *************************** Table: innodb_myisam_stopword Create Table: CREATE TABLE `innodb_myisam_stopword` ( `value…, 0 rows affected (0.00 sec) mysql: INSERT INTO innodb_ft_list2 SELECT * FROM innodb_myisam_stopword; Query OK, 543 rows affected (0…

Filed Under: Insight for DBAs, Insight for Developers, MySQL Tagged With: , , , , ,

Post: Beware of MyISAM Key Cache mutex contention

August 12, 2008 By 10 Comments

… loading data to MyISAM tables at very high rate. Hundreds of millions rows are loaded daily into single MySQL instance with bursts… copied from key_cache to the thread local space. Happily MyISAM allows you to create multiple key caches . We use “keycache… doing such changes we got insert rate to MySQL close to 200K rows/sec using standard multi value inserts which is pretty good…

Filed Under: Insight for DBAs Tagged With: ,

Post: ANALYZE: MyISAM vs Innodb

September 3, 2008 By 9 Comments

…with INSERT in Innodb table we do not get NULL cardinality as with MyISAM but …when computing stats: mysql> show variables like “myisam_stats_method”; +———————+—————+ | Variable_name | Value | +———————+—————+ | myisam_stats_method | nulls_unequal …

Filed Under: Insight for DBAs Tagged With: ,

Post: INSERT INTO ... SELECT Performance with Innodb tables.

July 12, 2006 By 22 Comments

… in MyISAM table. So why was this done, being pretty bad for MySQL Performance and concurrency ? The reason is – replication. In MySQL before…/tbl1.txt’ INTO TABLE tbl2; instead of: INSERT INTO tbl2 SELECT * from tbl1; INSERT … INTO OUTFILE does not have to set extra… 224576 MySQL thread id 1794751, query id 6994931 localhost root Sending data insert into test select * from sample ——– As you can see INSERT…

Filed Under: Insight for DBAs Tagged With: ,

Post: Quick comparison of MyISAM, Infobright, and MonetDB

September 29, 2009 By 30 Comments

… statements. I wanted to see how fast MyISAM vs. MonetDB would interpret really large INSERT statements, the kind produced by mysqldump. But… size of the resulting table on disk (smaller is better): MyISAM is 787MB, MonetDB is 791MB, and Infobright is 317MB. Next… underlying language. An abstracted front-end is a great idea; MySQL abstracts the storage backend, but why not do both? Last…

Filed Under: MySQL Tagged With: , , ,
« Older Posts
Newer Posts »