History | Log In     View a printable version of the current page.  
   HOME       BROWSE PROJECT       FIND ISSUES   
    QUICK SEARCH:  
Issue Details (XML | Word | Printable)

Key: QB-3183
Type: Improvement Improvement
Status: Resolved Resolved
Resolution: Fixed
Priority: Critical Critical
Assignee: Unassigned
Reporter: AlSt
Votes: 0
Watchers: 0
Operations

If you were logged in you would be able to see more operations.
QuickBuild

Use different approach for truncate table (especially when using postgres)

Created: 24/May/18 09:17 AM   Updated: 05/Jul/18 05:33 AM
Component/s: None
Affects Version/s: 7.0.31
Fix Version/s: 8.0.10

Original Estimate: Unknown Remaining Estimate: Unknown Time Spent: Unknown


 Description  « Hide
Hi

we see a lot of problems lately because the measurements compression does a truncate of tables which actually takes a couple of hours.

Can you please implement a different truncation mechanism to avoid these long running truncate statements.

As far as I've seen there is no foreign key dependency in the "qb_measurement_datarXX" tables so it should be possible to just do the following steps in a single transaction:
1. lock qb_measurement_datarXX row exclusive via: LOCK TABLE qb_measurement_datarXX IN ROW EXCLUSIVE mode;
2. create a new table with a name like "qb_measurement_datarXX_new" with: CREATE TABLE qb_measurement_datarXX_new (LIKE qb_measurement_datarXX)
3. rename the table to something like "qb_measurement_datarXX_old" with: ALTER TABLE qb_measurement_datarXX RENAME TO qb_measurement_datarXX_old
4. rename the new table to "qb_measurement_datarXX" via: ALTER TABLE qb_measurement_datarXX_new RENAME TO qb_measurement_datarXX
5. commit the changes (unlocks the table and finishes the transaction)
6. start DROP TABLE qb_measurement_datarXX in the background

as creating a new table and the renaming just takes seconds and not hours this would not interrupt the operation for that long. And the DROP TABLE can just run out of process as it is not used in any way.
If there is some concern that the new table name could interrupt with something a generated name for just this purpose would also suffice.

And just to clarify what's meaning long execution time, the log line is as follows:
Finished truncating dead table [MeasurementDataR00], [0] rows removed in [5339615] millis

here 0 means that truncate is used because TRUNCATE basically does not return the exact amount of rows deleted. And it took around 1.5 hours to just execute the TRUNCATE.

 All   Comments   Work Log   Change History      Sort Order:
[ Permlink | « Hide ]
AlSt [05/Jul/18 05:33 AM]
That seems to have finally fixed our problems. No hanging TRUNCATE calls anymore. I'll keep you posted if/once that comes up again, but I would say it looks pretty promising.

[ Permlink | « Hide ]
AlSt [25/Jun/18 06:08 AM]
Thanks for the configuration option.

I'll schedule a QB upgrade and let you know if this helped afterwards.

[ Permlink | « Hide ]
Steve Luo [23/Jun/18 02:50 AM]
We have added an option <Use DELETE> in Measurement Pugin settings, when it is turned on, QB will use DELETE FROM instead of TRUNCATE instead. Let me know if this can work.

[ Permlink | « Hide ]
AlSt [22/Jun/18 06:37 AM]
I did a lot more monitoring and also research on this.

My results:
TRUNCATE is considered as schema change (DDL) and because of that it also can be blocked by any Exclusive Lock in the whole DB. So my former approach most likely wouldn't help anything.
The only thing that helps (as far as I can see) is to not use a TRUNCATE at all and use DELETE instead because that is just a data modification (DML).

So I'm asking you to add an option to always use DELETE and do not try TRUNCATE at all. As far as I can see a TRUNCATE is tried first and afterwards if that fails a DELETE is tried.
But TRUNCATE itself does not fail, it just blocks inserts afterwards into the measurements_data_rXX table and this uses up all connections in the DB connection pool.

Here is just an example of our monitoring output:

Locks:
 191660 | relation | RowExclusiveLock | quickbuild-rx | pg_largeobject_loid_pn_index
 191660 | relation | RowExclusiveLock | quickbuild-rx | pg_largeobject

