next »

[WickedShell]

I went to write a plugin the other day, that would register B3 on a cronjob to periodically optimize database tables, as well as maybe make a backup. In the interest of not repeating work I did a quick grep through B3's source code, and noticed that setup.py allows tables to be optimized at startup, but that it is also commented out. Is there a reason for this? For the most part the database doesn't explode out of control with wasted space, but some plugins do start to generate sizeable overhead.

Looking at the databases for some of the B3's I have access to, some of them are floating at several megabytes of overhead, (30MiB size, 2 MiB overhead) (this is the entire DB, not just a specific table). I guess that's not to ridiculous, but it does bother that optimization bone of mine.

Is there a reason this was disabled? The big culprit in terms of overhead is actually the chatlogger plugin with about 4 to 8 times as much overhead as anything sels, which already has a periodic clean up function associated with it, with ctime being the second biggest accumulator. Is it worth writing a general DB optimize plugin, or is this something that will be left to plugins to manage? (And should I shoot the 2 or 3 lines of python code courgette would need to include optimize with chatlogger). Or am I just being ridiculous (or really wrong)?

Sidenote; I'm not surprised chat logger and ctime are the big overhead consumers, everything else only tends to insert things or update, not remove.

[MordyT]

Well, possibly one of the reasons it was disabled (if I remember) is some thread where by optimizing it would lock the tables, disallowing another service to use them.

I would be all over a database backup plugin, esp if it e-mails the database. One script I use (WHMCS) does this daily, awesome tool.

[WickedShell]

Ah... Yes, that would be a problem, although if it was run early enough at startup by core b3 code, everything else could just be made to wait, which would make startup slower, and require a restart to optimize, but that's easier to explain

As far as backing it up, I was mostly thinking of going so far as to do the equivalent to a phpmyadmin export, and save it to a local folder. But once it's there I imagine emailing it would be pretty trivial. Although it might start to hit message size limits eventually (I don't know how big some of the reaaally huge B3 databases are, especially for people who have multiple b3's pointed to the same DB/clients table). But I guess I'll start that as a separate plugin.

[Courgette]

I don't remember what the issue was with optimization but I have an other argument against it.
Database table space optimization is the responsibility of a sysadmin and as such is not to be treated at the application level.

B3 is already quite a complex application and to ensure its long term health we'd better try to keep it as simple as we can. In other words, we'd better keep its responsibilities limited.

Current B3 responsibilities are :

• connect to a game server
• fetch game server events
• convert them to B3 events
• dispatch events to plugins
• provide to plugins means of controlling the game server
• provide data persistence

[MordyT]

http://forum.bigbrotherbot.net/xlrstats/xlrstats-not-running-correctly-on-mysql-5-5/ is the thread I was thinking of.

[Mikobiko]

I use mysqldumper with a cron type task, it optimizes the database tables as part of the backup routine, and sends daily emails letting me know if theyw ere successful.

Really would suggest that.

[WickedShell]

I have to agree with the above, the following are the only reasons why I was considering it.

One - I keep having to deal with people who can not figure out how to maintain a working cron job, back up schedules, or can't understand the effort.

Two - While I agree that it's system level/sysadmin stuff, I was unsure as some stuff like chatlogger plugin manages history depth already.

And yes, in essence all the code I was going to do was setup an entry into the crontab, and let it run it from there. So verdict then is that leave the sysadmin's to do it as is there job. (And thus I should just start doing it for people rather then throwing them a plugin to not bother me about it?