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)