Architecture #4428: Avoid useless requests and commits in SQL database - Rudder - Issue Tracker

Actions

Copy link

Architecture #4428

closed

Avoid useless requests and commits in SQL database

Added by Christophe Nowicki about 11 years ago. Updated about 11 years ago.

Status:

Released

Priority:

Assignee:

Nicolas CHARLES

Category:

Performance and scalability

Target version:

2.10.0~beta1

Pull Request:

Effort required:

Name check:

Fix check:

Regression:

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 3 (0 open — 3 closed)

Related issues 1 (0 open — 1 closed)

Updated by Nicolas CHARLES about 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 about 11 years ago

Tracker changed from Bug to User story

Updated by Nicolas CHARLES about 11 years ago

Tracker changed from User story to Architecture

Updated by Jonathan CLARKE about 11 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 about 11 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

Updated by Jonathan CLARKE about 11 years ago

Status changed from 12 to Pending release

#10

Updated by Vincent MEMBRÉ about 11 years ago

Category changed from Architecture - Dependencies to Performance and scalability

#11

Updated by Vincent MEMBRÉ about 11 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/

Actions

Copy link

Also available in: Atom PDF

Architecture #4527: Don't commit on database when no modification are made	Released	François ARMAND	2014-02-26	Actions
Architecture #4528: Change the query made to check the database status in the system Technique	Released	Jonathan CLARKE	2014-02-26	Actions
Architecture #4526: Change the validation query for the database	Released	François ARMAND	2014-02-26	Actions

Project

General

Custom queries

Profile

Rudder