Improving Database Performance?

An anonymous reader asks: "An acquaintance of mine runs a fair sized web community. Having nearly 280,000 users in a single MySQL database with about 12 mirrors accessing it, performance has become an big issue. I was wondering what practical methods could be used to lessen the load and decrease user query times. Funds are limited, so he'd rather not pay for a commercial database system at this time."

Comments

some MySQL optimization tips

[ubiquitin]

You can push MySQL way beyond 280,000 customer records. I know because I've done it.

With properly normalized data, on fast, current, commodity hardware (10krpm drives for instance), using InnoDB, you can pretty easily push MySQL into the 5 to 10 million records-per-table range before you start really needing a bigger relational database engine. This assumes no more than 1% of your data is needing to be updated per day. Staying with GPL database software is a really smart thing to do: you don't know how much time and money gets spent on just negotiating with Oracle over their licenses: it is anything but simple. Small business web sites cease to be "small business" when they grow beyond of .01% of 10 million per day.

A non-trivial part of my business is in advising companies in how to get the most out of MySQL. Replication is one part of that, but having the right data structure for scalability is really key. Want more? Ask around at: www.phpconsulting.com

sqlrelay

[Vlad_Drak]

SQLRelay http://sqlrelay.sourceforge.net/ might be a good option here. If you do end up switching the backend from MySQL to PostGres or whatever, it's supported there too.

Re:Memcached

[Anonymous Coward]

"Add more RAM" sounds good, but it is not necessarily a real solution.

For many "real" databases, there is most definitely a big IO performance issue, regardless of the amount of RAM in the system. The main reason is the requirement that data be in stable storage after a commit succeeeds in order to comply with ACID semantics (though being MySQL, it wouldn't surprise me if this requiement didn't bother him).

If the database is read-only (and being MySQL, that wouldn't surprise me), then sure, adding more RAM until disk access goes to 0 would be one way to get more performance.

Otherwise, the best way to get really good performance out of a database, after there is a *reasonable* amount of RAM and CPU power there, is to add a good disk controller with a nice battery backed writeback cache.

Without a battery backed disk controller cache, the log disk's rotational latency quickly becomes a bottleneck and you end up with a limit of 100-200 transactions per second (that modify data, plain selects don't have this limit of course).

ask the source

[hankaholic]

You might suggest that your friend consider asking MySQL for a quote:

http://www.mysql.com/company/contact/

Their Enterprise contracts are probably a bit much for your friend's needs, but they may offer single-incident support for optimization and tuning assistance.

If he doesn't mind delving into DBA-land, he may want to buy a book. If he values the time it would take him to get up to speed and would rather spend it on other pursuits, it may well be worth the money to get some help.

Either way, he'll have to spend something (time or money) -- it's a question of how much his time is worth to him.

Re:Date searches may not work as you think

[Samus]

You might not be getting much performance out of that index anyway. I can't speak to your specific database but in general if you have a large table and the indexed column has a small number of possible values, you won't be buying yourself a whole lot. Let's say that 30 plus percent of your 100,000 plus record table at any one time has not been processed. IO wise that probably won't be much better than a table scan. Additionally if a table has a lot of writes done to it, a lot of indexes will hamper performance. Often online activity and reporting don't mix very well. Reports can easily swamp a database. I've seen companies that actually use a reporting database that is synched up nightly, just so that their onlines aren't affected negatively. Perhaps in your case you could even consider having 2 tables that are identical instructure except that one is for incoming records and the other is for processed records. Databases are a tricky beast. As you well know what works for a hundred or a thousand records doesn't always scale to a hundred thousand.

Something I recently found...

[cr0sh]

In a bone-headed move, I might add - but it is something worth checking out to see if this is part of the issue:

I am in the process of re-doing my website using PHP and MySQL. My new site will be complete DB driven, to the point that the page content is driven and built from PHP stored in the DB. My goal is to be able to update the website from anywhere in the world with a web connection. I am custom writing this system, rather than use a pre-existing CMS or other blogging system (and there were quite a few that were tempting) - because I wanted to learn PHP and MySQL by doing, rather than by observing.

Anyhow, one of my editors was slowing down on an update - that is, when I clicked "update" to update the site, it was taking a long time to update the database. Various tests indicated that it wasn't PHP with the issue, but running 'top' on my dev box indicated that the apache process was thrashing on these updates. I checked the code, and here is what I discovered:

In my update code, I was issuing a SQL insert for each field in a record, where I was updating multiple fields on the same record, rather than doing an insert with all the fields to be updated in the SQL statement. If I had 10 fields to update, that was 10 INSERTS, instead of the single I should have been doing. As I said, this was a bone-headed move I won't be doing again in the future. Once I corrected the issue, my performance shot up immediately on the update.

I would imagine that the same could be true of any simple SELECT - select out all the fields (and only those fields) you need at one shot, then loop thru the records building your output (whatever it is). Optimize the queries well, too (a misplaced pair of parentheses can make a WORLD of difference in some cases).

In short, keep the number of queries to the backend as short and sweet as possible, reducing the load (and thrashing) on the backend. This should be common sense design, but sometimes in the thrill and rush to build something, programmers forget this, and it can easily cause issues down the line (I was lucky in that I caught it very early in my design of the system).

Good luck, and I hope this helps...