… even higher buffer sizes. set sort_buffer_size=1000000; mysql> select * from gt order by i desc limit 10; 10 rows in set (0.70 sec) mysql> show… is inefficient with large sort_buffer_size ? mysql> set sort_buffer_size=100000000; Query OK, 0 rows affected (0.00 sec) mysql> select * from gt order by…
Post: Why you should ignore MySQL's key cache hit ratio
February 28, 2010 By 28 Comments
… the key_buffer_size until, when the buffer is full, Key_reads/Uptime reduces to a number you’re comfortable with. Set key_buffer_size really… your IO system’s capabilities. MySQL doesn’t have good instrumentation for scientifically choosing a key_buffer_size setting, but there are many unscientific…
Post: MySQL 5.5 and MySQL 5.6 default variable values differences
February 21, 2013 By 8 Comments
… | | SORT_BUFFER_SIZE | 2097152 | 262144 | | LC_MESSAGES_DIR | /mnt/nfs/dist/mysql-5.5.30-linux2.6-x86_ | /mnt/nfs/dist/mysql-5.6.10-linux-glibc2. | | MAX_ALLOWED_PACKET | 1048576 | 4194304 | | JOIN_BUFFER_SIZE…_blocks_time now set to 1000 making Innodb Buffer Pool Size scan resistant by default. Very welcome change ! thread_cache_size is enabled by…
Post: How much memory can MySQL use in the worst case?
February 12, 2009 By 17 Comments
… “global buffers” that aren’t really global. Suppose you set sort_buffer_size to 128K — I can quite well connect and say “set @@sort_buffer_size := 1 * 1024 * 1024 * 1024;” and my sort_buffer_size is… for user locks! I set my max_allowed_packet bigger and ran the following on my laptop: mysql> set @a := repeat(‘a’, 1024…
Post: MySQL: what read_buffer_size value is optimal ?
September 17, 2007 By 25 Comments
… generally hear about read_buffer_size tuning ? If you want fast full table scans for large table you should set this variable to some high value. Sample my.cnf values on large memory sizes recommend 1M settings and MySQL built-in… row in set (51.77 sec) Here are the results I’ve got: read_buffer_size impace on scan performanceread_buffer_sizeTime (sec)820045…
Post: Troubleshooting MySQL Memory Usage
March 21, 2012 By 5 Comments
…MySQL to use reasonable global buffers, such as innodb_buffer_size, key_buffer_size etc, you have reasonable amount of connections but yet MySQL… sum(data_length+index_length) | +——————————-+ | 126984 | +——————————-+ 1 row in set (0.98 sec) This however will …
Post: Can we improve MySQL variable handling ?
February 18, 2012 By 8 Comments
…: mysql> set global sort_buffer_size=DEFAULT; Query OK, 0 rows affected (0.00 sec) mysql> select @@global.sort_buffer_size; +—————————+ | @@global.sort_buffer_size | +—————————+ | 2097152 | +—————————+ 1 row in set… value Right now I can select: mysql> select @@global.sort_buffer_size; +—————————+ | @@global.sort_buffer_size | +—————————+ | 2097152 | +—————————+ 1 row in set (0.00 sec) If I…
Post: Impact of the sort buffer size in MySQL
October 25, 2010 By 8 Comments
… parameter sort_buffer_size is one the MySQL parameters that is far from obvious to adjust. It is a per session buffer that is… 1 1000` do START=`date +%s.%N` OUT=`mysql -e “set session sort_buffer_size=32*1024*$i;select * from sorttest order by data… sort merge passes. For the small values of the sort buffer size, below 440KB, there are many sort merge passes and the…
Post: Review of MySQL 5.6 Defaults Changes
October 8, 2012 By 10 Comments
…great summary of defaults changes in MySQL 5.6 compared to MySQL 5.5 In general there are… means it makes it even more important to set this variable to higher values for sessions …with simple and complicated queries efficiently automatically. join_buffer_size = 256k The default is now double of what…
Post: MySQL Session variables and Hints
December 28, 2006 By 2 Comments
… to require large sort you can do SET sort_buffer_size=50000000 before executing query and SET sort_buffer_size=DEFAULT after executing the query. I noticed in production hints are used much more frequently than setting session… it is not created dynamically some complex way) – adding extra MySQL calls with possibly their own error control is more complicated…
