[PERFORM] Questions about 2 databases.
Hello All, I have a couple of questions about running 2 databases: 1) on a single 7.4.6 postgres instance does each database have it own WAL file or is that shared? Is it the same on 8.0.x? 2) what's the high performance way of moving 200 rows between similar tables on different databases? Does it matter if the databases are on the same or seperate postgres instances? Background: My web app does lots of inserts that aren't read until a session is complete. The plan is to put the heavy insert session onto a ramdisk based pg-db and transfer the relevant data to the master pg-db upon session completion. Currently running 7.4.6. Individual session data is not as critical as the master pg-db so the risk associated with running the session pg-db on a ramdisk is acceptable. All this is to get past the I/O bottleneck, already tweaked the config files, run on multiple RAID-1 spindles, profiled the queries, maxed the CPU/ram. Migrating to 64bit fedora soon. Thanks, this mailing list has been invaluable. Jelle ---(end of broadcast)--- TIP 8: explain analyze is your friend
[PERFORM] iostat question
Hello All, I have an iostat question in that one of the raid arrays seems to act differently than the other 3. Is this reasonable behavior for the database or should I suspect a hardware or configuration problem? But first some background: Postgresql 7.4.2 Linux 2.4.20, 2GB RAM, 1-Xeon 2.4ghz with HT turned off 3Ware SATA RAID controller with 8 identical drives configured as 4 RAID-1 spindles 64MB RAM disk postgresql.conf differences to postgresql.conf.sample: tcpip_socket = true max_connections = 128 shared_buffers = 2048 vacuum_mem = 16384 max_fsm_pages = 5 wal_buffers = 128 checkpoint_segments = 64 effective_cache_size = 196000 random_page_cost = 1 default_statistics_target = 100 stats_command_string = true stats_block_level = true stats_row_level = true The database is spread over 5 spindles: /ram0 holds the busiest insert/update/delete table and assoc. indexes for temporary session data /sda5 holds the OS and most of the tables and indexes /sdb2 holds the WAL /sdc1 holds the 2nd busiest i/u/d table (70% of the writes) /sdd1 holds the single index for that busy table on/sdc1 Lately we have 45 connections open from a python/psycopg connection pool. 99% of the reads are cached. No swapping. And finally iostat reports: Device:rrqm/s wrqm/s r/s w/s rsec/s wsec/srkB/swkB/s avgrq-sz avgqu-sz await svctm %util /dev/sda50.01 3.32 0.01 0.680.16 32.96 0.0816.48 48.61 0.09 12.16 2.01 0.14 /dev/sdb20.00 6.38 0.00 3.540.01 79.36 0.0039.68 22.39 0.123.52 1.02 0.36 /dev/sdc10.03 0.13 0.00 0.080.271.69 0.13 0.84 24.06 0.13 163.28 13.75 0.11 /dev/sdd10.01 8.67 0.00 0.770.06 82.35 0.0341.18 107.54 0.09 10.51 2.76 0.21 The /sdc1's await seems awfully long compared to the rest to the stats. Jelle -- http://www.sv650.org/audiovisual/loading_a_bike.mpeg Osama-in-October office pool. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PERFORM] Context Switching issue: Spinlock doesn't fix.
Hello, Is context switching problem resolved in 8.0? Can I drop in another Xeon? Thanks, Jelle On Wed, 2 Jun 2004, Josh Berkus wrote: Folks, I've been testing varying SPINS_PER_DELAY in a client's installation of PostgreSQL against a copy of a production database, to test varying this statistic as a way of fixing the issue. It does not seem to work. I've tested all of the following graduated levels: 100 (the original) 250 500 1000 2000 5000 1 2 3 5 None of these quantities seem to make any difference at all in the number of context switches -- neither down nor up. Seems to me like this is a dead end. Does anyone have test results that show otherwise? -- http://www.jibjab.com ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [PERFORM] Wierd context-switching issue on Xeon
Same problem with dual 1Ghz P3's running Postgres 7.4.2, linux 2.4.x, and 2GB ram, under load, with long transactions (i.e. 1 cannot serialize rollback per minute). 200K was the worst observed with vmstat. Finally moved DB to a single xeon box. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
