Anope IRC Services

Anope.org => Anope General => Topic started by: andreas on March 24, 2007, 08:56:00 PM

Title: mysql database speedup
Post by: andreas on March 24, 2007, 08:56:00 PM

I was testing anope on a slower machine and noticed that anope's periodic database updates with mysql enabled took a lot of time to complete. A single database update triggered by UpdateTimeout or the operserv "update" command on Anope-1.7.18 lasted over a minute. During that time,  anope would be completely unresponsive to user commands. Adding the following SQL indexes speeded things up a lot for me, allowing those 1-2 minute updates to finish in less than ten seconds:

Code: [Select]

CREATE UNIQUE INDEX level ON anope_cs_levels (channel, position);
CREATE UNIQUE INDEX ttb ON anope_cs_ttb (channel, ttb_id);

Title:
Post by: Pieter Bootsma on March 25, 2007, 07:11:26 AM

That's actually quite a big saving.... We'll look into changing the database scheme with these indexes soon

Title:
Post by: andreas on March 25, 2007, 07:57:00 AM

You should note that both of these indexes are for ChanServ tables, so the speedup is only for databases that have a lot of channel registrations. There are around 30 anope_cs_levels rows and 10 anope_cs_ttb rows for each channel registration, so most of the time spent during the update of the Chanserv database is spent updating these tables. The updates are performed with thousands of UPDATE statements that operate on individual rows of these tables. Without the indexes, each UPDATE statement requires a complete linear search of these tables, which can get pretty expensive.

Title: transactions
Post by: jilles on April 28, 2007, 08:00:17 PM

It may help to use explicit transactions. From a quick look at the code it does not seem to, so it will use one transaction per query which is really slow.

Title:
Post by: HiroP on April 30, 2007, 01:03:26 PM

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.

Title:
Post by: katsklaw on May 03, 2007, 01:04:24 AM

as Anope is open source and we welcome patches created by users, any improvements could be sunmitted for review and possibly applied to the next version.

Title:
Post by: Pieter Bootsma on May 03, 2007, 07:48:17 PM

Those SQL statements basically are a patch ;) They just need to be put into the current files in the distribution, that's not too hard. I'll probably take a look at this for .19