Slashdot Mirror

← Back to Stories (view on slashdot.org)

Improving Database Performance?

Posted by Cliff on from the lead-foot-meet-database-gas-pedal dept.

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."

5 of 95 comments (clear)

  1. Memcached by Exstatica · · Score: 5, Informative

    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

    1. Re:Memcached by stevey · · Score: 4, Informative

      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.

  2. MySQL replication. by billn · · Score: 4, Informative


    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.

    --
    - billn
  3. More importantly by Safety+Cap · · Score: 4, Informative
    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.

    --
    Yeah, right.
  4. High Performance MySQL by ajayrockrock · · Score: 4, Informative


    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/