Re: [HACKERS] Early locking option to parallel backup
On 11/06/2017 12:30 PM, Stephen Frost wrote: * Lucas (luca...@gmail.com) wrote: pg_dump was taking more than 24 hours to complete in one of my databases. I begin to research alternatives. Parallel backup reduced the backup time to little less than a hour, but it failed almost every time because of concurrent queries that generated exclusive locks. It is difficult to guarantee that my applications will not issue queries such as drop table, alter table, truncate table, create index or drop index for a hour. And I prefer not to create controls mechanisms to that end if I can work around it. I certainly understand the value of pg_dump-based backups, but have you considered doing file-based backups? That would avoid the need to do any in-database locking at all, and would give you the ability to do PITR too. Further, you could actually restore that backup to another system and then do a pg_dump there to get a logical representation (and this would test your physical database backup/restore process too...). Yes, a point in time recovery has the advantage of keeping the backup more up-to-date, but has the disadvantage of being more expensive and complex. In my case, point in time recovery would require an upgrade of 10 TB of storage space and my stakeholders did not approved this investment yet. I suspect that there is lots of users that uses pg_dump as primary backup tool and that they would benefit of a more reliable parallel backup. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Early locking option to parallel backup
Em 05/11/2017 21:09, Andres Freund escreveu: On 2017-11-05 17:38:39 -0500, Robert Haas wrote: On Sun, Nov 5, 2017 at 5:17 AM, Lucas <luca...@gmail.com> wrote: The patch creates a "--lock-early" option which will make pg_dump to issue shared locks on all tables on the backup TOC on each parallel worker start. That way, the backup has a very small chance of failing. When it does, happen in the first few seconds of the backup job. My backup scripts (not included here) are aware of that and retries the backup in case of failure. I wonder why we don't do this already ... and by default. Well, the current approach afaics requires #relations * 2 locks, whereas acquiring them in every worker would scale that with the number of workers. Yes, that is why I proposed as an option. As an option will not affect anyone that does not want to use it. IIUC the problem here is that even though a lock is already held by the main backend an independent locker's request will prevent the on-demand lock by the dump worker from being granted. It seems to me the correct fix here would be to somehow avoid the fairness logic in the parallel dump case - although I don't quite know how to best do so. It seems natural to think several connections in a synchronized snapshot as the same connection. Then it may be reasonable to grant a shared lock out of turn if any connection of the same shared snapshot already have a granted lock for the same relation. Last year Tom mentioned that there is already queue-jumping logic of that sort in the lock manager for other purposes. Although seems conceptually simple, I suspect the implementation is not. On the other hand, the lock-early option is very simple and has no impact on anyone that does not want to use it. --- Lucas -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Preventing deadlock on parallel backup
Tom, Yes, it is what I mean. Is what pg_dump uses to get things synchronized. It seems to me a clear marker that the same task is using more than one connection to accomplish the one job. Em 08/09/2016 6:34 PM, "Tom Lane" <t...@sss.pgh.pa.us> escreveu: > Lucas <luca...@gmail.com> writes: > > The queue jumping logic can not use the distributed transaction id? > > If we had such a thing as a distributed transaction id, maybe the > answer could be yes. We don't. > > I did wonder whether using a shared snapshot might be a workable proxy > for that, but haven't pursued it. > > regards, tom lane >
Re: [HACKERS] Preventing deadlock on parallel backup
I agree. It is an ugly hack. But to me, the reduced window for failure is important. And that way an failure will happen right away to be submitted to my operators as soon as possible. The queue jumping logic can not use the distributed transaction id? On my logic, if a connection requests a shared lock that is already granted to another connection in the same distributed transaction it should be granted right away... make sense? Em 08/09/2016 4:15 PM, "Tom Lane" <t...@sss.pgh.pa.us> escreveu: > Lucas <luca...@gmail.com> writes: > > I made a small modification in pg_dump to prevent parallel backup > failures > > due to exclusive lock requests made by other tasks. > > > The modification I made take shared locks for each parallel backup worker > > at the very beginning of the job. That way, any other job that attempts > to > > acquire exclusive locks will wait for the backup to finish. > > I do not think this would eliminate the problem; all it's doing is making > the window for trouble a bit narrower. Also, it implies taking out many > locks that would never be used, since no worker process will be touching > all of the tables. > > I think a real solution involves teaching the backend to allow a worker > process to acquire a lock as long as its master already has the same lock. > There's already queue-jumping logic of that sort in the lock manager, but > it doesn't fire because we don't see that there's a potential deadlock. > What needs to be worked out, mostly, is how we can do that without > creating security hazards (since the backend would have to accept a > command enabling this behavior from the client). Maybe it's good enough > to insist that leader and follower be same user ID, or maybe not. > > There's some related problems in parallel query, which AFAIK we just have > an ugly kluge solution for ATM. It'd be better if there were a clear > model of when to allow a parallel worker to get a lock out-of-turn. > > regards, tom lane >
[HACKERS] Preventing deadlock on parallel backup
People,
I made a small modification in pg_dump to prevent parallel backup failures
due to exclusive lock requests made by other tasks.
The modification I made take shared locks for each parallel backup worker
at the very beginning of the job. That way, any other job that attempts to
acquire exclusive locks will wait for the backup to finish.
In my case, each server was taking a day to complete the backup, now with
parallel backup one is taking 3 hours and the others less than a hour.
The code below is not very elegant, but it works for me. My whishlist for
the backup is:
1) replace plpgsql by c code reading the backup toc and assembling the lock
commands.
2) create an timeout to the locks.
3) broadcast the end of copy to every worker in order to release the locks
as early as possible;
4) create a monitor thread that prioritize an copy job based on a exclusive
lock acquired;
5) grant the lock for other connection of the same distributed transaction
if it is held by any connection of the same distributed transaction. There
is some sideefect I can't see on that?
1 to 4 are within my capabilities and I may do it in the future. 4 is to
advanced for me and I do not dare to mess with something so fundamental
rights now.
Anyone else is working on that?
On, Parallel.c, void RunWorker(...), add:
PQExpBuffer query;
PGresult *res;
query = createPQExpBuffer();
resetPQExpBuffer(query);
appendPQExpBuffer(query,
"do language 'plpgsql' $$"
" declare "
"x record;"
" begin"
"for x in select * from pg_tables where schemaname not in
('pg_catalog','information_schema') loop"
"raise info 'lock table %.%', x.schemaname, x.tablename;"
"execute 'LOCK TABLE
'||quote_ident(x.schemaname)||'.'||quote_ident(x.tablename)||' IN ACCESS
SHARE MODE NOWAIT';"
"end loop;"
"end"
"$$" );
res = PQexec(AH->connection, query->data);
if (!res || PQresultStatus(res) != PGRES_COMMAND_OK)
exit_horribly(modulename,"Could not lock the tables to begin the
work\n\n");
PQclear(res);
destroyPQExpBuffer(query);
Re: [HACKERS] Buffer Requests Trace
Answering your first question: running tpcc for 1 minute, in a database with 64 warehouses (6~7GB), with a buffer pool of 128MB (around 1.8% of database size) and a hit ratio of ~91%, I get a throughput of 45~50 transactions per second. I did some experiments and I got the following information about my tpcc database and benchmark. The database is created with 64 warehouses. Table|Index | Data Size | Index Size | Total +--+---++- stock | stock_pkey | 2209 MB | 263 MB | 2472 MB order_line | order_line_pkey | 2041 MB | 678 MB | 2719 MB customer | idx_customer_name| 1216 MB | 146 MB | 1420 MB customer | customer_pkey| 1216 MB | 58 MB | 1420 MB history| | 164 MB| | 164 MB oorder | oorder_pkey | 134 MB| 68 MB | 362 MB oorder | idx_order| 134 MB| 80 MB | 362 MB oorder | oorder_o_w_id_o_d_id_o_c_id_o_id_key | 134 MB| 80 MB | 362 MB new_order | new_order_pkey | 27 MB | 17 MB | 45 MB item | item_pkey| 10168 kB | 2208 kB | 12 MB district | district_pkey| 776 kB| 72 kB | 880 kB warehouse | warehouse_pkey | 384 kB| 16 kB | 432 kB By executing the tpcc benchmark for 1 minute I get about 2.9 million buffer requests. The distribution of these requests in the relations and indexes are (in descending order): customer1383399 stock_pkey 442600 stock321370 order_line 255314 order_line_pkey 156132 oorder58665 oorder_pkey 57895 customer_pkey 44471 new_order_pkey39552 idx_customer_name 28286 new_order 25861 item_pkey 11702 item 11606 district 11389 district_pkey 7575 warehouse 5276 idx_order 4072 oorder_o_w_id_o_d_id_o_c_id_o_id_key 2410 warehouse_pkey 1998 history1958 All this information seems normal to me. However, from the 2.9 million buffer requests over ~800k pages, only ~150k distinct pages are being requested. This behavior could be explained by the benchmark accessing only a small set of the 64 warehouses instead of having a normal distributed access over the 64 warehouses. In other words, I think that the execution time of the benchmark is irrelevant, assuming that the transactions follow a normal distribution regarding accesses to warehouses. On Wed, Oct 15, 2014 at 7:41 PM, Jeff Janes jeff.ja...@gmail.com wrote: On Wed, Oct 15, 2014 at 6:22 AM, Lucas Lersch lucasler...@gmail.com wrote: So is it a possible normal behavior that running tpcc for 10min only access 50% of the database? Furthermore, is there a guideline of parameters for tpcc (# of warehouses, execution time, operations weight)? I'm not familiar with your benchmarking tool. With the one I am most familiar with, pgbench, if you run it against a database which is too big to fit in memory, it can take a very long time to touch each page once, because the constant random disk reads makes it run very slowly. Maybe that is something to consider here--how many transactions were actually executed during your 10 min run? Also, the tool might build tables that are only used under certain run options. Perhaps you just aren't choosing the options which invoke usage of those tables. Since you have the trace data, it should be pretty easy to count how many distinct blocks are accessed from each relation, and compare that to the size of the relations to see which relations are unused or lightly used. Cheers, Jeff -- Lucas Lersch
Re: [HACKERS] Buffer Requests Trace
Sorry for taking so long to answer. I am sending attached the patch with the changes I did to pgsql code. I followed the steps for compiling and installing pgsql from: http://www.postgresql.org/docs/current/static/install-short.html In summary, the page_id of the page being released in ReleaseBuffer() and ReleaseAndReadBuffer() is written to the file: /usr/loca/pgsql/data/trace. This file is created manually. I have also created a PrivateDirtyFlag for each backend, in analogy to the PrivateRefCount. I use this to keep track if the current backend performed an update operation in a page in the buffer pool or simply a read operation (it is not relevant now). The trace file consists of one line for each ReleaseBuffer() or ReleaseAndReadBuffer() call. The line has the format: operation,tblSpace,dbNode,relNode,blockNumber Once the trace file is complete after the execution of the tpcc benchmark, I use the following bash script to get only unique pages: cut -d',' -f2-5 trace | sort -n -t',' -k1 -k2 -k3 -k4 | uniq Today I realized that I was making a mistake in executing the oltpbenchmark application. From the 64 warehouses created for tpcc, only 1 was being accessed (the 14k distinct pages that I mentioned). I increased the terminal option of the tpcc benchmark from 1 to 64, resulting in one terminal for each warehouse. This provided me with a higher number of distinct pages being accessed. Unfortunately, from the 800k pages in the database (64 warehouses), executing tpcc for 10min resulted in 400k distinct pages being accessed. This number is much better than the previous results, but I think it is still not realistic. I would like to thank you guys for all the attention given to my problem :) On Wed, Oct 15, 2014 at 9:49 AM, Simon Riggs si...@2ndquadrant.com wrote: On 14 October 2014 17:08, Lucas Lersch lucasler...@gmail.com wrote: Unfortunately, in the generated trace with over 2 million buffer requests, only ~14k different pages are being accessed, out of the 800k of the whole database. Am I missing something here? We can't tell what you're doing just by knowing the number of unique items in your list. -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Lucas Lersch diff -rupN ./postgresql-9.3.5_original/config.log ./postgresql-9.3.5_trace/config.log --- ./postgresql-9.3.5_original/config.log 2014-10-15 10:30:11.552923099 +0200 +++ ./postgresql-9.3.5_trace/config.log 2014-10-15 10:35:45.786580479 +0200 @@ -349,7 +349,7 @@ configure:7734: $? = 0 configure:7755: result: yes configure:7766: checking for library containing setproctitle configure:7807: gcc -o conftest -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -D_GNU_SOURCEconftest.c -lm 5 -/tmp/ccf4os1l.o: In function `main': +/tmp/ccMWb4Lv.o: In function `main': conftest.c:(.text.startup+0x7): undefined reference to `setproctitle' collect2: ld returned 1 exit status configure:7814: $? = 1 @@ -389,7 +389,7 @@ configure: failed program was: | return 0; | } configure:7807: gcc -o conftest -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -D_GNU_SOURCEconftest.c -lutil -lm 5 -/tmp/ccSriijn.o: In function `main': +/tmp/ccxsb80w.o: In function `main': conftest.c:(.text.startup+0x7): undefined reference to `setproctitle' collect2: ld returned 1 exit status configure:7814: $? = 1 @@ -431,7 +431,7 @@ configure: failed program was: configure:7845: result: no configure:7853: checking for library containing dlopen configure:7894: gcc -o conftest -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -D_GNU_SOURCEconftest.c -lm 5 -/tmp/ccZgGSzt.o: In function `main': +/tmp/ccBvyipD.o: In function `main': conftest.c:(.text.startup+0x7): undefined reference to `dlopen' collect2: ld returned 1 exit status configure:7901: $? = 1 @@ -479,7 +479,7 @@ configure:7988: $? = 0 configure:8019: result: none required configure:8027: checking for library containing shl_load configure:8068: gcc -o conftest -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -D_GNU_SOURCEconftest.c -ldl -lm 5 -/tmp/cc2JE0qC.o: In function `main': +/tmp/ccSVMb9F.o: In function `main': conftest.c:(.text.startup+0x7): undefined reference to `shl_load' collect2: ld returned 1 exit status configure:8075: $? = 1 @@ -564,7 +564,7 @@ configure:8254: $? = 0 configure:8285: result: none required
Re: [HACKERS] Buffer Requests Trace
I am recording the BufferDesc.tag.blockNum for the buffer along with the spcNode, dbNode, relNode, also present in the tag. On Wed, Oct 15, 2014 at 2:27 PM, Simon Riggs si...@2ndquadrant.com wrote: On 15 October 2014 12:49, Lucas Lersch lucasler...@gmail.com wrote: Sorry for taking so long to answer. I am sending attached the patch with the changes I did to pgsql code. I followed the steps for compiling and installing pgsql from: http://www.postgresql.org/docs/current/static/install-short.html Are you recording the bufferid or the blockid? -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Lucas Lersch
Re: [HACKERS] Buffer Requests Trace
So is it a possible normal behavior that running tpcc for 10min only access 50% of the database? Furthermore, is there a guideline of parameters for tpcc (# of warehouses, execution time, operations weight)? On Wed, Oct 15, 2014 at 3:09 PM, Simon Riggs si...@2ndquadrant.com wrote: On 15 October 2014 13:44, Lucas Lersch lucasler...@gmail.com wrote: I am recording the BufferDesc.tag.blockNum for the buffer along with the spcNode, dbNode, relNode, also present in the tag. The TPC-C I/O is random, so if you run it for longer you should see a wider set. Cacheing isn't possible as a way to improve txn rates. Check that you're touching all tables. -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Lucas Lersch
Re: [HACKERS] Buffer Requests Trace
I got the following numbers from my tpcc database: Data size: ~6059MB Index size: ~1390MB Total size: ~7400MB Even considering index-only scans, the ratio of around 50% of the database pages being accessed seems unrealistic to me. On Wed, Oct 15, 2014 at 3:50 PM, Stephen Frost sfr...@snowman.net wrote: * Lucas Lersch (lucasler...@gmail.com) wrote: So is it a possible normal behavior that running tpcc for 10min only access 50% of the database? Furthermore, is there a guideline of parameters for tpcc (# of warehouses, execution time, operations weight)? Depends- you may be aware that we support index-only scans in certain situations. This means that only the index page for a given relation (and the visibility map) are accessed, and the heap is not. Thanks, Stephen -- Lucas Lersch
[HACKERS] Buffer Requests Trace
Hello, I changed the buffer manager code in order to generate a trace of page requests from the buffer manager perspective. In summary, whenever ReleaseBuffer() or ReleaseAndReadBuffer() are called, I print the page currently being released which is identified by the tuple (tableSpace, dbNode, relationNode, blockNumber). I am now running a tpcc benchmark from http://oltpbenchmark.com/ Initially I create and load the database with a scale factor of 64. This sums up to a database of around 6.7GB (~ 800k pages). Then I execute the tpcc benchmark for 1 minute with only 1 terminal. Finally I analyse the trace of the buffer requests made by the execution of the benchmark only (creation and loading not considered). Unfortunately, in the generated trace with over 2 million buffer requests, only ~14k different pages are being accessed, out of the 800k of the whole database. Am I missing something here? Best regards. -- Lucas Lersch
Re: [HACKERS] Buffer Requests Trace
Sorry, I do not understand the question. But I forgot to give an additional information: I am printing the page id for the trace file in ReleaseBuffer() only if it is a shared buffer, I am not considering local buffers. I assumed that local buffers were used only for temporary tables. On Tue, Oct 14, 2014 at 6:25 PM, Stephen Frost sfr...@snowman.net wrote: * Lucas Lersch (lucasler...@gmail.com) wrote: Unfortunately, in the generated trace with over 2 million buffer requests, only ~14k different pages are being accessed, out of the 800k of the whole database. Am I missing something here? What do you have shared_buffers set to..? Thanks, Stephen -- Lucas Lersch
Re: [HACKERS] Buffer Requests Trace
shared_buffers is 128MB and the version of pgsql is 9.3.5 On Tue, Oct 14, 2014 at 6:31 PM, Lucas Lersch lucasler...@gmail.com wrote: Sorry, I do not understand the question. But I forgot to give an additional information: I am printing the page id for the trace file in ReleaseBuffer() only if it is a shared buffer, I am not considering local buffers. I assumed that local buffers were used only for temporary tables. On Tue, Oct 14, 2014 at 6:25 PM, Stephen Frost sfr...@snowman.net wrote: * Lucas Lersch (lucasler...@gmail.com) wrote: Unfortunately, in the generated trace with over 2 million buffer requests, only ~14k different pages are being accessed, out of the 800k of the whole database. Am I missing something here? What do you have shared_buffers set to..? Thanks, Stephen -- Lucas Lersch -- Lucas Lersch
Re: [HACKERS] Buffer Requests Trace
Aren't heap and index requests supposed to go through the shared buffers anyway? On Tue, Oct 14, 2014 at 7:02 PM, Stephen Frost sfr...@snowman.net wrote: * Lucas Lersch (lucasler...@gmail.com) wrote: shared_buffers is 128MB and the version of pgsql is 9.3.5 I suspect you're not tracking what you think you're tracking, which is why I brought up shared_buffers. ~14k * 8192 (page size) = ~110MB What it sounds like you're actually tracking are shared buffer requests and not heap or index requests. Now, perhaps the test you're running only touched 110MB of the 6G database, but that seems pretty unlikely. Thanks, Stephen -- Lucas Lersch
Re: [HACKERS] Buffer Requests Trace
I see this... but ReleaseBuffer() simply decrements the reference count of page the buffer currently holds. Assuming that a ReadBuffer() - ReleaseBuffer() pattern is used for interacting with the shared_buffers, there will be a ReleaseBuffer() call for any page (heap or index) loaded into the shared_buffers. On Tue, Oct 14, 2014 at 7:21 PM, Stephen Frost sfr...@snowman.net wrote: * Lucas Lersch (lucasler...@gmail.com) wrote: Aren't heap and index requests supposed to go through the shared buffers anyway? Sure they do, but a given page in shared_buffers can be used over and over again for different heap and index pages.. Thanks, Stephen -- Lucas Lersch
[HACKERS] Postgre inner work question
Hi! Does postgre execute the queries following a execution plan tree, where the leafs are table scans, and the nodes are joins? I'm looking for a database where I can get a cardinality from a partial result of the execution... for example, print the cardinality of the results until the next join operator use this result... Thanks!
[HACKERS] Postgre inner work question
Hi! Does postgre execute the queries following a execution plan tree, where the leafs are table scans, and the nodes are joins? I'm looking for a database where I can get a cardinality from a partial result of the execution... for example, print the cardinality of the results until the next join operator use this result... Thanks!
Re: [HACKERS] scheduler in core
2010/2/20 Andrew Dunstan and...@dunslane.net We're not going to change that because some companies have insane corporate policies. I agree, Andrew... This is an outside benefit... not a reason or justification... I believe that a general purpose scheduler is similar to the autovacuum... it is not really needed, we can always configure an external scheduler. But I liked a LOT... For me is not a question of must be in core is a question of cost/benefit. I do not see much cost, but a lot of benefits: Like Joshua said abstract away from external solutions and operating system dependencies. Like Dimitri said Main advantage over cron or another scheduler being that it'd be part of my transactional backups. To me is the reliability of having the partition creation/removal being part of the database, be able of make consolidations, cleanups and periodic consistency checks and diagnostics without external dependencies. I wonder if the scheduler already existed before the implementation of the autovacuum, its implementation would not be a function executed by the in-core scheduler? - - Lucas -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] scheduler in core
Tom, I believe that in core may be installed by default in case of the pgAgent or similar solution... Many big companies does not allow the developers to configure and install components we need to request everthing in 10 copies of forms... By making it in core or installed by default means that we have more chance that the db scheduler would be widely accepted... And more important... we would not have to check its availability on the setup and provide an alternate scheduler if the database scheduler is off... I believe that a database scheduler would allow me to drop 20 thousand lines of java code in my server... 2010/2/20 Tom Lane t...@sss.pgh.pa.us Dimitri Fontaine dfonta...@hi-media.com writes: Dave Page dp...@pgadmin.org writes: Why not just use pgAgent? It's far more flexible than the design you've suggested, and already exists. What would it take to have it included in core, I don't think this really makes sense. There's basically no argument for having it in core other than I'm too lazy to install a separate package. Unlike the case for autovacuum, there isn't anything an in-core implementation could do that an external one doesn't do as well or better. So I'm not eager to take on additional maintenance burden for such a thing. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers -- Lucas
Re: [HACKERS] SELECT ... FOR UPDATE [WAIT integer | NOWAIT] for 8.5
2009/5/11 Hans-Juergen Schoenig postg...@cybertec.at the thing with statement_timeout is a little bit of an issue. you could do: SET statement_timeout TO ...; SELECT FOR UPDATE ... SET statement_timeout TO default; Why not extend the SET instruction to allow configuration parameters to be set only in the duration of the transaction or the next n commands? -- Lucas Brito
