Обсуждение: Size of Postgres Transaction Logs
I had been using Postgres database version 6.5.3. Initially, I had the problem of the data files growing very huge and thereby affecting the performance of the database. However, on employing vacuum regularly, I found that the size of files occupied on the disk would reduce except for the INDEX FILES. It was rather funny to see that even though there are no records in the table, the index file was occupying about 6-8MB. The only solution I could find was to delete and recreate the index. When I migrated to Postgres 7.x (7.0.3 and 7.1.1), I find that the concept of a separate transaction log has been introduced (This was located as pg_log in 7.0.3 in the data directory and as a numbered file in the global directory under the data directory). I find that if there are repeated periodic operations on the postgres database, the transaction log size increases very rapidly (I was aghast to see that after initializing a new database with only 2 tables and a program polling one of the tables every 20 secs, the transaction log had grown to 120MB overnight) and there appears to be no mechanism of reducing the log size except for taking a dump, reinitializing the database and reimporting the dump. I tried vacuuming the database(s) and unfortunately did not succeed in reducing the overall size of the log. I have tried truncating the log file size by using some of the unix tools/programs but any tampering of the log renders all the databases/tables useless. Dumping the database and restoring it is too cumbersome to be done on a day to day basis. My question is whether after vacuuming, the cleared space in the log file reused and also whether the overall performance of the DB is affected by a large log file on disk. Further, I would like to know if there is any mechanism of releasing the unused space from the log. Thanks Vikaas
Vikaas BV <vikas@cellcloud.com> writes: > I find that if there > are repeated periodic operations on the postgres database, the > transaction log size increases very rapidly (I was aghast to see that > after initializing a new database with only 2 tables and a program > polling one of the tables every 20 secs, the transaction log had grown > to 120MB overnight) and there appears to be no mechanism of reducing the > log size except for taking a dump, reinitializing the database and > reimporting the dump. The WAL log should be auto-truncated at checkpoints, assuming that the old entries are no longer needed. However, if you leave uncommitted transactions sitting around, their WAL entries can't be deleted until you commit or abort them. I'd only expect massive WAL growth if you have very long-running transactions ... regards, tom lane
At 12:45 PM 5/18/01 -0400, Tom Lane wrote: > >The WAL log should be auto-truncated at checkpoints, assuming that the >old entries are no longer needed. However, if you leave uncommitted >transactions sitting around, their WAL entries can't be deleted until >you commit or abort them. I'd only expect massive WAL growth if you >have very long-running transactions ... Uhoh. Scenario 1: Using FCGI, persistent DB connections and Perl DBI. The problem I see is that the Perl DBI doesn't have a BEGIN transaction, can only rollback or commit, which implicitly begins a new transaction. So what happens typically is: 1) open DB connection. 2) wait for http request. 3) rollback (to implicitly begin a new transaction so that 'now' isn't years ago :) ). 4) do DB stuff 5) rollback/commit. Would we get massive WAL growth if the long running transactions are inactive (stuck at step 2) - not doing anything, but other transactions are active? Scenario 2: search/query engine - only selects are done, so db connection and transaction held open for long periods. Lots of selects being done. Any issues with this? Cheerio, Link.
Lincoln Yeoh <lyeoh@pop.jaring.my> writes: > So what happens typically is: > 1) open DB connection. > 2) wait for http request. > 3) rollback (to implicitly begin a new transaction so that 'now' isn't > years ago :) ). > 4) do DB stuff > 5) rollback/commit. > Would we get massive WAL growth if the long running transactions are > inactive (stuck at step 2) - not doing anything, but other transactions are > active? Not if they haven't done anything yet. "BEGIN" doesn't really start a transaction, it's only your first database access that gets the transaction engine running. Furthermore, if I recall the WAL stuff correctly, what really counts for WAL is your first *write* access. A read-only xact doesn't exist as far as WAL is concerned. Nonetheless, I'm pretty concerned about this log-growth issue. See current pghackers thread about whether we really need WAL to provide UNDO support. AFAICS, we could truncate the WAL log at each checkpoint if we didn't need UNDO. regards, tom lane