… the MySQL configuration. MySQL Configuration …buffer_pool_size = 55G innodb_log_file_size = 1G innodb_log_files_in_group = 4 innodb_buffer…KEY (`transactionid`), KEY `marketsegment` (`price`,`customerid`), KEY `registersegment` (`cashregisterid`,`price`,`customerid`), KEY … secondary indexes or even …
Comment: Benchmarking single-row insert performance on Amazon EC2
May 16, 2012 By 11 Comments
…key, I would expect Inno to fill the B-tree pretty efficiently. With a 55GB buffer… 4 times bigger. So around 300M or 400M rows, I would expect Inno…a memory wall. Given that Memory sizes are perhaps 10 times bigger …wrote http://bazaar.launchpad.net/~mdcallag/mysql-patch/mytools/annotate/head%3A/bench…
Post: Join Optimizations in MySQL 5.6 and MariaDB 5.5
April 4, 2012 By 2 Comments
… w/ buffer size 6M (Hash Join and Key-ordered Scan disabled) You can see that the lowest query time is for MySQL 5.6… MySQL 5.6 MySQL 5.6 w/ join_buffer_size=6M & read_rnd_buffer_size=6M MariaDB 5.5 MariaDB 5.5 w/ join_buffer_size=6M & mrr_buffer_size… in the manual either for MariaDB or MySQL, but you need to appropriately increase read_rnd_buffer_size/mrr_buffer_size because these have an impact…
Post: Multi Range Read (MRR) in MySQL 5.6 and MariaDB 5.5
March 21, 2012 By 10 Comments
… lot of point primary key lookups to one or more range primary key lookup. Thereby, converting Random access to one or more sequential access… is the combined size of the secondary key tuples fetched, and N is the buffer size. In MySQL 5.6 the buffer size used by MRR can…_keys=on’ (only on MariaDB 5.5) optimizer_switch=’mrr_cost_based=off’ read_rnd_buffer_size=4M (only on MySQL 5.6) mrr_buffer_size…
Post: Troubleshooting MySQL Memory Usage
March 21, 2012 By 3 Comments
… have configured MySQL to use reasonable global buffers, such as innodb_buffer_size, key_buffer_size etc, you have reasonable amount of connections but yet MySQL takes much more memory than you would expect, causing swapping or…
Post: Index Condition Pushdown in MySQL 5.6 and MariaDB 5.5 and its performance impact
March 12, 2012 By 12 Comments
… in preparation for the talk comparing the optimizer enhancements in MySQL 5.6 and MariaDB 5.5. We are taking a… optimizer enhancement Index Condition Pushdown (ICP). Its available in both MySQL 5.6 and MariaDB 5.5 Now let’s take… for filtering records. For example, suppose you have a key defined as: KEY `i_l_partkey` (`l_partkey`,`l_quantity`,`l_shipmode…
Comment: Too many connections? No problem!
March 15, 2012 By 28 Comments
… join_buffer_size=6M key_buffer_size=12M server-id = 1 log-bin=/data/mysql/drbd expire_logs_days = 5 max_binlog_size = 100M [client] socket=/data/mysql/mysql… security aspect and not an attack, will have any suggestions or ideas of what might be causing thereby increase the number…
Post: Ultimate MySQL variable and status reference list
January 8, 2011 By 4 Comments
…MySQL …or look for content in the Percona documentation or…key_buffer_sizeblogpercona.commanual key_cache_age_thresholdblogpercona.commanual key_cache_block_sizeblogpercona.commanual key_cache_division_limitblogpercona.commanual Key_read_requestsblogpercona.commanual Key_readsblogpercona.commanual Key…
Post: Some little known facts about Innodb Insert Buffer
January 13, 2009 By 11 Comments
… Insert buffer only can work for non-unique keys …buffer pool same as other pages. This property allows insert buffer to survive transaction commits and even MySQL…size” is size (in pages) of insert buffer which is not merged. The fact size…or 1.5 hours on this server… and there are insert buffers…
Post: Getting real life query speeds with MySQL
January 10, 2007 By 10 Comments
To check for query performance improvements followed indexing/query changes or MySQL configuration changes our customers often decide to run the query… of page caches inside MySQL (key_buffer and innodb_buffer_pool_size) which can be cleared by MySQL restart. Even if you restart MySQL server there is…
