…: 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…
Post: Innodb Table Locks
July 31, 2012 By 4 Comments
… table instances (note – same table gets counted twice) in use but zero tables are locked. Innodb does not need any row locks… unlocking it straight after. Not so for Innodb. Unless table is being locked explicitly Innodb “converts” table lock to “no lock” hence…. You almost never will run into problems with Innodb table level locks because innodb will only set intentional level locks for everything…
Post: Modeling InnoDB Scalability on Multi-Core Servers
January 26, 2011 By 11 Comments
… recently published prompted me to do some mathematical modeling of InnoDB‘s scalability as the number of cores in the server… a reasonably typical commodity machine except for the high core count, which is more than I can remember seeing in the… done great work in the last couple of years making InnoDB scale and perform better on modern hardware.
Post: Quickly preloading Innodb tables in the buffer pool
May 1, 2008 By 9 Comments
… more information. But lest first start with feature request for Innodb Team: All ways I mention here are hacks and they… native support. It would be great if Innodb would implement command to preload table to Innodb buffer pool, which would simply go… you can load Innodb Table Clustered Index in the buffer pool pretty efficiently by using something like SELECT count(*) FROM tbl WHERE…
Post: Tuning InnoDB Concurrency Tickets
May 24, 2010 By 5 Comments
…2 Duplicates: 0 Warnings: 0 mysql> SELECT COUNT(*) FROM test_table; — 3 Tickets Used +———-+ | COUNT(*) | +———-+ | 3 | +———-+ 1 row in set (0… 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: 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 (3…
Post: InnoDB Flushing: a lot of memory and slow disk
March 31, 2011 By 57 Comments
… ~ 10GB of data, 12G innodb_buffer_pool_size, 1G innodb_log_file_size), MySQL 5.5.10 with innodb_adaptive_flushing=ON (default…. To make the situation even worse, the count of flushed neighbor pages is counted toward the number of pages we asked to… jumps in flushing pages. Make the algorithm independent of innodb_io_capacity and innodb_max_dirty_pages_pct. (This is important for…
Post: How well does your table fits in innodb buffer pool ?
December 9, 2010 By 12 Comments
…, COUNT(*) cnt, SUM(dirty = 1) dirty, SUM(hashed = 1) hashed FROM innodb_buffer_pool_pages_index GROUP BY index_id) bp JOIN innodb…_id = innodb_sys_tables.id JOIN innodb_index_stats ON innodb_index_stats.table_name = innodb_sys_tables.name AND innodb_sys_indexes.name = innodb_index_stats.index_name AND innodb_index_stats.table_schema = innodb…
Post: InnoDB vs MyISAM vs Falcon benchmarks - part 1
January 8, 2007 By 117 Comments
… was a popular myth that MyISAM is faster than InnoDB in reads, as InnoDB is transactional, supports Foreign Key and has an… MyISAM / InnoDB: libexec/mysqld –no-defaults –user=root –key-buffer-size=1500M –innodb-buffer-pool-size=1500M –innodb-log-file-size=100M –innodb-thread… in Part 2). There MyISAM shows bad scalability with increasing count of thread. I think the reason is pread system call…
Post: The perils of InnoDB with Debian and startup scripts
January 28, 2009 By 13 Comments
…. Why is that happening? Look at SHOW INNODB STATUS: ===================================== 090128 8:29:03 INNODB MONITOR OUTPUT ===================================== Per second averages calculated from the last 15 seconds ———- SEMAPHORES ———- OS WAIT ARRAY INFO: reservation count 39125236, signal count…
