mysql performance on freebsd 7

classic Classic list List threaded Threaded
9 messages Options
Reply | Threaded
Open this post in threaded view
|

mysql performance on freebsd 7

t.pajor
Hello,

I'm using mysql 5.1.24-rc in 5 separate jails.

Question:

10 clients are pushing queries (10 separate machines). 2 clients to 1 mysql daemon. One client is performing an update on param_stat_short_level_1_0, second on param_stat_short_level_1_1.

Each client needs to push 150000 queries (client is spawning 15 forks to push the 150000, so each fork is pushing 10000, each fork is opening it's own mysql connection).

query to push:

INSERT INTO param_stat_short_level_1_0 SET param = '10311', object = '10311', value = '4283' ON DUPLICATE KEY UPDATE param = '10311', object = '10311', value = '4283', min = IF(4283<min,4283,min), max = IF(4283>max,4283,max), counter = counter+1;

Always a different param, and object values, value is rand(1,10000).

Stats for the operations are in http://p1.kei.pl/stats.
Stats are collected after each clients pushes 150000, 100 times.

My point here is that MySQL on Linux (p5) performs 150000 queries in an average of 18.1099, when the same queries on FreeBSD (p1) takes 28.7643.

Did I misconfigured something or this are the proper result and FreeBSD just could not give more?

My configs below, thanks for Your time.

http://p1.kei.pl/TLD
http://p1.kei.pl/dmesg
http://p1.kei.pl/boot_loader_conf
http://p1.kei.pl/sysctl_conf
http://p1.kei.pl/my_cnf
http://p1.kei.pl/stats



_______________________________________________
[hidden email] mailing list
http://lists.freebsd.org/mailman/listinfo/freebsd-performance
To unsubscribe, send any mail to "[hidden email]"
Reply | Threaded
Open this post in threaded view
|

Re: mysql performance on freebsd 7

Gergely CZUCZY
May I ask whether you are using innodb or myisam? both on freebsd and Linux.
And what filesystem are you using for mysql's data files? UFS, ZFS or anything
else?

IIRC there's some hack to reduce gettimeofday()'s precision in advance to
enhance it's performance, that could also help a bit. Have you built your mysql
with linking it against libthr?

On Wed, 21 May 2008 15:01:32 +0200
<[hidden email]> wrote:

> Hello,
>
> I'm using mysql 5.1.24-rc in 5 separate jails.
>
> Question:
>
> 10 clients are pushing queries (10 separate machines). 2 clients to 1 mysql
> daemon. One client is performing an update on param_stat_short_level_1_0,
> second on param_stat_short_level_1_1.
>
> Each client needs to push 150000 queries (client is spawning 15 forks to push
> the 150000, so each fork is pushing 10000, each fork is opening it's own
> mysql connection).
>
> query to push:
>
> INSERT INTO param_stat_short_level_1_0 SET param = '10311', object = '10311',
> value = '4283' ON DUPLICATE KEY UPDATE param = '10311', object = '10311',
> value = '4283', min = IF(4283<min,4283,min), max = IF(4283>max,4283,max),
> counter = counter+1;
>
> Always a different param, and object values, value is rand(1,10000).
>
> Stats for the operations are in http://p1.kei.pl/stats.
> Stats are collected after each clients pushes 150000, 100 times.
>
> My point here is that MySQL on Linux (p5) performs 150000 queries in an
> average of 18.1099, when the same queries on FreeBSD (p1) takes 28.7643.
>
> Did I misconfigured something or this are the proper result and FreeBSD just
> could not give more?
>
> My configs below, thanks for Your time.
>
> http://p1.kei.pl/TLD
> http://p1.kei.pl/dmesg
> http://p1.kei.pl/boot_loader_conf
> http://p1.kei.pl/sysctl_conf
> http://p1.kei.pl/my_cnf
> http://p1.kei.pl/stats
>
>
>
> _______________________________________________
> [hidden email] mailing list
> http://lists.freebsd.org/mailman/listinfo/freebsd-performance
> To unsubscribe, send any mail to "[hidden email]"

--
Üdvölettel,

Czuczy Gergely
Harmless Digital Bt
mailto: [hidden email]
Tel: +36-30-9702963

signature.asc (194 bytes) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: mysql performance on freebsd 7

Dmitriy Marchenko
In reply to this post by t.pajor
> I'm using mysql 5.1.24-rc in 5 separate jails.
>
> Question:
>
> 10 clients are pushing queries (10 separate machines). 2 clients to 1 mysql daemon. One client is performing an update on param_stat_short_level_1_0, second on param_stat_short_level_1_1.
>
> Each client needs to push 150000 queries (client is spawning 15 forks to push the 150000, so each fork is pushing 10000, each fork is opening it's own mysql connection).
>
> query to push:
>
> INSERT INTO param_stat_short_level_1_0 SET param = '10311', object = '10311', value = '4283' ON DUPLICATE KEY UPDATE param = '10311', object = '10311', value = '4283', min = IF(4283<min,4283,min), max = IF(4283>max,4283,max), counter = counter+1;
>
> Always a different param, and object values, value is rand(1,10000).
>
> Stats for the operations are in http://p1.kei.pl/stats.
> Stats are collected after each clients pushes 150000, 100 times.
>
> My point here is that MySQL on Linux (p5) performs 150000 queries in an average of 18.1099, when the same queries on FreeBSD (p1) takes 28.7643.
>
> Did I misconfigured something or this are the proper result and FreeBSD just could not give more?

It would be very helpful if you provided I/O usage as well. I guess the FreeBSD box appears to be I/O-bound. Re-run your benchmarks with noatime mount option on the partition which holds database files (or even make that partition async as some Linux filesystems do).

I think Linux outperforms FreeBSD that much in the test because of it's variable sized disk buffer cache (vs FreeBSD's fixed sized one). You can figure out how much data has to be actually written to disk and set vfs.hibufspace, vfs.lobufspace and vfs.maxbufspace sysctls in the /boot/loader.conf appropriately. Other vfs.* sysctls can also be helpful.

Regards, Dmitriy.

> My configs below, thanks for Your time.
>
> http://p1.kei.pl/TLD
> http://p1.kei.pl/dmesg
> http://p1.kei.pl/boot_loader_conf
> http://p1.kei.pl/sysctl_conf
> http://p1.kei.pl/my_cnf
> http://p1.kei.pl/stats

_______________________________________________
[hidden email] mailing list
http://lists.freebsd.org/mailman/listinfo/freebsd-performance
To unsubscribe, send any mail to "[hidden email]"
Reply | Threaded
Open this post in threaded view
|

Re: mysql performance on freebsd 7

t.pajor
In reply to this post by Gergely CZUCZY
> May I ask whether you are using innodb or myisam? both on freebsd and Linux.
> And what filesystem are you using for mysql's data files? UFS, ZFS or anything
> else?

MySQL engine used is MEMORY, filesystem is ufs, but it's not relevant.
 
> IIRC there's some hack to reduce gettimeofday()'s precision in advance to
> enhance it's performance, that could also help a bit.

What do You mean?

> Have you built your mysql with linking it against libthr?

It's a standard build from ports-current.

Stats are at http://p1.kei.pl/mon/



_______________________________________________
[hidden email] mailing list
http://lists.freebsd.org/mailman/listinfo/freebsd-performance
To unsubscribe, send any mail to "[hidden email]"
Reply | Threaded
Open this post in threaded view
|

Re: mysql performance on freebsd 7

Gergely Czuczy-3
On Wed, 21 May 2008 16:45:07 +0200
<[hidden email]> wrote:

> > May I ask whether you are using innodb or myisam? both on freebsd
> > and Linux. And what filesystem are you using for mysql's data
> > files? UFS, ZFS or anything else?
>
> MySQL engine used is MEMORY, filesystem is ufs, but it's not relevant.
sure it is relevant. check my results:
http://phoemix.harmless.hu/dbtests/

 
> > IIRC there's some hack to reduce gettimeofday()'s precision in
> > advance to enhance it's performance, that could also help a bit.
>
> What do You mean?
I mean what I've told you, no more, no less.
google://freebsd+gettimeofday+mysql+tuning

> > Have you built your mysql with linking it against libthr?
>
> It's a standard build from ports-current.
Standard build from ports"-current" pops up a dialog called "OPTIONS",
where you can set these options. How have you set them?

>
> Stats are at http://p1.kei.pl/mon/
>
>
>
> _______________________________________________
> [hidden email] mailing list
> http://lists.freebsd.org/mailman/listinfo/freebsd-performance
> To unsubscribe, send any mail to
> "[hidden email]"

--

Sincerely,

Gergely CZUCZY,
Harmless Digital
mailto: [hidden email]

Legacy software is software that works.

signature.asc (194 bytes) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: mysql performance on freebsd 7

Tomasz Pajor-4
> > > May I ask whether you are using innodb or myisam? both on freebsd
> > > and Linux. And what filesystem are you using for mysql's data
> > > files? UFS, ZFS or anything else?
> >
> > MySQL engine used is MEMORY, filesystem is ufs, but it's not relevant.
> sure it is relevant. check my results:
> http://phoemix.harmless.hu/dbtests/

How the filesystem is relevant when tables are in memory?

> Standard build from ports"-current" pops up a dialog called "OPTIONS",
> where you can set these options. How have you set them?

env BUILD_OPTIMIZED BUILD_STATIC make install clean



_______________________________________________
[hidden email] mailing list
http://lists.freebsd.org/mailman/listinfo/freebsd-performance
To unsubscribe, send any mail to "[hidden email]"
Reply | Threaded
Open this post in threaded view
|

Re: mysql performance on freebsd 7

Gergely Czuczy-3
On Wed, 21 May 2008 19:03:55 +0200
Tomasz Pajor  <[hidden email]> wrote:

> > > > May I ask whether you are using innodb or myisam? both on
> > > > freebsd and Linux. And what filesystem are you using for
> > > > mysql's data files? UFS, ZFS or anything else?
> > >
> > > MySQL engine used is MEMORY, filesystem is ufs, but it's not
> > > relevant.
> > sure it is relevant. check my results:
> > http://phoemix.harmless.hu/dbtests/
>
> How the filesystem is relevant when tables are in memory?
first, they somehow have to be put into the memory
second, for consistency (yeah, this word is missing from toysql-users'
vocabulary) it has to write the data to the disk. otherwise you'd lose
anything on a crash


--

Sincerely,

Gergely CZUCZY,
Harmless Digital
mailto: [hidden email]

Legacy software is software that works.

signature.asc (194 bytes) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: mysql performance on freebsd 7

Tomasz Pajor-4
> > How the filesystem is relevant when tables are in memory?
> first, they somehow have to be put into the memory
> second, for consistency (yeah, this word is missing from toysql-users'
> vocabulary) it has to write the data to the disk. otherwise you'd lose
> anything on a crash

http://dev.mysql.com/doc/refman/5.1/en/memory-storage-engine.html

"As indicated by the name, MEMORY tables are stored in memory. They use hash indexes by default, which makes them very fast, and very useful for creating temporary tables. However, when the server shuts down, all rows stored in MEMORY tables are lost. The tables themselves continue to exist because their definitions are stored in .frm files on disk, but they are empty when the server restarts."

so only the structure is in file not data



_______________________________________________
[hidden email] mailing list
http://lists.freebsd.org/mailman/listinfo/freebsd-performance
To unsubscribe, send any mail to "[hidden email]"
Reply | Threaded
Open this post in threaded view
|

Re: mysql performance on freebsd 7

Gergely Czuczy-3
ahh, sorry. missunderstood that. i've seen many places they've put the
data into memory disks. i've thought that's being done here also.

On Wed, 21 May 2008 19:33:56 +0200
Tomasz Pajor  <[hidden email]> wrote:

> > > How the filesystem is relevant when tables are in memory?  
> > first, they somehow have to be put into the memory
> > second, for consistency (yeah, this word is missing from
> > toysql-users' vocabulary) it has to write the data to the disk.
> > otherwise you'd lose anything on a crash  
>
> http://dev.mysql.com/doc/refman/5.1/en/memory-storage-engine.html
>
> "As indicated by the name, MEMORY tables are stored in memory. They
> use hash indexes by default, which makes them very fast, and very
> useful for creating temporary tables. However, when the server shuts
> down, all rows stored in MEMORY tables are lost. The tables
> themselves continue to exist because their definitions are stored
> in .frm files on disk, but they are empty when the server restarts."
>
> so only the structure is in file not data

--

Sincerely,

Gergely CZUCZY,
Harmless Digital
mailto: [hidden email]

Legacy software is software that works.

signature.asc (194 bytes) Download Attachment