Best CPU config for a busy DB server

[Database Administrator]

Hey everyone,

I'm pretty sure this is right but I wanted to double-check:

Is it correct that mysql 5.0 is threaded in such a way that a DB server taking
lots of queries from many clients will be able to utilize lots of CPUs/core
on a multi-cpu, multi-core system?

Or are multi CPUs/cores a waste?

Thanks,

JW
--

----------------------
System Administrator - Cedar Creek Software
http://www.cedarcreeksoftware.com

[Database Administrator]

Yes it can use multiple cores. Mysqld is a multithreaded service.

Saravanan

--- On Sat, 5/10/08, JW wrote:

> From: JW
> Subject: Best CPU config for a busy DB server
> To: mysql@lists.mysql.com
> Date: Saturday, May 10, 2008, 3:52 AM
> Hey everyone,
>
> I'm pretty sure this is right but I wanted to
> double-check:
>
> Is it correct that mysql 5.0 is threaded in such a way that
> a DB server taking
> lots of queries from many clients will be able to utilize
> lots of CPUs/core
> on a multi-cpu, multi-core system?
>
> Or are multi CPUs/cores a waste?
>
> Thanks,
>
> JW
> --
>
> ----------------------
> System Administrator - Cedar Creek Software
> http://www.cedarcreeksoftware.com
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/mysql?unsub=s...babu@yahoo.com


__________________________________________________ __________________________________
Be a better friend, newshound, and
know-it-all with Yahoo! Mobile. Try it now. http://mobile.yahoo.com/;_ylt=Ahu06i...Dypao8Wcj9tAcJ

[Database Administrator]

On Friday 09 May 2008 04:32:10 pm Saravanan wrote:
> --- On Sat, 5/10/08, JW wrote:
> > From: JW
> > Is it correct that mysql 5.0 is threaded in such a way that
> > a DB server taking lots of queries from many clients will be able\
> > to utilize lots of CPUs/core on a multi-cpu, multi-core system?
> >
> > Or are multi CPUs/cores a waste?
> >
> > Thanks,
> >
> > JW


> Yes it can use multiple cores. Mysqld is a multithreaded service.
>
> Saravanan

I just found this interesting tidbit:

*******
"MySQL On Multi-Core Machines - The DevShed technical tour explains that MySQL
can spawn new threads, each of which can execute on a different
processor/core.

What it doesn’t say is that a single thread can only execute on a single core,
and if that thread locks a table, then no other threads that need that table
can execute until the locking thread/query is complete. Short answer: MySQL
works well on multi-core machines until you lock a table."
********

One of our programmers was wondering if this is referring to such implicit
lock such as when you you read from a table (SELECT) or only explicit table
locking, which we don't (currently) use in any of our code.

Does anyone know?

JW

--

----------------------
System Administrator - Cedar Creek Software
http://www.cedarcreeksoftware.com

[Database Administrator]

At 05:05 PM 5/9/2008, you wrote:
>On Friday 09 May 2008 04:32:10 pm Saravanan wrote:
> > --- On Sat, 5/10/08, JW wrote:
> > > From: JW
> > > Is it correct that mysql 5.0 is threaded in such a way that
> > > a DB server taking lots of queries from many clients will be able\
> > > to utilize lots of CPUs/core on a multi-cpu, multi-core system?
> > >
> > > Or are multi CPUs/cores a waste?
> > >
> > > Thanks,
> > >
> > > JW
>
>
> > Yes it can use multiple cores. Mysqld is a multithreaded service.
> >
> > Saravanan
>
>I just found this interesting tidbit:
>
>*******
>"MySQL On Multi-Core Machines - The DevShed technical tour explains that
>MySQL
>can spawn new threads, each of which can execute on a different
>processor/core.
>
>What it doesn’t say is that a single thread can only execute on asingle
>core,
>and if that thread locks a table, then no other threads that need thattable
>can execute until the locking thread/query is complete. Short answer: MySQL
>works well on multi-core machines until you lock a table."
>********
>
>One of our programmers was wondering if this is referring to such implicit
>lock such as when you you read from a table (SELECT) or only explicit table
>locking, which we don't (currently) use in any of our code.
>
>Does anyone know?
>
> JW


Table locking will occur with MyISAM tables when any row(s) of the table is
being updated (Update,Delete,Insert,Load Data etc).
If you are only executing Select statements, then they can be executed in
parallel and won't be blocked.

You can have 4 or more processors, but the biggest bottleneck will still be
disk access. Putting more RAM into the computer will help if you if you
also tune your My.Cnf file to make use of the extra RAM. If the table is
small enough, you could try and put it all in memory using the Heap table
engine and avoid disk access altogether.

Mike


>--
>
>----------------------
>System Administrator - Cedar Creek Software
>http://www.cedarcreeksoftware.com
>
>--
>MySQL General Mailing List
>For list archives: http://lists.mysql.com/mysql
>To unsubscribe: http://lists.mysql.com/mysql?unsub=mos99@fastmail.fm

[Database Administrator]

> Table locking will occur with MyISAM tables when any row(s) of the table is
> being updated (Update,Delete,Insert,Load Data etc).
> If you are only executing Select statements, then they can be executed in
> parallel and won't be blocked.


Just curious: you say "with MyISAM tables" - do any of the other table types
(InnoDB, Falcon, etc) behave differently?

Thanks,

JW

--

----------------------
System Administrator - Cedar Creek Software
http://www.cedarcreeksoftware.com

[Database Administrator]

On Sat, May 10, 2008 at 4:24 PM, JW wrote:
>> Table locking will occur with MyISAM tables when any row(s) of the table is
>> being updated (Update,Delete,Insert,Load Data etc).
>> If you are only executing Select statements, then they can be executed in
>> parallel and won't be blocked.
>
>
> Just curious: you say "with MyISAM tables" - do any of the other table types
> (InnoDB, Falcon, etc) behave differently?
>
> Thanks,
>
> JW

"When locks are necessary, InnoDB uses row-level locking."
MySQL 5.0 Certification Study Guide, page 419

--
Rob Wultsch
wultsch@gmail.com
wultsch (aim)