[tiredofcareer]

Got it. So you have users that expect to have real-time conversations in the comments on a blog, meaning you can't optimize a blog application like a blog is designed to be used, meaning you have to pay for SSDs in order to make your blog function at all because apparently MySQL can't handle N inserts/second and however many people have these conversations refreshing every ten seconds, generating a few SELECTs that are rapidly in query cache.

I completely understand how this could be a problem and how switching providers would fix it.

[jacques_chester]

Sarcasm aside, you clearly don't understand what my problem is.

1. Recent Comments invalidates every page it appears on whenever a comment is posted in any thread. In practice that means that the entire site cache is invalid. That breaks whole-page caching models.

2. This means that Wordpress will regenerate from scratch.

3. This means first of all generating the page, which joins multiple tables including TEXT fields. Because of the brilliant design of MySQL, these joins ignore indices on the joining fields and frequently the join will occur on disk.

4. The Recent Comments plugin also causes joins on disk because it too refers to tables with TEXT fields.

5. The query cache helps a lot, but the site on Linode was still observably jammed on I/O, even when MySQL was given an entire server to itself.

However, if you feel you can do it better, I am happy to engage your services as a fulltime replacement. WPEngine said they could do it for $250/month (they couldn't). Pagely said they could do it for $149/month (they couldn't). I invite your bid.

[tiredofcareer]

> Sarcasm aside, you clearly don't understand what my problem is.

Yeah, I spent this entire thread clueless about the issue you're running into, even though you spelled it out a few different times because you think I don't get it. Wordpress falls over under normal site load, film at eleven.

Since you want to switch to condescension, I'm assuming wise sir moved MySQL's tmpdir to a RAM disk and found that unsatisfactory for his mystical, MySQL-breaking SELECT/INSERT workload? Also, I'm far more expensive, and I know that WPEngine is multitenancy Wordpress on Linode in the backend. (That one's free.)

[jacques_chester]

We're not being very productive here, are we? I could nitpick your comment just now but it wouldn't change your mind either.

You think I'm an idiot. Possibly you think I'm a liar.

I don't think you're an idiot. All I can do is point out that I looked at the numbers, I've tested various strategies or tools (and adopted most of them), I referred the problem to the experts, and this is where I've had to go.

So let's just ignore each other from now on.

[tiredofcareer]

I think you're unnecessarily combative in the face of advice and sitting comfortably atop your pillar of experience, ready to shoot down anyone that dare take time to offer you advice. You've appealed to your authority on this matter more times than I can count. Look at how you've approached the conversation from the very first reply, which set the tone for the rest:

    - I've been using WP Supercache since it was released
    - [I've been using] Nginx for I think 4 years at this point
    - Basically, I was there
    - I saw the numbers and I know why they wound up looking the way they did
    - I've been running Wordpress blogs since 2004
    - You clearly don't understand what my problem is

Now I've asked you something specific. You've lamented that you identified the issue as on-disk joins, when MySQL has to resort to an on-disk temporary table due to a TEXT column. That's discussed here[1]. I'm assuming, because I didn't assume you are stupid (unlike in the inverse), that you deduced this was the case by inspecting created_tmp_disk_tables. I then asked if you tried removing the disk from the picture by creating a RAM disk, mounting it somewhere, then instructing MySQL to use it for its temporary disk table area by setting tmpdir. I also assume you know that tmpdir defaults to the system /tmp, which might not be on a filesystem that you prefer[2]. Again, I assumed you knew these things, and just asked if you tried them.

How do you respond? "Let's ignore each other." So now I'm left wondering if you genuinely don't know how to scale MySQL, and you've tired yourself of appealing to your own authority in order to prove me wrong. What I'm telling you, is the notion that your blog network creating a workload for MySQL that it is incapable of operating on commodity disk is completely ridiculous, and I'd laugh you out of an interview if I pressed you like this. I think you gave up, but I wasn't saying it, but now that you've gone at me like this, I will. You're basically saying you couldn't make MySQL work with a <50QPS write load (I refuse to believe you're writing more than 50QPS to MySQL) because of some TEXT columns.

