MySQL Forums
Forum List  »  Performance

Re: Has anyone found a Viable Solution?
Posted by: joustin
Date: October 05, 2005 04:58AM

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 ?

Options: ReplyQuote


Subject
Views
Written By
Posted
8011
December 07, 2006 10:30AM
9388
September 23, 2005 01:56AM
Re: Has anyone found a Viable Solution?
4687
October 05, 2005 04:58AM
5843
October 05, 2005 11:49PM
5183
October 06, 2005 01:34AM
6405
October 06, 2005 04:57PM
6215
January 21, 2008 10:14AM
5633
January 21, 2008 10:53AM
5065
May 19, 2008 09:41AM
5322
February 05, 2008 10:27AM
5801
March 12, 2008 08:37AM
5903
November 14, 2008 12:31AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.