[Feedback] Database Logging System

[XTZGZoReX]

This is just a thread where I'd like feedback before writing the patch for MaNGOS.

I wrote a database logging patch for my own core, as well as Trinity, and would now like to also provide it for MaNGOS. In [7873], Vladimir did a change for me that was necessary to do this. So, kudos to him .

Now, there is one downside of database logging: Colors will have to be.. 'altered'. I had issues with colors being saved to the database (which is obviously totally wrong!), and thus, I removed colors completely. I don't currently know a way to do this with colors enabled. Of course it'd work, but logs would be ugly when selecting them from the database. So, I may choose to remove colors. This is one of the main things I want feedback on.

Now, that's pretty much the only 'real' problem I've had during development of this.

Database logging should generally make a server much faster, because dispatching a query to MySQL usually is faster than writing to a file (after all, MySQL handles the file writing, and thus won't slow down MaNGOS). This is assuming the MySQL server is on a decent machine, with decent settings, of course.

Now, just so you have something to drool over:

mysql> select count(*) from realmd.logs;
+----------+
| count(*) |
+----------+
| 31201271 |
+----------+
1 row in set (0.00 sec)

Yep, 31 million rows. This is with LogLevel 3 (debug build), on a server with 30~ players, latency is around 40ms. With file logging completely disabled, of course.

How the logs table structure will look:

CREATE TABLE `logs` (
 `time` int(14) NOT NULL, -- time of this log call
 `realm` int(4) NOT NULL, -- 0 if realmd
 `type` int(4) NOT NULL, -- will be defined in Log.h
 `string` text -- actual string
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

So, any feedback on all this is appreciated.

[painejake]

I think it's a great idea.

As we discussed in IRC even without logging the colors directly there are plenty of ways of sorting the different log types into different colors..

[leak]

I thought about that too, but running a server with over 1000 people online the pure amount of data made me hesitate.

Since most of those log messages are of repetetive nature anyways i thought:

Why not put those log messages into the db just like the mangos strings and call/log them just by an ID?

Ofc not the deep core stuff but the whole game mechanics logs could use it i guess.

This would also be a good step towards localisation of the mangos core (language patterns)

just my 2 cents

[XTZGZoReX]

Won't work. Since the strings you have before you load mangos_string -- what about them?

[painejake]

I personally think this would be one of the most efficient ways of storing logs.. They are easy to manage and easy to find what you are looking for, You can pull results in any sorted order you fancy not to mention, how many times do you lose a database or data in that database as opposed to a log file?

I'm defiantly looking forward the patch of this. Also the performance increase (if any) that it brings

[Wyk3d]

Database logging should generally make a server much faster, because dispatching a query to MySQL usually is faster than writing to a file (after all, MySQL handles the file writing, and thus won't slow down MaNGOS). This is assuming the MySQL server is on a decent machine, with decent settings, of course.

That doesn't sound quite right .. have you tried just removing the line "fflush(logFile)" from the logging methods and comparing the performance ?

[Wyk3d]

And if that's not enough, use http://www.cplusplus.com/reference/clibrary/cstdio/setvbuf/ after opening the stream to create a larger write buffer. With that, file writing should be very fast, probably faster than mysql queries. Ofcourse this means that in the event of a crash, the log entries which have not been written yet will be lost, but it's the same with the async sql statements (assuming you didn't DirectExecute) so if that doesn't bother you then this shouldn't either. The idea is to maximize performance, if you need more flexibility in viewing logs you can always write an app to convert a log file to a DB table.

[XTZGZoReX]

Just a heads-up.

Talked with Wyk3d on IRC and came to the conclusion that the way I intended to do it originally (LoginDatabase.PExecute) is just fine for this purpose.

I'm working on the patch submission now.

[painejake]

Nice can't wait to test