Thread: Huge number of disk writes after migration to 8.1
Hi, 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. So, any clues what's going on? The hardware is Dual Opteron 275, 8 GBs RAM, two 73 GB 15K SCSI 320 with software RAID1. The OS is 64bit Debian Sarge with 8.1.2 build from sources in exactly the same way as 8.0.3 was built before. My postgresql.conf: shared_buffers=30000 max_prepared_transactions=0 #(line added after migration 8.1) work_mem=256000 maintenance_work_mem=512000 max_fsm_pages=1800000 max_fsm_relations=80000 fsync_on checkpoints_segments=12 effective_cache_size=800000 log_destination='syslog' client_min_messages=warning log_min_messages=notice log_min_duration_statement=200 stats_start_collector = on stats_command_string = on The system is quite busy but the numer of queries hasn't changed after migration. The cluster contains about 100 databases (some of them with 2000 tables) with total size of 20GBs. During peak hours it hits 160 SELECTS and about 18 modifying queries per second. The DBs are fully vacuumed every day, and vacuum analyze is performed on critical tables every 30 minutes. -- Marcin
"Marcin" <migor@op.pl> 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
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 20000 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
Marcin <migor@op.pl> 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
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
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 10000 -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
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 10000 -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
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