Обсуждение: Transactions
I'm having problems with transactions, lately. I'm checking power-failure conditions on my psql-based programs, and found that a simple loop with
BEGIN TRANSACTION
INSERT INTO test VALUES('any #n');
COMMIT TRANSACTION
leaves me with no records inserted at all after an abrupt reset on the server. The loop is outside the transaction, id est, the BEGIN/COMMIT is executed every time. Is there any way to assure this, I mean, to actually write the records and to not rely on the cache to do it? Because I think it is somewhere floating in the Linux cache and it's never wrote physically, and when the power is down, everything is lost.
Thank you in advance,
Leandro Fanzone
Leandro Fanzone wrote:
> is executed every time. Is there any way to assure this, I mean, to
> actually write the records and to not rely on the cache to do it?
AFAIR there is an option doing exactly what you want (but i can't
remembet which:().
usage: /usr/bin/postmaster [options]
-B nbufs set number of shared buffers
-D datadir set data directory
-S silent mode (disassociate from tty)
-a system use this authentication system
-b backend use a specific backend server executable
-d [1-5] set debugging level
-i listen on TCP/IP sockets as well as Unix domain socket
-N nprocs set max number of backends (1..1024, default 32)
-n don't reinitialize shared memory after abnormal exit
-o option pass 'option' to each backend servers
-p port specify port for postmaster to listen on
-s send SIGSTOP to all backend servers if one dies
Did you try "-B 1" ?
cu
--
Nabil Sayegh
Leandro Fanzone wrote: > I'm having problems with transactions, lately. I'm checking > power-failure conditions on my psql-based programs, and found that a > simple loop with I found this article at dejanews: "[...] I see about a 50x to 100x performace increase with the inserts. This is because the default PostgreSQL setting is to write any commit through the disk cache directly onto disk. If you don't use 'begin' and 'commit' then every operation is a transaction and therefore written to disk immediately. [...]" cu -- Nabil Sayegh
Yes, I heard something similar, but it's the same problem, I think the transaction log never reaches the disk. I tried -B1 but complains (it must be 16 at least). Thank you for your help, anyway. Any other ideas? Leandro Fanzone. Nabil Sayegh wrote: > Leandro Fanzone wrote: > > > I'm having problems with transactions, lately. I'm checking > > power-failure conditions on my psql-based programs, and found that a > > simple loop with > > I found this article at dejanews: > > "[...] I see about a 50x to 100x performace increase with the inserts. > This is because the default PostgreSQL setting is to write any commit > through the disk cache directly onto disk. If you don't use 'begin' > and 'commit' then every operation is a transaction and therefore written > to disk immediately. [...]" > > cu > -- > Nabil Sayegh
Leandro Fanzone <leandro@hasar.com> writes:
> BEGIN TRANSACTION
> INSERT INTO test VALUES('any #n');
> COMMIT TRANSACTION
> leaves me with no records inserted at all after an abrupt reset on the
> server.
Are you perhaps starting the postmaster with "-o -F" (no fsync)?
If so, don't use that switch. It buys performance at the cost
of not being secure against server system crashes.
regards, tom lane
On Tue, 5 Dec 2000, Leandro Fanzone wrote:
> I'm having problems with transactions, lately. I'm checking
> power-failure conditions on my psql-based programs, and found that a
> simple loop with
>
> BEGIN TRANSACTION
> INSERT INTO test VALUES('any #n');
> COMMIT TRANSACTION
>
> leaves me with no records inserted at all after an abrupt reset on the
> server. The loop is outside the transaction, id est, the BEGIN/COMMIT is
> executed every time. Is there any way to assure this, I mean, to
> actually write the records and to not rely on the cache to do it?
> Because I think it is somewhere floating in the Linux cache and it's
> never wrote physically, and when the power is down, everything is lost.
> Thank you in advance,
Do you accidently pass the "-F" option to the postgres backends? This will
disable fsync (and boost performance). Remove the "-o -F" option to
postmaster from your start-up script and everything should be fine.
You will still experience data loss if you do not use a journalling
filesystem; get the ext3 patches or try reiserfs.
cu,
Helge
--
Hi! I'm a .signature virus! Put me into your .signature and help me spread!
% rm * .o
rm: cannot remove '.o': No such file or directory
In fact I was passing -F, I don't know why, I suppose someone told me that it
would improve performance. I'll test the results without -F.
I want to thank you all for answering quickly.
Leandro Fanzone.
Helge Bahmann wrote:
> On Tue, 5 Dec 2000, Leandro Fanzone wrote:
>
> > I'm having problems with transactions, lately. I'm checking
> > power-failure conditions on my psql-based programs, and found that a
> > simple loop with
> >
> > BEGIN TRANSACTION
> > INSERT INTO test VALUES('any #n');
> > COMMIT TRANSACTION
> >
> > leaves me with no records inserted at all after an abrupt reset on the
> > server. The loop is outside the transaction, id est, the BEGIN/COMMIT is
> > executed every time. Is there any way to assure this, I mean, to
> > actually write the records and to not rely on the cache to do it?
> > Because I think it is somewhere floating in the Linux cache and it's
> > never wrote physically, and when the power is down, everything is lost.
> > Thank you in advance,
>
> Do you accidently pass the "-F" option to the postgres backends? This will
> disable fsync (and boost performance). Remove the "-o -F" option to
> postmaster from your start-up script and everything should be fine.
>
> You will still experience data loss if you do not use a journalling
> filesystem; get the ext3 patches or try reiserfs.
>
> cu,
> Helge
> --
> Hi! I'm a .signature virus! Put me into your .signature and help me spread!
>
> % rm * .o
> rm: cannot remove '.o': No such file or directory
On Tue, Dec 05, 2000 at 04:25:20PM +0100, Helge Bahmann wrote:
> You will still experience data loss if you do not use a journalling
> filesystem; get the ext3 patches or try reiserfs.
I believe this statement is wrong.
fsync should force all data to disk. Period. Regardless of using ext2,
ext3, or reiserfs.
ext3/reiserfs *may* gain some benefit if running with -F, but even then,
I believe both still only journal meta-data, not the data itself, so you
could still have data stuck in cache if you don't use fsync.
mrc
--
Mike Castle Life is like a clock: You can work constantly
dalgoda@ix.netcom.com and be right all the time, or not work at all
www.netcom.com/~dalgoda/ and be right at least twice a day. -- mrc
We are all of us living in the shadow of Manhattan. -- Watchmen
On Tue, 5 Dec 2000, Mike Castle wrote: > On Tue, Dec 05, 2000 at 04:25:20PM +0100, Helge Bahmann wrote: > > You will still experience data loss if you do not use a journalling > > filesystem; get the ext3 patches or try reiserfs. > > I believe this statement is wrong. > > fsync should force all data to disk. Period. Regardless of using ext2, > ext3, or reiserfs. yes, if fsync completes, the data is on disk; but if you hard-reset the system (as indicated in the original mail) while fsync is in progress you will likely end up with a garbled filesystem > > ext3/reiserfs *may* gain some benefit if running with -F, but even then, > I believe both still only journal meta-data, not the data itself, so you > could still have data stuck in cache if you don't use fsync. correct > > mrc > BTW: Has anyone tried using O_SYNC instead of fsync in postgres? It ought to be faster, but I wonder if anyone has some hard data on the difference. Helge -- Hi! I'm a .signature virus! Put me into your .signature and help me spread! % rm * .o rm: cannot remove '.o': No such file or directory
On Tue, Dec 05, 2000 at 06:15:28PM +0100, Helge Bahmann wrote:
> yes, if fsync completes, the data is on disk; but if you hard-reset the
> system (as indicated in the original mail) while fsync is in progress you
> will likely end up with a garbled filesystem
And a journalling fs is not necessarily going to help you with that case
due to hardware issues. Harddrives, especially on commodity level equipment
like PC's, have been known to write garbage as power fluctuates during a
write in this kind of scenario. So you may well journal garbage as well.
Not necessarily a big improvement.
mrc
--
Mike Castle Life is like a clock: You can work constantly
dalgoda@ix.netcom.com and be right all the time, or not work at all
www.netcom.com/~dalgoda/ and be right at least twice a day. -- mrc
We are all of us living in the shadow of Manhattan. -- Watchmen
On Tue, Dec 05, 2000 at 06:15:28PM +0100, Helge Bahmann wrote: > On Tue, 5 Dec 2000, Mike Castle wrote: > > > On Tue, Dec 05, 2000 at 04:25:20PM +0100, Helge Bahmann wrote: > > > You will still experience data loss if you do not use a journalling > > > filesystem; get the ext3 patches or try reiserfs. > > > > I believe this statement is wrong. > > > > fsync should force all data to disk. Period. Regardless of using ext2, > > ext3, or reiserfs. > yes, if fsync completes, the data is on disk; but if you hard-reset the > system (as indicated in the original mail) while fsync is in progress you > will likely end up with a garbled filesystem Look at 'softupdates' as implemented in FreeBSD. In a nutshell, this does all disk writes as 'transactions', which are either committed or rolled back if the system hangs during an update. fsck after a reboot becomes unnecessary (6 powerfails so far, fsck never found anything needing fixing). But all this just protects filesystme integrity, it can't recreate data. Don't think ext3 would help; If you power down a box, expect to shag the disk (without something like softupdates). UPS might be an idea if it's a concern. -- Rasputin Jack of All Trades :: Master of Nuns
On Wed, 6 Dec 2000, Rasputin wrote: > On Tue, Dec 05, 2000 at 06:15:28PM +0100, Helge Bahmann wrote: > > yes, if fsync completes, the data is on disk; but if you hard-reset the > > system (as indicated in the original mail) while fsync is in progress you > > will likely end up with a garbled filesystem > > Look at 'softupdates' as implemented in FreeBSD. > In a nutshell, this does all disk writes as 'transactions', > which are either committed or rolled back if the system > hangs during an update. Nonsense; it does write ordering of requests to ensure that (most!) metadata dependencies are preserved (e.g. you never have an inode pointing into the void because the data was not yet written). Writes are still not atomic. Read www.usenix.org/publications/library/proceedings/usenix99/full_papers/mckusick.pdf for a full description. > > fsck after a reboot becomes unnecessary you may loose blocks if you do not run fsck; some blocks may be marked used, but in fact aren't; there is no way around this with the structure of traditional filesystems (other than journalling) > (6 powerfails so far, fsck never found anything needing fixing). > But all this just protects filesystme integrity, it can't recreate data. > Don't think ext3 would help; If the application can guarantee integrity within it's files, then meta-data journalling will suffice. (Postgres can) > If you power down a box, expect to shag the disk > (without something like softupdates). > UPS might be an idea if it's a concern. Agreed. -- This signature is intentionally left blank.