MonitorWare Knowledge Base

I post in this particular section of the forum because the issue that I am facing with phpLogCon is not with its functionality, but implementation.

The frontend is nice, and the software seems rather organized, however the time it takes to perform a database search is way too long.

Here is a concrete example: I would like to see the list of log entries in the last 24 hours whose message contains "authentication failure" to see if there are bots trying to force a login to my ssh server.

I wrote a system utility in perl called logparse that generated this mysql query and executed it, returning the results in a simple table:

SELECT ReceivedAt, FromHost, SysLogTag, Message from SystemEvents where (ReceivedAt > DATE_SUB(NOW(), INTERVAL 1 DAY)) AND (Message REGEXP 'authentication failure') GROUP BY Message ORDER BY ReceivedAt

The time it took to execute was very short (considering most of the execution time was probably the mysql select)
real 0m2.419s
user 0m0.029s
sys 0m0.005s

Now, we will try the same query on phplogcon, using the advanced search feature to specify the time period in the last 24 hours and the message field containing 'authentication failure'. The page eventually refreshed with the results, with this performance metric in the bottom banner:

Page rendered in: 24.9766 seconds | DB queries: 3

Now here's the thing I don't understand: why do we use three database queries to do something that only takes one, and take 12 times the time required? Granted, the app is generating a somewhat complex webpage, and not a simple text-based table, but perhaps also we do not trust the inherent features that MySQL is capable of. The thing that I really like about this software package is that it has it's own search syntax. Is it not possible to convert the string "datelastx:3 messagetype:1,3,5 authentication failure" into the SQL query above?

In short, I think PhpLogCon is a great app, and I know for a fact it can go faster. Much faster.

-Amin Astaneh
USF Research Computing
http://rc.usf.edu

• Custom phpLogCon configuration?
• Maintenance Contract?
• Installation support?

Hi,

I am sure there is still lots of space for performance optimation in phpLogCon. Thanks for the detailed analyzes and sql statement sample. I will look into this based on your sample here, and see what we can do to optimize the database performance in phpLogCon.

Regarding the DB queries, this a counter for all database queries which phpLogCon performs. There is actually only one queries performed for the search at all, the other two queries won't take much performance.

--
best regards,
Andre Lorbach

Please let me add that from a design POV phpLogCon uses a driver layer to abstract logic from data sources. So we may not be able to use all MySQL features because they are not compatible with other database systems. Please note that the driver layer is also able to utilize plain text files. HOWEVER, the drivers are designed in a way that they should be able to gain performance benefits from the kind of data source they drive. Thus, for example, there is a MySQL driver while there also is a more generic database driver. I just wanted to point this out, I have not enough insight into the details to answer the actual question.

Hi Amin,

I have done some research and made some performances changes to the code. Actually we had a SELECT MIN and SELECT MAX statement performed to get the first and last available record which caused the performance leak. Then next update will include the performance changes.

You can get an update mysql db driver directly from here during meantime for testing:
http://git.adiscon.com/?p=phplogcon.git ... f639345c26

Kindly copy the logstreamdb.class.php file into your src/classes directory of phpLogCon.

best regards,
Andre Lorbach

Andre-

Thanks for the patch. Speeds are definitely much faster. We appreciate the quick turnaround.

I have another question that comes to mind: Has anyone ever considered regular expression support for searches? That would be rather useful for searching logs.

-Amin

Amin,

you are the first one to ask for regular expression support, it is certainly an interesting enhancment.
For Database drivers (MYSQL), I think we can include REGEX support very easily.

Btw you can use our Bugtracker to submit feature requests, then it will be easier for us to track this particular feature request:
http://bugzilla.adiscon.com/phplogcon-bugs.html

best regards,
Andre Lorbach

Hi again,

just a notification, we have eliminated the need of Min() and Max() selects for search queries. Database performance is much better in phpLogCon Version 2.5.24 and higher. Regex Support is still on the todo list.

You may want to give the new Version a try

best regards,
Andre Lorbach

Hi Amin,

I know it has be some time, but you may want to know that you can use REGEXP searches now in phpLogCon 2.7.0.
See the doc for how to use REGEXP searches: http://www.phplogcon.org/module-Static_ ... html.phtml
To download phpLogCon 2.7.0, go here: http://www.phplogcon.org/downloads

--
best regards,
Andre Lorbach