maria-developers team mailing list archive

[worklog-noreply@xxxxxxxxxxxx]

-----------------------------------------------------------------------
                              WORKLOG TASK
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
TASK...........: Fix table_cache negative scalability
CREATION DATE..: Fri, 18 Dec 2009, 16:31
SUPERVISOR.....: Bothorsen
IMPLEMENTOR....: 
COPIES TO......: 
CATEGORY.......: Server-Sprint
TASK ID........: 73 (http://askmonty.org/worklog/?tid=73)
VERSION........: WorkLog-3.4
STATUS.........: Un-Assigned
PRIORITY.......: 60
WORKED HOURS...: 0
ESTIMATE.......: 0 (hours remain)
ORIG. ESTIMATE.: 0

PROGRESS NOTES:



DESCRIPTION:

Fix the problem described in this blog entry:
http://www.mysqlperformanceblog.com/2009/11/16/table_cache-negative-scalability/

You can read the blog, or the text below.

--- quoted text ---

November 16, 2009
table_cache negative scalability 
Posted by peter | Vote on Planet MySQL 

Couple of months ago there was a post by FreshBooks on getting great performance
improvements by lowering table_cache variable. So I decided to investigate what
is really happening here.

The “common sense” approach to tuning caches is to get them as large as you can
if you have enough resources (such as memory). With MySQL common sense however
does not always works – we’ve seen performance issues with large
query_cache_size also sort_buffer_size and read_buffer_size may not give you
better performance if you increase them. I found this also applies to some other
buffers.
 
Even though having previous experience of surprised behavior I did not expect
such a table_cache issue – the LRU for cache management is classics and there
are scalable algorithms to deal with it. I would expect Monty to implement one
of them.

To do the test I have created 100.000 empty tables containing single integer
column and no indexes and when ran SELECT * FROM tableN in the loop. Each table
in such case is accessed only once and on any but first run each access would
require table replacement in table cache based on LRU logic.

MySQL Sandbox helped me to test this with different servers easily.

I did test on CentOS 5.3, Xeon E5405, 16GB RAM and EXT3 file system on the SATA
hard drive. 

MySQL 5.0.85 Created 100.000 tables in around 3min 40 sec which is about 450
tables/sec  – This indicates the “fsync” is lying on this test system as default
sync_frm option is used.

With default table_cache=64 accessing all tables take 12 sec which is almost
8500 tables/sec  which is a great speed. We can note significant writes to the
disk during this read-only benchmark. Why ? Because for MyISAM tables table
header has to be modified each time the table is opened. In this case the
performance was so great because all 100.000 tables data (first block of index)
was placed close by on disk as well as fully cached which made updates to
headers very slow. In the production systems with table headers not in OS cache
you often will see significantly low numbers – 100 or less.

With significantly larger table_cache=16384 (and appropriately adjusted number
of open files) the same operation takes 660 seconds which is 151 tables/sec
which is around 50 times slower. Wow. This is the slow down. We can see the load
becomes very CPU bound in this case and it looks like some of the table_cache
algorithms do not scale well.

The absolute numbers are also very interesting – 151 tables/sec is not that bad
if you look at it as an absolute number. So if you tune table cache is “normal”
case and is able to bring down your miss rate (opened_tables) to 10/sec or less
by using large table_cache  you should do so. However if you have so many tables
you still see 100+ misses/sec while your data (at least table headers) is well
cached so the cost of table cache miss is not very high, you may be better of
with significantly reduced table cache size. 

The next step for me was to see if the problem was fixed in MySQL 5.1 – in this
version table_cache was significantly redone and split in table_open_cache and
table_definition_cache and I assumed the behavior may be different as well.
MySQL 5.1.40

I started testing with default table_open_cache=64 and
table_definition_cache=256 – the read took about 12 seconds very close to MySQL
5.0.85.

As I increased table_definition_cache to 16384 result remained the same so this
variable is not causing the bottleneck. However increasing table_open_cache to
16384 causes scan to take about 780 sec which is a bit worse than MySQL 5.0.85.
So the problem is not fixed in MySQL 5.1, lets see how MySQL 5.4 behaves.
MySQL 5.4.2

MySQL 5.4.2 has higher default table_open_cache so I took it down to 64 so we
can compare apples to apples. It performs same as MySQL 5.0 and MySQL 5.1 with
small table cache.

With table_open_cache increased to 16384 the test took 750 seconds so the
problem exists in MySQL 5.4 as well.

So the problem is real and it is not fixed even in Performance focused MySQL
5.4. As we can see large table_cache (or table_open_cache_ values indeed can
cause significant performance problems. Interesting enough Innodb has a very
similar task of managing its own cache of file descriptors (set by
innodb_open_files) As the time allows I should test if Heikki knows how to
implement LRU properly so it does not have problem with large number. We’ll see.


ESTIMATED WORK TIME

ESTIMATED COMPLETION DATE
-----------------------------------------------------------------------
WorkLog (v4.0.0)