…) unsigned NOT NULL AUTO_INCREMENT, PRIMARY KEY (`parent_id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 CREATE TABLE `child_A` ( `id` int(10…_1` FOREIGN KEY (`parent_id`) REFERENCES `parent` (`parent_id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 Notice that in the case of child… are additional tabs: Tables – Names, number of children, parents, count of columns, row counts, and comments — a great way for a high level…
Comment: INSERT INTO ... SELECT Performance with Innodb tables.
May 22, 2013 By 22 Comments
… related output from show engine innodb status: —TRANSACTION BB0E42A, ACTIVE 3 sec fetching rows, thread declared inside InnoDB 334 mysql tables in use 4, locked 4 3264 lock struct(s), heap size 440760, 799203 row… as SELECT ld.label_id, cai.inventory_id, COUNT(1) as installed_count from client_app_inventory cai inner join device d…
Comment: Benchmarking Percona Server TokuDB vs InnoDB
May 22, 2013 By 32 Comments
… of InnoDB throughput down to ~1500 insert/sec (and counting) while TokuDB remained steadily around 20000 insert/sec. I inserted around 100Million rows…
Post: Benchmarking Percona Server TokuDB vs InnoDB
May 7, 2013 By 32 Comments
… can easily fill TokuDB tables with 1bln of rows on this SSD, and projected InnoDB performance on this size will be the… #####plugin options innodb_read_io_threads = 16 innodb_write_io_threads = 4 innodb_io_capacity = 4000 innodb_use_native_aio=0 #not innodb options (fixed) port = 3306 back_log = 50 max_connections = 2000 max_prepared_stmt_count=500000…
Post: Is Synchronous Replication right for your app?
May 14, 2013 By 18 Comments
… locking in Innodb is a single row (well, the PRIMARY KEY index entry for that row). This means typically on a single Innodb node… to the database periodically? if( $last_count % 100 == 0 ) { $db->do( “UPDATE achievements SET count = $last_count where achievement = ‘killed_troll’”; } change your…
Post: More on MySQL transaction descriptors optimization
April 26, 2013 By Leave a Comment
… Dimitri was using 8 tables with 1M rows each (about 2.1GB) for his tests,…innodb_flush_method = O_DIRECT innodb_log_buffer_size = 16M innodb_buffer_pool_size = 52G innodb_log_file_size = 2000M innodb…threads=<1..1024> –test=oltp.lua –oltp_tables_count=8 –oltp-table-size=1000000 –rand-init=…
Post: Virident vCache vs. FlashCache: Part 2
May 17, 2013 By 3 Comments
… were conducted with approximately 78GiB of data (32 tables, 10M rows each) and a 4GiB buffer pool. The cache devices were…: #####fixed innodb options innodb_file_format = barracuda innodb_buffer_pool_size = 4G innodb_file_per_table = true innodb_data_file_path = ibdata1:100M innodb_flush_method… = 1 innodb_fast_shutdown = 1 #not innodb options (fixed) back_log = 50 wait_timeout = 120 max_connections = 5000 max_prepared_stmt_count=500000…
Post: Understanding the maximum number of columns in a MySQL table
April 8, 2013 By 5 Comments
… this: /* Hack to avoid bugs with small static rows in MySQL */ reclength=max(file->min_record_length(…+ n_length+int_length+com_length > 65535L || int_count > 255) { my_message(ER_TOO_MANY_FIELDS, ER(…create. If you try setting the storage engine to InnoDB however, you will get an error message! The …
Post: Is your MySQL buffer pool warm? Make it sweat!
April 16, 2013 By 6 Comments
…-stdin –dispatcher-plugin thread-pool –thread-pool-threads-count 100 –session-init-query \”set innodb_fake_changes=1\” > /var/log/playback.log… –session-init-query command-line option, we set the option innodb_fake_changes to prevent INSERTs, UPDATEs, and DELETEs from corrupting… is an important step because update statements that match no rows or insert statements that have duplicate key errors may be…
Post: ANALYZE: MyISAM vs Innodb
September 3, 2008 By 9 Comments
… populate antest_innodb table which is same but uses Innodb format: mysql> insert into antest_innodb select * from antest; Query OK, 245760 rows affected… is estimate for the row count in the table) It is worth to note if you do ALTER TABLE Innodb, same as MyISAM… rows affected (11.48 sec) Rows matched: 245760 Changed: 245760 Warnings: 0 mysql> update antest_innodb set c=NULL; Query OK, 245760 rows affected…
