MySQL Bugs: #13915: MyISAM is not scalable on multi-cpu box

Bug #13915	MyISAM is not scalable on multi-cpu box
Submitted:	11 Oct 2005 8:05	Modified:	11 Apr 2008 17:30
Reporter:	Vadim Tkachenko	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server: MyISAM storage engine	Severity:	S3 (Non-critical)
Version:	5.0.12	OS:	Linux (Linux RedHat AS 3)
Assigned to:	Assigned Account	CPU Architecture:	Any

Description:
I tested MyISAM under bulk queries:
count-range-like queries:
SELECT count(id) FROM sbtest WHERE id BETWEEN N AND M AND C NOT LIKE 'N'
count-range queries:
SELECT count(id) FROM sbtest WHERE id BETWEEN N AND M
sum-range queries:
SELECT sum(k) FROM sbtest WHERE id BETWEEN N AND M
with range diapason = 1000, 10000, 50000

Linux quadxeon 2.4.21-15.ELsmp
RedHat AS 3
4 CPU (with HT):
model name : Intel(R) XEON(TM) MP CPU 2.00GHz
stepping : 2
cpu MHz : 1988.344
cache size : 512 KB

MemTotal: 3997756 kB

MyISAM exhibits equal results for 1,2,4,8,16 threads, although I used 4-way box.

How to repeat:
I used sysbench (sysbench.sf.net).

main script & mysql options attached.

Suggested fix:
Reasons of bad scalabilty:
rw_locks inside mi_rnext, mi_rkey, mi_range
intensive pread syscalls
key_cache mutex

Vadim, can you please add the patches?

Hi,

we are testing MySQL on a new intel 1950 series and we are achieving performance worse than with old hardware architecture (Dual CPU).

new intel 1950 hardware architecture:

dual quadcore Intel(R) Xeon(R) CPU E5320 @ 1.86GHz 64 bit
8 GB ram

OS: Scientific Linux 5.0 kernel 2.6.18-53.1.4.el5

Mysql tested releases:

MySQL-server-community-5.0.45-0.rhel5.x86_64 
mysql-server-5.0.22-2.1.x86_64

The TYPE of all tables is MyISAM

In both releases cases we set the in /etc/my.cf:

set-variable    = key_buffer=384M
set-variable    = max_allowed_packet=24M
set-variable    = table_cache=512
set-variable    = sort_buffer=2M
set-variable    = record_buffer=2M
set-variable    = thread_cache=8
set-variable    = tmp_table_size=500M
set-variable    = thread_concurrency=8
set-variable    = myisam_sort_buffer_size=64M

We run 1,2,4,8 parallel DBAccessProcess, the results (min:sec):
1 process: old 1:09, new 1:20
2 processes: old 1:48, new 3:50
4 processes: old 2:47, new 12:38
8 processes: old 6:12 , new 23:43

The performances with new hardware get worse with the increase of the number of parallel processes.

Is it possible to estimate a when the problem (in what release) will be solved?
(hard question)

Thanks,

Armando & Luigi

Hi, I'm not a performance expert, but I dare to say that your results are so bad that even a fix for this bug would not cure it. I guess that you could suffer from bad configuration, or perhaps even bad application design. Admittedly these are only wild speculations. There is too little information for me to give better advice.

However, one thing I noticed is the small key buffer. You are using MyISAM tables only. And you spend less than 5% of your RAM for the key cache. Don't you use indexes? Or only a few? Either your key cache suffers from many evictions or you don't have the indexes you need. If you are doing mostly table scans, parallel processing does not help, but randomizes disk accesses to death. Please watch your key cache statistics, e.g. with "show status like 'key%';".

Please do also watch CPU utilization and disk I/O. I dare to guess that your CPU load decreases with the number of processes, while the I/O load remains at the maximum, the disk can handle.

Re table_cache. Please watch "show status like 'open_table%';". If it comes near to your table cache size, please increase it.

Similar applies to the thread cache: "show status like 'threads%';".

For thread_concurrency I recommend to increase it to the number of parallel connections plus some system threads.

I wonder about the big tmp tables. Are you using them intensely? In that case I'd say you have too little memory. Of course this depends on the number and size of your tables. But intuitively I would not go with less than 4GB per core. And you don't have just a single SATA drive, do you?

Hi Vadim, can you please send me the patches that you used to do the benchmarks?

Ingo,

I did not use patches for benchmarks.

What do you mean by patches ?

Sorry I did not answer before - I did not get emails from bug system.

Hi,
I'm referring to my last comment about MySQL performance problem in dual quad core architecture:

we upgrade the MySQL from version 5.0.45-0.rhel5 to version 5.0.51a-0.rhel5

[root@bbr-sql ~]# rpm -qa | grep -i mysql
MySQL-server-community-5.0.51a-0.rhel5.x86_64
MySQL-shared-community-5.0.51a-0.rhel5.x86_64
MySQL-client-community-5.0.51a-0.rhel5.x86_64
[root@bbr-sql ~]#

and apparently without any understood reason the system solves the performance problem. It was quite unexpected because no patch was announced in change log involved in this issue.

Do you have an idea for this behaviour?

Thanks a lot,  Armando & Luigi

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

Amando, Vadim,

did I understand you right?
Is it ok, when we close this issues because its fixed in newest version.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".