> -----Original Message-----
> From: Hamid Hashemi Golpayegani [mailto:[EMAIL PROTECTED]]
> Sent: domingo, 01 de abril de 2001 15:07
> To: Nemholt, Jesper Frank; [EMAIL PROTECTED]
> Subject: RE: Dual Processor and MySQL !
>
>
> Hi ,
>
> Thank you very much for your compelete description about
> MySQL and multi
> processor platforms .
> According to your mail if I have several connection to my SQL
> server and I
> have more than 1 CPU and installed the SMP on my machine the MySQL
> performance will be better than a single CPU and If I have
> only 1 connection
> to my SQL server then single and dual CPU are not import for
> this situation
Correct.
> .
> So it is ok ! cause I have about 200 concurrence connection to my SQL
> machine and it seems that be better to have more that one CPU .
Yes, with more than one concurrent query running, you'll benefit from SMP.
Exactly how much depends on many things (table locking is one critical thing
with MySQL), but with 200 concurrent connections, you'll get pretty close to
100% benefit, which means if you go from one 500 MHz to two 500 MHz CPUs,
you'll get close to twice the performance.
...but the execution time of one singlethreaded SQL on a idle machine will
not get faster. To get this you need a faster CPU, not more CPUs.
Try to run a "show processlist" every now & then and see how many
connections you have, and how many of those are active or sleeping/waiting
for a table to be unlocked. Depending on database design, you might find,
with MySQL, that most of the time is spent waiting for a table to unlock.
There are some guidelines in the MySQL manual on how to avoid this (by
inserting into temporary tables and the likes), but most of these solutions
will get rather ugly. Other SQL servers ike Oracle lock on row level which
is _much_ better. I hope this will come in MySQL soon.
> About other SQL servers that you said support multi
> processing alone without
> depending on OS and SMP . Can you tell me them ?!
> I thinks that Oracle would be one of them .
All of them (well, those I know) rely on the SMP support in the operating
system. In fact, the SQL server doesn't need to know about SMP (or how many
CPUs the machine have). The only place I know of where the SMP support is
built into the application is on MacOS nad applications like Adobe
Photoshop. This is a very ugly hack and only made because Adobe needed the
speed, there were SMP hardware, but the OS didn't support SMP.
What is important is whether the SQL server is multithreaded or not (and
support the thread implementation on the operating system it runs on). If it
is multithreaded and dedicate a thread to every query, or maybe is even able
to split a query into several threads, then it'll utilize whatever SMP is
offered by the OS.
Beware that many OSes don't support SMP or have pathetic thread support.
You'll find fairly good SMP & thread support in most commercial unix
versions (Solaris, HP-UX, Tru64, AIX etc.), Windows NT/2000 and recent
versions of Linux.
Most of the *BSD implementations, and Windows 9x doesn't support SMP, and
some not even threads.
What I don't know about MySQL is how well (if at all) it is able to split up
a query into several threads.
On a SQL server like Oracle I know that complex queries with several
subselects and things like that are divided into several threads which
improve performance alot on a SMP machine.
The DBAs where I work use this extensively when they create their SQL
statements in order to get the most performance out of the machine (and the
query result as fast as possible). Most of your production machines have
more than 8 CPUs, so there is alot to win on them.
--
Un saludo / Venlig hilsen / Regards
Jesper Frank Nemholt
Unix System Manager
Compaq Computer Corporation
Phone : +34 699 419 171
E-Mail: [EMAIL PROTECTED]
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