I'd have far more respect for you if you'd just say, yeah, I probably could make MySQL keep up with my blog workload, I just didn't put much effort into it and bought SSDs on a provider I don't prefer instead.

(But wait: I don't understand. Username oddly appropriate.)

[1]: http://dev.mysql.com/doc/refman/5.0/en/internal-temporary-ta...

[2]: http://dev.mysql.com/doc/refman/5.0/en/temporary-files.html

[jacques_chester]

> I think you're unnecessarily combative in the face of advice and sitting comfortably atop your pillar of experience, ready to shoot down anyone that dare take time to offer you advice.

I regret now being such a grump about it. But nothing you've so far suggested is new. I felt lectured down to and I felt supremely pissed off by it.

My remark that we should stop talking was because it was becoming increasingly acrimonious and I didn't see the point in further e-peen waving.

> you deduced this was the case by inspecting created_tmp_disk_tables

I did.

> I then asked if you tried removing the disk from the picture by creating a RAM disk

I did in 2007, actually, on a physical server I had access to. It would reliably lock up the DomU. I might not have been the only one[1]. I think I moved to Linode in 2008.

> So now I'm left wondering if you genuinely don't know how to scale MySQL

Entirely possible. I have as little to do with MySQL as I can. When the site slows down I learn a little more.

Take for example the documentation you referred to, in particular:

    Some conditions prevent the use of an in-memory 
    temporary table, in which case the server uses an 
    on-disk table instead:

    * Presence of a BLOB or TEXT column in the table

I learnt about that after a long period of fiddling with the tmp_table_size and max_heap_table_size values.

> What I'm telling you, is the notion that your blog network creating a workload for MySQL that it is incapable of operating on commodity disk is completely ridiculous, and I'd laugh you out of an interview if I pressed you like this.

I didn't believe it either. Yet there it was, chewing up disk. I got a lot of relief from implementing various caching strategies, switching web servers and so on and so forth. But eventually it was consistently bottlenecked on the database. So I broke the site into two servers, which gave me a few more years. But eventually it was, again, bottlenecked on MySQL.

> You're basically saying you couldn't make MySQL work with a <50QPS write load (I refuse to believe you're writing more than 50QPS to MySQL) because of some TEXT columns.

I didn't say that it's inserting. I'm saying that it creates temp tables on disk to satisfy fairly standard page and widget queries. If you thought I was talking about insertions then I can understand your skepticism.

88 QPS since last restart, FWIW. Hardly the world's biggest installation. About 90% of queries are served from the query cache; but of those that aren't, around 44% of joins are performed on-disk. That's pretty much what I've seen every time I look: around 45% of joins going to disk.

> I probably could make MySQL keep up with my blog workload, I just didn't put much effort into it and bought SSDs on a provider I don't prefer instead.

At the start of this thread you said that you've had varnish serve 3k RPS in front of a Wordpress instance on modest hardware. I agree that such performance is doable, even quite straightforward, for the common use case.

But if you take away caching, Wordpress is not quite so performant. And that's my problem; the whole-page caching strategy that makes thousands of RPS fairly straightforward doesn't work for me, because Recent Comments invalidates the entire cache.

So I have two choices: either I do without that particular widget and let varnish or nginx server up what are essentially static pages 95% of the time (and I have an nginx rule that does this with the gzipped pages that WP Supercache writes to disk).

Or I can accept that, because of the unusual pattern of usage, I am closer to the uncached baseline than most Wordpress installations are. Because the bloggers I host asked nicely, I have chosen the latter.

Putting my own anger down for a minute, I am happy to take any other advice you have. I projected onto you my own frustration.

[1] http://bugzilla.xensource.com/bugzilla/show_bug.cgi?id=1078