… – you have configured MySQL to use reasonable global buffers, such as innodb_buffer_size, key_buffer_size etc, you have reasonable amount of… where memory could be allocated but it is a lot larger problems with MySQL 5.5 with addition of user variables… (406936 + 18144) Dictionary memory allocated 2623331 Buffer pool size 7864319 Buffer pool size, bytes 128849002496 Free buffers 1 Database pages 8252672 Old database…
Post: Are larger buffers always better ?
June 6, 2006 By 9 Comments
Sometimes I see people thinking about buffers as “larger is always better” so if “large” MySQL sample configuration is … memory as well as cold provide quite unexpected improvements. sort_buffer_size – recently I worked with case which was running much… some 10 distinct c values. read_buffer_size and read_rnd_buffer_size – These are buffers used by MyISAM to perform reads, in…
Post: Choosing innodb_buffer_pool_size
November 3, 2007 By 38 Comments
… Buffer Pool. This of course assumes your database is large so you need large buffer pool, if not – setting buffer pool a bit larger than your database size will be enough. You also should account for growth of course. You need buffer pool a bit (say 10%) larger than your data (total size of Innodb…
Post: How to estimate time it takes Innodb to Recover ?
May 9, 2007 By 7 Comments
… the larger database you’ll have the more scattered records will be in the end thus larger database tend to recover longer. Buffer Pool Size This affects recovery time from two parts aspects – first if you have small buffer… case. It also affects recovery speed during recovery time – the larger buffer pool you have the more pages it can hold and…
Post: Adaptive checkpointing
November 13, 2008 By 9 Comments
… setting ‘innodb_max_dirty_pages_pct’ smaller or setting the larger buffer pool size. We might be never at a loss about… transaction log file synchronously, InnoDB is allowed to treat its buffer pool as write-back cache. The transaction log from the… files, because InnoDB uses the transaction log circulately. But, the larger transaction log file may not solve the problem of dirty…
Post: Some little known facts about Innodb Insert Buffer
January 13, 2009 By 11 Comments
… unique. Insert buffer is allocated in the Innodb system table space. Even though it is called “buffer” similar to “doublewrite buffer” it is really the space in the tablepace. Though it can be cached in the buffer pool… there are insert buffers which are 10x and 100x larger than this. Innodb unfortunately offers no control for insert buffer while it surely…
Post: Read Buffers, mmap, malloc and MySQL Performance
September 12, 2007 By 8 Comments
… found kernel has to setup the page tables. The larger the buffer is allocated the more pages needs to be set up… at least increase memory consumptions as deallocating of these large buffers will not instantly return data to the OS. Plus I… pages. These already can be used for Innodb Buffer Pool and Key Buffer but could be used for other large allocations. Finally…
Comment: How fast can you sort data with MySQL ?
August 19, 2007 By 26 Comments
… between 100-256k were fastest while a sort_buffer_size of 512k or larger showed a slowdown (not much but still repeatable… comments for http://www.mysqlperformanceblog.com/2006/06/06/are-larger-buffers-always-better/ ) For example based on your knowledge and findings…
Comment: Are larger buffers always better ?
June 6, 2006 By 9 Comments
[...] Are larger buffers always better ?: “Sometimes I see people thinking about buffers as ‘larger is always better’ so if ‘large’ MySQL sample configuration is designed for 2GB and they happen to have 16, they would simply multiply all/most values by 10 and hope it will work well. [...]
Post: Beware: key_buffer_size larger than 4G does not work
February 14, 2007 By 17 Comments
… some 64GB or RAM running MyISAM, so they set key_buffer_size to 16G… and every few days MySQL crashes. Why ? Because key_buffer_size over 4GB in size is not really supported (checked… only fix was not made available but even maximum key_buffer_size was not restricted to safe limit. Former would be…
