…: ## InnoDB options innodb_buffer_pool_size = 55G innodb_log_file_size = 1G innodb_log_files_in_group = 4 innodb_buffer_pool_instances = 4 innodb_adaptive…_file_per_table = 1 ## Disabling query cache query_cache_size = 0 query_cache_type = 0 You can see that the buffer pool is sized at 55G and I am using 4 buffer pool instances to reduce the contention caused by buffer pool mutexes. Another…
Comment: Benchmarking single-row insert performance on Amazon EC2
May 16, 2012 By 11 Comments
… on transaction-id makes the insertions easy. Basically you fill in 100 million rows, then start a new partition and fill… because the entire table fits in main memory. Each row is on the order of 30 bytes in the primary table, and so a… the B-tree pretty efficiently. With a 55GB buffer pool, we would expect the primary table to simply fit. But with all 4…
Post: Join Optimizations in MySQL 5.6 and MariaDB 5.5
April 4, 2012 By 2 Comments
… fits entirely in memory. For the purpose of benchmarking in-memory workload, the InnoDB buffer pool size is set to 6G and the buffer pool was warmed up, so that the relevant pages were already loaded in the buffer pool… w/ join_buffer_size=4M & mrr_buffer_size=4M Created_tmp_disk_tables 0 0 0 0 0 0 Created_tmp_tables 1 1…
Post: Multi Range Read (MRR) in MySQL 5.6 and MariaDB 5.5
March 21, 2012 By 10 Comments
… fits entirely in memory. For the purpose of benchmarking in-memory workload, the InnoDB buffer pool size is set to 6G and the buffer pool was warmed up, so that the relevant pages were already loaded in the buffer pool… range scans had to be performed. As in the table above you can with default buffer size of 256K, MariaDB 5.5 shows…
Post: Index Condition Pushdown in MySQL 5.6 and MariaDB 5.5 and its performance impact
March 12, 2012 By 12 Comments
… look at and benchmarking optimizer enhancements one by one. So in the same way this blog post is aimed at a new optimizer enhancement Index Condition Pushdown (ICP). Its available in both MySQL 5.6 and MariaDB 5.5 Now let… Condition Pushdown Traditional B-Tree index lookups have some limitations in cases such as range scans, where index parts after the…
Post: Troubleshooting MySQL Memory Usage
March 21, 2012 By 3 Comments
…, which can use like this: ———————- BUFFER POOL AND MEMORY ———————- Total memory allocated 132183490560; in additional pool allocated 0 Internal hash tables (constant factor + variable factor… 425080 (406936 + 18144) Dictionary memory allocated 2623331 Buffer pool size 7864319 Buffer pool size, bytes 128849002496 Free buffers 1 Database pages 8252672 Old database pages…
Comment: Performance problem with Innodb and DROP TABLE
March 15, 2012 By 23 Comments
… the table, so the tablespace be droped also. so , why the pages associate with that tablespace in the buffer pool must be discarded too? in my opinion, when the table is droped ,then no statement…,then the useless pages in the buffer pool can be discared when buffer pool is full or other situation, not at the time when drop table.
Comment: Innodb Performance Optimization Basics
March 28, 2012 By 70 Comments
… am facing major issues with slow query output. The tables are in InnoDB with MySQL 5 and RHEL 5. Its a 32…/mysql/data innodb_buffer_pool_size = 2000M innodb_additional_mem_pool_size = 2M innodb_log_file_size = 65M innodb_log_buffer_size = 8M innodb…. select count(1),field1 from Table where REQUEST_TIME >= ’2012-03-26′ group by field1; 14 rows in set (15 min 54…
Post: MariaDB 5.3 is released as GA!
March 5, 2012 By 17 Comments
… through speedier joins, faster subqueries, and elimination of useless tables in joins Thread pooling and a segmented key cache for improved scalability That… diagnostics, improvements to partitioning, and the addition of multiple InnoDB buffer pools. Best of all, MariaDB 5.5 might be here pretty…
Comment: Predicting how long data load would take
March 26, 2012 By 12 Comments
One trick for really huge, hash-partitioned InnoDB tables is to sort by the partition rule, then by the … the partition, load in PK order. This will result in very nice buffer pool behavior. We loaded a 1.5 billion row table in “only” 30…
