hi wesley and the gang,
thank you very much for your help so far. i optimized the indexes and added
another one i obviously forgot before, and the database is smoother now. a
bit at least, it now maxes out at 65q/s instead of 50...
At 13:12 06.09.01, you wrote:
> > idea? And how am I supposed to find the slow queries?
>
>Find all the queries that interact with this table. EXPLAIN them. Time them.
*all* of them? there are lots. as said before, it is the main user table. a
user account is updated every time somebody request a page. and i need to
join to that table very often to find out, when the corresponding user
logged in last and what his name is. bad design? bad idea? what do you think?
i could split these queries below into multiple queries, first getting the
userid and then firing off another query to get the name (without a join).
but i thought letting the database handle this should be faster.
> > 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"?
>
>Yes. I wouldn't let queries with times like these near a busy production
>server I was responsible for unless I was positive they wouldn't and couldn't
>happen more than a handful of times a day. :-)
>
>Post the output of EXPLAIN for these queries. Along with the create defns
>for these other tables.
mysql> explain select * from cookies left join users on
cookies.uid=users.uid left join extended on users.uid=extended.uid where
cookies.cookie="ee33c9ec7950a1e5c39f37bf72d2de51";
+----------+-------+---------------+---------+---------+-------+------+-------+
| table | type | possible_keys | key | key_len | ref | rows | Extra |
+----------+-------+---------------+---------+---------+-------+------+-------+
| cookies | const | PRIMARY | PRIMARY | 32 | const | 1 | |
| users | const | PRIMARY | PRIMARY | 4 | const | 1 | |
| extended | const | PRIMARY | PRIMARY | 3 | const | 1 | |
+----------+-------+---------------+---------+---------+-------+------+-------+
3 rows in set (3.35 sec)
mysql> explain select * from cookies left join users on
cookies.uid=users.uid left join sessions on users.uid=sessions.uid where
cookies.cookie="5c50d7a7319ae6c8529f535aa5f19109";
+----------+-------+---------------+---------+---------+-------+------+-------+
| table | type | possible_keys | key | key_len | ref | rows | Extra |
+----------+-------+---------------+---------+---------+-------+------+-------+
| cookies | const | PRIMARY | PRIMARY | 32 | const | 1 | |
| users | const | PRIMARY | PRIMARY | 4 | const | 1 | |
| sessions | const | PRIMARY | PRIMARY | 4 | const | 1 | |
+----------+-------+---------------+---------+---------+-------+------+-------+
3 rows in set (4.55 sec)
doesnīt look too bad for me. except that timing information at the end :-(.
but thatīs because the database is almost completely locked up again
(processlist full of queries with Locked status).
the table definition is quite long, iīll append it to the end of the mail.
>On a busy database (thousands of queries per second), it doesn't take
>long for the whole database to get totally bogged down if a massive
>web of blocks happens like this. And mysqld doesn't always recover.
well, yes. i noticed that :-(. thank you for your good explanation though.
but i think itīs probably not very fruitful to look for queries in the
processlist that have "locked" status, because they are probably not the
slow ones that caused the block. am i correct?
>Banning queries that take longer than 0.00 seconds at the mysql> prompt
>(run them a few times - one or two >0.00s and it's suspect) is just
>a handy rule of thumb for measuring nastiness of queries. For proper
>measurements, one could benchmark queries - or one could stick a unique
>comment into every query and just watch processlists. You will come to
>recognise the common & slow queries because you will see them in the
>processlists often!
i also tried logging the queries that appear often with "copying to temp
table" status and now have a nice set of them, though i donīt quite
understand *why* the are copying. below are two:
(the rows count is *way* to high, probably because the timestamps are
ancient by the time i ran explain select)
select count(*) from adviews where click="f" and uid=7618 and cid=11 and
datestamp>=999782664
mysql> explain select count(*) from adviews where click="f" and uid=7618
and cid=11 and datestamp>=999782664 ;
+---------+------+-------------------------+------+---------+-------+------+------------+
| table | type | possible_keys | key | key_len | ref | rows
| Extra |
+---------+------+-------------------------+------+---------+-------+------+------------+
| adviews | ref | uid,click,cid,datestamp | uid | 4 | const | 2365
| where used |
+---------+------+-------------------------+------+---------+-------+------+------------+
1 row in set (0.00 sec)
index on all fields, optimized nightly. actually i just want to know
whether there is a corresponding entry or not. count(*) is supposed to be
fast, i thought??
select count(*) from chat where type="msg" and timestamp>999783881
mysql> explain select count(*) from chat where type="msg" and
timestamp>999783881 ;
+-------+-------+---------------+-----------+---------+------+------+------------+
| table | type | possible_keys | key | key_len | ref | rows |
Extra |
+-------+-------+---------------+-----------+---------+------+------+------------+
| chat | range | timestamp | timestamp | 4 | NULL | 753 | where
used |
+-------+-------+---------------+-----------+---------+------+------+------------+
1 row in set (0.00 sec)
hello please? wtf is mysql coping to temp table?
thanks again,
henning
--------------- create table defs ---------------------------------
CREATE TABLE `chat` (
`uid` int(11) NOT NULL default '0',
`cid` int(11) NOT NULL auto_increment,
`timestamp` int(11) NOT NULL default '0',
`type` enum('msg','emote','new','priv','away','cmd') NOT NULL default 'msg',
`uidto` int(11) NOT NULL default '0',
`msg` varchar(255) NOT NULL default '',
PRIMARY KEY (`cid`),
KEY `timestamp` (`timestamp`)
) TYPE=MyISAM
CREATE TABLE `adviews` (
`uid` int(11) NOT NULL default '0',
`bid` int(11) NOT NULL default '0',
`cid` int(11) NOT NULL default '0',
`datestamp` int(11) NOT NULL default '0',
`page` tinyint(4) NOT NULL default '0',
`click` enum('f','t') NOT NULL default 'f',
KEY `uid` (`uid`),
KEY `click` (`click`),
KEY `bid` (`bid`),
KEY `cid` (`cid`),
KEY `datestamp` (`datestamp`)
) TYPE=MyISAM
CREATE TABLE `extended` (
`uid` mediumint(9) NOT NULL default '0',
`passwort` varchar(32) NOT NULL default '',
`wantsex` enum('mann','frau') NOT NULL default 'mann',
`ort` varchar(45) NOT NULL default '',
`laenge` decimal(6,4) NOT NULL default '0.0000',
`breite` decimal(6,4) NOT NULL default '0.0000',
`zeigort` enum('nein','dist','ja') NOT NULL default 'dist',
`blindmail` enum('f','t') NOT NULL default 'f',
`email` varchar(100) NOT NULL default '',
`mailnotify` enum('f','t') NOT NULL default 'f',
`realname` varchar(100) NOT NULL default '',
`url` varchar(100) NOT NULL default '',
`message` varchar(250) NOT NULL default '',
`trash` varchar(255) NOT NULL default '',
`self` tinyint(4) NOT NULL default '0',
`birth` varchar(10) NOT NULL default '',
`newsletter` enum('f','t') NOT NULL default 'f',
PRIMARY KEY (`uid`),
KEY `breite` (`breite`),
KEY `laenge` (`laenge`)
) TYPE=MyISAM
CREATE TABLE `cookies` (
`cookie` char(32) NOT NULL default '',
`uid` int(11) unsigned default '0',
`cid` int(11) unsigned NOT NULL auto_increment,
PRIMARY KEY (`cookie`),
UNIQUE KEY `cid` (`cid`),
KEY `uid` (`uid`)
) TYPE=MyISAM
CREATE TABLE `sessions` (
`uid` int(11) NOT NULL default '0',
`cachedc1` char(32) NOT NULL default '',
`cachedc2` char(32) NOT NULL default '',
`cachedc3` char(32) NOT NULL default '',
`cachedc4` char(32) NOT NULL default '',
`cachedc5` char(32) NOT NULL default '',
`cachedc6` char(32) NOT NULL default '',
`cachedc7` char(32) NOT NULL default '',
`cachedc8` char(32) NOT NULL default '',
`cachedc9` char(32) NOT NULL default '',
`cachedc10` char(32) NOT NULL default '',
`cachedc11` char(32) NOT NULL default '',
`cachedc12` char(32) NOT NULL default '',
`cachedc13` char(32) NOT NULL default '',
`cachedc14` char(32) NOT NULL default '',
`cachedc15` char(32) NOT NULL default '',
`cachedc16` char(32) NOT NULL default '',
`cachedc17` char(32) NOT NULL default '',
`cachedc18` char(32) NOT NULL default '',
`cachedc19` char(32) NOT NULL default '',
`cachedc20` char(32) NOT NULL default '',
`cachedi1` int(11) NOT NULL default '0',
`cachedi2` int(11) NOT NULL default '0',
`cachedi3` int(11) NOT NULL default '0',
`cachedi4` int(11) NOT NULL default '0',
`cachedi5` int(11) NOT NULL default '0',
`cachedi6` int(11) NOT NULL default '0',
`cachedi7` int(11) NOT NULL default '0',
`cachedi8` int(11) NOT NULL default '0',
`cachedi9` int(11) NOT NULL default '0',
`cachedi10` int(11) NOT NULL default '0',
`cachedi11` int(11) NOT NULL default '0',
`cachedi12` int(11) NOT NULL default '0',
`cachedi13` int(11) NOT NULL default '0',
`cachedi14` int(11) NOT NULL default '0',
`cachedi15` int(11) NOT NULL default '0',
`cachedi16` int(11) NOT NULL default '0',
`cachedi17` int(11) NOT NULL default '0',
`cachedi18` int(11) NOT NULL default '0',
`cachedi19` int(11) NOT NULL default '0',
`cachedi20` int(11) NOT NULL default '0',
`cacheds1` enum('none','mann','frau') NOT NULL default 'none',
`cacheds2` enum('none','mann','frau') NOT NULL default 'none',
`cacheds3` enum('none','mann','frau') NOT NULL default 'none',
`cacheds4` enum('none','mann','frau') NOT NULL default 'none',
`cacheds5` enum('none','mann','frau') NOT NULL default 'none',
`cacheds6` enum('none','mann','frau') NOT NULL default 'none',
`cacheds7` enum('none','mann','frau') NOT NULL default 'none',
`cacheds8` enum('none','mann','frau') NOT NULL default 'none',
`cacheds9` enum('none','mann','frau') NOT NULL default 'none',
`cacheds10` enum('none','mann','frau') NOT NULL default 'none',
`cacheds11` enum('none','mann','frau') NOT NULL default 'none',
`cacheds12` enum('none','mann','frau') NOT NULL default 'none',
`cacheds13` enum('none','mann','frau') NOT NULL default 'none',
`cacheds14` enum('none','mann','frau') NOT NULL default 'none',
`cacheds15` enum('none','mann','frau') NOT NULL default 'none',
`cacheds16` enum('none','mann','frau') NOT NULL default 'none',
`cacheds17` enum('none','mann','frau') NOT NULL default 'none',
`cacheds18` enum('none','mann','frau') NOT NULL default 'none',
`cacheds19` enum('none','mann','frau') NOT NULL default 'none',
`cacheds20` enum('none','mann','frau') NOT NULL default 'none',
`prec` char(32) NOT NULL default '',
`prei` int(11) NOT NULL default '0',
`pres` enum('none','mann','frau') NOT NULL default 'none',
`votecookie` char(32) NOT NULL default '',
`timestamp` int(11) NOT NULL default '0',
`pren` char(32) NOT NULL default '',
`prer` decimal(4,2) NOT NULL default '0.00',
`cachedn1` char(32) NOT NULL default '',
`cachedn2` char(32) NOT NULL default '',
`cachedn3` char(32) NOT NULL default '',
`cachedn4` char(32) NOT NULL default '',
`cachedn5` char(32) NOT NULL default '',
`cachedn6` char(32) NOT NULL default '',
`cachedn7` char(32) NOT NULL default '',
`cachedn8` char(32) NOT NULL default '',
`cachedn9` char(32) NOT NULL default '',
`cachedn10` char(32) NOT NULL default '',
`cachedn11` char(32) NOT NULL default '',
`cachedn12` char(32) NOT NULL default '',
`cachedn13` char(32) NOT NULL default '',
`cachedn14` char(32) NOT NULL default '',
`cachedn15` char(32) NOT NULL default '',
`cachedn16` char(32) NOT NULL default '',
`cachedn17` char(32) NOT NULL default '',
`cachedn18` char(32) NOT NULL default '',
`cachedn19` char(32) NOT NULL default '',
`cachedn20` char(32) NOT NULL default '',
`cachedr1` decimal(4,2) NOT NULL default '0.00',
`cachedr2` decimal(4,2) NOT NULL default '0.00',
`cachedr3` decimal(4,2) NOT NULL default '0.00',
`cachedr4` decimal(4,2) NOT NULL default '0.00',
`cachedr5` decimal(4,2) NOT NULL default '0.00',
`cachedr6` decimal(4,2) NOT NULL default '0.00',
`cachedr7` decimal(4,2) NOT NULL default '0.00',
`cachedr8` decimal(4,2) NOT NULL default '0.00',
`cachedr9` decimal(4,2) NOT NULL default '0.00',
`cachedr10` decimal(4,2) NOT NULL default '0.00',
`cachedr11` decimal(4,2) NOT NULL default '0.00',
`cachedr12` decimal(4,2) NOT NULL default '0.00',
`cachedr13` decimal(4,2) NOT NULL default '0.00',
`cachedr14` decimal(4,2) NOT NULL default '0.00',
`cachedr15` decimal(4,2) NOT NULL default '0.00',
`cachedr16` decimal(4,2) NOT NULL default '0.00',
`cachedr17` decimal(4,2) NOT NULL default '0.00',
`cachedr18` decimal(4,2) NOT NULL default '0.00',
`cachedr19` decimal(4,2) NOT NULL default '0.00',
`cachedr20` decimal(4,2) NOT NULL default '0.00',
`nextcn` tinyint(4) NOT NULL default '0',
`swap` enum('ja','nein') NOT NULL default 'ja',
`oldi` int(11) NOT NULL default '0',
`oldc` char(32) NOT NULL default '',
`olds` enum('none','mann','frau') NOT NULL default 'none',
`oldn` char(32) NOT NULL default '',
`oldr` decimal(4,2) NOT NULL default '0.00',
`oldcn` tinyint(4) NOT NULL default '0',
PRIMARY KEY (`uid`)
) TYPE=MyISAM
---------------------------------------------------------------------
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
