…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…server. There is no comparable variables of how many user variables are allocated (and how much memory they use). Memory Tables MEMORY …
Post: Join Optimizations in MySQL 5.6 and MariaDB 5.5
April 4, 2012 By 2 Comments
… optimizer_switch=’batched_key_access=on’ join_buffer_size=6M read_rnd_buffer_size=6M Also note…-memory workload Now let’s see how effective are the join optimizations …much of a difference to in-memory workload. Also BKA relies on both the join_buffer_size and the read_rnd_buffer_size/mrr_buffer_size…
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…
Post: How much overhead is caused by on disk temporary tables
August 16, 2007 By 26 Comments
… see how much overhead do on disk temporary tables cause compared to MEMORY tables. To have things comparable I used medium size table… default key_buffer_size which is not large enough to fit all key blocks from temporary table index, so we get a lot of key…> set global key_buffer_size=8000000; Query OK, 0 rows affected (0.03 sec) mysql> set global max_heap_table_size=1000000000; Query OK…
Post: How much memory can MySQL use in the worst case?
February 12, 2009 By 17 Comments
… possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections”. This was never true — … 100M user variable and a 100M lock key. Note how long it took to run these statements…s not something that’s worth worrying about much, in my opinion. I think it’s …
Post: Why you should ignore MySQL's key cache hit ratio
February 28, 2010 By 27 Comments
… second kind of time information you’re lacking is how much time each buffer hit or miss takes. If you approach application performance… how important those tables are to you, how big the indexes are on disk, and so on. Raise the key_buffer_size until, when the buffer is full, Key_reads/Uptime…
Post: Using Multiple Key Caches for MyISAM Scalability
November 26, 2008 By 8 Comments
…the sum key buffer size (4000000000 in this case) and actual index size: select concat(“SET GLOBAL “, t.table_schema,”_”,t.table_name,”.key_buffer_size=”,round… So going through complex or not multiple key cache creation exercise you probably wonder how much performance gains should you expect. In …
Post: What exactly is read_rnd_buffer_size
July 24, 2007 By 4 Comments
…but it does not really tell you how exactly read_rnd_buffer_size works as well as which layer…row pointers together with key value – which are offsets for MyISAM and primary key values for Innodb or…buffer in the sorted order – it can be pretty much sequential if you’re lucky. The read_rnd_buffer_size…
Post: Predicting how long data load would take
May 24, 2007 By 12 Comments
…_insert_tree_size increase myisam_sort_buffer_size or key_buffer_size increase. Innodb typically needs large innodb_buffer_pool_size and large innodb_log_file_size to perform… 3600; du -sh” in the database data directory to see how much data is loaded per hour (assuming your Innodb tablespace did…
Post: How much memory Innodb locks really take ?
July 13, 2006 By 11 Comments
… lock table, located in the buffer pool where small record allocated …`j` varchar(255) default NULL, PRIMARY KEY (`i`), KEY `j` (`j`) ) ENGINE=InnoDB … locked row; Now lets see how much exclusinve locks take: mysql> begin; Query…1878960 6093 lock struct(s), heap size 503104 MySQL thread id 7429, …
