Hi,
> > Looking in the process table reveals (when the page is fast) lots of
> > sleeping processes, sometimes (when the page is slow -- 30sec to load a
> web
> > page) lots (20+) processes that are locked. Usually they are some SELECTs
> > and UPDATEswaiting for a single table that is the most update intensive
> > (one update per web page served, changing one to three columns in one row
> > that is indexed by a primary key). The complete DB size 380MB, the
> "problem
> > table" is 4MB large.
>
>Focus on this table. Specifically on the queries that access this table.
>At least one query is much slower than it needs to be. Post the create
>description of the table. Post some of the queries that block on this
>table. If you can, find the query or queries that are slow on this table.
>Optimise them.
All the updates to the table are of the style described above (one to three
colums changed, row indexed by primary key). Well, with SELECTs itīs a
different story: many queries join in different ways to the table. FYI,
itīs the table where all user information is stored, so I need a join every
time I need e.g. to find the name corresponding to an userid. Is that a bad
idea? And how am I supposed to find the slow queries?
>It might be as simple as the table having a varchar column being compared
>numerically (without quotes). It might be as simple as needing an index to
>that table.
I wish. The table is constant-row-length (no variable colums) and indexed
on the important fields.
I`m not quite sure whether it is really a contention problem. A standard
query takes about 0.2msec on this computer (own benchmark); even if slow
queries took 20ms and all queries where slow queries, the system should
nevertheless be able to answer 50 queries/sec.
If it was a contention problem, I could switch to another table type. Would
that help? What do you think?
TIA,
henning
----------- create description follows -----------------------
#
# Table structure for table 'users'
#
CREATE TABLE users (
uid int(11) NOT NULL auto_increment,
active enum('f','t') DEFAULT 't' NOT NULL,
lastlogin int(11) DEFAULT '0' NOT NULL,
sex enum('unbekannt','mann','frau') DEFAULT 'unbekannt' NOT NULL,
isuser enum('gast','user') DEFAULT 'gast' NOT NULL,
ratewhat enum('beides','mann','frau') DEFAULT 'beides' NOT NULL,
numlogins mediumint(9) DEFAULT '0' NOT NULL,
votescast mediumint(9) DEFAULT '0' NOT NULL,
username char(20) NOT NULL,
offline enum('f','t') DEFAULT 'f' NOT NULL,
perstopre enum('f','t') DEFAULT 'f' NOT NULL,
gotmail enum('f','t') DEFAULT 'f' NOT NULL,
prevvote smallint(6) DEFAULT '0' NOT NULL,
forumfloat enum('f','t') DEFAULT 't' NOT NULL,
nested enum('f','t') DEFAULT 'f' NOT NULL,
msgperpage tinyint(3) unsigned DEFAULT '20' NOT NULL,
newfirst enum('f','t') DEFAULT 'f' NOT NULL,
adm
set('stats','banner','bilder','foren','uinfo','nobanner','matchmaking','db','chat','mails')
NOT NULL,
getmails enum('f','t') DEFAULT 't' NOT NULL,
firstlogin int(11) DEFAULT '0' NOT NULL,
PRIMARY KEY (uid),
KEY username (username)
);
---------- the two types of slow queries (from the slow log)
---------------------
Type I:
select * from cookies left join users on cookies.uid=users.uid left join
sessions on users.uid=sessions.uid where
cookies.cookie="e3bd03382561eb3619b66fbea2af217d";
select * from cookies left join users on cookies.uid=users.uid left join
extended on users.uid=extended.uid where
cookies.cookie="5226220e3b62cef71a13524ec7a413ac";
(above queries have to be performed at the beginning of every webpage to
find the current user. i donīt really think they are slow; they just lock
because of something else.)
i just tried the above query ten times. result: 7x 0.00sec, 1x0.01, 1x0.02,
1x0.04. does that spell "slow"?
Type II:
update users set lastlogin=999697993, perstopre="f" where uid=40651;
update users set lastlogin=999698763, votescast="1514", prevvote="-8" where
uid=54307;
(the usual locked queries)
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
