[PERFORM] Effects of IDLE processes
Hi ALL, I was wondering if there is a DB performance reduction if there are a lot of IDLE processes. 30786 ?S 0:00 postgres: user1 gmadb 10.10.10.1 idle 32504 ?S 0:00 postgres: user1 gmadb 10.10.10.1 idle 32596 ?S 0:00 postgres: user1 gmadb 10.10.10.1 idle 1722 ?S 0:00 postgres: user1 gmadb 10.10.10.1 idle 1724 ?S 0:00 postgres: user1 gmadb 10.10.10.1 idle 3881 ?S 0:00 postgres: user1 gmadb 10.10.10.1 idle 6332 ?S 0:00 postgres: user1 gmadb 10.10.10.1 idle 6678 ?S 0:00 postgres: user1 gmadb 10.10.10.1 idle 6700 ?S 0:00 postgres: user1 gmadb 10.10.10.1 idle 6768 ?S 0:00 postgres: user1 gmadb 10.10.10.1 idle 8544 ?S 0:00 postgres: user1 gmadb 10.10.10.1 idle 8873 ?S 0:00 postgres: user1 gmadb 10.10.10.1 idle 8986 ?S 0:00 postgres: user1 gmadb 10.10.10.1 idle 9000 ?S 0:00 postgres: user1 gmadb 10.10.10.1 idle 9010 ?S 0:00 postgres: user1 gmadb 10.10.10.1 idle 9013 ?S 0:00 postgres: user1 gmadb 10.10.10.1 idle 9016 ?S 0:00 postgres: user1 gmadb 10.10.10.1 idle 9019 ?S 0:00 postgres: user1 gmadb 10.10.10.1 idle 9020 ?S 0:00 postgres: user1 gmadb 10.10.10.1 idle TIA, ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[PERFORM] Degradation of postgres 7.4.5 on FreeBSD/CygWin
Hi All I'm really desparate about this. The problem has occurried in both of my customers first with cygwin and now with FreeBSD 5.3. After 2 months, postgres start get down the performance, and simple queries that should run in 100ms now tooks about 15 secs. Another behaviour, the data is growing to much, with no reason, just like the comparision. So, to solve problem, for the 5th time, a made a backup, dropped the entire database, recreate e reimported. One friend of mine tell me about same problem in linux and he go back to 7.3.x, and with me 5 times. The old data have this sizes: $ du -ks * | sort -nr 1379872 base 131202 pg_xlog 390 global 336 serverlog 74 pg_clog 8 postgresql.conf 4 pg_hba.conf 2 postmaster.opts 2 pg_ident.conf 2 PG_VERSION The Reimported database has this sizes: $ du -ks * | sort -nr 916496 base 131202 pg_xlog 134 global 14 serverlog 10 pg_clog 8 postgresql.conf 4 pg_hba.conf 2 postmaster.pid 2 postmaster.opts 2 pg_ident.conf 2 PG_VERSION This Procedure took 100 ms, but before re-import it took about 15secs, in a process that have a 1000 itens its took about 4 hours to finish, and after re-import 5 minutes. The bottleneck is this recursion procedure, that is a part os others procedure, but it have a simple query. CREATE OR REPLACE FUNCTION Produt_Repos(numeric, double precision, integer, integer) RETURNS double precision AS ' DECLARE xcodpro ALIAS FOR $1; xPesfor ALIAS FOR $2; xAnoALIAS FOR $3; xMesALIAS FOR $4; oMatpro RECORD; xPreRep DOUBLE PRECISION; nPreRep DOUBLE PRECISION; xQtdKgs DOUBLE PRECISION; xPreCus DOUBLE PRECISION; BEGIN xPreRep := 0; IF xPesFor <> 0 THEN FOR oMatpro IN SELECT a.qtdpro, a.codmat, b.pesfor FROM matpro a, produt b WHERE a.codpro = xCodpro AND b.codpro = a.codmat LOOP xQtdKgs := oMatpro.QtdPro / xPesFor; nPreRep := Produt_Repos( oMatpro.codmat, coalesce(oMatpro.pesfor, 0.0), xAno, xMes); xPrerep := xPrerep + (nPreRep * xQtdKgs); IF nPreRep = 0 THEN SELECT coalesce(PreCus, 0.0) INTO xPreCus FROM produt_fecha WHERE codpro = oMatPro.codmat and ano = xAno and mes = xMes LIMIT 1; xPreRep := xPrerep + ( xPrecus * xQtdKgs ); END IF; END LOOP; END IF; RETURN xPreRep; END; ' LANGUAGE 'plpgsql'; This are my configs: msginfo: msgmax: 16384 (max characters in a message) msgmni: 40 (# of message queues) msgmnb: 2048 (max characters in a message queue) msgtql: 40 (max # of messages in system) msgssz: 8 (size of a message segment) msgseg: 2048 (# of message segments in system) shminfo: shmmax: 16384 (max shared memory segment size) shmmin: 1 (min shared memory segment size) shmmni:4000 (max number of shared memory identifiers) shmseg: 128 (max shared memory segments per process) shmall: 4 (max amount of shared memory in pages) seminfo: semmap: 30 (# of entries in semaphore map) semmni: 40961 (# of semaphore identifiers) semmns: 16380 (# of semaphores in system) semmnu: 30 (# of undo structures in system) semmsl: 16380 (max # of semaphores per id) semopm:100 (max # of operations per semop call) semume: 10 (max # of undo entries per process) semusz: 92 (size in bytes of undo structure) semvmx: 32767 (semaphore maximum value) semaem: 16384 (adjust on exit max value) max_connections = 30 shared_buffers = 8192 # min 16, at least max_connections*2, 8KB each sort_mem = 32768# min 64, size in KB vacuum_mem = 32768 # min 1024, size in KB max_fsm_pages = 4 # min max_fsm_relations*16, 6 bytes each max_fsm_relations = 2000# min 100, ~50 bytes each These are my crontab activities: $ crontab -l 00 13 * * 1-5 /bin/sh /home/postgres/backup.sh >/dev/null 2>&1 00 19 * * 1-5 /bin/sh /home/postgres/backup.sh >/dev/null 2>&1 00 23 * * 1-5 /usr/local/pgsql/bin/psql supre -c "vacuum analyze;" >>/dev/null 2>&1 00 23 * * 6 /usr/local/pgsql/bin/psql supre -c "reindex database supre;" >>/dev/null 2>&1 00 23 * * 7 /usr/local/pgsql/bin/psql supre -c "vacuum full analyze;" >>/dev/null 2>&1 So guys, i'm really desparate about this issue, and i think i'm doing everthing right. Please help me. If i tell to my customer that he is having the same problem that in cygwin version, after spending money to change from windows to freebsd,upgrading server, etc, problably he will kill me. :) Best Regards Rodrigo Moreno ---(end of broadcast)--- TIP 6: Have you searched our list archives?
Re: [PERFORM] Degradation of postgres 7.4.5 on FreeBSD/CygWin
On Fri, Feb 18, 2005 at 11:54:34AM -0300, Rodrigo Moreno wrote: > 00 23 * * 1-5 /usr/local/pgsql/bin/psql supre -c "vacuum analyze;" >>>/dev/null 2>&1 Isn't vacuum once a day a bit too little with heavy activity? You should probably consider autovacuum. > 00 23 * * 6 /usr/local/pgsql/bin/psql supre -c "reindex database supre;" >>>/dev/null 2>&1 REINDEX DATABASE does (AFAIK) only index the indexes on the system tables in the database. /* Steinar */ -- Homepage: http://www.sesse.net/ ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] Effects of IDLE processes
JM <[EMAIL PROTECTED]> writes: > I was wondering if there is a DB performance reduction if there are a > lot of > IDLE processes. There will be some overhead, but I dunno if anyone's ever tried to measure it. regards, tom lane ---(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: [PERFORM] Degradation of postgres 7.4.5 on FreeBSD/CygWin
"Rodrigo Moreno" <[EMAIL PROTECTED]> writes: > After 2 months, postgres start get down the performance, and simple queries > that should run in 100ms now tooks about 15 secs. > Another behaviour, the data is growing to much, with no reason, just like > the comparision. Are you vacuuming on a regular basis? Do you have the FSM settings high enough to cover the database? 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: [PERFORM] Degradation of postgres 7.4.5 on FreeBSD/CygWin
On Fri, Feb 18, 2005 at 09:32:25AM -0500, Tom Lane wrote: > Are you vacuuming on a regular basis? Do you have the FSM settings high > enough to cover the database? He posted his cron settings ;-) /* Steinar */ -- Homepage: http://www.sesse.net/ ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
RES: [PERFORM] Degradation of postgres 7.4.5 on FreeBSD/CygWin
Hi, this is only max 15 concurrent conections. And is not a heavy performance database, so i think this is not necessary vacumm more than once a day. In another customer, has only 5 users and the database have 300mb, small database, and has the same behaviour (haven't modified postgresql). My first instalation was not changed anything in postgresql.conf, but in this new server (FreeBSD) i have changed some parameters. as showed in my crontab list, i think this is enough: 00 13 * * 1-5 /bin/sh /home/postgres/backup.sh >/dev/null 2>&1 00 19 * * 1-5 /bin/sh /home/postgres/backup.sh >/dev/null 2>&1 00 23 * * 1-5 /usr/local/pgsql/bin/psql supre -c "vacuum analyze;" >>/dev/null 2>&1 00 23 * * 6 /usr/local/pgsql/bin/psql supre -c "reindex database supre;" >>/dev/null 2>&1 00 23 * * 7 /usr/local/pgsql/bin/psql supre -c "vacuum full analyze;" >>/dev/null 2>&1 These my changed configs in postgresql.conf: max_connections = 30 shared_buffers = 8192 sort_mem = 32768 vacuum_mem = 32768 max_fsm_pages = 4 max_fsm_relations = 2000 But why after 2 months the database has 1.3gb and after reimport on 900mb ? Both customer are smaller databases, but one of them, has 8 years os data, it's the reason of size 900mb, these are too smaller database. Regards Rodrigo Moreno ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: RES: [PERFORM] Degradation of postgres 7.4.5 on FreeBSD/CygWin
this is only max 15 concurrent conections. And is not a heavy performance database, so i think this is not necessary vacumm more than once a day. In another customer, has only 5 users and the database have 300mb, small database, and has the same behaviour (haven't modified postgresql). My first instalation was not changed anything in postgresql.conf, but in this new server (FreeBSD) i have changed some parameters. as showed in my crontab list, i think this is enough: 00 13 * * 1-5 /bin/sh /home/postgres/backup.sh >/dev/null 2>&1 00 19 * * 1-5 /bin/sh /home/postgres/backup.sh >/dev/null 2>&1 00 23 * * 1-5 /usr/local/pgsql/bin/psql supre -c "vacuum analyze;" We just told you - it's nowhere near enough. Vacuum once an hour. Size of the database is not that relevant, its size of changes that is. Chris ---(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: RES: [PERFORM] Degradation of postgres 7.4.5 on FreeBSD/CygWin
00 23 * * 1-5 /usr/local/pgsql/bin/psql supre -c "vacuum analyze;" Also, this is bad - you are not vacuuming all your databases, which will cause you data loss one day with transaction wraparound. Use the vacuumdb utility that comes with PostgreSQL instead. Chris ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: RES: [PERFORM] Degradation of postgres 7.4.5 on FreeBSD/CygWin
"Rodrigo Moreno" <[EMAIL PROTECTED]> writes: > max_fsm_pages = 4 > max_fsm_relations = 2000 > But why after 2 months the database has 1.3gb and after reimport on 900mb ? 40k pages = 320M bytes = 1/3rd of your database. Perhaps you need a larger setting for max_fsm_pages. However, 30% bloat of the database doesn't particularly bother me, especially when you are using infrequent vacuums. Bear in mind that, for example, the steady-state fill factor of a b-tree index is usually estimated at less than 70%. A certain amount of wasted space is not only intended, but essential for reasonable performance. What you need is to take a more detailed look at the behavior of that function that's getting so slow. Are the query plans changing? Is the loop iterating over many more rows than before? You haven't told us anything that would account for 100x slowdown. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
RES: RES: [PERFORM] Degradation of postgres 7.4.5 on FreeBSD/CygWin
Thanks to all, at this moment, can't stop the database and put back the old database, but at night i will take more analyzes on old database and reimported and i put here the results. Thanks a lot Rodrigo -Mensagem original- De: Tom Lane [mailto:[EMAIL PROTECTED] Enviada em: sexta-feira, 18 de fevereiro de 2005 12:00 Para: Rodrigo Moreno Cc: pgsql-performance@postgresql.org Assunto: Re: RES: [PERFORM] Degradation of postgres 7.4.5 on FreeBSD/CygWin "Rodrigo Moreno" <[EMAIL PROTECTED]> writes: > max_fsm_pages = 4 > max_fsm_relations = 2000 > But why after 2 months the database has 1.3gb and after reimport on 900mb ? 40k pages = 320M bytes = 1/3rd of your database. Perhaps you need a larger setting for max_fsm_pages. However, 30% bloat of the database doesn't particularly bother me, especially when you are using infrequent vacuums. Bear in mind that, for example, the steady-state fill factor of a b-tree index is usually estimated at less than 70%. A certain amount of wasted space is not only intended, but essential for reasonable performance. What you need is to take a more detailed look at the behavior of that function that's getting so slow. Are the query plans changing? Is the loop iterating over many more rows than before? You haven't told us anything that would account for 100x slowdown. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PERFORM] [HACKERS] win32 performance - fsync question
Magnus prepared a trivial patch which added the O_SYNC flag for windows and mapped it to FILE_FLAG_WRITE_THROUGH in win32_open.c. We pg_benched it and here are the results of our test on my WinXP workstation on a 10k raptor: Settings were pgbench -t 100 -c 10. fsync = off: ~ 280 tps fsync on, WAL=fsync: ~ 35 tps fsync on, WAL=open_sync write cache policy on: ~ 240 tps fsync on, WAL=open_sync write cache policy off: ~ 80 tps 80 tps, btw, is about the results I'd expect from linux on this hardware. Also, the open_sync method plays much nicer with RAID devices, but it would need some more rigorous testing before I'd personally certify it as safe. As an aside, it doesn't look like the open_sync can be trusted with write caching policy on the disk (the default), and that's worth noting. Merlin ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] PostgreSQL clustering VS MySQL clustering
Josh Berkus wrote: > Tatsuo, > > >>Yes. However it would be pretty easy to modify pgpool so that it could >>cope with Slony-I. I.e. >> >>1) pgpool does the load balance and sends query to Slony-I's slave and >> master if the query is SELECT. >> >>2) pgpool sends query only to the master if the query is other than >> SELECT. Don't you think that this is unsafe ? SELECT foo(id), id FROM bar; where foo have side effect. Is pgpool able to detect it and perform this select on the master ? Regards Gaetano Mendola ---(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: [PERFORM] PostgreSQL clustering VS MySQL clustering
Jim C. Nasby wrote: > On Thu, Jan 20, 2005 at 10:08:47AM -0500, Stephen Frost wrote: > >>* Christopher Kings-Lynne ([EMAIL PROTECTED]) wrote: >> >>>PostgreSQL has replication, but not partitioning (which is what you want). >> >>It doesn't have multi-server partitioning.. It's got partitioning >>within a single server (doesn't it? I thought it did, I know it was >>discussed w/ the guy from Cox Communications and I thought he was using >>it :). > > > No, PostgreSQL doesn't support any kind of partitioning, unless you > write it yourself. I think there's some work being done in this area, > though. Seen my last attempts to perform an horizontal partition I have to say that postgres do not support it even if you try to write it yourself (see my post "horizontal partion" ). Regards Gaetano Mendola ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] seq scan cache vs. index cache smackdown
Magnus Hagander wrote: > I don't think that's correct either. Scatter/Gather I/O is used to SQL > Server can issue reads for several blocks from disks into it's own > buffer cache with a single syscall even if these buffers are not > sequential. It did make significant performance improvements when they > added it, though. > > (For those not knowing - it's ReadFile/WriteFile where you pass an array > of "this many bytes to this address" as parameters) Isn't that like the BSD writev()/readv() that Linux supports also? Is that something we should be using on Unix if it is supported by the OS? -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(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
