StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>It's hard to broadbrush things, but a moderately high-level view is possible.</p> <ul> <li>You need to evaluate read:write ratios. For tables with ratios lower than about 5:1, you will probably benefit from InnoDB because then inserts won't block selects. But if you aren't using transactions, you should change <code>innodb_flush_log_at_trx_commit</code> to 1 to get performance back over MyISAM.</li> <li>Look at the memory parameters. MySQL's defaults are very conservative and some of the memory limits can be raised by a factor of 10 or more on even ordinary hardware. This will benefit your SELECTs rather than INSERTs.</li> <li>MySQL can log things like queries that aren't using indices, as well as queries that just take too long (user-defineable).</li> <li>The query cache can be useful, but you need to instrument it (i.e. see how much it is used). Cacti can do that; as can Munin.</li> <li>Application design is also important: <ul> <li>Lightly caching frequently fetched but smallish datasets will have a big difference (i.e. cache lifetime of a few seconds). </li> <li>Don't re-fetch data that you already have to hand.</li> <li>Multi-step storage can help with a high volume of inserts into tables that are also busily read. The basic idea is that you can have a table for ad-hoc inserts (<code>INSERT DELAYED</code> can also be useful), but a batch process to move the updates within MySQL from there to where all the reads are happening. There are variations of this.</li> </ul></li> <li>Don't forget that perspective and context are important, too: what you might think is a long time for an <code>UPDATE</code> to happen might actually be quite trivial if that "long" update only happens once a day. </li> </ul>

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