MySQL Forums
Forum List  »  Performance

slow selects from a 3-element table
Posted by: mafmaf
Date: December 06, 2004 09:08AM

I'm experiencing some bizarre performance problems with mysql.

In the slow query log (>=1s) I see a lot of queries some of which are very simple.
For example, there is a table of 3 elements which is updated once a week but some
of the many selects on this table get 2-3 seconds for mysql to perform. The table
definition looks like this:

CREATE TABLE adm_cidbans (
id int(11) NOT NULL auto_increment,
cid varchar(32) NOT NULL default '',
btime datetime NOT NULL default '0000-00-00 00:00:00',
days int(11) NOT NULL default '0',
op varchar(32) NOT NULL default '',
op_ip varchar(20) NOT NULL default '',
reason varchar(255) NOT NULL default '',
PRIMARY KEY (cid),
KEY id (id)
) TYPE=MyISAM;

The only (!) queries on this table are the following:

SELECT UNIX_TIMESTAMP(btime) FROM adm_cidbans WHERE cid = '2486d3f1a94f807292f6f27c961c63aa'

(of course the value for cid in apostrophes is different for each query).

Sometimes when I run "show processlist" command, I can see that some threads
which are performing these queries have a state "statistics". A lot of other tables
have this state too.

The server the database is running is dual xeon with 4gb of ram 1.8gb of which is
used for mysql's key buffer. The load on the server is around 10, but vmstat shows
the processors are 50-60% idle. There is no swap. The number of mysql processes
is around 150, and the total number of all processes in the system is around 350.
The total number of mysql queries is around 500 per second on average but strangely
it's not at this level all the time -- it changes from 100 to 1000 in a few seconds
as if all threads were blocked for some reason.

Does anyone have an idea what could be the reason for that strange behaviour of
these slow selects from a 3-element table?

M.

Options: ReplyQuote


Subject
Views
Written By
Posted
slow selects from a 3-element table
2447
December 06, 2004 09:08AM


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.