The nice folks who make the fabulous blog-from-the-comfort-of-your-desktop XML-RPC client MarsEdit were nice enough to give us a free license so we could test compatibility with Lyceum. Thanks!
You heard that right. Lyceum 1.0 is on its way. It looks like it will arrive sometime in mid September. Things to look forward to:
- Synchronized with WordPress 2.0.11
- Vastly improved interface for assigning permissions to users
- More complete set of system-admin tools for creating and deleting users and blogs
- Performance optimizations, some of them very significant for large installations
- Even greater plugin and theme compatibility with WordPress
- Refined admin interface layout
Stay tuned.
Google Alerts informs me that someone who goes by Streaky has given WordPress MU and Lyceum a spin, and has this to say about them:
Firstly there’s WP-MU, which is the ‘official’ multi-user code. It’s stable and polished, yet has some issues – the main one for me is that every blog has its own set of database tables. I can’t for the life of me figure out why they did this. I can’t find any official information on it either.
The reasons I’ve seen banded about are that it makes the system more scalable and it’s easier to split sites between database servers. This might be true as far as splitting goes – but scalable? I’m not so sure.
[snip]
The second choice is Lyceum – this is better – sort of. Sites share one set of tables and there’s plenty of indexes – good so far. But wait, what version of Wordpress is it? Something old I can tell you that. This project is dead.
Worse, the global users feature of wp-mu is great, Lyceum doesn’t have that. The other issue is that there is a massive amount of coding is needed every time a new version of Wordpress is released – far more than wp-mu at any rate, which is probably why the project is almost dead.
Streaky is exactly right that our WP integration cycle has become frustratingly infrequent (current point release is synced with WP 2.0.1, trunk is at 2.0.9). This is going to change dramatically in coming months as a result of various projects that are in the works.
Streaky goes on to say that “a massive amount of coding is needed every time a new version of Wordpress is released”. This is actually not the case. Going from 2.0.x to 2.1 is going to take a little bit of thinking because there is some file reorganization. But merging in a minor version is achieved with a very managable merge command, and a little work resolving conflicts. Usually this takes about 2 hours per release.
Regarding “the global users feature of wp-mu”, I’m not sure what he is refering to here, and an initial search didn’t come up with anything that seemed relevant. Could readers familiar with MU fill me in on how this feature differs from Lyceum’s behavior?
The forum software has been upgraded to the latest version of Vanilla. Since its inception, the forum has been hopping with activity. I’m happy as ever with Vanilla, and glad to see that the forum has proven to be a valuable resource.
Eventually we’ll consider decomissioning the lyceum-users email list. What do you folks think? If a software project has a good online forum, what mailing lists does it need?
Michael Harmer of The Australian National University has created an LDAP plugin for Lyceum. He has documented and hosted the project on the fledgling Lyceum Add-Ons site. Check it out!
Something I’ve been meaning to do for a while is an audit of key Lyceum database queries to see if there are oportunities to fine-tune the database indexes. Part of the reason I hadn’t gotten around to this yet is because I was waiting for the opportunity to use a large real-life data set when anaylyzing the queries.
Such an opportunity recently presented itself. One of the biggest Lyceum installations is iBlog, “South Africa’s Blogging Community”. The creator/head-honcho, Mark, has been in touch with me for a while now, and has been expressing growing concerns about the performance of the site. Mark was nice enough to give me full access to his server, and boy was I happy to get my eager little hands on all that data. Below is a description of where I went, what I found, and — in the exciting conclusion — how iBlog, and Lyceum, improved.
Throughout this article I will use the MySQL EXPLAIN command. If you haven’t used it before, you may want to go take a quick look at the documenation to familiarize yourself with its purpose.
EXPLAIN provides a variety of information. For our purposes here, we will be focusing on its ‘rows’ output. This information is very useful and also very easy to understand. If you know what a database index is for — getting to data in a table without having to look through the entire table — then you can appreciate the ‘rows’ metric; it is an estimation of how many rows MySQL will have to examine in order to find the data.
Mark presented to me this query as an example of what was giving iBlog trouble. This is a query that is executed on every single viewing of any Lyceum blog, to retrieve the set of posts list on the blog’s front page. Mark said that it was taking around 6 seconds to execute! I will refer to this query as Trouble Query:
mysql> EXPLAIN EXTENDED SELECT DISTINCT * FROM posts INNER JOIN post2cat ON (post2cat.post_id = posts.ID) INNER JOIN categories ON (post2cat.category_id = categories.cat_ID) WHERE 1=1 AND post_date_gmt <='2006-12-13 09:14:59' AND (post_status="publish") AND post_status!="attachment" AND categories.blog='21' GROUP BY posts.ID ORDER BY post_date DESC LIMIT 0,10; +----+-------------+------------+--------+-------------------+---------+---------+----------------------------------+-------+----------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+------------+--------+-------------------+---------+---------+----------------------------------+-------+----------------------------------------------+ | 1 | SIMPLE | posts | ref | PRIMARY,page,main | page | 1 | const | 24350 | Using where; Using temporary; Using filesort | | 1 | SIMPLE | post2cat | ref | post_id | post_id | 4 | iblogcoza_1.posts.ID | 1 | Using index | | 1 | SIMPLE | categories | eq_ref | PRIMARY,blog | PRIMARY | 4 | iblogcoza_1.post2cat.category_id | 1 | Using where | +----+-------------+------------+--------+-------------------+---------+---------+----------------------------------+-------+----------------------------------------------+
Wow, 24250 rows scanned. Ick. Becaue of the Lyceum schema, MySQL should be able to pare down from categories pretty quickly, so we know something is amis. In order to root out the problem, let’s pretend we are the database and pare down the data ourselves step-by-step in the same order that MySQL will when executing Trouble Query. Maybe the design flaw will become evident.
mysql> explain select categories.cat_ID from categories where blog = 21; +----+-------------+------------+------+---------------+------+---------+-------+------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+------------+------+---------------+------+---------+-------+------+--------------------------+ | 1 | SIMPLE | categories | ref | blog | blog | 3 | const | 7 | Using where; Using index | +----+-------------+------------+------+---------------+------+---------+-------+------+--------------------------+
7 rows. Looks great. In fact, the results of that query are (22,3019,3021,3022,3023,9171,18333), which means that this blog only has 7 categories, which means that our index could not be more optimal. w00t! Let’s check the next level, post2cat:
mysql> explain select post_id from post2cat where category_id in (22,3019,3021,3022,3023,9171,18333); +----+-------------+----------+-------+---------------+---------+---------+------+-------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+----------+-------+---------------+---------+---------+------+-------+--------------------------+ | 1 | SIMPLE | post2cat | index | NULL | post_id | 8 | NULL | 43102 | Using where; Using index | +----+-------------+----------+-------+---------------+---------+---------+------+-------+--------------------------+
43102 rows! You know that ain’t right. And don’t let that “index” type fool you, it doesn’t mean that it is actually using an index, only that instead of scanning the main data store it is scanning the index (which is marginally faster due to the (usually) smaller size of the data on disk). ~40k, hmm that number seems familiar, where have I seen it before… wait a second…
mysql> select count(rel_id) from post2cat; +---------------+ | count(rel_id) | +---------------+ | 45779 | +---------------+
It’s scanning almost the entire table! It’s as if the category column does not have an index at all. Wait a second…
CREATE TABLE $wpdb->post2cat ( rel_id int unsigned NOT NULL auto_increment, post_id int unsigned NOT NULL default '0', category_id int unsigned NOT NULL default '0', PRIMARY KEY (rel_id), KEY post_id (post_id,category_id) ) ENGINE = InnoDB;
(source)
It doesn’t!! Ah ha, we have found our bottleneck! But before we try to fix it, let’s see how the rest of Trouble Query fares (using the set of numbers returned from the previous query):
mysql> EXPLAIN SELECT DISTINCT * FROM posts where post_date_gmt <='2006-12-13 09:14:59' AND (post_status="publish") AND post_status!="attachment" AND ID in (8694, 8775, [SNIP] , 49917, 49963) GROUP BY posts.ID ORDER BY post_date DESC LIMIT 0,10; +—-+————-+——-+——-+——————-+———+———+——+——+———————————————-+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +—-+————-+——-+——-+——————-+———+———+——+——+———————————————-+ | 1 | SIMPLE | posts | range | PRIMARY,page,main | PRIMARY | 4 | NULL | 385 | Using where; Using temporary; Using filesort | +—-+————-+——-+——-+——————-+———+———+——+——+———————————————-+
385 rows. This could probably be tweaked down some more, but it is nothing to be ashamed of.
Not having an index on a column in a WHERE clause, however, is something to be ashamed of. So, back to our dismal post2cat performance. Let’s see what adding an index can do for us:
ALTER TABLE post2cat ADD UNIQUE INDEX category_id (category_id,post_id);
Okay, that went through without any complaints. Now let’s try Trouble Query again and see if our new index makes a difference:
mysql> EXPLAIN EXTENDED SELECT DISTINCT * FROM posts INNER JOIN post2cat ON (post2cat.post_id = posts.ID) INNER JOIN categories ON (post2cat.category_id = categories.cat_ID) WHERE 1=1 AND post_date_gmt <='2006-12-13 09:14:59' AND (post_status="publish") AND post_status!="attachment" AND categories.blog='21' GROUP BY posts.ID ORDER BY post_date DESC LIMIT 0,10; +----+-------------+------------+--------+---------------------+-------------+---------+-------------------------------+------+----------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+------------+--------+---------------------+-------------+---------+-------------------------------+------+----------------------------------------------+ | 1 | SIMPLE | categories | ref | PRIMARY,blog | blog | 3 | const | 7 | Using where; Using temporary; Using filesort | | 1 | SIMPLE | post2cat | ref | category_id,post_id | category_id | 4 | iblogcoza_1.categories.cat_ID | 6 | Using index | | 1 | SIMPLE | posts | eq_ref | PRIMARY,page,main | PRIMARY | 4 | iblogcoza_1.post2cat.post_id | 1 | Using where | +----+-------------+------------+--------+---------------------+-------------+---------+-------------------------------+------+----------------------------------------------+
We went from 24352 rows scanned down to 14! Note that the number of rows scanned for Trouble Query is now fewer than for the no-join query on the posts table we tried just before. This an example of how EXPLAINing through each part of a query “in the same order that MySQL will” does not always give a completely accurate picture of what mysql is doing. But it certainly serves perfectly well for performance audits.
Like a father witnessing his child’s uncontrollable expresions of blissful abandon on Christmas morning after receiving a gift that is sure to bring many years of childhood joy and memories, my heart was warmed when Mark applied this new 1-1 cardinality B-Tree index to his database and reported back that his server load immediately went down by an order of magnitude, allowing iBlog to serve many more requests per second with existing hardware and paving the way for at-worst linear load-hardware scaling as his community grew.
So there you have it folks. With feedback and resources from a community member, a bunch of data, and taking the time to examine a few queries, we made dramatic performance improvements to Lyceum.
I have some exciting news. I’ve been working on Lyceum a lot lately, and have merged in all WordPress code up to 2.0.8 (2.0.9 is coming soon).
This, along with some other recent changes, has been a massive undertaking, and the code has changed A LOT. The new version is now installed on the demo site.
If you want to help make Lyceum better, cruise on over the demo site and try to break something, and then file bugs. Just spend 5 minutes creating, deleting, and modifying all sorts of things.. posts, links, categories, uploads…
http://demo.lyceum.ibiblio.org
No fancy testing techniques needed, just experiment, and if it breaks, file a bug.
The Lyceum Forum is now live!
http://forum.lyceum.ibiblio.org/
Thanks to everyone who contributed to the forum software discussion. I’m quite happy with Vanilla so far.
The user database for the forum and the wiki are now the same. Accounts created on the forum will work on the wiki.
Special note for users who already have a wiki account: Previously created wiki accounts will NOT work on the forum until they are registered on the forum. You also will not be able to log into the wiki until you create a forum account. What this means: if you have an existing wiki account, you should register on the forum with the same username as soon as possible.
