Re: [GENERAL] Huge number of disk writes after migration to 8.1
Bruce Momjian wrote: > Do you mean stats_command_string? I have seen degradation in using that Yes, I meant stats_command_string, sorry for my stupid mistake. > and am working on a patch for 8.2 to fix it. That's nice, because it's very helpful. Thanks for response, -- Marcin ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Huge number of disk writes after migration to 8.1
Do you mean stats_command_string? I have seen degradation in using that and am working on a patch for 8.2 to fix it. --- Marcin wrote: > Qingqing Zhou wrote: > > A similar problem was reported before: > > > > http://archives.postgresql.org/pgsql-admin/2005-12/msg00266.php > > > > But we conclude that's not related to pgstats. See if that's related to > > your situation. > > Unfortunately, I don't think so. The 8.0.3 run just fine. And I don't > see any pgsql_tmp files. The problems started after migration to 8.1.2. > I posted to pgsql-bugs, following Tom suggestion. > BTW, I made some benchmarks on test machine (which is 32bit 2xPIII > 1.4GHz) with pgbench and I was quite amazed: > pgbench -S -c 50 -t 1 -v pgbench > with stats collector disabled resulted in: > tps = 3178.346439 (including connections establishing) > tps = 3183.360731 (excluding connections establishing) > > with stats collector enabled, but stats_command_prompt disabled: > tps = 3143.376908 (including connections establishing) > tps = 3147.564695 (excluding connections establishing) > > and with stats_command_prompt enabled: > tps = 2446.136610 (including connections establishing) > tps = 2448.785260 (excluding connections establishing) > > However, I didn't notice any suspicious write activity. > > Thanks for suggestion, > -- > Marcin > > ---(end of broadcast)--- > TIP 5: don't forget to increase your free space map settings > -- 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 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Huge number of disk writes after migration to 8.1
Qingqing Zhou wrote: > A similar problem was reported before: > > http://archives.postgresql.org/pgsql-admin/2005-12/msg00266.php > > But we conclude that's not related to pgstats. See if that's related to > your situation. Unfortunately, I don't think so. The 8.0.3 run just fine. And I don't see any pgsql_tmp files. The problems started after migration to 8.1.2. I posted to pgsql-bugs, following Tom suggestion. BTW, I made some benchmarks on test machine (which is 32bit 2xPIII 1.4GHz) with pgbench and I was quite amazed: pgbench -S -c 50 -t 1 -v pgbench with stats collector disabled resulted in: tps = 3178.346439 (including connections establishing) tps = 3183.360731 (excluding connections establishing) with stats collector enabled, but stats_command_prompt disabled: tps = 3143.376908 (including connections establishing) tps = 3147.564695 (excluding connections establishing) and with stats_command_prompt enabled: tps = 2446.136610 (including connections establishing) tps = 2448.785260 (excluding connections establishing) However, I didn't notice any suspicious write activity. Thanks for suggestion, -- Marcin ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Huge number of disk writes after migration to 8.1
On Tue, 17 Jan 2006, Marcin wrote: > > Playing with postgresql.conf I changed "stats_command_string" to off, > reloaded config, and restarted connection from applications (the > postgresql > wasn't restarted). The write rate immediately drops down, and is now > at 8000-9000 blocks per second (which is still 4-5 times more than in > 8.0.3). > A similar problem was reported before: http://archives.postgresql.org/pgsql-admin/2005-12/msg00266.php But we conclude that's not related to pgstats. See if that's related to your situation. Regards, Qingqing ---(end of broadcast)--- TIP 1: 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: [GENERAL] Huge number of disk writes after migration to 8.1
Marcin <[EMAIL PROTECTED]> writes: > It seems that some changes to stats collector introduced in 8.1 are > now eating my CPU power, and probably also the (I)/O bandwidth. :( Yeah, something wrong there :-(. What did you say your platform was exactly? Would you strace the collector process, and send maybe ten K or so of trace output to pgsql-bugs? It's probably not appropriate for pgsql-general. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Huge number of disk writes after migration to 8.1
On Mon, Jan 16, 2006 at 05:09:18PM -0500, Qingqing Zhou wrote: > AFAICS the difference maybe related to this between 8.0 and 8.1 is the > introduction of autovacuum. But seems you didn't turn it on. Do you observed > only writes increased or both read/write? If the latter, that might be an > unoptimized query plan invloves seqscan ... Can you find out what's the > writes on? Autovacuum is turned off. The reads remains on very, very low level. According to monitored values from /sys/block/md?/stat (confirmed by iostat output) the reads rate stays at 70-80 blocks per second, while the writes rate keeps at 2 blocks. However, I found something interesting. Playing with postgresql.conf I changed "stats_command_string" to off, reloaded config, and restarted connection from applications (the postgresql wasn't restarted). The write rate immediately drops down, and is now at 8000-9000 blocks per second (which is still 4-5 times more than in 8.0.3). Looking at the CPU usage in top, I found strange stats for postgres processes (ps aux dump): 11:25 /usr/lib/postgresql/bin/postmaster 0:25 postgres: writer process 4:21 postgres: stats buffer process 201:46 postgres: stats collector process (PostgreSQL is running for two and a half day now). I found some ps -aux dumps from last week, (with 8.0.3 and stats_command_string enabled) and CPU usage was completely different: 31:07 /usr/lib/postgresql/bin/postmaster 175:10 postgres: writer process 30:55 postgres: stats buffer process 58:43 postgres: stats collector process (PostgreSQL was running for 12 days). It seems that some changes to stats collector introduced in 8.1 are now eating my CPU power, and probably also the (I)/O bandwidth. :( Thanks for help, -- Marcin ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Huge number of disk writes after migration to 8.1
"Marcin" <[EMAIL PROTECTED]> wrote > > I suffer from huge increase in number of disk writes after migration > from 8.0.3 to 8.1.2. The average number of sectors written increased 10 > times and transfer stays at 20MB of writes per second during work hours > (with 20% cpu time wasted in iowait). The number of queries lasting more > than 300ms increased thrice. > AFAICS the difference maybe related to this between 8.0 and 8.1 is the introduction of autovacuum. But seems you didn't turn it on. Do you observed only writes increased or both read/write? If the latter, that might be an unoptimized query plan invloves seqscan ... Can you find out what's the writes on? Regards, Qingqing ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
