On mer, 2003-01-29 at 17:25, Jan Hidders wrote:
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.
Well, an update (counter increment) comes with every page view, and we
often have several edits per minute.
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.
I made the searchindex updates REPLACE DELAYED last night, it may have
helped a bit.
I would try making the olds INSERT DELAYED too, but currently we need to
get the autoincrement index on the new insert in order to reference it
in the recentchanges table. (in Article::updateArticle() )
'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?
They _look_ okay to me.
Let's try the history of [[Talk:Ellipse]]:
mysql> explain SELECT
old_id,old_namespace,old_title,old_user,old_comment,old_user_text,old_timestamp,old_minor_edit
FROM old WHERE old_namespace=1 AND old_title='Ellipse' ORDER BY old_timestamp
DESC;
+-------+------+---------------+-----------+---------+-------+------+----------------------------+
| table | type | possible_keys | key | key_len | ref | rows | Extra
|
+-------+------+---------------+-----------+---------+-------+------+----------------------------+
| old | ref | old_title | old_title | 20 | const | 31 | where used; Using
filesort |
+-------+------+---------------+-----------+---------+-------+------+----------------------------+
Sounds good... Village pump bumps the rows up to 600-something, still a tiny fraction of
the table.
Search for "English civil war":
mysql> explain SELECT cur_id,cur_namespace,cur_title,cur_text FROM cur,searchindex
WHERE cur_id=si_page AND ( (MATCH (si_text) AGAINST ('english')) AND (MATCH
(si_text) AGAINST ('civil')) AND (MATCH (si_text) AGAINST ('war')) AND
(cur_is_redirect=0) ) AND (cur_namespace=0) LIMIT 0, 20;
+-------------+----------+----------------------+---------+---------+---------------------+------+------------+
| table | type | possible_keys | key | key_len | ref
| rows | Extra |
+-------------+----------+----------------------+---------+---------+---------------------+------+------------+
| searchindex | fulltext | si_page,si_text | si_text | 0 |
| 1 | where used |
| cur | eq_ref | cur_id,cur_namespace | cur_id | 4 | searchindex.si_page
| 1 | where used |
+-------------+----------+----------------------+---------+---------+---------------------+------+------------+
No diff in the explain results if we reduce to a single search term.
Note that the number of rows listed here is bogus when InnoDB tables are
involved; this may be true of fulltext search as well. (In theory it's
not scanning individual rows, but a big glob 'o' indexy goodness, so I
guess it would be meaningless.)
If we cut out the join to cur:
mysql> explain SELECT si_page FROM searchindex WHERE (MATCH (si_text) AGAINST
('english')) AND (MATCH (si_text) AGAINST ('civil')) AND (MATCH (si_text)
AGAINST ('war')) LIMIT 0, 20;
+-------------+----------+---------------+---------+---------+------+------+------------+
| table | type | possible_keys | key | key_len | ref | rows | Extra |
+-------------+----------+---------------+---------+---------+------+------+------------+
| searchindex | fulltext | si_text | si_text | 0 | | 1 | where used |
+-------------+----------+---------------+---------+---------+------+------+------------+
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.
Shouldn't be. However, on page save we do have to wait for the INSERT to
the old table to finish before we move on to the part that is wrapped in
a transaction, since we need the insert id.
-- brion vibber (brion @
pobox.com)