On Sat, 17 Mar 2007, Dirk Bonengel wrote:
just curious but Chris how successful were you in optimizing your MySQL installation? I take it as a given that many installations nowadays use MySQL as data store, so hints on optimizing MySQL would be a welcome addition to the wiki, I think
I was _very_ successful in tuning MySQL. I/O wait times have plummetted, and our average time-to-scan has dropped from several minutes down to 5 seconds. Here's what I added to /etc/my.cnf: skip-external-locking max_connections=50 innodb_flush_method=O_DSYNC innodb_flush_log_at_trx_commit=0 innodb_log_file_size=1024M innodb_log_buffer_size=8M innodb_buffer_pool_size=3072M innodb_additional_mem_pool_size=20M table_cache=96 query_cache_limit=5M query_cache_type=2 Most of these were recommendations from the MySQL tuning script someone else suggested, plus some stuff I found online. I'm still tweaking some of them, but this has made all the difference. Note that memory numbers are in relation to the amount of memory in the machine. I can't remember what all of these do at the moment -- some of them are pretty nuanced -- but you can find all of them in the MySQL manual. One directive -- I believe innodb_flush_method=O_DSYNC -- can cause data loss if your machine crashes, but I don't really care because this is just Bayes data. HTH. Chris St. Pierre Unix Systems Administrator Nebraska Wesleyan University ---------------------------- Never send mail to [EMAIL PROTECTED]
