I ran some benchmark tests with MySQL support. First, the basics:
Anope DB:
-OperServ- User : 8450 records, 2190 kB
-OperServ- Channel : 2093 records, 1205 kB
-OperServ- NS Groups : 36163 records, 7466 kB
-OperServ- NS Aliases : 45199 records, 5778 kB
-OperServ- ChanServ : 2580 records, 1842 kB
-OperServ- BotServ : 32 records, 3 kB
-OperServ- OperServ : 1798 records, 200 kB
(give or take a few)
Hardware:
Celeron 2.4 GHz
512 MB RAM
Hard drive: ExcelStor Technology J880 (7200rpm, PATA)
Software;
Anope 1.7.18 (1214) (ircd protocol: UltimateIRCd 3.0.0.a26+)
mysql Ver 14.12 Distrib 5.0.38, for pc-linux-gnu (i686) using readline 5.1
Linux kernel 2.6.17-gentoo-r8
gcc version 4.1.1 (Gentoo 4.1.1-r3)
With the standard Anope tables and no special optimization in the mysqld, startup of Anope took about 4.5 hours (measured between the log lines "Reflecting database records." and "Connected to Server 1").
Then I added the following indexes to the Anope tables:
CREATE UNIQUE INDEX level ON anope_cs_levels (channel, position);
CREATE UNIQUE INDEX ttb ON anope_cs_ttb (channel, ttb_id);
CREATE UNIQUE INDEX caccess ON anope_cs_access (display,channel);
CREATE UNIQUE INDEX cakick ON anope_cs_akicks (channel,dmask);
CREATE UNIQUE INDEX cbadword ON anope_cs_badwords (channel,word);
CREATE UNIQUE INDEX memo ON anope_ms_info (nm_id,serv);
CREATE UNIQUE INDEX naccess ON anope_ns_access (display,access);
CREATE UNIQUE INDEX akill ON anope_os_akills (user,host);
CREATE UNIQUE INDEX newsitem ON anope_os_news (type,num,time);
CREATE UNIQUE INDEX exception ON anope_os_exceptions (mask);
CREATE UNIQUE INDEX sgline ON anope_os_sglines (mask);
CREATE UNIQUE INDEX sqline ON anope_os_sqlines (mask);
CREATE UNIQUE INDEX szline ON anope_os_szlines (mask);
and also tweaked some MySQL server settings (cache, etc.). Some of the columns used in the above indexes were originally type TEXT, I changed those to VARCHAR(255) since MySQL can't fully index TEXT columns. (I'm not entirely certain whether they can actually all be UNIQUE, but it was sufficient for my purposes.)
That got me down to around 220-240 seconds startup time. Number of SQL queries for the initial startup: ~260k.
I thought the "INSERT .. ON DUPLICATE KEY UPDATE" syntax might be more efficient than separate UPDATE/INSERT statements but, by itself, that turned out to have almost no effect at all.
Just for fun, instead of sending the MySQL queries to the server one by one, I wanted to try queueing them in a buffer and flushing the whole chunk to the DB server once the buffer was full. In combination with the "INSERT .. ON DUPLICATE KEY UPDATE" syntax, this improved the startup time somewhat (160-180 seconds) but has some major drawbacks: Depending on the size of the buffer, this method consumes large amounts of resources. I had difficulties staying under 200 concurrent MySQL connections for example. Smaller buffers increase that number until you run out of connections or FDs while larger ones almost let the box grind to a halt
Secondly, the load average was noticeably higher than using the "normal" way.
For the time being, adding the table indexes appears to be a rather cheap way to dramatically improve the performance.