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

Memcached

[Exstatica]

Use Memcached. Which is used by livejournal, slashdot, wikipedia, and sourceforge. I also use it. My database has no load. It's not to difficult to implement and there are tons of API's

Re:Memcached

[SpaceLifeForm]

And add more RAM.

Re:Memcached

[stevey]

Pretty much, yes.

Here's an introduction to memcached I wrote which might explain it for you.

In short you modify all the parts of your code that fetch from the database to first check from the memory cache - and when storing invalidate the cache. In general most sites read data more than they write it so most of your accesses come from the cache - thus reducing the load upon your DB.

If you don't want to modify your code you could look at optimizing the setup of the database server, moving it, setting up replication, etc.

Still without more details it is hard to know what to suggest.

It Depends

[AtrN]

Without some idea of the access patterns, schema and actual DBMS configuration its hard to say what can be done to improve performance. There are purely mechanical things like caching as much as possible, getting faster disks, more memory, etc... but these may not even help depending upon the more fundamental issues of DB design and deployment. Depending upon the use MySQL may not even be appropriate given some of its limitations, PostgreSQL may be a better fit for instance if there's a lot of updating going on or client s/w is performing many queries to assemble views which could be better done closer to the data.

MySQL replication.

[billn]

Works like a champ.

Set up multiple read slaves to carry the bulk of your read traffic, especially for your mirrors. Considering MyISAM's native table locking behavior, this should reduce your master db load quite a bit, even just moving your mirror read-load to a slave replicant.

Also, query caching is a beautiful thing.

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

Re-design the schema...

[MosesJones]

280,000 records, even for MySQL, isn't that much and indicates that performance is being driven down either by tiny hardware or more likely...

1) Badly optimised queries
2) Poor index selection and maintainance
3) Generally poor schema design

It might also be that queries should be cross table with sub-queries(not a MySQL strong point).

9/10 poor database performance is due to bad database design.

Re:Just...

[dotgain]

Just randomly abort the CGI scripts and throw out an HTTP/503. That's how slashdot does it, and he did "ask slashdot".

More importantly

[Safety Cap]

It actually supports ACID, whereas MySQL does not.

So, for example, if you want to insert a string that is too big for the field, MySQL will gladly suck it up with nary a peep (meanwhile, your data is trashed: truncate hell), whereas Postgre (and other non-toy RDBMSs) will refuse to insert the record.

Wikipedia has a nice comparison.

A plea.

[linuxwrangler]

To the O.P.: Provide some info - we're not mind-readers. Today's User Friendly is somehow appropriate.

How well normalized is the schema? Mostly reads? Writes? Both? 280,000 users? So what. Do you mean simultaneous users or are only 2 on at a time? Are they accessing a single 100 record table or lots of large tables? Are they indexed properly? What is the OS, memory, disk, processor...? How much processing is required of the DB vs. the front-end. Have you run into any specific problems that might indicate that a different db might be more appropriate. What have you tried and what was the result?

To the editors: Please reject Ask Slasdot questions from posters who can't be bothered to provide the most basic background info.

This is Slashdot. I would like to believe that the typical reader could be rather more technically erudite.

"it depends"

[Anonymous Coward]

Like a poster above mentioned, it really depends on your access patterns.

The ABSOLUTELY MOST IMPORTANT THING is to set up some benchmarks that reflect your usage patterns. Once you have some solid benchmarks, you can set up a farm of test machines and start benchmarking, adjusting, benchmarking, adjusting, over and over until you've got the performance you need. I can't stress this enough. You need a good, automated benchmark system to get you the first 85-90% of the way. The last 10% has to be done "in the field" unless your benchmarks are REALLY good.

Generally, you want to minimize disk usage time on your databases. Everything else is just gravy. Make sure you've got some FAST disks for your MySQL boxes, and make sure they are striped RAID (or whatever your benchmarks show as being the fastest). Choose your filesystem (ext3, reiser, etc) the same way: use the one that benchmarks the fastest.

Next, there are lots of things you can tune in MySQL. For instance, did you know there's a "query cache" that will save the results of queries, indexed by the SQL query text? In *some* situations, it can be very useful. In others, it actually degrades performance. First learn about MySQL's various knobs and get them tuned.

Next, you might need to distribute reads to more mirrored DBs and/or to application-level caching like memcached. Depending on your app, this can give you a 100x speed increase.

Next, you might want to partition your database, if your data is suited for it. For instance, all queries for customers in Idaho go to a separate machine just for the idaho customers. All your application goes through a DB access layer that selects the right machine.

Basically, you need to get the "main loop" down: benchmark, adjust, benchmark, adjust, etc., etc, and then start trying things out!

The same goes for PostgreSQL.

But whatever you do, the LAST thing you want to do is mess with your database intregity. If anybody tells you to "turn off constraints" or "denormalize for performance", they are idiots. Your primary goal should always be data integrity! If you've got a real app, with real paying customers, and real valuable data (i.e., not a blog or something), you can't afford to throw 30 years of database best practices out the window to get a 5% speed increase. Today's SQL databases unfortunately don't even begin to implement even the most basic relational features, but that doesn't mean you shouldn't try. Just a tip...I've made plenty of consulting dollars fixing the mess people left when they started valuing performance over data integrity.

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.

High Performance MySQL

[ajayrockrock]

Check out the High Performance MySQL book for info on how to speed it up. Most of it's probably obvious for the hardcore DBA guy, but I found it useful:

http://www.oreilly.com/catalog/hpmysql/

Re:Postgre-SQL

[dotgain]

PHB: I think we should build an SQL database.
Dilbert (thinking):Does he know what he's talking about, or did he read it in a trade magazine ad?
Dilbert (speaking):What color would you like that database?
PHB: I think mauve has the most RAM. -

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.