Re: mysql scaling questions

Kris Kennaway wrote:
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@xxxxxxxxxxx
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

Hi Kris,

We are currently working one a testing various versions of MySQL. I may be able to put you in touch with the guy in charge of the project (on Monday) and see if there is anything he can bring to the table.

Tom
_______________________________________________
freebsd-performance@xxxxxxxxxxx mailing list
http://lists.freebsd.org/mailman/listinfo/freebsd-performance
To unsubscribe, send any mail to "freebsd-performance-unsubscribe@xxxxxxxxxxx"

Relevant Pages

  • Re: MySQL 4.0.18-32 RPM on SuSE 9.1 issue
    ... because simply make innodb available in the config ... daemon at startup (rcmyslq or /etc/init.d mysql) ...
    (alt.os.linux.suse)
  • RE: PHP5 and MySQL
    ... I did make config. ... runs config first and then does the install. ... Subject: PHP5 and MySQL ...
    (freebsd-questions)
  • Re: mysql scaling questions
    ... Gergely CZUCZY wrote: ... The default mysql config has very poor performance for innodb. ... Also make sure you are using identical config settings on the two systems. ... When I use your config file I see a large performance loss when I run locally even with innodb. ...
    (freebsd-performance)
  • Re: mysql scaling questions
    ... Gergely CZUCZY wrote: ... The default mysql config has very poor performance for innodb. ... Also make sure you are using identical config settings on the two systems. ... When I use your config file I see a large performance loss when I run locally even with innodb. ...
    (freebsd-performance)
  • Re: installing MySQL with FreeBSD pkg_add
    ... your new config in /var/db/mysql and make any necessary adjustments. ... There's some other configs in /usr/local/share so if you need something other than the small configuration file copy that one over. ... I am now trying to install MySQL. ...
    (freebsd-questions)