…_cost_based=off’ optimizer_switch=’batched_key_access=on’ join_buffer_size=6M read_rnd_buffer_size=6M Also note that the following… the purpose of benchmarking in-memory workload, the InnoDB buffer pool size is set to 6G and the buffer pool was warmed up… the purpose of benchmarking IO bound workload, the InnoDB buffer pool size is set to 1G and the buffer pool was not warmed…
Post: Multi Range Read (MRR) in MySQL 5.6 and MariaDB 5.5
March 21, 2012 By 10 Comments
…: SELECT key_column, pk_column FROM tbl WHERE key_column=x ORDER BY key_column (Note that secondary keys in InnoDB contain primary key columns) Buffer… the purpose of benchmarking in-memory workload, the InnoDB buffer pool size is set to 6G and the buffer pool was warmed up… the purpose of benchmarking IO bound workload, the InnoDB buffer pool size is set to 1G and the buffer pool was not warmed…
Post: Shard-Query EC2 images available
May 11, 2011 By 2 Comments
…. Note dates may not be in date_id order’; /*!40101 SET character_set_client = @saved_cs_client */; CREATE TABLE `dim_flight` ( `UniqueCarrier… innodb-thread-concurrency-timer-based innodb-thread-sleep-delay=0 innodb-use-sys-stats-table innodb-write-io-threads=4 join-buffer-size=16M key-buffer-size…
Post: Improved InnoDB fast index creation
November 6, 2011 By 4 Comments
…VARCHAR column to trigger table rebuilds without affecting the table size. mysql> SET expand_fast_index_creation=ON; Query OK, 0 …dropping keys that are part of a FOREIGN KEY constraint; mysqldump –innodb-optimize-keys ignores foreign keys because InnoDB requires a full table rebuild on foreign key…
Post: How much overhead is caused by on disk temporary tables
August 16, 2007 By 26 Comments
… set (34.47 sec) Wow this is over 15 times better so you surely need to make sure your key_buffer_size is set to accommodate needs of your temporary table. Do not forget about it especially if running only Innodb tables… queries: mysql> set global key_buffer_size=8000000; Query OK, 0 rows affected (0.03 sec) mysql> set global max_heap_table_size=1000000000; Query…
Comment: Database problems in MySQL/PHP Applications
November 6, 2006 By 13 Comments
…sock bind-address=10.234.94.71 skip-locking key_buffer_size = 2000M max_allowed_packet = 32M # table_cache=20M #…innodb_buffer_pool_size = 8000M #innodb_additional_mem_pool_size = 80M # Set .._log_file_size to 25 % of buffer pool size innodb_log_file_size = 1000M #innodb_log_buffer_size = 32M #innodb…
Post: SHOW INNODB STATUS walk through
July 17, 2006 By 40 Comments
…s), heap size 320 MySQL thread id 30898, query id 100626 localhost root Updating update iz set pad…table `test/child`: , CONSTRAINT `child_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `parent` (`id`) ON DELETE …if your innodb_log_buffer_size is optimal – if you see more than 30% of log buffer size …
Post: How much memory can MySQL use in the worst case?
February 12, 2009 By 17 Comments
… is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections”. This was never true — not… added memory for InnoDB: specifically, the buffer pool. (There’s also an innodb_additional_mem_pool_size but it’s typically set pretty small, like 20M). But even that’s not enough. Staying with InnoDB…
Post: Index Condition Pushdown in MySQL 5.6 and MariaDB 5.5 and its performance impact
March 12, 2012 By 12 Comments
… for filtering records. For example, suppose you have a key defined as: KEY `i_l_partkey` (`l_partkey`,`l_quantity`,`l_shipmode…
Post: Reality of Innodb Caching
April 21, 2011 By 8 Comments
… few times Innodb caches data in pages and even if you have working set consisting of relatively few rows your working set in… row size in this table is smaller but lets use this number for our math. For benchmark I’m using set number… of rows 224 bytes in size. Lets see what Benchmark really shows: Testing 100 out of 10000000 keys 24.79 seconds, 4034…
