Re: Table Locking (Was: Best CPU config for a busy DB server)

[mos]

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 <jw@stripped> wrote:
> > > From: JW <jw@stripped>
> > > 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


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=1