21–30 of 32 posts
It seems pretty clear that you don't have enough experience on your own to resolve this properly, so call in some help. It may be possible to make an architectural change that significantly reduces the resources you need, or perhaps you'll find that you unavoidably need more resources to do what you want. Someone who knows how to diagnose and analyze this properly can tell you that.
As is being discussed in the other thread.
Earlier quoted context omitted.
>What is the actual bottleneck? As mentioned in OP, it is MySQL. >you can't run a massive database on crappy hardware and expect it to work smoothly. Absolutely... but we do wish to milk the VPS for every cent of its worth and we're not sure we've got there yet. Thanks for your input.
> As mentioned in OP, it is MySQL. That's not specific enough. Yes, the problem is manifesting itself via MySQL, but the bottleneck will be somewhere else; whether RAM, CPU, or I/O-wait.
Earlier quoted context omitted.
> As mentioned in OP, it is MySQL. That's not specific enough. Yes, the problem is manifesting itself via MySQL, but the bottleneck will be somewhere else; whether RAM, CPU, or I/O-wait.
Exactly. If the OP can post the output of top, vmstat and iostat both when the problem occurs and after restarting the processes, that would be a start. There might be some simple thing you can tweak to improve things.
Earlier quoted context omitted.
>You need to examine the restart process and analyze Like I said in OP, we've identified MySQL to be the primary bottleneck and are already working on resolving this. >whose transactions are abandoned. At least some saving grace here... we're not selling anything... it is purely a content site Thanks for your input.
>Like I said in OP, we've identified MySQL to be the primary bottleneck and are already working on resolving this. Ah, yes. I remember from your prior post that you have very large databases and table sizes and are considering (or have begun) partitioning the largest tables. It turns out there is an innate partitioning scheme built into the most recent MySQL versions, but it has to be compiled into the running binary…
Perhaps, it is time for us to revisit this. Thanks again Paul.
Earlier quoted context omitted.
> As mentioned in OP, it is MySQL. That's not specific enough. Yes, the problem is manifesting itself via MySQL, but the bottleneck will be somewhere else; whether RAM, CPU, or I/O-wait.
For now, we're making our code/queries/optimization take the blame rather than the hardware.
Regarding apache/mysql: are you using stock distributions, or did you build them yourself? Are you using any unusual configuration in either of them?
You say you kill both mysql and apache, but what happens if you just kill one or the other? I'm just wondering if it is some buggy cgi script that is leaking resources.
Earlier quoted context omitted.
For now, we're making our code/queries/optimization take the blame rather than the hardware.
I can't really see how your code or queries could cause mysql or apache to gradually slow down and then speed up when you kill the processes. It sounds more like virtual memory of some process is growing too high causing thrashing, or apache has too many children, or you're running out of some resource, or something is leaking memory. Regarding apache/mysql: are you using stock distributions, or did you build them yo…
Actually, we've ascertained that it is not the code or queries that cause this per se, but it is the traffic patterns - human visitors + SE crawlers... at times, there are (natural) traffic bursts and that's when the server starts choking.
apache/mysql - stock distros, with some config tweaks, mainly in my.cnf.
>what happens if you just kill one or the other?
Good call. Actually, restarting just apache does the trick most of the times. What we suspect is, abrupt flooding in http requests causes MySQL to slow down which causes http requests to get queued up and finally choke the service.
while the root cause should be fixed ( sound like you're working on it ). consider using monit for the restarts instead of cron : http://mmonit.com/monit/
I've had load issues in Postgres, not MySQL before and it was due to autovacuum running on tables getting updated/inserted frequently. I'm not sure what the equivalent in MySQL is, but if you have a ton of insert/update queries, consider archiving your tables after a certain period of time, so that your main table doesn't have ton of rows. You can consider sharding of course, but also consider sharding the actual tab…
Thanks for your in-depth reply and suggestions. MySQL performance tuning and table structure/query optimization-wise, we've left few stones unturned (but I'm sure there still is a scope for further improvement - isn't there always?). Not using Rails. Server specs: 4 cores, 3.2GB RAM >and the easiest route is just to upgrade your server. Please refer to the other thread for more on this. Thanks again.
Earlier quoted context omitted.
I can't really see how your code or queries could cause mysql or apache to gradually slow down and then speed up when you kill the processes. It sounds more like virtual memory of some process is growing too high causing thrashing, or apache has too many children, or you're running out of some resource, or something is leaking memory. Regarding apache/mysql: are you using stock distributions, or did you build them yo…
>I can't really see how your code or queries could cause mysql or apache to gradually slow down and then speed up... Actually, we've ascertained that it is not the code or queries that cause this per se, but it is the traffic patterns - human visitors + SE crawlers... at times, there are (natural) traffic bursts and that's when the server starts choking. apache/mysql - stock distros, with some config tweaks, mainly i…