…count(distinct username) | +—————————+ | 5903053 | +—————————+ 1 row in set (2 min 8.92 sec) mysql> SHOW STATUS LIKE ‘Innodb_scan_pages%’; +——————————+——-+ | Variable_name | Value | +——————————+——-+ | Innodb…
Post: Extending Index for Innodb tables can hurt performance in a surprising way
May 22, 2010 By 10 Comments
… ENGINE=InnoDB AUTO_INCREMENT=6029313 DEFAULT CHARSET=latin1 mysql> select count(*) from idxitest where a=5 and b=5; +———-+ | count(*) | +———-+ | 60434 | +———-+ 1 row… rows in the selected plan, while using (a) index you can get result scanning only 1 row guaranteed. To really get…
Post: Shard-Query EC2 images available
May 11, 2011 By 2 Comments
… like ‘ontime_fact’G *************************** 1. row *************************** Name: ontime_fact Engine: InnoDB Version: 10 Row_format: Compact Rows: 6697533 Avg_row_length: 241 Data_length: 1616904192… only 2.5GB of data, so ICE gets over 16:1 compression ratio(compared to Innodb, 8:1 compared to raw input… the following row count (the first number is wall time, the second exec time, the third parse time). $ echo “select count(*) from ontime…
Post: When EXPLAIN estimates can go wrong!
October 13, 2011 By 7 Comments
…innodb_stats_sample_pages really would have no effect on the “number of rows…count of rows: mysql [localhost] {msandbox} (foo2) > select count(*) from test_estimate where type=6 \G *************************** 1. row *************************** count… of the intermediate pages to get a better estimate …
Post: Innodb Table Locks
July 31, 2012 By 4 Comments
Innodb uses row level locks right ? So if you see locked tables reported in SHOW ENGINE INNODB STATUS you might be confused and… instances (note – same table gets counted twice) in use but zero tables are locked. Innodb does not need any row locks for conventional selects… we asked Innodb to do locking reads so it has to lock all the rows which are being touched. However with Innodb‘s…
Post: Debugging problems with row based replication
May 6, 2010 By 12 Comments
…. A statement based replication slave can get out of sync with the master fairly…c1`) ) ENGINE=InnoDB; Query OK, 1 row affected (0.02 sec) Insert a single “seed” row into the… to play with: select count(*) from t1; +———-+ | count(*) | +———-+ | 65536 | +———-+ 1 row in set (0.02 sec…
Post: Innodb row size limitation
April 7, 2011 By 12 Comments
…-length columns for a given row on a single database page. Innodb has a limit for the maximum row size, which is slightly… a single row on a page. If your row has variable length columns and the complete row size exceeds this limit, InnoDB chooses variable…, not counting other fixed length columns. This exceeds the limit and therefore you get the error. You don’t have to get it…
Post: How FLUSH TABLES WITH READ LOCK works with Innodb Tables
March 23, 2012 By 20 Comments
…Rows_sent | Rows_examined | Rows_read | +——-+——+———–+———-+———+——+————————-+—————————–+———–+—————+———–+ | 10219 | root | localhost | dumptest | Query | 324 | Sending data | select count… from getting … Innodb …
Post: InnoDB vs MyISAM vs Falcon benchmarks - part 1
January 8, 2007 By 117 Comments
…get a maximal result of three runs. The raw numbers are available here: http://www.mysqlperformanceblog.com/files/benchmarks/innodb… times. READ_PK_RANGE_INDEX Query: SELECT count(id) FROM $tableName WHERE id between %d… query performs a scan of all million rows. InnoDB is better than MyISAM by ~30% …
Post: InnoDB compression woes
May 20, 2011 By 14 Comments
InnoDB compression is getting some traction, and I see quite contradictory…./sysbench –test=tests/db/parallel_prepare.lua –oltp-tables-count=16 –num-threads=16 –oltp-table-size=25000000… tables: 38278 sec. Compressed tables are create as: ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8, with final…
