Architecture #4428
closed
Avoid useless requests and commits in SQL database
Description
Hi,
I've activated the postgresql logs on my development rudder server /
there is only ONE host on this server and after two days / pgFuine
found more than 538k request in the logs, strange isn't?
See the attached pgFuine rapport : 1391089351.html
There is 100k request of COMMIT; looks like every request is atomic ?
(maybe it's useless to protect select requests ?)
The ORM does more than 165k requests for the 'eventlog' in information_schema.
Best Regards,
Files
Updated by Nicolas CHARLES almost 11 years ago
- Assignee set to Nicolas CHARLES
Thank you for this report, this is really helpful for us !
So, some insight on the numbers:
SELECT tables.table_name FROM information_schema.tables WHERE lower(table_name) = '';
is the check made by the JDBC driver to check the connection is still alive. It is obviously made too often
select id from ruddersysevents limit 0;
is the check made by the rudder-agent to check the postgres database is up and running
insert into RudderSysEvents (executionDate, nodeId, ruleId, directiveId, serial, Component, KeyValue, executionTimeStamp, eventType, msg, Policy) values ('','', '' , '', '', '', '', '', '', '', '' );
is the insertion of nodes reports in the databases
Select "statusupdate4"."key" as "statusupdate4_key", "statusupdate4"."date" as "statusupdate4_date", "statusupdate4"."lastid" as "statusupdate4_lastid" From "statusupdate" "statusupdate4" Where ("statusupdate4"."key" = '');
update "statusupdate" set "lastid" = (0), "date" = ('') Where ("statusupdate"."key" = '');
select T.nodeid, T.executiontimestamp, coalesce(C.iscomplete, false) as complete from (select distinct nodeid, executiontimestamp from ruddersysevents where id > 0 and id <= 0) as T left join (select true as isComplete, nodeid, executiontimestamp from ruddersysevents where id > 0 and id <= 0 and ruleId like '' and component = '' and keyValue = '') as C on T.nodeid = C.nodeid and T.executiontimestamp = C.executiontimestamp;
select max(id) as id from RudderSysEvents where id > 0 and executionTimeStamp < '';
are the computation of last time a node answered.
It seems clear to me that we should check the connection to the database much much less often. I'll be working on this one
As for the commit, indeed every requests are atomic; but i can't understand why there are so many of them (80103+24299, while there is only 37743 + 25100+863+750+124 insert/update
Updated by Nicolas CHARLES over 10 years ago
I so far fail to see why their are so many commit. My best guess is that their is something not so good going on between jdbcTemplate and Squeryl (the ORM we use)
We can also releive a bit of stress on the DB by replacing the query to check the database connectivity by "Select 1" (as in http://stackoverflow.com/questions/10684244/dbcp-validationquery-for-different-databases ). Still this check is too common, and it would be better that it happens less often (and have a look at setTestWhileIdle, setTestOnBorrow and setTestOnReturn properties)
The select id from ruddersysevents limit 1 query is made by the agent, to check that the database is still alive.
It could also be replaced by "Select 1", especially as the query seems to take some time
Updated by Nicolas CHARLES over 10 years ago
Ok, for the commit, i found the culprit.
In http://www.rudder-project.org/redmine/issues/2121 we introduced support for multiple databases, and basically copy-pasted a piece of code that manage the connections per databases, and finished everything by a "commit", which is clearly not what we want for select
I'm still fighting with Squeryl to get a proper way to implement a correction on this one, but there is clearly room for improvement
Updated by Nicolas CHARLES over 10 years ago
I've opened ticket #4525 to track the changes in branch 2.10 (so far, commits and validation query)
Updated by Nicolas CHARLES over 10 years ago
- Tracker changed from Bug to User story
I'm transforming this ticket into a meta ticket for the sub tasks
Updated by Nicolas CHARLES over 10 years ago
- Tracker changed from User story to Architecture
Updated by Jonathan CLARKE over 10 years ago
- Category set to Architecture - Dependencies
- Status changed from New to 13
- Target version set to 2.10.0~beta1
Updated by Jonathan CLARKE over 10 years ago
- Subject changed from pgFuine with an idle rudder server / why so many useless requests? to Avoid useless requests and commits in SQL database
- Status changed from 13 to 12
This has now been fixed everywhere applicable: useless commits are avoided, and the periodic checks in both the webapp and CFEngine have been simplified.
Updated by Jonathan CLARKE over 10 years ago
- Status changed from 12 to Pending release
Updated by Vincent MEMBRÉ over 10 years ago
- Category changed from Architecture - Dependencies to Performance and scalability
Updated by Vincent MEMBRÉ over 10 years ago
- Status changed from Pending release to Released
This bug has been fixed in Rudder 2.10.0~beta1, which was released today.
Check out:
The release announcement: http://www.rudder-project.org/pipermail/rudder-announce/2014-March/000084.html
The full ChangeLog: http://www.rudder-project.org/foswiki/bin/view/System/Documentation:ChangeLog210
Download information: https://www.rudder-project.org/site/get-rudder/downloads/