|
..or is this site running like a dead slug today?
|
no - for some reason - although I have been largely ignoring this server, the query that made the panel on the RHS is killing the server.
I thought I had changed it to be remotely quick, but now it is terrible...
|
Well, if you say so. But it was b***** awful even before it was trying to draw that frame.
Still, you're the expert, let's hope it's ok now..
|
Been completely down for about an hour, bad gateway.
Missed you all terribly.
|
|
I got bad gateway as well, seems OK now, although the number of topics on the non motoring forum seems to have dropped ...
Last edited by: helicopter on Tue 9 Feb 21 at 19:30
|
Yeah - that's what it does when a ton of the same query get stuck and it runs out of PHP processes because they're all waiting to hear back from the database server.
If anyone out there is an expert in SQL maybe they could have some suggestions for speeding this query up:
SELECT p.author, p.id, p.forum_id, p.subject, p.date_created, p.body
FROM posts p
JOIN forum f ON f.id = p.forum_id AND f.hidden = 0
JOIN thread t ON t.id = p.thread_id AND t.hidden = 0
WHERE p.hidden <> 1
ORDER BY p.id DESC LIMIT 6;
It wasn't a problem when the engine was Aria (or MyISAM) but InnoDB doesn't like the additional search on
f.hidden = 0, and
t.hidden = 0
Take them off and it is really quick but then you get to see all the nasty nasty things that mods write in their hate speech parlour and they would never forgive me ;-)
|
>>then you get to see all the nasty nasty things that mods write in their hate speech parlour
It'll all just be endless joyful prose in worshipful admiration of me, same-ole, same-ole.
|
Latest Forum Posts
This panel is killing the server...
|
.. just in case you wondered where it had gone ;-)
|
|
Dup of Zero's observation.
Last edited by: zippy on Tue 9 Feb 21 at 19:38
|
|
I think I have fixed it - finally. Fingers crossed
|
|
Was slow when I tried to use it about an hour ago, but all seems good now!!
|
Went downhill fast during the late afternoon. Blistering fast now.
Purely a matter of curiosity but what causes a routine that's run quite happily for 10 years to suddenly turn to treacle?
|
>>
>>
>> Purely a matter of curiosity but what causes a routine that's run quite happily for
>> 10 years to suddenly turn to treacle?
>>
Just the mods getting bored and looking to generate a thread while they sit around giggling.
|
>> Purely a matter of curiosity but what causes a routine that's run quite happily for
>> 10 years to suddenly turn to treacle?
Until recently it was an old tech forum using old tech tools and approaches in an old tech environment.
Now it's an old tech forum using old tech tools and approaches in a very new tech environment.
Not everything remains compatible over time so some bits break, and some bits continue as they were though without the ability to take advantage of the new tech environment.
The broken bits need changing and typically get changed to new approaches and tools.
The resulting jumble does not necessarily play well with other bits when put together.
|
SQL in particular, at least in my now dated experience, can be a real problem in migration from one underlying infrastructure to another (and I have experience on some infrastructures of even just a change of data being an issue).
The query as written above is simply a relatively high-level description of the data to be returned to fulfil it. The underlying infrastructure (the database management system) decides how best technically to satisfy that request. Move from one database to another, and though the query remains valid and requests the same logical data, the new system might decide to satisfy the request in an entirely different way (and presumably, in this case somewhat more inefficiently).
Even worse, in the case of IBM's DB2 (where my experience is admittedly rather more than 30 years old), the method of retrieving the data used to be decided by the database at first use of the query ("bind" time), which was a pain when data volumes later varied sufficiently to make the query (very) inefficient.
For example, if there were at initial use three records in a file/table subject to a particular query, the required one would be returned simply by reading all three, and selecting the appropriate one. If the number of records grew to hundreds of thousands, that initial use would still define (without further intervention) that all of the hundreds of thousand of records would be read to satisfy the query. In anticipation of the growth, a database designer might choose to "index" the records, potential reducing the reads to a very small number, but, with only three records in existence at first use, DB2 would still stubbornly choose to read all that existed for subsequent queries! (It was generally circumvented by arbitrarily populating with large numbers of records for a dummy first run - forcing use of the index - then deleting them). We would regularly lobby IBM to implement a UTFI SQL command ("Use This ******* Index) ;-)
|
>> "Use This ******* Index"
Yup. That was the kind of thing going on here.
Essentially in the query above one would expect it to be really quick. All the key fields are indexed. Heck, even the is_hidden fields are indexed.
Regardless one is at the mercy of the SQL optimiser at work which works out the best way to perform the query for you.
A useful tool is the "EXPLAIN" keyword which tells you what it does to do the query.
So in the above statement you get this:
mysql> EXPLAIN SELECT p.author, p.id, p.forum_id, p.subject, p.date_created, p.body
-> FROM posts p
-> JOIN forum f ON f.id = p.forum_id AND f.hidden = 0
-> JOIN thread t ON t.id = p.thread_id AND t.hidden = 0
-> WHERE p.hidden <> 1
-> ORDER BY p.id DESC LIMIT 6;
+------+-------------+-------+--------+---------------------------+---
--------+---------+------------------------+-------+------------------
----------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+-------+--------+---------------------------+---
--------+---------+------------------------+-------+------------------
----------------------------+
| 1 | SIMPLE | t | ref | PRIMARY,hidden | hidden | 1 | const | 11659 | Using index; Using temporary; Using filesort |
| 1 | SIMPLE | p | ref | forum_id,thread_id,hidden | thread_id | 8 | car4play_db.t.id | 11 | Using where |
| 1 | SIMPLE | f | eq_ref | PRIMARY,hidden | PRIMARY | 4 | car4play_db.p.forum_id | 1 | Using where |
+------+-------------+-------+--------+---------------------------+---
--------+---------+------------------------+-------+------------------
----------------------------+
I know it looks a jumble, but the important things to notice are that they way the optimiser has decided to run this query is to start with the 'thread' table, not the 'post' one, and that query will involve "Using temporary; Using filesort".
Sounds expensive ... and it is, very ...
This query used to be bad at 6 seconds, but once these start to build up in the queue it can take much longer than that and then we are into the spiral of death. BTW the pane results are cached for a few minutes, so that makes it more tolerable, but even still if it fails to get the cache in the first place one is still stuck.
The answer was to give the optimiser the hint to use the logic order presented in the query, and for that it was a case of RTFM.
Use the Keyword "STRAIGHT_JOIN". From the manual
"STRAIGHT_JOIN forces the optimizer to join the tables in the order in which they are listed in the FROM clause. You can use this to speed up a query if the optimizer joins the tables in nonoptimal order. STRAIGHT_JOIN also can be used in the table_references list..."
So you get this:
EXPLAIN SELECT STRAIGHT_JOIN p.author, p.id, p.forum_id, p.subject, p.date_created, p.body
FROM posts p
JOIN forum f ON f.id = p.forum_id AND f.hidden = 0
JOIN thread t ON t.id = p.thread_id AND t.hidden = 0
WHERE p.hidden <> 1
ORDER BY p.id DESC LIMIT 6;
+------+-------------+-------+--------+---------------------------+---
------+---------+-------------------------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+-------+--------+---------------------------+---
------+---------+-------------------------+------+-------------+
| 1 | SIMPLE | p | index | forum_id,thread_id,hidden | PRIMARY | 8 | NULL | 31 | Using where |
| 1 | SIMPLE | f | eq_ref | PRIMARY,hidden | PRIMARY | 4 | car4play_db.p.forum_id | 1 | Using where |
| 1 | SIMPLE | t | eq_ref | PRIMARY,hidden | PRIMARY | 8 | car4play_db.p.thread_id | 1 | Using where |
+------+-------------+-------+--------+---------------------------+---
------+---------+-------------------------+------+-------------+
Much better. All indexes used and no extra nasties involving temporary tables.
Furthermore query time is now less than 0.03s.
So there you have it. The move to InnoDB, which is the default on Amazon AWS means the optimiser is different and so the solution has to change to make the most of it.
And I get to learn a new feature.
|
|
That pretty well sums it up Mark
|
Is it just me.... - car4play
