On mer, 2003-01-29 at 14:15, Jan Hidders wrote:
On Wed, Jan 29, 2003 at 10:43:00PM +0100, Erik Moeller
wrote:
Is it
correct the we are now using the locking mechanism in MySQL? (I
haven't been following this list for a while.) If so then this is probably
causing all the delays. Was there is specific reason for starting to use
locking?
No. We switched to the InnoDB table type, which uses row-level locking
instead of MyISAM's table locking. So in general, things should get much
faster.
Not necessarily, row level locking causes sometimes a *lot* more overhead
and can block *more* than table level locking. I assume you have read the
manual but for good measure I will quote the relevant part anyway:
If you have concrete suggestions on how to reform our code, I'd *love*
to hear them. I say this in all seriousness; I freely admit that I'm new
at this database stuff, and am unsure of the best course of action.
Our slow locks currently seem to be happening on 'old' and
'searchindex', both MyISAM tables -- so no row-level locks on them. When
one thread is stuck reading the table, and other threads are sitting
waiting for it (or rather waiting for another thread which is waiting to
write the table), killing the stuck thread springs things back into
action.
'old' is usually being queried for the list of previous edits for a
given page, or sometimes user contribs; these queries have no joins, and
are WHERE'd with = on indexed fields. It appears to be properly indexed,
so I'm unsure why it sometimes takes so long. It's rarely written to
except for an INSERT on each page save.
'searchindex' has a row REPLACEd into it on every page save, and has
SELECTs run over most of the table quite frequently (fulltext MATCH
AGAINST), with a join on the integer index to the cur table. As an
experiment last night, I tried taking out the join and grabbing the cur
data in a separate query; it didn't improve performance any, so the join
doesn't seem to be blameable.
> Cons:
>
> * Takes more memory than page level or table locks.
> * Is slower than page level or table locks when used on a big part of
> the table, because one has to do many more locks.
> * Is definitely much worse than other locks if you do often do GROUP BY
> on a large part of the data or if one has to often scan the whole
> table.
This is (or should be) relatively rare; the vast majority of selects are
either on one row (check the existence of an article) or on a specific
subset (grab rows belonging to one or a particular set of articles).
GROUP BY does turn up in a couple of places:
* Recentchangeslinked (where it seems to be redundant, and should only
operate on a small subset of pages)
* Undelete (sysops only, rarely used, on a small table)
* Wantedpages (currently disabled)
> * With higher level locks one can also more
easily support locks of
> different types to tune the application as the lock overhead is less
> notable as for row level locks.
Examples?
Especially if you are also going to use transactions
that span more than 1
SQL statement (are you?) things may even get considerably worse.
If you mean chunks of statements actually wrapped in BEGIN/COMMIT
blocks, then there are only a couple of these at present, both done on
page save. The first should be quite lightweight in terms of rows, doing
a 1-row update to 'cur', a 1-row insert to 'recentchanges', and two more
updates to 'recentchanges' which hit respectively 1 row and as many rows
as are present that refer to the particular article.
The other does deletes and insertions on the links and brokenlinks
tables, and may hit a larger number of rows when touching oft-linked
pages. I've not yet seen any evidence that anything is blocking on locks
on the link tables.
-- brion vibber (brion @
pobox.com)