10 essential performance tips for MySQL

From workload profiling to the three rules of indexing, these expert insights are sure to make your MySQL servers scream

By Baron Schwartz, InfoWorld |  Software, databases, MySQL

Scalability is not as vague as you may believe. In fact, there are precise mathematical definitions of scalability that are expressed as equations. These equations highlight why systems don't scale as well as they should.

Take the Universal Scalability Law, a definition that is handy in expressing and quantifying a system's scalability characteristics. It explains scaling problems in terms of two fundamental costs: serialization and crosstalk.

Parallel processes that must halt for something serialized to take place are inherently limited in their scalability. Likewise, if the parallel processes need to chat with each other all the time to coordinate their work, they limit each other.

Avoid serialization and crosstalk, and your application will scale much better. What does this translate into inside of MySQL? It varies, but some examples would be avoiding exclusive locks on rows. Queues, point No. 3 above, tend to scale poorly for this reason.

MySQL performance tip No. 6: Don't focus too much on configuration

DBAs tend to spend a huge amount of time tweaking configurations. The result is usually not a big improvement and can sometimes even be very damaging. I've seen a lot of "optimized" servers that crashed constantly, ran out of memory, and performed poorly when the workload got a little more intense.

The defaults that ship with MySQL are one-size-fits-none and badly outdated, but you don't need to configure everything. It's better to get the fundamentals right and change other settings only if needed. In most cases, you can get 95% of the server's peak performance by setting about 10 options correctly. The few situations where this doesn't apply are going to be edge cases unique to your circumstances.

In most cases, server "tuning" tools aren't recommended because they tend to give guidelines that don't make sense for specific cases. Some even have dangerous, inaccurate advice coded into them -- such as cache hit ratios and memory consumption formulas. These were never right, and they've gotten even less correct as time has passed.

MySQL performance tip No. 7: Watch out for pagination queries

Applications that paginate tend to bring the server to its knees. In showing you a page of results, with a link to go to the next page, these applications typically group and sort in ways that can't use indexes, and they employ a LIMIT and offset that causes the server to do a lot of work generating, then discarding rows.

Optimizations can often be found in the user interface itself. Instead of showing the exact number of pages in the results and links to each page individually, you can just show a link to the next page. You can also prevent people from going to pages too far from the first page.

Join us:
Facebook

Twitter

Pinterest

Tumblr

LinkedIn

Google+

SoftwareWhite Papers & Webcasts

See more White Papers | Webcasts

Answers - Powered by ITworld

Join us:
Facebook

Twitter

Pinterest

Tumblr

LinkedIn

Google+

Ask a Question
randomness