mysql scaling questions

[Gergely CZUCZY]

On Sun, Dec 30, 2007 at 02:18:31PM +0100, Kris Kennaway wrote:
> Gergely CZUCZY wrote:
> >On Sat, Dec 01, 2007 at 12:22:34PM -1000, Jeff Roberson wrote:
> >>On Sat, 1 Dec 2007, Gergely CZUCZY wrote:
> >>
> >>>On Sat, Dec 01, 2007 at 04:06:55PM -0500, Mike Tancsa wrote:
> >>>>At 03:56 PM 12/1/2007, Gergely CZUCZY wrote:
> >>>>>I don't quite understand the question. It's the very same box, with
> >>>>>a dualboot configuration.
> >>>>Fire up the 3ware controller's RAID management software and make sure the same write caching strategy is set for FreeBSD and Linux. 
> >>>>The
> >>>>driver my default to different values.
> >>>>
> >>>>i.e. under "controller settings" make sure "write cache" and "queuing" are the same values for linux and freebsd.
> >>>Let's get back to this on monday. I'm at home now, and the
> >>>box is at me workplace, still running a test (i can't reboot it).
> >>Also, can you verify with a read-only test to see where it's at?  I have not tested writes with that many threads.  I notice mysql 
> >>goes much faster with a fresh table too.  So can you blow away and recreate the sysbench tables and then do read-only?  If that is 
> >>much slower we'll know there is some configuration problem or similar.
> >It will all be available here:
> >http://phoemix.harmless.hu/mysql/
> 
> Hi,
> 
> Sorry for the late reply, I was on vacation.  I have a couple of comments about this:
> 
> * You are using some very old mysql versions and trying to compare between diferent versions.  New minor releases in the 5.0 series had 
> very different performance, and you really need to be using at least 5.0.45 (I didn't notice a performance change between 5.0.45 and 
> 5.0.51 but there was a big improvement in every release up to .45.  In addition, versions older than (I think) .37 have serious 
> performance bugs).
> 
> I appreciate that you might be constrained by local requirements, but it's really not meaningful to compare different mysql versions if 
> your goal is to study OS performance.
It'd be a PITA to install the both versions. Maybe now, that the ports freeze is over, i can
do something. But honestly, every mysql version was faster on linux, than eny on FreeBSD,
even the .22 one was faster. We start work on Jan02, I will see what can I do.

> 
> * What database engine are you using?  I have only tested with innodb but maybe you are using myisam?  Please provide your exact 
> sysbench command lines.
MyISAM, of course. InnoDB is not any good with replication, and we need backup.

> * 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.

> * With the above, please compare read-only mode also (I think Jeff already asked you about this but I didnt see a reply).  That will 
> allow us to calibrate what is going on.
http://phoemix.harmless.hu/mysql/ro-all.png
The graph is mistitled as Read-write, but it's a readonly test.


Sincerely,

Gergely Czuczy
mailto: gergely.czuczy at harmless.hu

-- 
Weenies test. Geniuses solve problems that arise.
-------------- next part --------------
A non-text attachment was scrubbed...
Name: not available
Type: application/pgp-signature
Size: 2771 bytes
Desc: not available
Url : http://lists.freebsd.org/pipermail/freebsd-performance/attachments/20071230/2355dcb9/attachment.pgp