background preloader

Config

Facebook Twitter

Max_allowed_packet. The max_allowed_packet variable is used to control the maximum size of a query sent to MySQL. It’s function is fairly well defined in the manual but there is a significant gotcha that exists when changing the size of max_allowed_packet while using replication. When the replication threads are created the global max_allowed_packet value is copied in to the thread context like doing a set session command in the slave connection. This is done because replication enforces max_allowed_packet a bit differently than other threads. It accounts for both the size of the packet and the overhead of the replication header.

Utf8_general_ci vs utf8_unicode_ci. There are at least two important differences: Accuracy of sorting utf8_unicode_ci is based on the Unicode standard for sorting, and sorts accurately in a very wide range of languages. utf8_general_ci comes very close to correct Unicode sorting in many common languages, but has a number of inaccuracies in some languages making in unsuitable for correct sorting in those languages.Performance utf8_general_ci is faster at comparisons and sorting, because it takes a bunch of performance-related shortcuts. utf8_unicode_ci uses a much more complex comparison algorithm which aims for correct sorting according in a very wide range of languages.

utf8_general_ci vs utf8_unicode_ci

This makes it slower to sort and compare large numbers of fields. Unicode defines complex sets of rules for how characters should be sorted. These rules need to take into account language-specific conventions; not everybody sorts their characters in what we would call 'alphabetical order'. What should you use?

Innodb buffer. November 3, 2007 by Peter Zaitsev39 Comments My last post about Innodb Performance Optimization got a lot of comments choosing proper innodb_buffer_pool_size and indeed I oversimplified things a bit too much, so let me write a bit better description.

innodb buffer

Innodb Buffer Pool is by far the most important option for Innodb Performance and it must be set correctly. I’ve seen a lot of clients which came through extreme sufferings leaving it at default value (8M). So if you have dedicated MySQL Box and you’re only using Innodb tables you will want to give all memory you do not need for other needs for Innodb 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 may choose to set buffer pool as if your database size is already larger than amount of memory you have – so you do not forget to readjust it later.

The third important memory consumer would be OS cache. Innodb_flush_method. Properties: Description: This variable changes the way InnoDB open files and flush data to disk and is should be considered as very important for InnoDB performance.

innodb_flush_method

By default, InnoDB uses fsync() (without O_DSYNC) to flush both log and data files. Setting this variable to O_DIRECT will result in InnoDB using O_DIRECT while opening files and fsync() to flush both data and log files. O_DIRECT is useful when an application maintains it's own caching mechanism which is very well true for MySQL/InnoDB.

O_DSYNC makes InnoDB to use O_SYNC to open and flush the log files, and uses fsync() to flush data files. For Windows, the flush method is always async_unbuffered. Recommendation: If you are not doing anything unusual like SAN storage etc (which otherwise also you should reconsider before doing), always use O_DIRECT for this. Read More: Hope you enjoyed reading this. System performance benchmark. Sort. August 18, 2007 by Peter Zaitsev26 Comments I took the same table as I used for MySQL Group by Performance Tests to see how much MySQL can sort 1.000.000 rows, or rather return top 10 rows from sorted result set which is the most typical way sorting is used in practice.

sort

I tested full table scan of the table completes in 0.22 seconds giving us about 4.5 Million of rows/sec. Obviously we can’t get sorted result set faster than that. I placed temporary sort files on tmpfs (/dev/shm) to avoid disk IO as a variable as my data set fits in memory anyway and decided to experiment with sort_buffer_size variable. Datadir.