Wouldn't it be interesting to unfreeze the development and see if you can't refactor to avoid unnecessary queries?
Code topology can heavily affect the amount of queries.
What I meant was feature development... that's what got frozen long ago... code revision exclusively for performance optimization has been ongoing continually.
Yes, we enable slow query logs from time to time, look at those and try to optimize for those. MyISAM tables.
We've been cycling frequently (and painfully) through (2) (and less frequently through (1)) for the best part of three years now, so please don't accuse us at least of looking for a quick fix :)
With MyISAM tables you could have integrity problems. And if you are mixing innodb and myisam you are splitting buffers memory that well could go to just one kind (i.e. most memory for innodb) if you switch to that.
If innodb is slow in insertions, you could try using TokuDB as storage engine, that is pretty fast in that and have a bunch of advantages.
And if you are using MyISAM for some particular feature (i.e. full text indexing) MySQL 5.6 already have it in innodb, or you can separate that search to a sphyinx server.
are queries uniform over the data. For instance most of queries conerns only a little fragment of one table for instance most recent rows. Can you shard them ?
Let me try to answer: Slow - as in average Top loads (on CentOS) staying between 1 and 2 pretty much throughout the day; closer to 2 rather than to 1, peaking ALL the way up to 10 and even beyond several times a day, for periods as long as 15 to 30 minutes.
Do you have some other measurements? slow_log, queries without indexes, low key cache usage? Load is just one metric and can be very deceiving. Do you have any backup system which hits the disks at the same time, batch jobs, or something similar? Depending on your workload, "load" can vary - it just means the writes are being queued up.
It's definitely not a good sign, but try to get more specific. I've seen servers doing heavy network I/O with "normal" load over 5 times the number of cores.
Also, unless you're doing loads of selects and very few modifications, you could gain a lot by switching to InnoDB, or XtraDB, rather than MyISAM.
Have to second this. You should monitor load for sure... but depending on how many cores you are running a load of 1 or 2 could be absolutely nothing in the grand scheme of things.
Personally, the most important metric for us is average query response time.
Additionally as others have stated where you can use memcache or some other key/value store you should be working on implementing that.
The spike to 10+ sounds like it might be io blocking rather than CPU (which would be stuff like queries). If you look at your time wait (%wa) in top, and the output of iostat, you should be able to get an idea. If you're on VPS systems with older SATA drives that could be the bottle neck. If the Kernel is waiting for the disk(s) to be able to write more data to the storage bus/buffers it will block the storage process which will result in blocking for any application trying to access storage. As more and more processes block they'll go into poll/sleep loops and all those instructions will seem to spike the CPU load and make the CPU look busy even if the CPU is just sitting there saying "storage is still busy" over and over again. You probably have 1.00 to 2.00 true CPU load (queries, etc) which isn't that bad. But, you generally want to keep your CPU load below 1.00/per cpu. Otherwise, there is CPU level blocking. If you were swapping a lot (which you can also tell from top) that would aggravate and storage subsystem overloading. I would just post the output from top and iostat during one of the spikes here and see what people say rather than hiring an expensive consultant. You could probably find an experienced sysadmin to look at it as a favor, as well. They should be able to tell you quickly if you really need someone to look at your MySQL and app code to address this. Or, if you just need to add more nodes to your MySQL cluster (or look at something else like a NoSQL type setup).
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. :)
You should look into caching. If you are talking about webvet.com it looks like a perfect site for using something like Varnish. Most of the content probably doesn't change often so you should be able to have a long TTL.
Nothing that we'd really know of, because we never actually used any. :)
Having said that, I must admit that we did study the specs of some of those and found a few gaps here and there (at least so far as our requirement was concerned).
To list a few off the top of my head:
1) Returning of http response codes
2) Following 300 class redirects
3) Control over exact pixel size of the images
etc.
Why didn't you use them? A few missing features don't justify launching a whole new service, as the existing ones can just add the features in a day or so.
The primary reason we didn't use them was the lack of several features we needed.
We're not really concerned about the existing services adding the missing features - what we really wish to find out is whether or not there exist (at least a decent sized) market for such a service.
Nice idea - nice project. IMO, it could really thrive once it gets good traction - congrats.
One (small) suggestion: Change your tag line from:
'Build a Shyahi profile to showcase your developer contributions and social activity.'
to:
'Showcase your developer contributions and social activity'
and make it visible somewhere in the first screen-fold. HTH
