October 23, 2014

What exactly is read_rnd_buffer_size

Looking for documentation for read_rnd_buffer_size you would find descriptions such as “The read_rnd_buffer_size is used after a sort, when reading rows in sorted order. If you use many queries with ORDER BY, upping this can improve performance” which is cool but it does not really tell you how exactly read_rnd_buffer_size works as well as which […]

Beware large Query_Cache sizes

During last couple of months I had number of consulting projects with MySQL Query Cache being source of serious problems. One would see queries both for MyISAM and Innodb tables mysteriously stalling for few seconds with “NULL” in State column. These could be troubleshooted to waiting on Query Cache which at the same time had […]

System size and Performance Optimization

Performance optimization is never ending story, you can virtually always find something else to optimize but while on generic system, not tuned by expert you often can get significant performance increase in the matter of hours further performance improvements become more and more time consuming and expensive and gains smaller. This observation does not only […]

Beware: key_buffer_size larger than 4G does not work

I was working with customer today which has MySQL on a system with 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 with latest and greatest MySQL Enterprise 5.0.34). It works just fine […]

TMP_TABLE_SIZE and MAX_HEAP_TABLE_SIZE

We all know disk based temporary tables are bad and you should try to have implicit temporary tables created in memory where possible, do to it you should increase tmp_table_size to appropriate value and avoid using blob/text columns which force table creation on the disk because MEMORY storage engine does not support them Right ? […]

Undo area size restriction needed for Innodb

As you can read from my Innodb Architecture and Performance Optimization presentation Innodb automatically manages undo area in system tablespace so you never need to care about it. I present it as positive feature reducing administration effort needed but it also can cause a troubles as it happened for me today: InnoDB: 11 transaction(s) which […]

Choosing proper innodb_log_file_size

If you’re doing significant amount of writes to Innodb tables decent size of innodb_log_file_size is important for MySQL Performance. However setting it too large will increase recovery time, so in case of MySQL crash or power failure it may take long time before MySQL Server is operational again. So how to find the optimal combination […]

InnoDB page size

As maybe you know InnoDB uses hard page size 16Kb for datafiles and for buffer pool. However this size can be changed if you need it for your workload. go to file innobase/include/univ.i, lines:

UNIV_PAGE_SIZE is page size (as you see – default value 16Kb). Possible values for UNIV_PAGE_SIZE is 8K, 16K, 32K, 64K. […]

MySQL community set to meet at Percona Live London 2014

The countdown is on for Europe’s largest annual MySQL event, Percona Live London 2014. The two days of technical tutorials and sessions, November 3-4, will focus on the latest MySQL industry trends, news, best practices – and a look at what’s on the near- and long-term horizon within the global MySQL ecosystem. Percona Live London […]

Autumn: A season of MySQL-related conferences. Here’s my list

Autumn is a season of MySQL-related conferences and I’m about to hit the road to speak and attend quite a  few of them. This week I’ll participate in All Things Open, a local conference for me here in Raleigh, N.C. and therefore one I do not have to travel for. All Things Open explores open source, […]