On Wed, Jan 29, 2003 at 03:55:16PM -0800, Brion Vibber wrote:
On mer, 2003-01-29 at 14:15, Jan Hidders wrote:
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.
Ok, sorry for being critical in an unhelpful way. But I would still want to
know if there was a concrete problem that you wanted to solve with row-level
locking, or would going back to full MyISAM be an option? My gut feeling is
that row locking really doesn't help much because our access patterns are a
lot of small reads (fetching pages), a few big reads (recent changes et
cetera) and relatively rare small updates that don't mind if they have to
wait a few seconds.
Our slow locks currently seem to be happening on
'old' and
'searchindex', both MyISAM tables -- so no row-level locks on them.
Hmm, that is very mysterious indeed, and I have to think a little longer
about why that could be. The only thing I can think of now would be to
consider using INSERT DELAYED so that the updates don't wait on the SELECTs.
'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.
What does EXPLAIN say? Is it using the indices properly?
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.
Yes, ok, that doesn't sound too bad, but I was a little bit affraid that you
also had included the MyISAM tables in the transactin.
-- Jan Hidders
.---------------------------------------------------------------------.
| Post-doctoral researcher e-mail: jan.hidders(a)ua.ac.be |
| Dept. Math. & Computer Science tel: (+32) 3 820 2427 |
| University of Antwerp, room J1.06 fax: (+32) 3 820 2421 |
| (UIA) Universiteitsplein 1, B-2610 Wilrijk - Antwerpen (Belgium) |
`---------------------------------------------------------------------'