Agreed on both fronts (optimizing queries and understanding locking)<div><br></div><div>You may have already done this, but tuning the my.cnf file to take advantage of the hardware can lead to some gains as well. Specifically during peak traffic times. There are many out there, but here's one that we'd had success with:</div>
<div><br></div><div><a href="https://github.com/rackerhacker/MySQLTuner-perl">https://github.com/rackerhacker/MySQLTuner-perl</a></div><div><br><div class="gmail_quote">On Thu, Feb 21, 2013 at 3:15 PM, Eric Cope <span dir="ltr"><<a href="mailto:eric.cope@gmail.com" target="_blank">eric.cope@gmail.com</a>></span> wrote:<br>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div dir="ltr">Its my understanding that row(or table) locks can also be bottle necks. Multiple CPUs to execute queries don't help if they are writing to the same rows (or tables depending on your engine). I am far from an expert though.<div>
<br></div><div>Do you the the High Performance MySQL book from the Percona folks? It might have some details regarding this question.</div><span class="HOEnZb"><font color="#888888"><div><br>Eric</div></font></span></div>
<div class="HOEnZb"><div class="h5"><div class="gmail_extra"><br><br><div class="gmail_quote">
On Thu, Feb 21, 2013 at 4:09 PM, Mike Bydalek <span dir="ltr"><<a href="mailto:mike.bydalek@gmail.com" target="_blank">mike.bydalek@gmail.com</a>></span> wrote:<br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
My 2c on the matter as its been awhile since I've worked with MySQL.<br>
Yes, query optimization is extremely important for performance. The<br>
question really becomes, what are my physical bottlenecks?<br>
<br>
Multi-core CPUs allow for multiple simultaneous queries as I believe<br>
each query uses a thread. However, the biggest thing you can do for<br>
database performance is making sure you have enough disk IOPs to<br>
handle pulling and writing data.<br>
<br>
-Mike<br>
<div><div><br>
<br>
On Feb 21, 2013, at 1:04 PM, Nathan England <<a href="mailto:nathan@nmecs.com" target="_blank">nathan@nmecs.com</a>> wrote:<br>
<br>
><br>
> Hello All,<br>
><br>
> I'm interested in learning how to optimize MySQL or MariaDB for a multi-core<br>
> cpu. Can the database itself benefit from newer quad-core or more cpus?<br>
><br>
> I have been trying to find this information, but it seems to me that the<br>
> largest benefit does not come from the database using more than one core, but<br>
> in the design of the query and running multiple queries... is this true?<br>
><br>
> I would appreciate thoughts, articles, opinions, smacks upside the head...<br>
> Though I generally do not like arguments like "don't waste time with MariaDB,<br>
> just use PostgreSQL", I would even consider these...<br>
><br>
> Thanks!<br>
><br>
> --<br>
> Regards,<br>
><br>
> Nathan England<br>
><br>
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~<br>
> NME Computer Services <a href="http://www.nmecs.com" target="_blank">http://www.nmecs.com</a><br>
> Nathan England (<a href="mailto:nathan@nmecs.com" target="_blank">nathan@nmecs.com</a>)<br>
> Systems Administration / Web Application Development<br>
> Information Security Consulting<br>
> <a href="tel:%28480%29%20559.9681" value="+14805599681" target="_blank">(480) 559.9681</a><br>
><br>
</div></div>> ---------------------------------------------------<br>
> PLUG-discuss mailing list - <a href="mailto:PLUG-discuss@lists.phxlinux.org" target="_blank">PLUG-discuss@lists.phxlinux.org</a><br>
> To subscribe, unsubscribe, or to change your mail settings:<br>
> <a href="http://lists.phxlinux.org/mailman/listinfo/plug-discuss" target="_blank">http://lists.phxlinux.org/mailman/listinfo/plug-discuss</a><br>
---------------------------------------------------<br>
PLUG-discuss mailing list - <a href="mailto:PLUG-discuss@lists.phxlinux.org" target="_blank">PLUG-discuss@lists.phxlinux.org</a><br>
To subscribe, unsubscribe, or to change your mail settings:<br>
<a href="http://lists.phxlinux.org/mailman/listinfo/plug-discuss" target="_blank">http://lists.phxlinux.org/mailman/listinfo/plug-discuss</a><br>
</blockquote></div><br></div>
</div></div><br>---------------------------------------------------<br>
PLUG-discuss mailing list - <a href="mailto:PLUG-discuss@lists.phxlinux.org">PLUG-discuss@lists.phxlinux.org</a><br>
To subscribe, unsubscribe, or to change your mail settings:<br>
<a href="http://lists.phxlinux.org/mailman/listinfo/plug-discuss" target="_blank">http://lists.phxlinux.org/mailman/listinfo/plug-discuss</a><br></blockquote></div><br><br clear="all"><div><br></div>-- <br><font size="3"><div style="border-collapse:collapse;font-family:arial,sans-serif;font-size:13px">
<font face="arial, helvetica, sans-serif">Vimal (rhymes with Kimmel) Shah</font></div><div style="border-collapse:collapse;font-family:arial,sans-serif;font-size:13px"><span style="font-family:arial,helvetica,sans-serif;font-size:13px">Front-End / Infrastructure Engineer</span></div>
<span style="border-collapse:collapse;font-family:arial,sans-serif;font-size:13px"><font face="arial, helvetica, sans-serif">Sokikom<br>Mobile: (480) 752-9269<br>Email:<font color="#500050"> </font><a href="mailto:vimals@sokikom.com" style="color:rgb(42,93,176)" target="_blank">vimals@sokikom.com</a></font></span><div style="border-collapse:collapse;font-family:arial,sans-serif;font-size:13px">
<font face="arial, helvetica, sans-serif">Web:<font color="#500050"> </font><font color="#3333FF" style="color:rgb(42,93,176)"><a href="http://www.sokikom.com/" style="color:rgb(42,93,176)" target="_blank">www.sokikom.com</a></font></font></div>
<div><font face="arial, helvetica, sans-serif"><br></font></div></font><div><div style="border-collapse:collapse;font-family:arial,sans-serif;font-size:13px">Follow us: <a href="http://www.twitter.com/sokikom" style="color:rgb(42,93,176)" target="_blank">twitter.com/sokikom</a></div>
<div style="border-collapse:collapse;font-family:arial,sans-serif;font-size:13px">Like us: <a href="http://www.facebook.com/sokikom" style="color:rgb(42,93,176)" target="_blank">facebook.com/sokikom</a></div></div>
</div>