StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>The easiest first step is to separate your database and front-end application processes, putting these on to different machines. This usually lets you allocate considerably more memory to the <code>mysqld</code> process through modifications to <code>my.cnf</code> settings.</p> <p>As always, update to the latest version of your stack and back-end to be sure you're taking maximum advantage of your hardware.</p> <p>Pay close attention to the database calls running on your system and see if there's any way to speed these up. <code>EXAMINE</code> can often show the execution strategy for any given <code>SELECT</code> call, as an example, and will reveal where you have missing indexes, often the biggest performance drag of all. If you're getting hit with "table scan" or "using filesort" you're basically dead in the water on large datasets. You need indexes or a different schema.</p> <p>The second step is to layer in more front-end application servers and add in a load-balancer of some kind. This gives you additional front-end capacity to handle load better, but also loads down your database even more.</p> <p>The third step is to ensure that you're not doing any unnecessary <code>JOIN</code> operations during your <code>SELECT</code> calls. Examine what you need from the database very carefully and try and get everything from one table at a time. Tactical de-normalization is often the solution here, but with it comes sync problems if you're not careful to do this properly. Pay close attention when you're doing this and be sure you have extensive test coverage.</p> <p>The fourth step is often to buffer your heavy read activity on the database by using a cache like <a href="http://memcached.org/" rel="nofollow">Memcached</a> to avoid hitting the database as often. Remember that the fastest database call is the one you don't make.</p> <p>There's no magic bullet, but if you do things in that order you should have a fighting chance.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload