… MySQL 5.6 which takes 0.16s less as compared to MySQL 5.5 While with join_buffer_size set to 6M and read_rnd_buffer_size set to 6M, the query time for MySQL 5… MySQL 5.5 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…
Post: Multi Range Read (MRR) in MySQL 5.6 and MariaDB 5.5
March 21, 2012 By 10 Comments
… read_rnd_buffer_size, while MariaDB introduces a different variable to control the MRR buffer size mrr_buffer_size. Both buffer sizes default to 256K in MySQL 5.6… lookups as well. But this works only with joins and specifically with Block Access Join Algorithms. So I am not going to…
Comment: Too many connections? No problem!
March 15, 2012 By 28 Comments
… query_cache_size=32M thread_cache_size=30 table_cache=4096 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…
Post: How is join_buffer_size allocated?
July 5, 2010 By 9 Comments
… examining MySQL configuration, we quite often want to know how various buffer sizes are used. This matters because some buffers (sort_buffer_size for example… needed (key_buffer_size). There are many examples of this. What about join_buffer_size? I saw a my.cnf with a 128M join_buffer_size the other…
Post: Ultimate MySQL variable and status reference list
January 8, 2011 By 4 Comments
… constantly referring to the amazing MySQL manual, especially the option and variable reference table. But…io_threadsblogpercona.commanual insert_idblogpercona.commanual interactive_timeoutblogpercona.commanual join_buffer_sizeblogpercona.commanual keep_files_on_createblogpercona.commanual Key_blocks…
Post: MySQL Server Variables - SQL layer or Storage Engine specific.
June 8, 2006 By 14 Comments
… first MySQL versions did not bother naming variables with storage engines prefix. This is why we have key_buffer, not “myisam_key_buffer… currently works with MyISAM tables these are MyISAM related. join_buffer_size Buffer used for joins without indexes and few other cases. It is used…
Post: Shard-Query EC2 images available
May 11, 2011 By 2 Comments
… socket=/tmp/mysql-inno.sock [mysqld] socket=/tmp/mysql-inno.sock default-storage-engine=INNODB innodb-buffer-pool-instances=2 innodb-buffer-pool-size=5600M…-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…
Comment: How much memory can MySQL use in the worst case?
February 12, 2009 By 17 Comments
…stack + join_buffer_size + sort_buffer_size + binlog_cache_size…) is so much misleading. In particular, the join_buffer_size, sort_buffer_size are interesting to note: sort_buffer_size … stick with realistic approximations, and hope to allocate MySQL a little *less* than you would usually allow for…
Post: Side load may massively impact your MySQL Performance
November 13, 2011 By 7 Comments
… buffer pool contents. mysql [localhost] {msandbox} (information_schema) > select concat_ws(‘.’, t.schema, t.name, i.name) as index_name, sum(data_size… data_size_mb from innodb_sys_tables as t inner join innodb_sys_indexes as i using(table_id) inner join innodb_buffer_pool… experience. Summary As we can see in default configuration MySQL has buffer pool which can be easily washed away by large table…
Post: Helgrinding MySQL with InnoDB for Synchronisation Errors, Fun and Profit
November 24, 2011 By 2 Comments
…0x5e06270 is 0 bytes inside a block of size 800000 alloc’d ==9090== at 0x4C28FDF: … The RW lock spin stats join the company of the buffer pool and I/O stats. …JOIN*, enum_schema_table_state) (sql_show.cc:6238) ==9090== by 0x6A5E0C: JOIN::exec() (sql_select.cc:1863) ==9090== by 0x6A7D72: mysql…
