Project

General

Profile

Architecture #4428

Avoid useless requests and commits in SQL database

Added by Christophe Nowicki about 6 years ago. Updated almost 6 years ago.

Status:
Released
Priority:
2
Category:
Performance and scalability
Target version:
Pull Request:
Effort required:

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

1391089351.html (401 KB) 1391089351.html Christophe Nowicki, 2014-01-31 11:32

Subtasks

Architecture #4527: Don't commit on database when no modification are madeReleasedFrançois ARMANDActions
Architecture #4528: Change the query made to check the database status in the system TechniqueReleasedJonathan CLARKEActions
Architecture #4526: Change the validation query for the databaseReleasedFrançois ARMANDActions

Related issues

Has duplicate Rudder - Architecture #4525: Improve use of database in RudderRejectedActions

History

#1

Updated by Nicolas CHARLES about 6 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

#2

Updated by Nicolas CHARLES almost 6 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

#3

Updated by Nicolas CHARLES almost 6 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

#4

Updated by Nicolas CHARLES almost 6 years ago

I've opened ticket #4525 to track the changes in branch 2.10 (so far, commits and validation query)

#5

Updated by Nicolas CHARLES almost 6 years ago

  • Tracker changed from Bug to User story

I'm transforming this ticket into a meta ticket for the sub tasks

#6

Updated by Nicolas CHARLES almost 6 years ago

  • Tracker changed from User story to Architecture
#7

Updated by Jonathan CLARKE almost 6 years ago

  • Category set to Architecture - Dependencies
  • Status changed from New to 13
  • Target version set to 2.10.0~beta1
#8

Updated by Jonathan CLARKE almost 6 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.

#9

Updated by Jonathan CLARKE almost 6 years ago

  • Status changed from 12 to Pending release
#10

Updated by Vincent MEMBRÉ almost 6 years ago

  • Category changed from Architecture - Dependencies to Performance and scalability
#11

Updated by Vincent MEMBRÉ almost 6 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/

Also available in: Atom PDF