Percona's MySQL & InnoDB performance and scalability blog
….innodb_ft_config; +—————————+———+ | KEY | VALUE | +—————————+———+ | optimize_checkpoint_limit | 180 | | synced_doc_id | 1028261 | | last_optimized_word | | | deleted_doc_count | 0 | | total_word_count….innodb_ft_config; +—————————+———+ | KEY | VALUE | +—————————+———+ | optimize_checkpoint_limit | 180 | | synced_doc_id | 1032677 | | last_optimized_word | | | deleted_doc_count | 0 | | total_word_count…
…. Now let us populate antest_innodb table which is same but uses Innodb format: mysql> insert into antest_innodb select * from antest; Query… estimate for the row count in the table) It is worth to note if you do ALTER TABLE Innodb, same as MyISAM… and values will be more sensible: mysql> alter table antest_innodb type=innodb; Query OK, 245760 rows affected, 1 warning (51.87…
…: 38530 InnoDB_IO_r_bytes: 631275520 InnoDB_IO_r_wait: 0.204893 # InnoDB_rec_lock_wait: 0.000000 InnoDB_queue_wait: 0.000000 # InnoDB_pages… in order. +—————————+ | count(distinct username) | +—————————+ | 5903053 | +—————————+ 1 row in set (2 min 8.92 sec) mysql> SHOW STATUS LIKE ‘Innodb_scan_pages…
… count(i) from sample lock in share mode; +———-+ | count(i) | +———-+ | 1638400 | +———-+ 1 row in set (7.02 sec) Looking at SHOW INNODB STATUS…%” lock in share mode; +———-+ | count(*) | +———-+ | 102216 | +———-+ 1 row in set (4.44 sec) Looking at SHOW INNODB STATUS we can see: Total…
… to regulate count of threads working inside InnoDB. innodb_thread_concurrency is variable which set this count, and there are two friendly variables innodb_thread_sleep_delay and innodb_concurrency…
… note number one about MyISAM to Innodb migration is warning what Innodb is very slow in COUNT(*) queries. The part which I often however see omitted is fact it only applies to COUNT… to remove. So remember Innodb is not slow for ALL COUNT(*) queries but only for very specific case of COUNT(*) query without WHERE…
… piece of information is event counters – “reservation count” and “signal count” show how actively innodb uses internal sync array – how frequently slots are… thread is not runniing inside innodb kernel status could be “waiting in InnoDB queue” or “sleeping before joining InnoDB queue”. Latest one is…
…` (`a`) ) ENGINE=InnoDB AUTO_INCREMENT=6029313 DEFAULT CHARSET=latin1 mysql> select count(*) from idxitest where a=5 and b=5; +———-+ | count(*) | +———-+ | 60434 | +———-+ 1… (`id`), KEY `a` (`a`), KEY `a_2` (`a`,`b`) ) ENGINE=InnoDB AUTO_INCREMENT=6029313 DEFAULT CHARSET=latin1 mysql> select * from idxitest…
… Create_options: Comment: InnoDB free: 6144 kB *************************** 2. row *************************** Name: cbool Engine: InnoDB Version: 10 Row… comparison operator: mysql> select count(*) from cbool where c1NULL; +———-+ | count(*) | +———-+ | 1048576 | +———-+ 1 row in set…
…: mysql> select count(*) from INNODB_SYS_TABLES; +———-+ | count(*) | +———-+ | 48246 | +———-+ 1 row in set (8.04 sec) mysql> select count(*) from INNODB_SYS_INDEXES; +———-+ | count(*) | +———-+ | 451773 | +———-+ 1 row in set (2.75 sec) In this case The memory stats from SHOW INNODB STATUS…
Copyright © Percona LLC • Contact Us • MySQL and InnoDB are trademarks of Oracle Corp.