On Thu, Sep 06, 2001 at 12:02:07AM +0200, Henning Schroeder wrote:
> 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?
Find all the queries that interact with this table. EXPLAIN them. Time them.
> If it was a contention problem, I could switch to another table type. Would
> that help? What do you think?
Possibly, likely it would only make the symptoms a little less painful.
>
> 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.
> 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)
When these are locked, not only are they waiting for the selects that
came before them to finish, they are blocking selects behind them in
the queue from executing simultaneously with selects ahead of them.
Selects in the queue for the users table that join to other tables
(and you have plenty of them) will block updates/inserts to those
other tables.
Imagine now that an insert arrives to one of these other tables and
behind it comes another multi-table select, joining to other tables.
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.
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!
Wesley.
---------------------------------------------------------------------
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