Blocked queries:
 143449 | quickbuild-rx | {191660} | TRUNCATE TABLE QB_MEASUREMENT_DATAR03

Blocking:
 191660 | 2018-06-22 02:33:11.456998+02 | fastpath function call | /* from Configuration where id>=:fromId and id<=:toId */ select configurat0_.QB_ID as QB_ID1_8_, configurat0_.QB_ACTIVE_REPOSITORY as QB_ACTIV2_8_, configurat0_.QB_AGGREGATIONDOMS as QB_AGGRE3_8_, configurat0_.QB_ARTIFACT_CLEANUP_STRATEGY as QB_ARTIF4_8_, configurat0_.QB_ARTIFACT_STORAGEDOM as QB_ARTIF5_8_, configurat0_.qb_audit_request as qb_audit6_8_, configurat0_.QB_BUILD_CLEANUP_STRATEGY as QB_BUILD7_8_, configurat0_.QB_BUILD_CONDITION as QB_BUILD8_8_, configurat0_.QB_COMMAND_PRE_KILL_SCRIPT as QB_COMMA9_8_, configurat0_.QB_CONCURRENT as QB_CONC10_8_, configurat0_.QB_CUSTOM_COLUMN_CONFIG as QB_CUST11_8_, configurat0_.QB_DATA as QB_DATA12_8_, configurat0_.QB_DESCRIPTION as QB_DESC13_8_, configurat0_.QB_DISABLED as QB_DISA14_8_, configurat0_.QB_ERROR_MESSAGE as QB_ERRO15_8_, configurat0_.QB_FORCE_KILL_TIMEOUT as QB_FORC16_8_, configurat0_.QB_LEGACY_CMD_MODE as QB_LEGA17_8_, configurat0_.QB_LOG_LEVEL as QB_LOG_18_8_, configurat0_.QB_NAME as QB_NAME19_8_, configurat0_.QB_NODE_ASSIGNMENT as QB_NODE20_8_, conf


Here you can see that backend with PID 191660 is blocking PID 143449 which is the TRUNCATION.

