… test, the data fits in the buffer pool and has been loaded by the cold test already. The buffer pool and adaptive hash index are…_log_buffer_size=32M innodb_buffer_pool_instances=16 metadata_locks_hash_instances=32 table_open_cache_instances=8 sort_buffer_size=128k read_rnd_buffer_size=8M join_buffer…
Post: Migrating between MySQL schemas with Percona Xtrabackup
June 10, 2013 By 2 Comments
… the need to scan the full tables and in turn, load that data into and pollute the buffer pool. Note that this can be… operation (redo logs, binlogs, etc) as well further polluting the buffer pool. Enter Xtrabackup… As a quick refresher, Xtrabackup works by copying… in two scripts: mysql -N -B <<'EOF' > discard-ddl.sql SELECT CONCAT(‘ALTER TABLE `’, table_name, ‘` DISCARD TABLESPACE;’) AS _ddl FROM INFORMATION_SCHEMA.TABLES…
Post: MySQL and Percona Server in LinkBench benchmark
May 8, 2013 By 18 Comments
… the buffer pool is the same – 30GB. So now we explore the IO-bound scenario. Percona Server 5.5 outperforms MySQL in about… port=3306 innodb_buffer_pool_size = 30G innodb_flush_method = O_DIRECT innodb_log_file_size = 2000M innodb_log_files_in_group = 2…_capacity = 5000 sync_binlog=0 max_connections=5000 table_open_cache=5000 table-definition-cache=1000 query_cache_size=0 query_cache…
Post: Virident vCache vs. FlashCache: Part 2
May 17, 2013 By 3 Comments
… configuration for purposes of comparison. Given the apparent differences in time-based flushing in vCache and FlashCache, both cache devices were set… innodb_buffer_pool_size = 4G innodb_file_per_table = true innodb_data_file_path = ibdata1:100M innodb_flush_method = O_DIRECT innodb_log_buffer…
Post: Percona XtraBackup 2.1.0 'release candidate' for MySQL available for download
May 7, 2013 By Leave a Comment
…buffer pool dump files). Percona XtraBackup has implemented support for the InnoDB Buffer Pool Preloading introduced in MySQL 5.6. Starting with MySQL 5.6 buffer pool… been merged. Fixed by removing files corresponding to tables that are missing in the incremental backup directory. Bug fixed #856400….
Post: Percona XtraBackup 2.0.7 for MySQL available for download
May 6, 2013 By 1 Comment
…buffer pool dump files). Percona XtraBackup has implemented support for the InnoDB Buffer Pool Preloading introduced in MySQL 5.6. Starting with MySQL 5.6 buffer pool… been merged. Fixed by removing files corresponding to tables that are missing in the incremental backup directory. Bug fixed #856400….
Post: Benchmarking Percona Server TokuDB vs InnoDB
May 7, 2013 By 32 Comments
…_buffer_size = 256M innodb_flush_log_at_trx_commit = 1 innodb_buffer_pool_size = 36G innodb_log_file_size = 4G innodb_log_files_in… #default_table_type = InnoDB thread_stack = 192K tmp_table_size = 64M server-id = 10 #*** MyISAM Specific options key_buffer_size = 8M read_buffer_size = 1M read_rnd_buffer_size = 4M bulk_insert_buffer…
Post: More on MySQL transaction descriptors optimization
April 26, 2013 By Leave a Comment
….1GB) for his tests, while in our original tests we used a much larger one, 16 tables with 5M rows each (about…_log_buffer_size = 16M innodb_buffer_pool_size = 52G innodb_log_file_size = 2000M innodb_log_files_in_group = 2 innodb_file_per_table = true… To warmup server and load data and indices to the buffer pool we use following queries: select avg(id) from sbtest$i…
Comment: MySQL Partitioning - can save you or kill you
April 30, 2013 By 45 Comments
… not bad. (The last 2-3 partitions tend to stay in the buffer_pool.) If I recall correctly, any INSERT/DELETE/etc always… are likely to be slower in PARTITIONed tables unless you include something to facilitate pruning. A billion rows in a BTree is only… table into 100 partitions, the BTree in each partition would be about 4 levels. A “point query” in a billion-row (non-partitioned) table…
Post: MySQL 5.6 - InnoDB Memcached Plugin as a caching layer
March 29, 2013 By 10 Comments
… 0.0951 ms range per/cmd InnoDB MySQL Select (same table): Fetching [1,000,000] items: 441573 ms total 0.441573….0802 ms range per/cmd Keep in mind that the entire data set fits into the buffer pool, so there are no reads…=’key_id’”); id is PK of the container table While there are increases in both operations, there are some tradeoffs to consider…
