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