That TRUNCATE is DDL and not DML is the case also for MySQL (https://dev.mysql.com/doc/refman/8.0/en/truncate-table.html). So it might be a good idea to drop the TRUNCATE entirely. I know that emptying the table takes longer then, but it is just a DML then and does not react to any locks.
If you do not want to remove the truncate I'm totally happy with a configuration option in some way.

[ Permlink | « Hide ]
Steve Luo [18/Jun/18 10:16 AM]
Yes, in your case, the truncate process was not finished, so all insertions were waiting for truncate. But truncate itself may also wait for other operation to release lock. So we need know what is that operation. Truncate itself can be slow in some cases, but 1+ hour is still too long to accept.

[ Permlink | « Hide ]
AlSt [18/Jun/18 06:36 AM]
One addition to that. But as you can see in the list of current activity at least there is no other statement running before the time of the truncate which would indicate that this is the only long running query. The other queries (the inserts in that case) are waiting for the truncate to finish, not the other way round.

[ Permlink | « Hide ]
AlSt [18/Jun/18 06:35 AM]
I have already lock monitoring in, but I just extended it to give more information. Hopefully we see more now and discover why truncation takes that long.

[ Permlink | « Hide ]
Steve Luo [15/Jun/18 04:33 PM]
Sorry for the problem.

TRUNCATE TABLE usually is faster than DELETE FROM. After googling, it may be because TRUNCATE is waiting for some lock to be released. See below page:

https://stackoverflow.com/questions/19936204/postgres-truncate-is-slow

so, would you please check are there any locks that prevent the TRUNCATE from proceeding?

You may also stop the QuickBuild server and do TRUNCATE TABLE manually to see if it can be faster.

[ Permlink | « Hide ]
AlSt [15/Jun/18 08:38 AM]
It is hitting us now again more frequently:

These are the operations on the qb table:

  81116 | 2018-06-15 02:00:44.689286+02 | active | /* insert com.pmease.quickbuild.model.MeasurementDataR04 */ insert into QB_MEASUREMENT_DATAR04 (QB_METRIC_NAME, QB_SOURCE, QB_TIMESTAMP, QB_VALUE, QB_ID) values ($1, $2, $3, $4, $5)
  81117 | 2018-06-15 02:00:06.700729+02 | active | /* insert com.pmease.quickbuild.model.MeasurementDataR04 */ insert into QB_MEASUREMENT_DATAR04 (QB_METRIC_NAME, QB_SOURCE, QB_TIMESTAMP, QB_VALUE, QB_ID) values ($1, $2, $3, $4, $5)
 160559 | 2018-06-15 02:00:42.976858+02 | active | /* insert com.pmease.quickbuild.model.MeasurementDataR04 */ insert into QB_MEASUREMENT_DATAR04 (QB_METRIC_NAME, QB_SOURCE, QB_TIMESTAMP, QB_VALUE, QB_ID) values ($1, $2, $3, $4, $5)
 160561 | 2018-06-15 02:00:40.727769+02 | active | /* insert com.pmease.quickbuild.model.MeasurementDataR04 */ insert into QB_MEASUREMENT_DATAR04 (QB_METRIC_NAME, QB_SOURCE, QB_TIMESTAMP, QB_VALUE, QB_ID) values ($1, $2, $3, $4, $5)
 160560 | 2018-06-15 02:00:28.472221+02 | active | /* insert com.pmease.quickbuild.model.MeasurementDataR04 */ insert into QB_MEASUREMENT_DATAR04 (QB_METRIC_NAME, QB_SOURCE, QB_TIMESTAMP, QB_VALUE, QB_ID) values ($1, $2, $3, $4, $5)
 141984 | 2018-06-15 02:00:14.718237+02 | active | /* insert com.pmease.quickbuild.model.MeasurementDataR04 */ insert into QB_MEASUREMENT_DATAR04 (QB_METRIC_NAME, QB_SOURCE, QB_TIMESTAMP, QB_VALUE, QB_ID) values ($1, $2, $3, $4, $5)
  66300 | 2018-06-15 00:22:57.734209+02 | active | TRUNCATE TABLE QB_MEASUREMENT_DATAR04
 160707 | 2018-06-15 02:00:43.253994+02 | active | /* insert com.pmease.quickbuild.model.MeasurementDataR04 */ insert into QB_MEASUREMENT_DATAR04 (QB_METRIC_NAME, QB_SOURCE, QB_TIMESTAMP, QB_VALUE, QB_ID) values ($1, $2, $3, $4, $5)
 160708 | 2018-06-15 02:00:45.983212+02 | active | /* insert com.pmease.quickbuild.model.MeasurementDataR04 */ insert into QB_MEASUREMENT_DATAR04 (QB_METRIC_NAME, QB_SOURCE, QB_TIMESTAMP, QB_VALUE, QB_ID) values ($1, $2, $3, $4, $5)
 160709 | 2018-06-15 02:00:45.267552+02 | active | /* insert com.pmease.quickbuild.model.MeasurementDataR04 */ insert into QB_MEASUREMENT_DATAR04 (QB_METRIC_NAME, QB_SOURCE, QB_TIMESTAMP, QB_VALUE, QB_ID) values ($1, $2, $3, $4, $5)
  11980 | 2018-06-15 02:18:04.840034+02 | fastpath function call | /* from Configuration where id>=:fromId and id<=:toId */ select configurat0_.QB_ID as QB_ID1_8_, configurat0_.QB_ACTIVE_REPOSITORY as QB_ACTIV2_8_, configurat0_.QB_AGGREGATIONDOMS as QB_AGGRE3_8_, configurat0_.QB_ARTIFACT_CLEANUP_STRATEGY as QB_ARTIF4_8_, configurat0_.QB_ARTIFACT_STORAGEDOM as QB_ARTIF5_8_, configurat0_.qb_audit_request as qb_audit6_8_, configurat0_.QB_BUILD_CLEANUP_STRATEGY as QB_BUILD7_8_, configurat0_.QB_BUILD_CONDITION as QB_BUILD8_8_, config
urat0_.QB_COMMAND_PRE_KILL_SCRIPT as QB_COMMA9_8_, configurat0_.QB_CONCURRENT as QB_CONC10_8_, configurat0_.QB_CUSTOM_COLUMN_CONFIG as QB_CUST11_8_, configurat0_.QB_DATA as QB_DATA12_8_, configurat0_.QB_DESCRIPTION as QB_DESC13_8_, configurat0_.QB_DISABLED as QB_DISA14_8_, configurat0_.QB_ERROR_MESSAGE as QB_ERRO15_8_, configurat0_.QB_FORCE_KILL_TIMEOUT as QB_FORC16_8_, configurat0_.QB_LEGACY_CMD_MODE as QB_LEGA17_8_, configurat0_.QB_LOG_LEVEL as QB_LOG_18_8_, configurat0_.QB_NAME as QB_NAME19_8_, configurat0_.QB_NODE_ASSIGNMENT
 as QB_NODE20_8_, conf
 160716 | 2018-06-15 02:00:46.216015+02 | active | /* insert com.pmease.quickbuild.model.MeasurementDataR04 */ insert into QB_MEASUREMENT_DATAR04 (QB_METRIC_NAME, QB_SOURCE, QB_TIMESTAMP, QB_VALUE, QB_ID) values ($1, $2, $3, $4, $5)
 160717 | 2018-06-15 02:00:45.304035+02 | active | /* insert com.pmease.quickbuild.model.MeasurementDataR04 */ insert into QB_MEASUREMENT_DATAR04 (QB_METRIC_NAME, QB_SOURCE, QB_TIMESTAMP, QB_VALUE, QB_ID) values ($1, $2, $3, $4, $5)
 160718 | 2018-06-15 02:00:47.036611+02 | active | /* insert com.pmease.quickbuild.model.MeasurementDataR04 */ insert into QB_MEASUREMENT_DATAR04 (QB_METRIC_NAME, QB_SOURCE, QB_TIMESTAMP, QB_VALUE, QB_ID) values ($1, $2, $3, $4, $5)
 160723 | 2018-06-15 02:00:47.132311+02 | active | /* insert com.pmease.quickbuild.model.MeasurementDataR04 */ insert into QB_MEASUREMENT_DATAR04 (QB_METRIC_NAME, QB_SOURCE, QB_TIMESTAMP, QB_VALUE, QB_ID) values ($1, $2, $3, $4, $5)
 160724 | 2018-06-15 02:00:46.415248+02 | active | /* insert com.pmease.quickbuild.model.MeasurementDataR04 */ insert into QB_MEASUREMENT_DATAR04 (QB_METRIC_NAME, QB_SOURCE, QB_TIMESTAMP, QB_VALUE, QB_ID) values ($1, $2, $3, $4, $5)
 160725 | 2018-06-15 02:00:46.680211+02 | active | /* insert com.pmease.quickbuild.model.MeasurementDataR04 */ insert into QB_MEASUREMENT_DATAR04 (QB_METRIC_NAME, QB_SOURCE, QB_TIMESTAMP, QB_VALUE, QB_ID) values ($1, $2, $3, $4, $5)
 160730 | 2018-06-15 02:00:47.074841+02 | active | /* insert com.pmease.quickbuild.model.MeasurementDataR04 */ insert into QB_MEASUREMENT_DATAR04 (QB_METRIC_NAME, QB_SOURCE, QB_TIMESTAMP, QB_VALUE, QB_ID) values ($1, $2, $3, $4, $5)
 160731 | 2018-06-15 02:00:47.342473+02 | active | /* insert com.pmease.quickbuild.model.MeasurementDataR04 */ insert into QB_MEASUREMENT_DATAR04 (QB_METRIC_NAME, QB_SOURCE, QB_TIMESTAMP, QB_VALUE, QB_ID) values ($1, $2, $3, $4, $5)
 160732 | 2018-06-15 02:00:47.365443+02 | active | /* insert com.pmease.quickbuild.model.MeasurementDataR04 */ insert into QB_MEASUREMENT_DATAR04 (QB_METRIC_NAME, QB_SOURCE, QB_TIMESTAMP, QB_VALUE, QB_ID) values ($1, $2, $3, $4, $5)
 160733 | 2018-06-15 02:00:47.557861+02 | active | /* insert com.pmease.quickbuild.model.MeasurementDataR04 */ insert into QB_MEASUREMENT_DATAR04 (QB_METRIC_NAME, QB_SOURCE, QB_TIMESTAMP, QB_VALUE, QB_ID) values ($1, $2, $3, $4, $5)
 160734 | 2018-06-15 02:00:47.434562+02 | active | /* insert com.pmease.quickbuild.model.MeasurementDataR04 */ insert into QB_MEASUREMENT_DATAR04 (QB_METRIC_NAME, QB_SOURCE, QB_TIMESTAMP, QB_VALUE, QB_ID) values ($1, $2, $3, $4, $5)
 160735 | 2018-06-15 02:00:47.715708+02 | active | /* insert com.pmease.quickbuild.model.MeasurementDataR04 */ insert into QB_MEASUREMENT_DATAR04 (QB_METRIC_NAME, QB_SOURCE, QB_TIMESTAMP, QB_VALUE, QB_ID) values ($1, $2, $3, $4, $5)
 160737 | 2018-06-15 02:00:47.738924+02 | active | /* insert com.pmease.quickbuild.model.MeasurementDataR04 */ insert into QB_MEASUREMENT_DATAR04 (QB_METRIC_NAME, QB_SOURCE, QB_TIMESTAMP, QB_VALUE, QB_ID) values ($1, $2, $3, $4, $5)
 160736 | 2018-06-15 02:00:47.8906+02 | active | /* insert com.pmease.quickbuild.model.MeasurementDataR04 */ insert into QB_MEASUREMENT_DATAR04 (QB_METRIC_NAME, QB_SOURCE, QB_TIMESTAMP, QB_VALUE, QB_ID) values ($1, $2, $3, $4, $5)
 160738 | 2018-06-15 02:00:47.964013+02 | active | /* insert com.pmease.quickbuild.model.MeasurementDataR04 */ insert into QB_MEASUREMENT_DATAR04 (QB_METRIC_NAME, QB_SOURCE, QB_TIMESTAMP, QB_VALUE, QB_ID) values ($1, $2, $3, $4, $5)
 142003 | 2018-06-15 02:00:06.615268+02 | active | /* insert com.pmease.quickbuild.model.MeasurementDataR04 */ insert into QB_MEASUREMENT_DATAR04 (QB_METRIC_NAME, QB_SOURCE, QB_TIMESTAMP, QB_VALUE, QB_ID) values ($1, $2, $3, $4, $5)
 142004 | 2018-06-15 02:00:08.917919+02 | active | /* insert com.pmease.quickbuild.model.MeasurementDataR04 */ insert into QB_MEASUREMENT_DATAR04 (QB_METRIC_NAME, QB_SOURCE, QB_TIMESTAMP, QB_VALUE, QB_ID) values ($1, $2, $3, $4, $5)
 160742 | 2018-06-15 02:00:48.999219+02 | active | /* insert com.pmease.quickbuild.model.MeasurementDataR04 */ insert into QB_MEASUREMENT_DATAR04 (QB_METRIC_NAME, QB_SOURCE, QB_TIMESTAMP, QB_VALUE, QB_ID) values ($1, $2, $3, $4, $5)
 142006 | 2018-06-15 02:00:08.669503+02 | active | /* insert com.pmease.quickbuild.model.MeasurementDataR04 */ insert into QB_MEASUREMENT_DATAR04 (QB_METRIC_NAME, QB_SOURCE, QB_TIMESTAMP, QB_VALUE, QB_ID) values ($1, $2, $3, $4, $5)
 160743 | 2018-06-15 02:00:51.99501+02 | active | /* insert com.pmease.quickbuild.model.MeasurementDataR04 */ insert into QB_MEASUREMENT_DATAR04 (QB_METRIC_NAME, QB_SOURCE, QB_TIMESTAMP, QB_VALUE, QB_ID) values ($1, $2, $3, $4, $5)
 160744 | 2018-06-15 02:00:48.49079+02 | active | /* insert com.pmease.quickbuild.model.MeasurementDataR04 */ insert into QB_MEASUREMENT_DATAR04 (QB_METRIC_NAME, QB_SOURCE, QB_TIMESTAMP, QB_VALUE, QB_ID) values ($1, $2, $3, $4, $5)
 160746 | 2018-06-15 02:00:48.937013+02 | active | /* insert com.pmease.quickbuild.model.MeasurementDataR04 */ insert into QB_MEASUREMENT_DATAR04 (QB_METRIC_NAME, QB_SOURCE, QB_TIMESTAMP, QB_VALUE, QB_ID) values ($1, $2, $3, $4, $5)
 160747 | 2018-06-15 02:00:49.096771+02 | active | /* insert com.pmease.quickbuild.model.MeasurementDataR04 */ insert into QB_MEASUREMENT_DATAR04 (QB_METRIC_NAME, QB_SOURCE, QB_TIMESTAMP, QB_VALUE, QB_ID) values ($1, $2, $3, $4, $5)
 160748 | 2018-06-15 02:00:51.24197+02 | active | /* insert com.pmease.quickbuild.model.MeasurementDataR04 */ insert into QB_MEASUREMENT_DATAR04 (QB_METRIC_NAME, QB_SOURCE, QB_TIMESTAMP, QB_VALUE, QB_ID) values ($1, $2, $3, $4, $5)
 160754 | 2018-06-15 02:00:50.945667+02 | active | /* insert com.pmease.quickbuild.model.MeasurementDataR04 */ insert into QB_MEASUREMENT_DATAR04 (QB_METRIC_NAME, QB_SOURCE, QB_TIMESTAMP, QB_VALUE, QB_ID) values ($1, $2, $3, $4, $5)
 160756 | 2018-06-15 02:00:50.306648+02 | active | /* insert com.pmease.quickbuild.model.MeasurementDataR04 */ insert into QB_MEASUREMENT_DATAR04 (QB_METRIC_NAME, QB_SOURCE, QB_TIMESTAMP, QB_VALUE, QB_ID) values ($1, $2, $3, $4, $5)
 160755 | 2018-06-15 02:00:50.828155+02 | active | /* insert com.pmease.quickbuild.model.MeasurementDataR04 */ insert into QB_MEASUREMENT_DATAR04 (QB_METRIC_NAME, QB_SOURCE, QB_TIMESTAMP, QB_VALUE, QB_ID) values ($1, $2, $3, $4, $5)
 160761 | 2018-06-15 02:00:55.44376+02 | active | /* insert com.pmease.quickbuild.model.MeasurementDataR04 */ insert into QB_MEASUREMENT_DATAR04 (QB_METRIC_NAME, QB_SOURCE, QB_TIMESTAMP, QB_VALUE, QB_ID) values ($1, $2, $3, $4, $5)
 160762 | 2018-06-15 02:00:57.286926+02 | active | /* insert com.pmease.quickbuild.model.MeasurementDataR04 */ insert into QB_MEASUREMENT_DATAR04 (QB_METRIC_NAME, QB_SOURCE, QB_TIMESTAMP, QB_VALUE, QB_ID) values ($1, $2, $3, $4, $5)
 160763 | 2018-06-15 02:00:52.514302+02 | active | /* insert com.pmease.quickbuild.model.MeasurementDataR04 */ insert into QB_MEASUREMENT_DATAR04 (QB_METRIC_NAME, QB_SOURCE, QB_TIMESTAMP, QB_VALUE, QB_ID) values ($1, $2, $3, $4, $5)
 160770 | 2018-06-15 02:00:53.498495+02 | active | /* insert com.pmease.quickbuild.model.MeasurementDataR04 */ insert into QB_MEASUREMENT_DATAR04 (QB_METRIC_NAME, QB_SOURCE, QB_TIMESTAMP, QB_VALUE, QB_ID) values ($1, $2, $3, $4, $5)
 160771 | 2018-06-15 02:00:55.472267+02 | active | /* insert com.pmease.quickbuild.model.MeasurementDataR04 */ insert into QB_MEASUREMENT_DATAR04 (QB_METRIC_NAME, QB_SOURCE, QB_TIMESTAMP, QB_VALUE, QB_ID) values ($1, $2, $3, $4, $5)
 160772 | 2018-06-15 02:00:56.488807+02 | active | /* insert com.pmease.quickbuild.model.MeasurementDataR04 */ insert into QB_MEASUREMENT_DATAR04 (QB_METRIC_NAME, QB_SOURCE, QB_TIMESTAMP, QB_VALUE, QB_ID) values ($1, $2, $3, $4, $5)
 160786 | 2018-06-15 02:00:59.474122+02 | active | /* insert com.pmease.quickbuild.model.MeasurementDataR04 */ insert into QB_MEASUREMENT_DATAR04 (QB_METRIC_NAME, QB_SOURCE, QB_TIMESTAMP, QB_VALUE, QB_ID) values ($1, $2, $3, $4, $5)
 160787 | 2018-06-15 02:00:58.901543+02 | active | /* insert com.pmease.quickbuild.model.MeasurementDataR04 */ insert into QB_MEASUREMENT_DATAR04 (QB_METRIC_NAME, QB_SOURCE, QB_TIMESTAMP, QB_VALUE, QB_ID) values ($1, $2, $3, $4, $5)
 160788 | 2018-06-15 02:00:57.636525+02 | active | /* insert com.pmease.quickbuild.model.MeasurementDataR04 */ insert into QB_MEASUREMENT_DATAR04 (QB_METRIC_NAME, QB_SOURCE, QB_TIMESTAMP, QB_VALUE, QB_ID) values ($1, $2, $3, $4, $5)
 160796 | 2018-06-15 02:01:01.81768+02 | active | /* insert com.pmease.quickbuild.model.MeasurementDataR04 */ insert into QB_MEASUREMENT_DATAR04 (QB_METRIC_NAME, QB_SOURCE, QB_TIMESTAMP, QB_VALUE, QB_ID) values ($1, $2, $3, $4, $5)
 160798 | 2018-06-15 02:01:02.082908+02 | active | /* insert com.pmease.quickbuild.model.MeasurementDataR04 */ insert into QB_MEASUREMENT_DATAR04 (QB_METRIC_NAME, QB_SOURCE, QB_TIMESTAMP, QB_VALUE, QB_ID) values ($1, $2, $3, $4, $5)
 160797 | 2018-06-15 02:01:02.725291+02 | active | /* insert com.pmease.quickbuild.model.MeasurementDataR04 */ insert into QB_MEASUREMENT_DATAR04 (QB_METRIC_NAME, QB_SOURCE, QB_TIMESTAMP, QB_VALUE, QB_ID) values ($1, $2, $3, $4, $5)
(50 rows)

So the truncate table is still running while something already tries to put new data in. This exhausts the whole DB connection pool and even agents are disconnecting because of this. This leads to a lot of failed builds because the agent is just gone (because it waits for a DB connection and then runs into the read timeout of the http connection).

Is there anything we can do in version 7.0.31 to mitigate that problem? We disabled a lot of metrics in the measurements plugin now. Would this help?

Might it be better to add some more rotation to the raw measurement tables as a final fix? I do not know how the rotation works now but it seems that dead tables which are truncated should not be used at that day for pushing new data in.

[ Permlink | « Hide ]
Steve Luo [30/May/18 03:38 PM]
There is no need to lock the table because when QuickBuild truncates the table, there is no any operation on that table. So we may needn't worry about executing the procedure asynchronously.

[ Permlink | « Hide ]
AlSt [28/May/18 11:10 AM]
This should be absolutely possible to configure a truncation stored procedure. The only thing is that as far as I know there is no way of executing a statement asynchronously (the deletion of the old table) in that procedure.

You are absolutely right that this is a database specific thing. Maybe you also have some different idea to get this faster and do not rely on long running query processing.

That was just my idea for now. Maybe it is also possible to have a different approach of table rotation to be able to execute the truncation call without affecting anything else.

[ Permlink | « Hide ]
Steve Luo [26/May/18 02:44 PM]
Thank you for the detailed explanation. But it is hard for us to adopt the method like you mentioned as we need support different database, and, it seems that this issue is postgresql specific.

Is it possible to create a stored procedure so QuickBuild can call it to truncate the table?


Powered by Atlassian JIRA™ the Professional Issue Tracker. (Standard Edition, Version: 3.7.4-#189) - Bug/feature request - Contact Administrators