[HACKERS] Postgres Optimization: IO and Data Organization
I want to understand how Postgres organizes data and handles IO operations so that I will better know how to optimize a Postgres database server. I am looking for answers to specific questions and pointers to where this stuff is documented. How does Postgres organize its data? For example, is it grouped together on the disk, or is it prone to be spread out over the disk? Does vacuum reorganize the data? (Seeking to minimize disk head movement.) How does Postgres handle sequential IO? Does it treat is specially such as issuing large IO operations that span block boundaries? How does Postgres handle direct IOs (operations directly to disk, bypassing the buffer cache)? Will it issue multiple asynchronous IO operations? Is Postgres always one process per client, or can it spawn additional processes to parallelise some operations such as a nested loops join operation? Is there a recommended file system to use for Postgres data, such as ext2 or another non-journaling FS? ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[HACKERS] pg_restore: [archiver] input file does not appear to be a valid archive
I upgrade from PG 7.1.3 to 7.2, and I am trying to restore my dbs but I keep getting: [nsadmin@roam backup-20020622]$ pg_restore all-good.dmp pg_restore: [archiver] input file does not appear to be a valid archive ---(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] FATAL 2: InitRelink(logfile 0 seg 173) failed: No such
Tom Lane wrote: That really should be impossible --- it says that a rename() failed for a file we just created. I judge from the spelling of the error message that you are running 7.1. 7.1.3 However, given that you state a system reboot is necessary and sufficient to make the problem go away, I am going to stick my neck *way* out and suggest that: 1. You have the $PGDATA directory (or at least its pg_xlog subdirectory) mounted via NFS. 2. This is an NFS problem. I am not running NFS on this system. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] FATAL 2: InitRelink(logfile 0 seg 173) failed: No such
Tom Lane wrote: James Thornton [EMAIL PROTECTED] writes: I am not running NFS on this system. Oh well, scratch that theory. Perhaps you should tell us what you *are* running --- what OS, what hardware? I still believe that this must be a system-level bug and not directly Postgres' fault. [nsadmin@roam proc]$ cat version cpuinfo meminfo pci Linux version 2.4.7-10smp ([EMAIL PROTECTED]) (gcc version 2.96 2731 (Red Hat Linux 7.1 2.96-98)) #1 SMP Thu Sep 6 17:09:31 EDT 2001 processor : 0 vendor_id : GenuineIntel cpu family : 6 model : 7 model name : Pentium III (Katmai) stepping: 3 cpu MHz : 548.324 cache size : 512 KB fdiv_bug: no hlt_bug : no f00f_bug: no coma_bug: no fpu : yes fpu_exception : yes cpuid level : 2 wp : yes flags : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 mmx fxsr sse bogomips: 1094.45 total:used:free: shared: buffers: cached: Mem: 327278592 321400832 5877760 720896 10825728 52867072 Swap: 271392768 13783040 257609728 MemTotal: 319608 kB MemFree: 5740 kB MemShared: 704 kB Buffers: 10572 kB Cached: 39552 kB SwapCached: 12076 kB Active: 21956 kB Inact_dirty: 40668 kB Inact_clean: 280 kB Inact_target: 480 kB HighTotal: 0 kB HighFree:0 kB LowTotal: 319608 kB LowFree: 5740 kB SwapTotal: 265032 kB SwapFree: 251572 kB NrSwapPages: 62893 pages PCI devices found: Bus 0, device 0, function 0: Host bridge: Intel Corporation 440BX/ZX - 82443BX/ZX Host bridge (rev 3). Master Capable. Latency=64. Prefetchable 32 bit memory at 0xf000 [0xf3ff]. Bus 0, device 1, function 0: PCI bridge: Intel Corporation 440BX/ZX - 82443BX/ZX AGP bridge (rev 3). Master Capable. Latency=64. Min Gnt=136. Bus 0, device 7, function 0: ISA bridge: Intel Corporation 82371AB PIIX4 ISA (rev 2). Bus 0, device 7, function 1: IDE interface: Intel Corporation 82371AB PIIX4 IDE (rev 1). Master Capable. Latency=32. I/O at 0x1000 [0x100f]. Bus 0, device 7, function 2: USB Controller: Intel Corporation 82371AB PIIX4 USB (rev 1). IRQ 14. Master Capable. Latency=64. I/O at 0xdce0 [0xdcff]. Bus 0, device 7, function 3: Bridge: Intel Corporation 82371AB PIIX4 ACPI (rev 2). IRQ 9. Bus 0, device 14, function 0: Ethernet controller: Intel Corporation 82557 [Ethernet Pro 100] (rev 4). IRQ 11. Master Capable. Latency=64. Min Gnt=8.Max Lat=56. Prefetchable 32 bit memory at 0xf700 [0xf7000fff]. I/O at 0xdcc0 [0xdcdf]. Non-prefetchable 32 bit memory at 0xff00 [0xff0f]. Bus 0, device 15, function 0: PCI bridge: Digital Equipment Corporation DECchip 21152 (rev 3). Master Capable. Latency=64. Min Gnt=2. Bus 0, device 17, function 0: Ethernet controller: 3Com Corporation 3c905B 100BaseTX [Cyclone] (rev 36). IRQ 14. Master Capable. Latency=64. Min Gnt=10.Max Lat=10. I/O at 0xdc00 [0xdc7f]. Non-prefetchable 32 bit memory at 0xff10 [0xff10007f]. Bus 1, device 0, function 0: VGA compatible controller: ATI Technologies Inc 3D Rage Pro AGP 1X/2X (rev 92). IRQ 9. Master Capable. Latency=64. Min Gnt=8. Non-prefetchable 32 bit memory at 0xfd00 [0xfdff]. I/O at 0xfc00 [0xfcff]. Non-prefetchable 32 bit memory at 0xfcfff000 [0xfcff]. Bus 2, device 9, function 0: Unknown mass storage controller: Promise Technology, Inc. 20262 (rev 1). IRQ 9. Master Capable. Latency=64. I/O at 0xecf8 [0xecff]. I/O at 0xecf0 [0xecf3]. I/O at 0xece0 [0xece7]. I/O at 0xecd8 [0xecdb]. I/O at 0xec80 [0xecbf]. Non-prefetchable 32 bit memory at 0xfafe [0xfaff]. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] Postgres is eating my CPU
Postmaster is eating my CPU -- see ps and top output at http://jamesthornton.com/misc/postgres.txt or below (it wraps too much when posting to Google, but my server keeps getting overloaded). As you can see from the ps output, there are several INSERT statements -- these return after restarting Postgres and even rebooting the system. I checked the system log for that server, and there are only ~30 INSERTS over the last ~12 hours (all INSERTs called by AOLserver into the referer_log). Futhermore, I haven't been running any INSERT statements from psql, and no one else has access to this system. Yesterday, I ran vacuum analyze for the first time in a long time -- could that have caused this situation? System: Postgres 7.0.3, AOLserver 3.4/OpenACS 3.2.5/Postgres driver 2.0, Linux 7.1 P.S. -- Here's Don Baccus' reply from the OpenACS bboard... This is very strange ... my guess is that for some reason a lock is being held persistently and your processes are spinning on it. This should never (cough) happen. This is one for the PG hackers group, I think - Tom Lane's more likely to be able to give you help here than any of us. -- ps -fU postgres... UIDPID PPID C STIME TTY TIME CMD postgres 1842 1 0 12:41 ?00:00:00 /usr/local/pgsql/bin/postmaster -B 6000 -o -S 2000 -S -D /usr/local/pgsql/data postgres 1872 1842 82 12:41 ?01:06:20 /usr/local/pgsql/bin/postgres localhost nsadmin james INSERT postgres 1997 1842 0 13:11 ?00:00:02 /usr/local/pgsql/bin/postgres localhost nsadmin james idle postgres 2025 1842 21 13:15 ?00:10:04 /usr/local/pgsql/bin/postgres localhost nsadmin james INSERT postgres 2072 1842 27 13:30 ?00:08:51 /usr/local/pgsql/bin/postgres localhost nsadmin james INSERT postgres 2077 1842 25 13:31 ?00:07:41 /usr/local/pgsql/bin/postgres localhost nsadmin james INSERT postgres 2079 1842 25 13:31 ?00:07:44 /usr/local/pgsql/bin/postgres localhost nsadmin james INSERT postgres 2082 1842 25 13:31 ?00:07:40 /usr/local/pgsql/bin/postgres localhost nsadmin james INSERT postgres 2086 1842 26 13:33 ?00:07:41 /usr/local/pgsql/bin/postgres localhost nsadmin james INSERT postgres 2090 1842 0 13:35 ?00:00:01 /usr/local/pgsql/bin/postgres localhost nsadmin james idle postgres 2122 1842 6 13:41 ?00:01:22 /usr/local/pgsql/bin/postgres localhost nsadmin james INSERT postgres 2131 1842 0 13:41 ?00:00:00 /usr/local/pgsql/bin/postgres localhost nsadmin buxs idle postgres 2187 1842 20 13:54 ?00:01:32 /usr/local/pgsql/bin/postgres localhost nsadmin james INSERT postgres 2189 1842 19 13:54 ?00:01:28 /usr/local/pgsql/bin/postgres localhost nsadmin james INSERT postgres 2205 1842 0 13:59 ?00:00:00 /usr/local/pgsql/bin/postgres localhost nsadmin buxs idle postgres 2217 1842 0 14:00 ?00:00:00 /usr/local/pgsql/bin/postgres localhost nsadmin james idle postgres 2218 1842 0 14:00 ?00:00:00 /usr/local/pgsql/bin/postgres localhost nsadmin buxs idle postgres 2219 1842 0 14:00 ?00:00:00 /usr/local/pgsql/bin/postgres localhost nsadmin buxs idle postgres 2220 1842 0 14:00 ?00:00:00 /usr/local/pgsql/bin/postgres localhost nsadmin james idle top output for postgres user... 2:19pm up 2:31, 2 users, load average: 3.54, 5.55, 6.03 118 processes: 113 sleeping, 5 running, 0 zombie, 0 stopped CPU states: 195.3% user, 4.6% system, 0.0% nice, 807224.6% idle Mem: 319596K av, 289688K used, 29908K free, 0K shrd, 30884K buff Swap: 658584K av, 12K used, 658572K free 79636K cached PID USER PRI NI SIZE RSS SHARE STAT %CPU %MEM TIME COMMAND 2122 postgres 9 0 12300 12M 4560 S31.5 3.8 2:16 postmaster 2072 postgres 9 0 4144 4144 3524 S23.1 1.2 9:40 postmaster 2189 postgres 15 0 4152 4152 3528 R21.2 1.2 2:22 postmaster 2077 postgres 15 0 4152 4152 3532 R18.2 1.2 8:31 postmaster 1872 postgres 9 0 11848 11M 4128 R16.5 3.7 67:14 postmaster 2187 postgres 9 0 4148 4148 3528 S16.1 1.2 2:23 postmaster 2082 postgres 9 0 4144 4144 3524 S15.9 1.2 8:30 postmaster 2079 postgres 9 0 4140 4140 3520 S14.8 1.2 8:39 postmaster 2086 postgres 9 0 4140 4140 3516 S14.2 1.2 8:38 postmaster 2025 postgres 9 0 11800 11M 4084 R11.5 3.6 10:54 postmaster 2090 postgres 9 0 15548 15M 6748 S 1.1 4.8 0:01 postmaster 1842 postgres 8 0 1904 1904 1792 S 0.0 0.5 0:00 postmaster 1997 postgres 9 0 13864 13M 5752 S 0.0 4.3 0:02 postmaster 2131 postgres 9 0 4696 4696 4004 S 0.0 1.4 0:00 postmaster 2205 postgres 9 0 3996 3996 3388 S 0.0 1.2 0:00 postmaster 2217 postgres 9 0 11164 10M 3544 S 0.0 3.4 0:00 postmaster 2218 postgres 9 0 11704 11M 3616 S 0.0 3.6 0:00
