The number of update and insert requests seems quite stable over the past month. But the number of select requests has increased markedly. Why is that? It predates todays RAM upgrade. IIRC you re-jigged the MySQL tables. Is the increase simply because MySQL does more selects as a result of the DB work? Does it reflect new selects being generated as a result of a similar load?
Were you RAM constrained before and did that contribute to the record locking for long periods during maintenance? Which lead to the 502's. Just reading what has been reported I don't quite see how more RAM has addressed the problems reported. Were there performance issue at times outside the DB maintenance intervals?
Sorry gnif I don't mean to bombard you with questions it is just that I am not sure of the right question in MySQL speak.
No worries mate. The pickup in qps is coincides with the MyIASM to InnoDB conversion on those final few tables, we are seeing far fewer lock contentions and as such the total server throughput went up. It went up because prior when a table was locked, it would put the next query into a queue, this is fine provided the queue length doesn't blow out, like it started to. In short, we were limited by locking, which throttled throughput.
Once we switched to row level locking, there is no longer (much) lock contention, and as such all these requests now can run concurrently, so this increases our throughput, people get faster response, etc... This explains the jump in queries per second. I also went through and added some indexes to fix some slow queries, SMF developers don't seem to understand the importance of them, this is not the first time I have had to fix queries.
But then we hit I/O issues as we had also increased our IOPS, so instead of seeing as many 502's due to PHP being too busy, we were seeing more slower responses due to the now much increased IO load, which in turn affects the entire server, not just DB requests. Most people throw a SSD in to resolve this, but it is unwarranted in most cases and at best a SSD is a temporary fix. The usually better solution is to throw a bit more ram at the server and cache the indexes and entire working data set, since RAM is orders of a magnitude faster then a SSD, and once the server has warmed up (data is in RAM), it barely touches the disk except for updates/inserts.
Finally since we have more RAM then we expected, I thew some extra to Memcached.