… there was no caching of data at the filesystem level. The InnoDB indexes were built using ALTER TABLE fast index creation (merge sort… innodb_buffer_pool_size=64G innodb_log_file_size=4G innodb_file_per_table innodb_stats_on_metadata=off innodb_file_format=barracuda innodb_log_buffer_size=32M innodb…
Post: ZFS on Linux and MySQL
May 24, 2013 By 17 Comments
…best with InnoDB, with MyISAM you’ll have to start the snapshot while holding a “flush tables with read lock”… a drive, instead of crashing InnoDB, it will be caught by ZFS and the data will be read from the… Also, instead of having 1 or 2 spare drives per controller, I have 2 for the whole setup. A…
Post: Benchmarking Percona Server TokuDB vs InnoDB
May 7, 2013 By 32 Comments
… innodb_flush_neighbor_pages = none innodb_adaptive_flushing_method = keep_average innodb_file_per_table = true innodb_data_file_path = ibdata1:100M:autoextend innodb_flush_method = O_DIRECT innodb_log_buffer_size = 256M innodb…
Post: How to recover table structure from InnoDB dictionary
April 22, 2013 By 4 Comments
… an option: Table was dropped and innodb_file_per_table is ON Frm file corrupt, zeroed out, lost or SHOW CREATE TABLE crashes MySQL There… dictionary tables into some MySQL server. Use LOAD DATA INFILE constraints_parser generates mysql>LOAD DATA INFILE ‘/path/to/SYS_FIELDS’ REPLACE INTO TABLE…
Post: More on MySQL transaction descriptors optimization
April 26, 2013 By Leave a Comment
…3306 innodb_status_file=0 innodb_data_file_path=ibdata1:100M: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_in_group = 2 innodb_file_per_table = true innodb…
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 = O_DIRECT innodb_log_buffer_size = 128M innodb_flush…
Post: MySQL and Percona Server in LinkBench benchmark
May 8, 2013 By 18 Comments
…. By default linkbench dataset has 10M ids(after load of data size of datadir ~10GB). We used this dataset to check… = 1 innodb_log_buffer_size=128M innodb_max_dirty_pages_pct=80 innodb_file_format=barracuda innodb_file_per_table innodb_read_io_threads = 8 innodb_write_io_threads = 8 innodb_io…
Comment: How to recover table structure from InnoDB dictionary
May 8, 2013 By 4 Comments
1) innodb_file_per_table is OFF by default, which means all tables are in ibdata1. 2) if it was HW problem and …/docs/wiki/innodb-data-recovery-tool:mysql-data-recovery:start . The biggest challenge here is to find index_id for each table – http://www.percona.com/docs/wiki/innodb-data-recovery-tool:mysql-data-recovery:advanced_techniques#recovering_the_data_dictionary
Post: MySQL 5.6 - InnoDB Memcached Plugin as a caching layer
March 29, 2013 By 10 Comments
…, here is the basic approach: data = fetchCache(key) if (data) { return data } data = callExpensiveFunction(params) storeCache(data, key) return data Memcached is a very popular (and… ms per/cmd 0.3208 ms min per/cmd 0.4159 ms max per/cmd 0.0951 ms range per/cmd InnoDB MySQL Select (same table…
Post: Understanding the maximum number of columns in a MySQL table
April 8, 2013 By 5 Comments
…FRM file, you instead get some bunch of bytes per 19 columns). create_fields.elements*FCOMP is just …’s replaced by the following set of defines #define DATA_N_SYS_COLS 3 /* data0type.h */ /* from rem0types.h */…5.6 you can create a table with a few more columns in InnoDB! This led me on another idea…
