As an aside, pitcher.io looks cool... congrats :)

If you don't mind, I'd suggest toning down the 'Our' side of the copy a bit... e.g. 'Our amazing content. Your expert knowledge.' sounds too boastful (no offense) and might sound better with 'great' instead of 'amazing'...

Also: 'To start you off every Pitcher account comes loaded with content we love' might be better as 'To start you off every Pitcher account comes loaded with great/useful content' ...

HTH and all the best.

Wow!!! Didn't know about this - took a quick look and found it very interesting. Thanks.

>so call in some help.

As is being discussed in the other thread.

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

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.

Will try and post within a few hours. Thanks.

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

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

Ok, I still think you probably need to spend some money on better hardware, but here are a few ideas for eeking out some more performance from your existing setup:

- Limit the max apache child processes/threads to what your server can easily handle. That way during busy periods people will get an occasional error connecting to your server (or wait a while), but at least mysql won't be thrashing your server so it might improve overall performance during the busy times.

- Figure out which queries are using the most cpu time and optimize those.

- Have a look at your most frequently accessed pages and see if you can optimize database access in those pages.

However if your users are still sometimes getting error messages accessing your site you have to ask yourself whether that is going to hurt your business.

Are you sure you have a database connection pool in place? Ensure the # of active connections remain constant no matter how many http requests are coming in.

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.

I hope you're paying less than $10/month because you can get a dedicated server with 10 times more RAM here: http://www.ovh.com/us/dedicated-servers/

And how many reads are there usually a min? writes?

Paying way more than $10/mo for the VPS, close to 200 - and hey!! thanks for the ovh.com link - looks very interesting, will certainly try them out soon. Thanks.

Your server barely has more RAM than my phone. Your disk almost certainly has less reliable performance than my laptop's.

If you're doing something that makes any money you should get a real server before wasting days of effort or intentionally causing repeated outages.

Can you post which mpm you are using, and what your mpm configuration looks like for Apache? Does mysql report anything in its log? Do any requests return too many connections? Do you have some kind of heavy software running on the same server (trac for instance)? What kind of table engine are you using? Are you caching read data?

Yes, as mentioned in the other thread, we're way ahead of this - but thanks for your input all the same.

k, good luck!

>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 by way of a compiler flag:

http://dev.mysql.com/doc/refman/5.1/en/partitioning.html

I apologize if you've already heard this, or if it's already in place as a partial solution.

Yes, we did investigate MySQL's internal partitioning option briefly before deciding to roll our own scheme (which, after working quite well initially, is now beginning to create problems of its own).

Perhaps, it is time for us to revisit this. Thanks again Paul.

We are actually more than worried too, because we _know_ we're losing plenty of requests daily so trying to cut down the losses temporarily while we figure out a more long-term solution.

For the rest, this thread might give you some more info on the issue: https://news.ycombinator.com/item?id=6348903

Good call - thanks :)

>Do you really need all that data in real time? Are you querying all of it in your app?

Yes and yes. ALL the data records could potentially be queried via site navigation.