31–40 of 66 posts
I would not migrate to postgres without knowing the issue first. Here are some suggestions: Enable replication master/slaves+, assuming you have a read heavy architecture. Enable the " The Slow Query Log " [1], which will give you something like /var/log/mysql-slowquery.log, then you can look in here to find queries that take a long time. Find out what the bottleneck is, I'm willing to best you have limited I/O and t…
Thanks :)
There are some good suggestions in this thread already. Step #1 is to determine why it is slow.
If it is slow under load, then I am going to suspect that you are hitting table locks - which means you need to migrate to InnoDB (row locking + MVCC). Important to point out: a bigger server may not actually help enough here, because while you are waiting on locks nothing can be done while there is free capacity waiting to be used.
If it is just generally slow related to growth, it can probably be improved by indexing - which will help you reduce what data needs to be in RAM.
In either case, two third party tools to suggest: - pt-query-digest: aggregate your slow query log. You want to use 0 seconds as the threshold, record 20 minutes of slow queries during regular activity. Methodology described here: http://gtowey.blogspot.ca/2012/07/slow-query-log-is-not.html
- pt-online-schema-change: you can migrate from MyISAM to InnoDB online via triggers (not online by default).
Both tools part of http://www.percona.com/software/percona-toolkit
...assuming you're webvet.com it looks like you're scaling drupal, so these would be my first questions: are you using the views module? if so, kill that, as well as anything else by merlin (panels, etc). and look at your indexes. setup pressflow + percona. master & slave db. reverse proxy caching (varnish or nginx) are all good places to start. hostwise if you're not already on amazon, you can get a lot of mileage out of a service like voxel that lets you mix VPSs (cheap webheads) with physical hardware. it's not great and it may well be worth biting the more expensive bullet and going to AWS for long term needs, but it's a lot better than linode for an underwater db.
my email is in my profile if you'd like more detail - drupal is a bear at first but it can be scaled for a while.
In my experiences, the size of the database nor the number of records really matter. It's the number of queries that you are handling at one time that can really affect performance. Sure, if you have a single table with a ton of records this can slow things down - but not nearly as much as dealing with a high number of queries. I highly recommend this book - http://www.amazon.com/dp/0596101716/?tag=stackoverfl08-20 Y…
"Not having had in-house prior experience with such data volumes on MySQL ... At this point, we're considering the following 3 options:" 4. Hire or contract a consultant/expert. Part of their assignment could be to teach you.
Hey, thanks :) We are actually considering this, but it skipped my mind while making the post.
I have a little bit of experience dealing with large datasets and MySQL. This is going to be a little counter-intuitive to standard startup culture advice, but generally, the engineering effort to optimize databases past the initial live flow is not worth throwing money at the problem. You have to weigh the fixed cost of the optimization and the variable cost of the upkeep of the system against simply patching the sy…
Do you have any sort of caching in your app? Memcache, for instance, can go a long way in helping you scale.
We don't have Memcache (or anything similar) presently, though I do believe we looked at it sometime last year. Will get a re-check done on this - thanks. :)
Bringing in a consultant is probably your best bet. Database optimization needs to generally take into account YOUR data and usage patterns.
If you want to learn a bit yourself, pick up a copy of "High Performance MySQL." I've found that to be the most useful MySQL scaling book I own.
Figure out your slow queries. Run "SHOW PROCESSLIST" on the server and see what's typically running.
Look at the actual queries - if you're frequently running a query that looks through most rows of your biggest table, that's going to be a tough query to optimize. Instead, look to either caching or regular pre-computation of the results.
I've found that an unfortunate number of people aren't aware of "EXPLAIN" and its use to help figure out query issues. Learn and use it.
You're on a VPS. That may be fine, but it may have horrible I/O throughput. If you're writing a lot of data, or having queries that hitting some sort of mysql or OS cache, IO will be your bottleneck. Make sure it's fast enough. Look at average IO wait times. Test max. throughput.
MyISAM tables may be an issue. MyISAM tables use table level locking - only one session at a time will be able to update the table. This is quite possibly a problem if you're doing any updates or inserts to a table that is also frequently read from. Look to move to InnoDB soon. See: http://dev.mysql.com/doc/refman/5.1/en/internal-locking.html
Hi! There are some good suggestions in this thread already. Step #1 is to determine why it is slow. If it is slow under load, then I am going to suspect that you are hitting table locks - which means you need to migrate to InnoDB (row locking + MVCC). Important to point out: a bigger server may not actually help enough here, because while you are waiting on locks nothing can be done while there is free capacity waiti…
Thanks. :)