mysql scaling questions

[Kris Kennaway]

Gergely CZUCZY wrote:
> On Fri, Jan 04, 2008 at 08:33:08PM +0100, Kris Kennaway wrote:
>> Gergely CZUCZY wrote:
>>> On Thu, Jan 03, 2008 at 10:52:39PM +0100, Kris Kennaway wrote:
>>>> Gergely CZUCZY wrote:
>>>>
>>>>>> * Compare to my config file here:
>>>>>>
>>>>>> http://people.freebsd.org/~kris/scaling/my.cnf
>>>>>>
>>>>>> The default mysql config has very poor performance for innodb (you need at least innodb_thread_concurrency = 0 to disable some 
>>>>>> mysql brain-death).  Maybe tuning is required for myisam also.
>>>>>>
>>>>>> * Also make sure you are using identical config settings on the two systems.
>>>>> http://phoemix.harmless.hu/mysql/ verify yourself. Only the paths are
>>>>> updated, but the main parameters are just the same.
>>>> You are not in fact using the same config parameters as I am.  When I use your config file I see a large performance loss when I run 
>>>> locally even with innodb.
>>> So, the updated config is here:
>>> http://phoemix.harmless.hu/mysql/my.cnf.kris
>>> I've migrated the values from your config.
>>> And the plot is here:
>>> http://phoemix.harmless.hu/mysql/rw-kris.png
>>> I"ve also plotted a current production system, which crashes around 96 threads in
>>> the test, or so. That's a Linux-2.6.12 and MySQL 5.0.22, so it should be quite unoptimized
>>> compared to any recent version of anything.
>>> As it seems, you were right. These adjustments made a very slight performance boost, around
>>> 5%. But it's still lagging behind.
>>> Again, this is MyISAM, not innodb. The "tc4" and "tc16" strings mean the thread_concurrency
>>> is either set to 4 or 16. Your config had 16, but I've googled for it, and some resources
>>> suggested setting that to CPUs*2, and that's 4 in my case.
>>> I've updated the MySQL version on FreeBSD to 5.0.51, and will try to get this version
>>> on linux also, and do a comparision with that one, too.
>>> Sincerely,
>>> Gergely Czuczy
>>> mailto: gergely.czuczy at harmless.hu
>> OK.  The caching problem with myisam is unrelated to the other config changes then.
> 
> I have to finish my test, sorry for not being able to give more results.
> The boss decided to put back this box to the serverfarm, and since we're
> not using FreeBSD, just I'm a bsd-guy, I won't be able to do anything farther
> on on this topic.
> 
> I hope I did something useful with this tests of my at all.
> 
> And thank you all for helping with all the ideas, especially to
> you, Kris.

Thanks for bringing it up.  If anyone else is interested it would be 
good to figure out if there is a way to improve the myisam caching 
behaviour.  It is likely that performance will also improve once the 
lockmgr changes are completed.

Kris