Brion,
Thank you for posting the queries which each page view request triggers. These might be optimized somewhat, as you suggested.
I'm ever more concerned, though, with the pauses of 10 seconds or more -- some even lasting several minutes! -- during which no access to the Wikipedia is possible at all.
Are these "no access" periods due to the page view queries? I thought the delays were caused rather by table locks.
In my experience with MS SQL Server 6.5 (and above), mere read-only queries do not cause table locking. Rather, it is only update queries which lock tables -- especially when performed within a "transaction".
Ed Poor
On Mon, 2002-11-18 at 06:32, Poor, Edmund W wrote:
In my experience with MS SQL Server 6.5 (and above), mere read-only queries do not cause table locking. Rather, it is only update queries which lock tables -- especially when performed within a "transaction".
Ah, but this is MySQL, where black is white, up is down, and ignorance is strength. :)
SELECTs don't (or at least shouldn't) block each other, but the implicit table-level locking of intermingled SELECTs and UPDATEs can make this indirectly happen.
* A client issues a SELECT that takes a long time to run. * Another client then issues an UPDATE on a used table. This client will wait until the SELECT is finished. * Another client issues another SELECT statement on the same table. As UPDATE has higher priority than SELECT, this SELECT will wait for the UPDATE to finish. It will also wait for the first SELECT to finish!
Now, we have a *lot* of SELECTs that involve the cur table. We also have a *lot* of UPDATEs that involve the cur table -- on every page load we increment some row's cur_count. Should be quick, but since it has to wait until any other SELECTs are done, and any later SELECTs have to wait until *it* is done...
If I understand correctly, this means we can get stuck in a cascade of waiting for the previous selects to finish before we get anything, as if we were serving a gazillion pages all through from one pipe.
I've made the various counter update queries LOW_PRIORITY; we'll see if that helps any.
I think it was once suggested that breaking the page counters to a separate table (two numeric columns, ID and count) might be a good idea.
-- brion vibber (brion @ pobox.com)
On Tue, Nov 19, 2002 at 12:08:06AM -0800, Brion Vibber wrote:
I think it was once suggested that breaking the page counters to a separate table (two numeric columns, ID and count) might be a good idea.
4th normal form isn't really practical; let's shoot for 3rd normal, and switch to a real database :)
I'm also thinking that maybe a users "options" are better off encoded in a Cookie? For instance, Set-Cookie: options=101010101010 Would save us from doing ANY database accesses; and if the user ever changed their display preferences, we could set a new cookie. And unlike the "login" cookie, this one would be session specific, never carry across sessions.
Jonathan
Jonathan Walther wrote:
I'm also thinking that maybe a users "options" are better off encoded in a Cookie? For instance, Set-Cookie: options=101010101010 Would save us from doing ANY database accesses; and if the user ever changed their display preferences, we could set a new cookie. And unlike the "login" cookie, this one would be session specific, never carry across sessions.
Which reminds me: Should some user options be *only* saved in cookies? I think about edit box size, maybe some "tiny skin" option, etc. The reason for this is what I encounter every day: The settings I have are great on my 1280 screen at home, but they s**k on my 800x600 screen at work. Changing that would make the 'pedia at home look weird in turn. I've been working on a "small icons only" skin for tiny screens/resolutions. I am far from finished, though.
Magnus
On Die, 2002-11-19 at 09:08, Brion Vibber wrote:
I think it was once suggested that breaking the page counters to a separate table (two numeric columns, ID and count) might be a good idea.
It would probably help with our current configuration. If we really want to make the switch to PostgreSQL, it can't harm.
We could also use the InnoDB plugin. Both Slashdot and K5 use it for performance reasons, and it shouldn't require us to modify much/any code. Besides better locking, it could provide huge performance benefits because of its caching.
http://www.mysql.com/doc/en/InnoDB_overview.html
InnoDB provides MySQL with a transaction-safe (ACID compliant) table handler with commit, rollback, and crash recovery capabilities. InnoDB does locking on row level and also provides an Oracle-style consistent non-locking read in SELECTs. These features increase multiuser concurrency and performance. There is no need for lock escalation in InnoDB, because row level locks in InnoDB fit in very small space. InnoDB tables support FOREIGN KEY constraints as the first table type in MySQL.
InnoDB has been designed for maximum performance when processing large data volumes. Its CPU efficiency is probably not matched by any other disk-based relational database engine.
Technically, InnoDB is a complete database backend placed under MySQL. InnoDB has its own buffer pool for caching data and indexes in main memory. InnoDB stores its tables and indexes in a tablespace, which may consist of several files. This is different from, for example, MyISAM tables where each table is stored as a separate file. InnoDB tables can be of any size also on those operating systems where file-size is limited to 2 GB.
wikitech-l@lists.wikimedia.org