… more details, check out detailed guide on tuning innodb buffer pool innodb_log_file_size – This depends on your recovery speed needs but… advantage of Innodb properties – clustering by primary key, having primary key in all indexes (so keep primary key short), fast lookups by primary keys (try…
Post: Choosing innodb_buffer_pool_size
November 3, 2007 By 39 Comments
My last post about Innodb Performance Optimization got a lot of comments choosing proper innodb_buffer_pool_size and indeed I oversimplified things… cache, key_buffer, mysql threads, temporary tables, per thread sort buffer which can be allocated. There are also things like innodb additional…
Post: Innodb row size limitation
April 7, 2011 By 12 Comments
…, fdisclaimer TEXT, fcopyright TEXT, fstylesheet TEXT, fterms TEXT, PRIMARY KEY (id) ) Engine=InnoDB; Now you insert some test data into it: mysql… row has variable length columns and the complete row size exceeds this limit, InnoDB chooses variable length columns for off-page storage…-in-innodb/ for further reference). It’s worth mentioning that this limit applies to the byte-size of values, not the character-size…
Post: Ultimate MySQL variable and status reference list
January 8, 2011 By 4 Comments
….commanual innodb_change_bufferingblogpercona.commanual innodb_checksumsblogpercona.commanual innodb_commit_concurrencyblogpercona.commanual innodb_concurrency_ticketsblogpercona.commanual innodb_data_file_pathblogpercona.commanual Innodb_data_fsyncsblogpercona.commanual innodb_data….commanual innodb_write_io_threadsblogpercona.commanual insert_idblogpercona.commanual interactive_timeoutblogpercona.commanual join_buffer_sizeblogpercona.commanual keep_files_on_createblogpercona.commanual Key…
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) For…: 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… dataset (InnoDB tables) with a Scale Factor of 2 (InnoDB dataset size ~5G). I did not use Scale Factor of 40 (InnoDB dataset size ~95G…
Post: Join Optimizations in MySQL 5.6 and MariaDB 5.5
April 4, 2012 By 4 Comments
… ran it on TPC-H dataset (InnoDB tables) with a Scale Factor of 2 (InnoDB dataset size ~5G). Note that query cache is…_cost_based=off’ optimizer_switch=’batched_key_access=on’ join_buffer_size=6M read_rnd_buffer_size=6M Also note that the following… values for Innodb_buffer_pool_reads and Innodb_data_read. We can see that with appropriately sized buffers less no. of Innodb_buffer_pool…
Post: Shard-Query EC2 images available
May 11, 2011 By 2 Comments
… innodb-io-capacity=1000 innodb-log-buffer-size=32M innodb-log-file-size=128M innodb-open-files=1000 innodb_fast_checksum innodb-purge-threads=1 innodb-read-ahead=linear innodb… 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=64M local-infile=on lock-wait-timeout=300 log-error=/var/log/mysqld-innodb.log max…
Post: Why you should ignore MySQL's key cache hit ratio
February 28, 2010 By 28 Comments
… [baron@localhost ~]$ mysqladmin ext -ri10 | grep Key_reads | Key_reads | 6030962 | | Key_reads | 98 | | Key_reads | 89 | | Key_reads | 104 | This server is doing …about InnoDB tuning? You might be wondering, what about InnoDB tuning? What is the best way to choose an innodb_buffer_pool_size setting…
Post: Statistics of InnoDB tables and indexes available in xtrabackup
September 14, 2009 By 6 Comments
… is that or another index in InnoDB … you had to calculate it yourself by multiplying size of row (which I should add…_day`,`isexternal`), KEY `from_message_id` (`from_message_id`,`link_id`) ) ENGINE=InnoDB AUTO_INCREMENT=26141165 DEFAULT CHARSET=utf8; And size of file… PRIMARY key (which is the table by itself, as InnoDB is clustering data by primary key) takes 497839 pages ( 16KB each) and size of…
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…
