"not related" code blocks for removal of dead rows when using vacuum and this kills the performance

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Hi

We have a master code block which starts small, tiny operations that create a table and inserts data into that table in many threads.  

Nothing is done the master code, we follow an Orchestration pattern , where master just sends a message about what to do and that is done in other database connections not related connections used by master code.

In the master code I add sleep after the CRUD operations are done to make it easier to test. The test table will not change in the rest of this master code (in real life it happens more in the master code off course) .

Then we start testing VACUUM and very simple SQL testing in another window.

We can now show we have performance of "3343.794 ms" and not "0.123 ms", which is what we get when we are able to remove dead rows and run a new analyze.

The problem is that as long as the master code is active, we cannot remove alle dead rows and that what seems to be killing the performance.

With active I mean in hanging on pg_sleep and remember that this master has not created the test table or inserted any data in this test table it self.

Is the expected behavior ?

Is possible to around this problem in any way ?

In this note you find a detailed description and a simple standalone test script  https://gitlab.com/nibioopensource/resolve-overlap-and-gap/-/issues/67#note_1779300212

I have tested on "PostgreSQL 14.10 (Homebrew) on aarch64-apple-darwin23.0.0, compiled by Apple clang version 15.0.0 (clang-1500.0.40.1), 64-bit" and "PostgreSQL 12.6 (Ubuntu 12.6-0ubuntu0.20.04.1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 9.3.0-17ubuntu1~20.04) 9.3.0, 64-bit"

Thanks .

Lars


[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux