Re: [HACKERS] stuck spin lock with many concurrent users
Tatsuo Ishii [EMAIL PROTECTED] writes How can I check it? The 'stuck' message should at least give you a code location... FATAL: s_lock(0x2ac2d016) at spin.c:158, stuck spinlock. Aborting. Hmm, that's SpinAcquire, so it's one of the predefined spinlocks (and not, say, a buffer spinlock). You could try adding some debug logging here, although the output would be voluminous. But what would really be useful is a stack trace for the stuck process. Consider changing the s_lock code to abort() when it gets a stuck spinlock --- then you could gdb the coredump. Nice idea. I will try that. It appeared that the deadlock checking timer seems to be the source of the problem. With the default settings, it checks deadlocks every 1 second PER backend. So if there are 1000 backends, every 1 msec there's a signal and a shared memory locking in average. That would be too much. If increase the dealock_timeout to , say 10, the problem seems gone. Also the performance increased SIGNIFICANTLY. Before that I got only 1-2 TPS, but now I get ~20 TPS using pgbench -c 1000. Here is the backtrace: #0 0x2ab56d21 in __kill () from /lib/libc.so.6 #1 0x2ab56996 in raise (sig=6) at ../sysdeps/posix/raise.c:27 #2 0x2ab580b8 in abort () at ../sysdeps/generic/abort.c:88 #3 0x80ece1a in s_lock_stuck (lock=0x2ac2d016 \001, file=0x816e7bc spin.c, line=158) at s_lock.c:70 #4 0x80ecf3e in s_lock_sleep (spins=20001, timeout=1, microsec=5000, lock=0x2ac2d016 \001, file=0x816e7bc spin.c, line=158) at s_lock.c:109 #5 0x80ecfa3 in s_lock (lock=0x2ac2d016 \001, file=0x816e7bc spin.c, line=158) at s_lock.c:136 #6 0x80efb4d in SpinAcquire (lockid=6) at spin.c:158 #7 0x80f2305 in HandleDeadLock (postgres_signal_arg=14) at proc.c:819 #8 signal handler called #9 0x2abeb134 in semop (semid=32786, sops=0x7fffeebc, nsops=1) at ../sysdeps/unix/sysv/linux/semop.c:34 #10 0x80ee460 in IpcSemaphoreLock (semId=32786, sem=13, interruptOK=1 '\001') at ipc.c:426 #11 0x80f217f in ProcSleep (lockMethodTable=0x81c1708, lockmode=6, lock=0x2ce0ab18, holder=0x2ce339b0) at proc.c:666 #12 0x80f14ff in WaitOnLock (lockmethod=1, lockmode=6, lock=0x2ce0ab18, holder=0x2ce339b0) at lock.c:955 #13 0x80f1298 in LockAcquire (lockmethod=1, locktag=0x7fffeffc, xid=130139, lockmode=6) at lock.c:739 #14 0x80f0a23 in LockPage (relation=0x2dbeb9d0, blkno=0, lockmode=6) #15 0x8071ceb in RelationGetBufferForTuple (relation=0x2dbeb9d0, len=132) at hio.c:97 #16 0x8070293 in heap_update (relation=0x2dbeb9d0, otid=0x7114, newtup=0x82388c8, ctid=0x70b0) at heapam.c:1737 #17 0x80b6825 in ExecReplace (slot=0x823af60, tupleid=0x7114, estate=0x8238a58) at execMain.c:1450 #18 0x80b651e in ExecutePlan (estate=0x8238a58, plan=0x8238d00, operation=CMD_UPDATE, numberTuples=0, direction=ForwardScanDirection, destfunc=0x823b680) at execMain.c:1125 #19 0x80b5af3 in ExecutorRun (queryDesc=0x8239080, estate=0x8238a58, feature=3, count=0) at execMain.c:233 #20 0x80f6d93 in ProcessQuery (parsetree=0x822bc18, plan=0x8238d00, dest=Remote) at pquery.c:295 #21 0x80f599b in pg_exec_query_string ( query_string=0x822b8c0 update accounts set abalance = abalance + 277 where aid = 41148\n, dest=Remote, parse_context=0x81fc850) at postgres.c:810 #22 0x80f68c6 in PostgresMain (argc=4, argv=0x7380, real_argc=3, real_argv=0x7c94, username=0x81cd981 t-ishii) at postgres.c:1908 #23 0x80e1ee3 in DoBackend (port=0x81cd718) at postmaster.c:2120 #24 0x80e1acc in BackendStartup (port=0x81cd718) at postmaster.c:1903 #25 0x80e0e26 in ServerLoop () at postmaster.c:995 #26 0x80e0853 in PostmasterMain (argc=3, argv=0x7c94) at postmaster.c:685 #27 0x80c4865 in main (argc=3, argv=0x7c94) at main.c:175 #28 0x2ab509cb in __libc_start_main (main=0x80c4750 main, argc=3, argv=0x7c94, init=0x80656c4 _init, fini=0x81395ac _fini, rtld_fini=0x2aab5ea0 _dl_fini, stack_end=0x7c8c) at ../sysdeps/generic/libc-start.c:92 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [HACKERS] stuck spin lock with many concurrent users
Tatsuo Ishii wrote: Tatsuo Ishii [EMAIL PROTECTED] writes How can I check it? The 'stuck' message should at least give you a code location... FATAL: s_lock(0x2ac2d016) at spin.c:158, stuck spinlock. Aborting. Hmm, that's SpinAcquire, so it's one of the predefined spinlocks (and not, say, a buffer spinlock). You could try adding some debug logging here, although the output would be voluminous. But what would really be useful is a stack trace for the stuck process. Consider changing the s_lock code to abort() when it gets a stuck spinlock --- then you could gdb the coredump. Nice idea. I will try that. It appeared that the deadlock checking timer seems to be the source of the problem. With the default settings, it checks deadlocks every 1 second PER backend. IIRC deadlock check was called only once per backend. In my understanding the deadlock check is performed every time the backend aquires lock. Once the it aquires, it kill the timer. However, under heavy transactions such as pgbench generates, chances are that the checking fires, and it tries to aquire a spin lock. That seems the situation. -- Tatsuo Ishii ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] stuck spin lock with many concurrent users
Tatsuo Ishii [EMAIL PROTECTED] writes: It appeared that the deadlock checking timer seems to be the source of the problem. With the default settings, it checks deadlocks every 1 second PER backend. I don't believe it. setitimer with it_interval = 0 should produce one interrupt, no more. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [HACKERS] stuck spin lock with many concurrent users
Tatsuo Ishii [EMAIL PROTECTED] writes: In my understanding the deadlock check is performed every time the backend aquires lock. Once the it aquires, it kill the timer. However, under heavy transactions such as pgbench generates, chances are that the checking fires, and it tries to aquire a spin lock. That seems the situation. It could be that with ~1000 backends all waiting for the same lock, the deadlock-checking code just plain takes too long to run. It might have an O(N^2) or worse behavior in the length of the queue; I don't think the code was ever analyzed for such problems. Do you want to try adding some instrumentation to HandleDeadlock to see how long it runs on each call? regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Re: Encrypting pg_shadow passwords
Jim and Bruce wrote: [ a lot of stuff ] What this discussion seems to come down to is whether we should take a backward step in one area of security (security against wire-sniffing) to take a forward step in another (not storing plaintext passwords). It seems largely a matter of local conditions which hazard you consider greater (though I would note that anyone who is able to examine the contents of pg_shadow has *already* broken into your database). Anyway, I doubt anyone will convince anyone else to change sides on that point. My take on the matter is that we shouldn't invest any more effort in crypt-based solutions (here crypt means specifically crypt(3), it's not a generic term). The future is double encryption using MD5 --- or s/MD5/more-modern-hash-algorithm-of-your-choice/, the exact choice is irrelevant to my point. We ought to get off our duffs and implement that, then encourage people to migrate their clients ASAP. The crypt code will be supported for awhile longer, but strictly as a backwards-compatibility measure for old clients. There's no reason to spend any additional work on it. For the same reason I don't see any value in the idea of adding crypt-based double encryption to clients. We don't really want to support that over the long run, so why put effort into it? regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] Re: Encrypting pg_shadow passwords
Bruce Momjian [EMAIL PROTECTED] writes: The only reason to add double-crypt is so we can continue to use /etc/passwd entries on systems that use crypt() in /etc/passwd. In the long run, though, we want to drop crypt(3) usage entirely. It's just too much of a pain in the neck to depend on the C library's crypt(), for two reasons: 1. It's not in libc on all systems, leading to constant problems when linking clients, particularly with shared libraries that have to have a dependency on another shared library because of this. (Search the archives for problems about can't find crypt. There are many such reports.) 2. crypt() isn't guaranteed compatible across platforms, meaning that your clients may be unable to log in anyway. See for example http://fts.postgresql.org/db/mw/msg.html?mid=57516 Using our own MD5 (or whatever) code will avoid these problems. regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] Re: Encrypting pg_shadow passwords
Bruce Momjian [EMAIL PROTECTED] writes: For the same reason I don't see any value in the idea of adding crypt-based double encryption to clients. We don't really want to support that over the long run, so why put effort into it? The only reason to add double-crypt is so we can continue to use /etc/passwd entries on systems that use crypt() in /etc/passwd. Haven't many systems (at least Linux and FreeBSD) switched from this to other algorithms as default, like MD5? (and usually found in /etc/shadow) Yes, most BSD's are MD5. I wasn't sure about Linux. Most recent (3-4 years and newer) use PAM, which can use MD5 as an underlying module. -- Trond Eivind Glomsrød Red Hat, Inc. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Re: Encrypting pg_shadow passwords
Bruce Momjian [EMAIL PROTECTED] writes: Bruce Momjian [EMAIL PROTECTED] writes: For the same reason I don't see any value in the idea of adding crypt-based double encryption to clients. We don't really want to support that over the long run, so why put effort into it? The only reason to add double-crypt is so we can continue to use /etc/passwd entries on systems that use crypt() in /etc/passwd. Haven't many systems (at least Linux and FreeBSD) switched from this to other algorithms as default, like MD5? (and usually found in /etc/shadow) Yes, most BSD's are MD5. I wasn't sure about Linux. Most recent (3-4 years and newer) use PAM, which can use MD5 as an underlying module. But what is the default? crypt or md5? Varies. In Red Hat Linux, it's been user configurable during install for a couple of years now - it's been default to on for most of that time, AFAIR. -- Trond Eivind Glomsrød Red Hat, Inc. ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Re: Encrypting pg_shadow passwords
Bruce Momjian [EMAIL PROTECTED] writes: My take on the matter is that we shouldn't invest any more effort in crypt-based solutions (here crypt means specifically crypt(3), it's not a generic term). The future is double encryption using MD5 --- or s/MD5/more-modern-hash-algorithm-of-your-choice/, the exact choice is irrelevant to my point. We ought to get off our duffs and implement that, then encourage people to migrate their clients ASAP. The crypt code will be supported for awhile longer, but strictly as a backwards-compatibility measure for old clients. There's no reason to spend any additional work on it. For the same reason I don't see any value in the idea of adding crypt-based double encryption to clients. We don't really want to support that over the long run, so why put effort into it? The only reason to add double-crypt is so we can continue to use /etc/passwd entries on systems that use crypt() in /etc/passwd. Haven't many systems (at least Linux and FreeBSD) switched from this to other algorithms as default, like MD5? (and usually found in /etc/shadow) -- Trond Eivind Glomsrød Red Hat, Inc. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[HACKERS] Benchmarking
I need to check the scalability of a machine with postgresql and Im doing it with pgbench but Im getting values with a variation of a 40% with the same pgbench call... Just the same variation if I restart posgresql or overwrite the db... So just wondering if theres another benchmarking tool for postgres... Perhaps should I write my own one? ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [HACKERS] functions returning records
On Tue, 26 Jun 2001 17:11:47 -0400 (EDT), you wrote: I started thinking about Tom's idea to implement functions as table source. To me, it seems that a very few changes are necessary: a) parser must be changed to allow functioncall to be a table_ref (easy) b) when a Query node is generated out of such a call select * from foo() it should be almost identical to one generated out of select * from (select * from foo) with one distinction: list of query attributes should be completed based on return type of foo(). c) executor should support execution of such Query node, properly extracting things out of function's return value and placing them into result attributes. Coming from a Sybase environment I would love to have functions return a result set. A few things to think of: 1: will it be possible to return multiple result sets? (in Sybase any select statement that is not redirected to variables or a table goes to the client, so it is quite common to do multiple selects). Does the postgresql client library support this? 2: will it be possible to put a single result set in a table. Something like resultfunction (argument) INTO TABLENAME or INSERT INTO TABLENAME resultfunction(argument) -- __ Nothing is as subjective as reality Reinoud van Leeuwen [EMAIL PROTECTED] http://www.xs4all.nl/~reinoud __ ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Benchmarking
I need to check the scalability of a machine with postgresql and Im doing it with pgbench but Im getting values with a variation of a 40% with the same pgbench call... You might be looking at the effect of the kernel buffer cache. Try run pgbench several times with same settings. Another point is how many transactions pgbench runs (-t option). More transactions would give more statble results. Here is my small script to run pgbench. I usually run it 2 or 3 times and take only the last run result. #! /bin/sh pgbench -i -s 2 test for i in 1 2 4 8 16 32 64 128 do t=`expr 640 / $i` pgbench -t $t -c $i test echo = sync == sync;sync;sync;sleep 10 echo = sync done == done ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
