MonitorWare Knowledge Base

[joshtlogan]

I have a large number of machines logging through rsyslog to Postgres. PhpLogCon was working great for the first week. Now I do not get a response from the application because the SQL query is trying to load everything into memory.

STATEMENT: SELECT id, devicereportedtime, facility, priority, fromhost, syslogtag, infounitid, message FROM SystemEvents ORDER BY id DESC

Is it possible to only have it select the number of rows that will be shown on the page?

Thanks.

[alorbach]

Hi,

this issue is very difficult to handle, if the PDO driver should be able to handle all kind of databases. The root problem is, that each database engine handles limiting of data queries with a different format. MySQL uses LIMIT x,x for example, Microsoft MSSQL uses TOP(x) at the beginning of the statement.

There are only two things which can solve this issue at this point.
A) regulary clearing the database and removing old entries, so that the database size does not exceed a specific size.
B) Adding a Database logstream class which is specialized for Postgres SQL.

I will talk to Rainer about the Option B. If somebody knows another option, I would be happy to know.

best regards,
Andre Lorbach

[alorbach]

Hi again,

I have done some performance enhancments in the PDO Database logstream class which should increase performance for MYSQL and POSTGRES databases. Please try the new version from the dev branch 2.5.5 available for download from here:
http://www.phplogcon.org/downloads

best regards,
Andre Lorbach

[joshtlogan]

Works a lot better!

I have tried out 2.5.5 and it does work well for the default screen and moving around. It is still slow when I do some searching, but it is a step in the right direction.

Is there a way to see what queries are being sent to Postgres?

Some times:
Default page (no search)
Page rendered: 0.2114 seconds

Searching for: " source:Host string severity:5 "
Page rendered: 200.3241 seconds

Thanks. JOSH

[alorbach]

Hi Josh,

you can uncomment line 695 in classes/logstreampdo.class.php to print the created sqlquery in the top of the page. This is the line number valid for version 2.5.5.
Regarding your query, if you search for a host, a LIKE query is perfomed by default which needs a lot of performance by default.

If you use = in the source filter, phpLogCon will perform and equal query instead of using LIKE. This should speed up the results a lot:

Code: Select all

source:=Host
source:=Host severity:5
source:=Host severity:5 string


I know that out filter documentation is still missing, this is one of the next things we need to finish

best regards,
Andre Lorbach

[MTatersKT738]

I have a SQL database that has 41 million rows in it. This is only the last three months of data. I do not have the control over data retention as this is another department. I do have access to the data through a DSN. This is obviously too HUGE to work with this product. I would like to tweek the code in order to only show the last 250-500 items in the database?

I am having similar problems where PHPLogCon just hangs and eventually stops.

Could someone point my in the right direction?

TIA,
Jason

[rgerhards]

Hi Jason,

do you use the latest release? There were some changes recently that should dramatically improve performance on large databases.

Rainer

[MTatersKT738]

Yes. I am using the latest release. I had downloaded that and tried that this morning before posting. I should have posted that in the first post. I apologize.

[rgerhards]

mhhh... unfortnately the primary developer of phpLogCon is having a week off (even without Internet... ). Can you verify that no min(), max() calls are made? Also, I would check if you have proper indexes defined. By default the schema does not have them, but that does not work well for larger deployments. What you need to index depends very much on the queries you usually do...

Rainer

[alorbach]

Hi,

can you give little more detail about your enviroment?
For example which kind of database engine are you using?
How have you setup your data source in phpLogCon?
What exact version are you using?

best regards,
Andre

[MTatersKT738]

I am using the Microsoft SQL Server ODBC Driver Version 03.86.3959. I have set up the datasource in PHPLogCon and I belive I am getting the connection because when I change the setting it gives me a connection error (therefor the correct settings must be connecting). I have set up the correct DSN in they system and that tests fine as well. I am using PHPLogCon 2.5.24.

If you need further information please let me know.

[alorbach]

Hrm it is hard to tell what is going wrong without a detailed error message.
I am not sure if even the database connection works yet at your end.

If you are trying to use the UserDB Interface, it can only be installed and used with a MYSQL Server. Within phpLogCon you can use the PDO Database driver to use MSSQL Databases as source.

best regards,
Andre Lorbach

[MTatersKT738]

I am really confident that my database is making a connection. I have tested the DSN a number of different wasy and it does infact connect. I belive it to be the large volume of data that is "choking" the program.

Where in the code would I have to change in order to only get the most recent 1000-2000 syslogs? I belive this would be the best solution as I do not have control over the syslog database other then acces.

[alorbach]

It is possible that the large amount of data is causing problems, but again, without any details or an actual error message it is impossible for me judge this problem.

regards,
Andre

[erik]

There are only two things which can solve this issue at this point.
A) regulary clearing the database and removing old entries, so that the database size does not exceed a specific size.
B) Adding a Database logstream class which is specialized for Postgres SQL.

I will talk to Rainer about the Option B. If somebody knows another option, I would be happy to know.

best regards,
Andre Lorbach

how about table rotation? i was using another report gui for rsyslsog and it comes with a separate script
that rotates the tables by date (and it also comes with a cleanup script that sanitizes the DB).
i'm now evaluating phplogcon but the point in this thread concerns me as we have quite a number of
machines whose logs we need to centralize.
if the log server bogs down due to a very large DB, archiving the logs might prove to be useless on our part if the only way
to fix it is by deleting old entries.