… 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 with latest and greatest MySQL Enterprise 5.0.34). It works just fine until you have less than 4GB worth of key cache used and…
Post: Troubleshooting MySQL Memory Usage
March 21, 2012 By 5 Comments
… MySQL is troubleshooting memory usage. The problem usually starts like this – you 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…
Post: How is join_buffer_size allocated?
July 5, 2010 By 9 Comments
When examining MySQL configuration, we quite often want to know how various buffer sizes are used. This matters because some buffers (sort_buffer_size for… the corresponding buffers are allocated only as big as needed (key_buffer_size). There are many examples of this. What about join_buffer_size…
Post: Should we give a MySQL Query Cache a second chance ?
April 10, 2011 By 64 Comments
…, at least we can use same “cheat” as MySQL 5.5 uses for Innodb Buffer Pool and have multiple MySQL Query Cache instances hashed by… applications. Many applications in MySQL have very large number of queries doing updates through primary key and use primary key for selects. I believe… unsure about though as MySQL Query Cache is most useful for complex expensive queries, for which tracking exact rows used from query syntax…
Post: Getting real life query speeds with MySQL
January 10, 2007 By 10 Comments
… check for query performance improvements followed indexing/query changes or MySQL configuration changes our customers often decide to run the query… 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…. Get large number of similar queries from your MySQL server logs or generate using different user ids etc but make sure you…
Post: MySQL 5.6 vs MySQL 5.5 and the Star Schema Benchmark
March 11, 2013 By 7 Comments
…_blocks_time=1000 on MySQL 5.5.30 and testing multiple buffer pools on MySQL 5.5.30. Finally, MySQL 5.6.10 has…: MySQL 5.5 and 5.6 with a 4G buffer pool instead of the default 128M Rationale: Since O_DIRECT is not used… varchar(15), C_MktSegment varchar(10), KEY(C_Name), KEY(C_City), KEY(C_Region), KEY(C_Phone), KEY(C_MktSegment) ); DROP TABLE IF EXISTS…
Post: Multi Range Read (MRR) in MySQL 5.6 and MariaDB 5.5
March 21, 2012 By 10 Comments
… the importance of the size of the buffer used for sorting the secondary key tuples. If the buffer size is large enough only a… MRR was used in both MySQL 5.6 and MariaDB 5.5. Handler_mrr_rowid_refills counts how many times the buffer used by MRR had to be reinitialized, because the buffer was small and not all index tuples could fit in the buffer…
Post: Why you should ignore MySQL's key cache hit ratio
February 28, 2010 By 28 Comments
… MySQL‘s key cache hit ratio is wrong, even when you hear it from experts. There are two major problems with the key buffer… will use key buffer, key_buffer, and key cache interchangeably. However, I will be careful about the difference between “rate” and “ratio”. In this article, the key…
Post: Shard-Query EC2 images available
May 11, 2011 By 2 Comments
…`), KEY `DepDelay` (`DepDelay`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT=’Contains all avaialble data from 1988 to 2010′; mysql> use ontime1; Database changed mysql> show… socket=/tmp/mysql-inno.sock [mysqld] socket=/tmp/mysql-inno.sock default-storage-engine=INNODB innodb-buffer-pool-instances=2 innodb-buffer-pool-size…-thread-sleep-delay=0 innodb-use-sys-stats-table innodb-write-io-threads=4 join-buffer-size=16M key-buffer-size=64M local-infile…
Post: Using Multiple Key Caches for MyISAM Scalability
November 26, 2008 By 8 Comments
… to initialize key buffers according to table sizes and activity (in this case taken with 50-50 weight though you may use other formula), while maintaining the restriction on the sum key buffer size (4000000000 in this case… mysql directly as explained here to create key caches. Now you can use much more simple command to assign tables to the key caches: mysql…
