Thread
-
Re: [HACKERS] Postgres Performance
Tom Lane <tgl@sss.pgh.pa.us> — 1999-09-08T22:40:37Z
Michael Simms <grim@argh.demon.co.uk> writes: >> If I do a large search the first time is about three times slower than >> any subsequent overlapping (same data) searches. I would like to always >> get the higher performance. > What happens the first time is that it must read the data off the disc. After > that the data comes from memory IF it is cached. Disc read will always be > slower with current disc technology. There is that effect, but I suspect Edwin may also be seeing another effect. When a tuple is first inserted or modified, it is written into the table with a marker saying (in effect) "Inserted by transaction NNN, not committed yet". To find out whether the tuple is really any good, you have to go and consult pg_log to see if that transaction got committed. Obviously, that's slow, so the first subsequent transaction that does so and finds that NNN really did get committed will rewrite the disk page with the tuple's state changed to "Known committed". So, the first select after an update transaction will spend additional cycles checking pg_log and marking committed tuples. In effect, it's doing the last phase of the update. We could instead force the update to do all its own housekeeping, but the overall result wouldn't be any faster; probably it'd be slower. > I would imagine (Im not an expert, but through observation) that if > you drasticly increase the number of shared memory buffers, then when > you startup your front-end simply do a select * from the tables, it > may even keep them all in memory from the start. The default buffer space (64 disk pages) is not very large --- use a larger -B setting if you have the memory to spare. regards, tom lane
-
Re: [HACKERS] Postgres Performance
Edwin Ramirez <ramirez@doc.mssm.edu> — 1999-09-09T20:52:25Z
I believe that disk pages are 1k in linux systems, that would mean that I am allocating 3M when using "postmaster -i -B 3096 -o -S 2048" and 2M for sorting. That is very low. However, some of the postgres processes have memory segments larger than 3M (see bottom). > I would imagine (Im not an expert, but through observation) that if > you drasticly increase the number of shared memory buffers, then when > you startup your front-end simply do a select * from the tables, it > may even keep them all in memory from the start. That's basically what I tried to do, but I am unable to specify a very large number (it complained when I tried -B > ~3900). Do these buffer contain the actual table data? I understand that the OS is buffering the data read from disk, but postgres is competing with all the other processes on the system. I think that if postgres had a dedicated (user configurable) cache, like Oracle, then users could configure the system/postgres better. 4:29pm up 83 days, 23:42, 5 users, load average: 0.00, 0.01, 0.00 75 processes: 74 sleeping, 1 running, 0 zombie, 0 stopped CPU states: 0.1% user, 1.1% system, 0.0% nice, 98.7% idle Mem: 128216K av, 98812K used, 29404K free, 67064K shrd, 18536K buff Swap: 80288K av, 22208K used, 58080K free 14924K cached PID USER PRI NI SIZE RSS SHARE STAT LIB %CPU %MEM TIME COMMAND 16633 postgres 0 0 26536 1384 1284 S 0 0.0 1.0 0:02 postmaster 18190 postgres 0 0 27708 3432 2720 S 0 0.0 2.6 0:00 postmaster 18303 postgres 0 0 27444 2728 2196 S 0 0.0 2.1 0:00 postmaster 18991 postgres 0 0 27472 2908 2392 S 0 0.0 2.2 0:00 postmaster 19154 postgres 0 0 27408 2644 2140 S 0 0.0 2.0 0:06 postmaster 19155 postgres 0 0 27428 2712 2188 S 0 0.0 2.1 0:00 postmaster 19157 postgres 0 0 27840 10M 10144 S 0 0.0 8.6 0:08 postmaster 19282 postgres 0 0 27560 3332 2732 S 0 0.0 2.5 0:11 postmaster 19335 postgres 0 0 27524 3112 2528 S 0 0.0 2.4 0:03 postmaster 19434 postgres 0 0 27416 2700 2192 S 0 0.0 2.1 0:00 postmaster