June 19, 2013

Post: MySQL 5.6 vs MySQL 5.5 and the Star Schema Benchmark

March 11, 2013 By 7 Comments

… speed is very consistent, just a few tenths of a second off per run.  I then rebooted the machine and fired up… in pages afterwards: mysql> select sq.*, pages / (@@innodb_buffer_pool_size / 16384) * 100 pct_buffer_pool from ( select table_name, index_name, count(*) pages, sum…

Filed Under: MySQL Tagged With: , , , , ,

Post: Why MySQL could be slow with large tables ?

June 9, 2006 By 141 Comments

…speed up accesses to database. Some people would also remember if indexes are helpful or not depends on index selectivity…mysql> select count(pad) from large; +————+ | count(pad) | +————+ | 31457280 | +————+ 1 row in set (4 min 58.63 sec) mysql> select count… in Innodb which …

Filed Under: Insight for DBAs Tagged With: ,

Post: Recovery deleted ibdata1

August 10, 2012 By 9 Comments

…age 7782360 To speed up flushing we can set dirty pages percentage to zero: mysql> set global innodb_max_dirty…mysql# And restart MySQL: root@localhost:/var/lib/mysql# /etc/init.d/mysql restart After the restart all InnoDB tables are reachable: mysql> select count(*) from sbtest; +———-+ | count…

Filed Under: Insight for DBAs, MySQL Tagged With:

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

February 26, 2013 By 1 Comment

… speed up bulk loading into an InnoDB…mysql> select * from information_schema.innodb_ft_config; +—————————+———+ | KEY | VALUE | +—————————+———+ | optimize_checkpoint_limit | 180 | | synced_doc_id | 1032677 | | last_optimized_word | | | deleted_doc_count…

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

Post: SHOW INNODB STATUS walk through

July 17, 2006 By 40 Comments

…event counters – “reservation count” and “signal count” show how actively innodb uses internal sync array …InnoDB 442 mysql tables in use 1, locked 0 MySQL thread id 8079, query id 728899 localhost root Sending data select… is hash index Innodb builds for some pages to speed up row lookup replacing …

Filed Under: Insight for DBAs Tagged With:

Post: Index Condition Pushdown in MySQL 5.6 and MariaDB 5.5 and its performance impact

March 12, 2012 By 12 Comments

… in preparation for the talk comparing the optimizer enhancements in MySQL 5.6 and MariaDB 5.5. We are taking a… optimizer enhancement Index Condition Pushdown (ICP). Its available in both MySQL 5.6 and MariaDB 5.5 Now let’s take…

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

Post: Join performance of MyISAM and Innodb

May 29, 2006 By 6 Comments

… KEY `c` (`c`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8; select count(t1.pad),count(t2.pad) from t1,t1 t2 …index and generally highly optimized Innodb builds hash indexes which helps to speed up lookup by indexes by passing…the index, so there is no need for MySQL to request next row matching index Note: …

Filed Under: Benchmarks Tagged With: , ,