…) 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… 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
… 5.5. Here is the 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… 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
…, I can confirm the steady downfall of InnoDB throughput down to ~1500 insert/sec (and counting) while TokuDB remained steadily around 20000…
Post: Benchmarking Percona Server TokuDB vs InnoDB
May 7, 2013 By 32 Comments
… #####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
… your hotspots were really bad in standalone Innodb, you might consider relaxing the fsync: set innodb_flush_log_at_trx_commit to… 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
…:autoextend innodb_flush_log_at_trx_commit = 2 innodb_flush_method = O_DIRECT innodb_log_buffer_size = 16M innodb_buffer_pool_size = 52G innodb_log_file_size = 2000M innodb_log_files… sysbench –num-threads=<1..1024> –test=oltp.lua –oltp_tables_count=8 –oltp-table-size=1000000 –rand-init=on –report-interval…
Post: Virident vCache vs. FlashCache: Part 2
May 17, 2013 By 3 Comments
…: #####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: Finally. How to verify if all MySQL records were recovered
April 25, 2013 By Leave a Comment
…. There are two ways to know how many records an InnoDB page stores. The index page has a header PAGE_N… number of records the page stores. Obviously it doesn’t count any deleted records. The second method to confirm how many… is to follow records pointers – and count them. As you might know, records inside an InnoDB page are organized in an unidirectional…
Post: Understanding the maximum number of columns in a MySQL table
April 8, 2013 By 5 Comments
…*FCOMP+288+ n_length+int_length+com_length > 65535L || int_count > 255) { my_message(ER_TOO_MANY_FIELDS, ER(ER_TOO… can create. If you try setting the storage engine to InnoDB however, you will get an error message! The exact error… of 1,000 columns. This is the code from ha_innodb.cc that enforces that limit: if (form->s->fields > 1000…
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… server. You will need Percona Server in order to use innodb_fake_changes. Thread pool Percona Playback added a connection pool…
