…: 2 Duplicates: 0 Warnings: 0 mysql> SELECT COUNT(*) FROM test_table; — 3 Tickets Used +———-+ | COUNT(*) | +———-+ | 3 | +———-+ 1 row in set (…innodb_thread_concurrency=16 innodb_flush_method = O_DIRECT innodb_write_io_threads=8 innodb_read_io_threads=8 innodb_io_capacity=500 innodb…
Post: InnoDB vs MyISAM vs Falcon benchmarks - part 1
January 8, 2007 By 117 Comments
… MyISAM / InnoDB: libexec/mysqld –no-defaults –user=root –key-buffer-size=1500M –innodb-buffer-pool-size=1500M –innodb-log-file-size=100M –innodb-thread…. Perhaps there is still a problem with InnoDB mutexes. READ_KEY_POINT_NO_DATA Query: SELECT state_id FROM $tableName WHERE country… 1.64 – 3.85 times. READ_PK_RANGE_INDEX Query: SELECT count(id) FROM $tableName WHERE id between %d and %d MyISAM…
Post: Quickly preloading Innodb tables in the buffer pool
May 1, 2008 By 9 Comments
… mentioned you can load Innodb Table Clustered Index in the buffer pool pretty efficiently by using something like SELECT count(*) FROM tbl WHERE… some non PRIMARY Indexes preloaded you can use something like SELECT count(*) from tbl WHERE index_col like “%0%” for each index… BLOB/TEXT externally stored columns you can use similar query: SELECT count(*) from tbl WHERE blob_col like “%0%”. Note if you…
Post: Efficient Boolean value storage for Innodb Tables
April 23, 2008 By 6 Comments
…Create_options: Comment: InnoDB free: 6144 kB *************************** 2. row *************************** Name: cbool Engine: InnoDB Version: 10 …-Aware comparison operator: mysql> select count(*) from cbool where c1NULL; +———-+ | count(*) | +———-+ | 1048576 | +———-+ 1 row in set…
Post: How FLUSH TABLES WITH READ LOCK works with Innodb Tables
March 23, 2012 By 20 Comments
… | 10219 | root | localhost | dumptest | Query | 324 | Sending data | select count(*) from A,B | 0 | 0 | 2359297 | | 10290 | root…SELECT queries to let backup to proceed, but resolving server gridlock one way or another. If you’re just using Innodb…
Post: Copying InnoDB tables between servers
July 31, 2009 By 11 Comments
…innodb_data_home_dir = ./ xtrabackup: innodb_data_file_path = ibdata1:10M:autoextend xtrabackup: innodb_log_group_home_dir = ./ xtrabackup: innodb… done. When finished quick check mysql> select count(*) from order_line; +———-+ | count(*) | +———-+ | 32093604 | +———-+ 1 row in set (…
Post: Join performance of MyISAM and Innodb
May 29, 2006 By 6 Comments
… today which involved benchmarks of Join speed for MyISAM and Innodb storage engines for CPU bound workload, this is when data…` (`i`), KEY `c` (`c`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8; select count(t1.pad),count(t2.pad) from t1,t1 t2 where t1.id… data is clustered together with index and generally highly optimized Innodb builds hash indexes which helps to speed up lookup by…
Post: Moving from MyISAM to Innodb or XtraDB. Basics
November 22, 2010 By 2 Comments
… time in Innodb you want larger updates to reduce cost of transaction commit. You also may want to avoid excessive SELECT COUNT(*) FROM TBL (with no where clause) which is very fast for MyISAM but does table/index scan for Innodb. Defaults… most important values to check are innodb_flush_log_at_trx_commit, innodb_buffer_pool_size and innodb_log_file_size. There are…
Comment: How much memory Innodb Dictionary can take ?
May 19, 2010 By 3 Comments
… to InnoDB tables! This is an unsupported operation.” In my db when I run sql get below error: mysql> SELECT count(*) FROM INNODB_SYS_TABLES; ERROR 1146 (42S02): Table ‘test.INNODB_SYS_TABLES’ doesn’t exist
Comment: How Percona does a MySQL Performance Audit
November 25, 2008 By 17 Comments
Michael, There is example (quite simplified): if application is running SELECT COUNT(*) from InnoDB_table to scan 100.000.000 rows, there is no good way to optimize it if you do not want to change application.
