Confirming on Fedora 4, P4 32bit, MySQL 4.1.12.
I'm working on small tables (< 20,000 rows) and having hangs for tens of minutes (!?). It started occuring after upgrading from MySQL 3.x to 4.1 branch. Queries that normally run for 0.5 second now get killed by timeout.
I've just found one interesting thing. Here's a query that hangs the server (a lot of cpu load)
SELECT DISTINCT o2.id, sum( o2.qtt ) AS sum, (
sum( o2.qtt ) /17598716
) AS
`share` FROM outgoing2 AS o2, outgoing AS o
GROUP BY o2.id
ORDER BY o2.id ASC
Now - I've found that the `outgoing` table is not needed here as I've accidentally put it. After correcting the query to:
SELECT DISTINCT o2.id, sum( o2.qtt ) AS sum, (
sum( o2.qtt ) /17598716
) AS
`share` FROM outgoing2 AS o2
GROUP BY o2.id
ORDER BY o2.id ASC
... it finishes in about 1 second (still slower, but working at last).
I repeat: THE SAME SELECT with SAME DATA in mysql 3.x worked like charm, giving results instantly. After moving the whole complete structure + data to MySQL 4.1 - I can't work anymore.
I've tried mangling with caches and buffers - no effect. I've put heavy load on memory, also tried reverting to defaults (near clean my.cnf) - no effect. I've found some relevant info on:
http://www.vbulletin.com/forum/archive/index.php/t-41370.html
http://www.vbulletin.com/forum/archive/index.php/t-2338.html
Anyone of MySQL AB reading this thread ?