Re: [PERFORM] Vacuum looping?
On Fri, Jul 27, 2007 at 05:32:11PM -0400, Steven Flatt wrote: > weren't convinced was doing anything), then start a manual vacuum with a > higher vacuum_cost_limit to get things cleaned up quicker. What are your vacuum_cost_* settings? If you set those too aggressively you'll be in big trouble. The second pass on the vacuum means that maintenance_work_memory isn't large enough. -- Jim Nasby [EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) pgpWfT3kiMjGA.pgp Description: PGP signature
Re: [PERFORM] When/if to Reindex
On Wed, Jul 18, 2007 at 01:08:30PM -0400, Steven Flatt wrote: > We're using Postgres 8.2.4. > > I'm trying to decide whether it's worthwhile to implement a process that > does periodic reindexing. In a few ad hoc tests, where I've tried to set up > data similar to how our application does it, I've noticed decent performance > increases after doing a reindex as well as the planner being more likely to > choose an index scan. > > Some background: we make extensive use of partitioned tables. In fact, I'm > really only considering reindexing partitions that have "just closed". In > our simplest/most general case, we have a table partitioned by a timestamp > column, each partition 24 hours wide. The partition will have an index on > the timestamp column as well as a few other indexes including a primary key > index (all b-tree). Is there a programmatic way I can decide, upon the > "closing" of a partition, which, if any, of these indexes will benefit from > a reindex? Can I determine things like average node density, node depth, or > any other indication as to the quality of an index? Will pg_class.relpages > be any help here? Looking at that stuff will help determine if the index is bloated, or if it's just bigger than optimal. Once you're done writing to an index, it might be worth reindexing with a fillfactor of 100% to shrink things down a bit. > Is it a simple matter of running some queries, reindexing the table, then > running the queries again to determine overall performance change? If so, > what queries would exercise this best? > > Just trying to determine if the extra cost of reindexing newly closed > partitions will be worth the performance benefit of querying the data. > Reindexing a table with a day's worth of data is taking on the order of a > few hours (10s of millions of rows). > > The docs say that: > > "...for B-tree indexes a freshly-constructed index is somewhat faster to > access than one that has been updated many times, because logically adjacent > pages are usually also physically adjacent in a newly built index... It > might be worthwhile to reindex periodically just to improve access speed." That's the other consideration, though if you're seeing a big difference I suspect it's an issue of indexes fitting in cache or not. -- Jim Nasby [EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) pgpZ9ie7CO9kL.pgp Description: PGP signature
Re: [PERFORM] Parrallel query execution for UNION ALL Queries
On Wed, Jul 18, 2007 at 11:30:48AM -0500, Scott Marlowe wrote: > EnterpriseDB, a commercially enhanced version of PostgreSQL can do > query parallelization, but it comes at a cost, and that cost is making > sure you have enough spindles / I/O bandwidth that you won't be > actually slowing your system down. I think you're thinking ExtendDB. :) -- Jim Nasby [EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) pgp4I6wlmsXWx.pgp Description: PGP signature
Re: [PERFORM] ionice to make vacuum friendier?
On Wed, Jul 18, 2007 at 10:03:00AM +0100, Heikki Linnakangas wrote: > Ron Mayer wrote: > > Seems Linux has IO scheduling through a program called ionice. > > > > Has anyone here experimented with using it rather than > > vacuum sleep settings? > > I looked at that briefly for smoothing checkpoints, but it was > unsuitable for that purpose because it only prioritizes reads, not writes. > > It maybe worth trying for vacuum, though vacuum too can do a lot of > writes. In the worst case, the OS cache is saturated with dirty pages, > which blocks all writes in the system. > > If it did prioritize writes as well, that would be *excellent*. Any > kernel hackers out there looking for a project? My understanding is that FreeBSD will prioritize IO based on process priority, though I have no idea how it's actually accomplished or how effective it is. But if we put in special support for this for Linux we should consider FBSD as well. -- Jim Nasby [EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) pgpB5pBTxbkJ2.pgp Description: PGP signature
Re: [PERFORM] Postgres configuration for 64 CPUs, 128 GB RAM...
On Tue, Jul 17, 2007 at 04:10:30PM +0200, Marc Mamin wrote: > shared_buffers= 262143 You should at least try some runs with this set far, far larger. At least 10% of memory, but it'd be nice to see what happens with this set to 50% or higher as well (though don't set it larger than the database since it'd be a waste). How big is the database, anyway? > # on our current best production server with 4GB RAM (not dedicated to > Postgres), work_mem is set to 600 MB > # this limitation is probably the bottleneck for our application as the > files in pgsql_tmp grows up to 15 GB > # during large aggregations (we have a locking mechanismus to avoid > parallel processing of such transactions) Keep in mind that a good filesystem will be caching most of pgsql_tmp if it can. > max_prepared_transaction = 100 Are you using 2PC? If not, there's no reason to touch this (could could just set it to 0). > # I use the default for the bgwriter as I couldnt find recommendation on > those > > #bgwriter_delay = 200ms # 10-1ms between rounds > #bgwriter_lru_percent = 1.0 # 0-100% of LRU buffers > scanned/round > #bgwriter_lru_maxpages = 5 # 0-1000 buffers max > written/round > #bgwriter_all_percent = 0.333 # 0-100% of all buffers > scanned/round > #bgwriter_all_maxpages = 5 # 0-1000 buffers max > written/round You'll probably want to increase both maxpages parameters substantially, assuming that you've got good IO hardware. > #CHECKPOINT > > # xlog will be on a separate disk > checkpoint_segments=256 > > checkpoint_timeout = 5min The further apart your checkpoints, the better. Might want to look at 10 minutes. I'd also set checkpoint_warning to just a bit below checkpoint_timeout and watch for warnings to make sure you're not checkpointing a lot more frequently than you're expecting. -- Jim Nasby [EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) pgpAtlNgiEMna.pgp Description: PGP signature
Re: [PERFORM] TRUNCATE TABLE
On Fri, Jul 13, 2007 at 09:12:34PM +0200, Pavel Stehule wrote:
> Hello,
>
> I tested speed difference between TRUNCATE TABLE and DROP TABLE
> (tested on my notebook ext3 and Linux fedora 7):
>
> CREATE OR REPLACE FUNCTION test01() RETURNS SETOF double precision
> AS $$
> DECLARE t1 timestamp with time zone;
> BEGIN
> CREATE TEMP TABLE foo(a integer);
> FOR i IN 1..1000 LOOP
>INSERT INTO foo SELECT 1 FROM generate_series(1,1);
>t1 := clock_timestamp();
>TRUNCATE TABLE foo;
>RETURN NEXT EXTRACT('ms' FROM clock_timestamp()-t1);
> END LOOP;
> RETURN;
> END;
> $$ LANGUAGE plpgsql;
>
> CREATE OR REPLACE FUNCTION test02() RETURNS SETOF double precision
> AS $$
> DECLARE t1 timestamp with time zone;
> BEGIN
> FOR i IN 1..1000 LOOP
>EXECUTE 'CREATE TEMP TABLE foo(a integer);';
>EXECUTE 'INSERT INTO foo SELECT 1 FROM generate_series(1,1);';
>t1 := clock_timestamp();
>EXECUTE 'DROP TABLE foo;';
>RETURN NEXT EXTRACT('ms' FROM clock_timestamp()-t1);
> END LOOP;
> RETURN;
> END;
> $$ LANGUAGE plpgsql;
Are you sure you can ignore the added cost of an EXECUTE? I tried the following
as a test, but my repeatability sucks... :/
CREATE OR REPLACE FUNCTION test02() RETURNS SETOF double precision AS $$
DECLARE t1 timestamp with time zone;
BEGIN
CREATE TEMP TABLE foo(a integer);
FOR i IN 1..1000 LOOP
EXECUTE 'INSERT INTO foo SELECT 1 FROM generate_series(1,1)';
t1 := clock_timestamp();
EXECUTE 'TRUNCATE TABLE foo';
RETURN NEXT EXTRACT('ms' FROM clock_timestamp()-t1);
END LOOP;
RETURN;
END;
$$ LANGUAGE plpgsql;
decibel=# drop table foo;select count(*), min(t), max(t), avg(t),
stddev_samp(t),stddev_pop(t) from test01() t(t);drop table foo;select count(*),
min(t), max(t), avg(t), stddev_samp(t),stddev_pop(t) from test03() t(t);drop
table foo;select count(*), min(t), max(t), avg(t), stddev_samp(t),stddev_pop(t)
from test01() t(t);drop table foo;select count(*), min(t), max(t), avg(t),
stddev_samp(t),stddev_pop(t) from test03() t(t);drop table foo;select count(*),
min(t), max(t), avg(t), stddev_samp(t),stddev_pop(t) from test01() t(t);drop
table foo;select count(*), min(t), max(t), avg(t), stddev_samp(t),stddev_pop(t)
from test03() t(t);
ERROR: table "foo" does not exist
count | min | max| avg| stddev_samp|stddev_pop
---+---+--+--+--+--
1000 | 0.533 | 1405.747 | 3.444874 | 44.4166419484871 | 44.3944280726548
(1 row)
Time: 44945.101 ms
DROP TABLE
Time: 11.204 ms
count | min | max| avg| stddev_samp|stddev_pop
---+---+--+--+--+--
1000 | 0.446 | 1300.168 | 7.611269 | 79.7606049935278 | 79.7207147159672
(1 row)
Time: 44955.870 ms
DROP TABLE
Time: 148.186 ms
count | min | max | avg| stddev_samp |stddev_pop
---+--++--+-+--
1000 | 0.46 | 21.585 | 1.991845 | 1.2259573313755 | 1.22534419938848
(1 row)
Time: 47566.985 ms
DROP TABLE
Time: 5.065 ms
count | min | max| avg| stddev_samp|stddev_pop
---+---+--+--+--+--
1000 | 0.479 | 1907.865 | 5.368207 | 73.8576562901696 | 73.8207182251985
(1 row)
Time: 48681.777 ms
DROP TABLE
Time: 7.863 ms
count | min | max| avg| stddev_samp | stddev_pop
---+---+--+--+-+-
1000 | 0.562 | 1009.578 | 2.998867 | 31.874023877249 | 31.858082879064
(1 row)
Time: 37426.441 ms
DROP TABLE
Time: 4.935 ms
count | min | max | avg| stddev_samp|stddev_pop
---+--++--+--+--
1000 | 0.42 | 20.721 | 2.064845 | 1.24241007069275 | 1.24178871027844
(1 row)
Time: 47906.628 ms
decibel=#
--
Jim Nasby [EMAIL PROTECTED]
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)
pgpjnuK3pXGp4.pgp
Description: PGP signature
Re: [PERFORM] FORGOT TO CONFIGURE RAID! DELL POWEREDGE 2950
On Sat, Jul 14, 2007 at 12:19:51PM +0200, Hannes Dorbath wrote: > Gregory Stark wrote: > >> From the DELL site it seems this `PERC 5/i' on board controller > >> (assuming that's what you have) doesn't even have a BBU. If you don't > >> plan to post here in a few weeks again about data corruption, go out and > >> shop a serious controller. > > > > This is a bit of a strange comment. A BBU will improve performance but > > Postgres doesn't require one to guarantee data integrity. > > > > If your drives have write caching disabled (ie write-through) and your > > controller does write-through caching and you leave fsync=on and > > full_page_writes=on which is the default then you shouldn't have any data > > integrity issues. > > That was my point, controllers without BBU usually leave drive caches > turned on, as with drive caches off performance would be unbearable bad. Wow, are you sure about that? I've never heard it before, but that'd be pretty disturbing if it's true... -- Jim Nasby [EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) pgpWzoqBohtIF.pgp Description: PGP signature
Re: [PERFORM] TRUNCATE TABLE
On Fri, Jul 13, 2007 at 12:30:46PM -0400, Tom Lane wrote: > Adriaan van Os <[EMAIL PROTECTED]> writes: > > I started another test. I copied an existing database (not very large, > > 35 tables, typically a few hundred up to a few thousand records) with > > CREATE DATABASE testdb TEMPLATE mydb and started to remove random > > tables from testdb with DROP TABLE and TRUNCATE TABLE. I did this with > > the query tool of pgAdmin III, to exclude any doubts about my own > > software (that uses pqlib). > > Can you try it with plain psql? pgAdmin is a variable that wasn't > accounted for in my tests. > > > The hardware is an Intel dual-core 17-inch > > MacBook Pro running Mac OS X 10.4. > > Hmm. I thought you said Fedora before. However, I'd done a few tests > yesterday on my own Mac laptop (Al G4) and not gotten results that were > out of line with HPUX or Fedora. > > Does anyone else want to try replicating these tests? The following is consistently between 1 and 3 ms: decibel=# create table i as select * from generate_series(1,2) i; drop table i; SELECT Time: 42.413 ms DROP TABLE Time: 1.415 ms decibel=# select version(); version PostgreSQL 8.3devel on i386-apple-darwin8.10.1, compiled by GCC i686-apple-darwin8-gcc-4.0.1 (GCC) 4.0.1 (Apple Computer, Inc. build 5363) (1 row) Time: 46.870 ms decibel=# \! uname -a Darwin platter.local 8.10.1 Darwin Kernel Version 8.10.1: Wed May 23 16:33:00 PDT 2007; root:xnu-792.22.5~1/RELEASE_I386 i386 i386 decibel=# Truncate is a different story... this is consistently either 6 something ms or 17 something ms: decibel=# insert into i select generate_series(1,2); truncate i; INSERT 0 2 Time: 600.940 ms TRUNCATE TABLE Time: 6.313 ms decibel=# This is on a 17" MBP, fsync turned on. -- Jim Nasby [EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) pgpRIJk3GXUER.pgp Description: PGP signature
Re: [PERFORM] PostgreSQL publishes first real benchmark
On Mon, Jul 09, 2007 at 01:48:44PM -0400, Jignesh K. Shah wrote: > > Hi Heikki, > > Heikki Linnakangas wrote: > > > >That's really exciting news! > > > >I'm sure you spent a lot of time tweaking the settings, so let me ask > >you something topical: > > > >How did you end up with the bgwriter settings you used? Did you > >experiment with different values? How much difference did it make? > > > > Background writer is still a pain to get it right.. I say it is a > necessary evil since you are trying to balance it with trying to level > writes to the disks and lock contentions caused by the writer itself to > the postgresql connections. Our typical problem will arise at the high > number of users where all users are suddenly locked due to the bgwriter > holding the locks.. Using the hotuser script (which uses pearl/Dtrace > combination) we ran quite a bit of numbers trying to see which ones > results in less overall time spent in PGLock* calls and yet gave good > uniform writes to the disks. After reaching the published settings, > everynow and then we would try playing with different values to see if > it improves but generally seemed to degrade if changed.. (Of course your > mileage will vary depending on config, workload, etc). > > Still I believe the locking mechanism needs to be revisited at some > point since that seems to be the one which will eventually limit the > number of users in such a workload. (Specially if you dont hit the right > settings for your workload) Do you know specifically what locks were posing the problem? I have a theory that having individual backends run the clock sweep limits concurrency and I'm wondering if you were seeing any of that. The lock in question would be BufFreelistLock. -- Jim Nasby [EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) pgp1QUfHi98xR.pgp Description: PGP signature
Re: [PERFORM] Filesystem Direct I/O and WAL sync option
On Tue, Jul 03, 2007 at 04:06:29PM +0100, Heikki Linnakangas wrote: > Dimitri wrote: > >I'm very curious to know if we may expect or guarantee any data > >consistency with WAL sync=OFF but using file system mounted in Direct > >I/O mode (means every write() system call called by PG really writes > >to disk before return)... > > You'd have to turn that mode on on the data drives as well to get > consistency, because fsync=off disables checkpoint fsyncs of the data > files as well. BTW, it might be worth trying the different wal_sync_methods. IIRC, Jonah's seen some good results from open_datasync. -- Jim Nasby [EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) pgpztJcZkDoad.pgp Description: PGP signature
Re: [PERFORM] Performance Problem with Vacuum of bytea table (PG 8.0.13)
On Fri, May 25, 2007 at 10:29:30AM +0200, Bastian Voigt wrote: > Hi *, > for caching large autogenerated XML files, I have created a bytea table > in my database so that the cached files can be used by multiple servers. > There are about 500 rows and 10-20 Updates per minute on the table. The > files stored in the bytea are anything from 10kB to 10MB. My PostgreSQL > version is 8.0.13 on Gentoo Linux (x86) with PostGIS 1.2.0. > > For vacuum I use the pg_autovacuum daemon. It decided to vacuum my cache > table about every 3 hours, the vacuum process takes 20-30 minutes > (oops!) every time. You'll want to decrease autovacum_vacuum_scale_factor to 0.2 if you're on anything less than 8.2. -- Jim Nasby [EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) pgpmVh9hXWqRK.pgp Description: PGP signature
Re: [PERFORM] Memory allocation and Vacuum abends
What does top report as using the most memory? On Wed, May 23, 2007 at 11:01:24PM -0300, Leandro Guimar?es dos Santos wrote: > Hi all, > > > > I have a 4 CPU, 4GB Ram memory box running PostgreSql 8.2.3 under Win 2003 in > a very high IO intensive insert application. > > > > The application inserts about 570 rows per minute or 9 rows per second. > > > > We have been facing some memory problem that we cannot understand. > > > > From time to time memory allocation goes high and even after we stop > postgresql service the memory continues allocated and if were restart the > service the Postgres crash over. > > > > It's a 5 GB database size already that was born 1 and a half month ago. We > have 2 principal tables partitioned. > > > > Above is the log file. Do anyone have any idea what could the problem be.. > > > > Thanks in advance. > > > > > > 2007-05-23 13:21:00 LOG: CreateProcess call failed: A blocking operation was > interrupted by a call to WSACancelBlockingCall. > > (error code 1450) > > 2007-05-23 13:21:00 LOG: could not fork new process for connection: A > blocking operation was interrupted by a call to WSACancelBlockingCall. > > > > 2007-05-23 13:21:06 LOG: could not receive data from client: An operation on > a socket could not be performed because the system lacked sufficient buffer > space or because a queue was full. > > 2007-05-23 13:21:17 LOG: server process (PID 256868) exited with exit code > 128 > > 2007-05-23 13:21:17 LOG: terminating any other active server processes > > 2007-05-23 13:21:17 WARNING: terminating connection because of crash of > another server process > > 2007-05-23 13:21:17 DETAIL: The postmaster has commanded this server process > to roll back the current transaction and exit, because another server process > exited abnormally and possibly corrupted shared memory. > > 2007-05-23 13:21:17 HINT: In a moment you should be able to reconnect to the > database and repeat your command. > > 2007-05-23 13:21:17 WARNING: terminating connection because of crash of > another server process > > 2007-05-23 13:21:17 DETAIL: The postmaster has commanded this server process > to roll back the current transaction and exit, because another server process > exited abnormally and possibly corrupted shared memory. > > 2007-05-23 13:21:17 WARNING: terminating connection because of crash of > another server process > > 2007-05-23 13:21:17 DETAIL: The postmaster has commanded this server process > to roll back the current transaction and exit, because another server process > exited abnormally and possibly corrupted shared memory. > > > -- Jim Nasby [EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) pgp0D3edkaqiH.pgp Description: PGP signature
Re: [PERFORM] Simulate database fragmentation
On Wed, May 23, 2007 at 11:58:06AM -0700, Y Sidhu wrote: > Is there any easy way to take a database and add/delete records to create > fragmentation of the records and indexes. I am trying to recreate high > vacuum times. Update random rows, then do a vacuum. That will result in free space in random locations. At that point you'd probably want to update some ranges of rows, enough so that they get forced to new pages. A better idea might be to just insert random data. -- Jim Nasby [EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) pgpVs5CIluK1t.pgp Description: PGP signature
Re: [PERFORM] Domains versus Check Constraints
On Tue, May 22, 2007 at 12:56:21PM -0400, Chander Ganesan wrote: > Are there any performance improvements that come from using a domain > over a check constraint (aside from the ease of management component)? No. Plus support for domain constraints isn't universal (plpgsql doesn't honor them, for example). -- Jim Nasby [EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) pgpHXSEWtrc1J.pgp Description: PGP signature
Re: [PERFORM] Feature suggestion : FAST CLUSTER
On Tue, May 22, 2007 at 09:29:00AM +0200, PFC wrote: > This does not run a complete sort on the table. It would be about as > fast as your seq scan disk throughput. Obviously, the end result is > not as > good as a real CLUSTER since the table will be made up of several ordered > chunks and a range lookup. Therefore, a range lookup on the clustered > columns would need at most N seeks, versus 1 for a really clustered table. > But it only scans the table once and writes it once, even counting index > rebuild. Do you have any data that indicates such an arrangement would be substantially better than less-clustered data? -- Jim Nasby [EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) pgpxnzY69XnoC.pgp Description: PGP signature
Re: [PERFORM] Postgres Benchmark Results
On Sun, May 20, 2007 at 04:58:45PM +0200, PFC wrote: > > I felt the world needed a new benchmark ;) > So : Forum style benchmark with simulation of many users posting and > viewing forums and topics on a PHP website. > > http://home.peufeu.com/ftsbench/forum1.png Any chance of publishing your benchmark code so others can do testing? It sounds like a useful, well-thought-out benchmark (even if it is rather specialized). Also, I think it's important for you to track how long it takes to respond to requests, both average and maximum. In a web application no one's going to care if you're doing 1000TPS if it means that every time you click on something it takes 15 seconds to get the next page back. With network round-trip times and what-not considered I'd say you don't want it to take any more than 200-500ms between when a request hits a webserver and when the last bit of data has gone back to the client. I'm guessing that there's about 600MB of memory available for disk caching? (Well, 600MB minus whatever shared_buffers is set to). -- Jim Nasby [EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [PERFORM] Postgres Benchmark Results
On Sun, May 20, 2007 at 08:00:25PM +0200, Zoltan Boszormenyi wrote: > I also went into benchmarking mode last night for my own > amusement when I read on the linux-kernel ML that > NCQ support for nForce5 chips was released. > I tried current PostgreSQL 8.3devel CVS. > pgbench over local TCP connection with > 25 clients and 3000 transacts/client gave me > around 445 tps before applying NCQ support. > 680 tps after. > > It went over 840 tps after adding HOT v7 patch, > still with 25 clients. It topped at 1062 tps with 3-4 clients. > I used a single Seagate 320GB SATA2 drive > for the test, which only has less than 40GB free. > So it's already at the end of the disk giving smaller > transfer rates then at the beginning. Filesystem is ext3. > Dual core Athlon64 X2 4200 in 64-bit mode. > I have never seen such a performance before > on a desktop machine. I'd be willing to bet money that the drive is lying about commits/fsync. Each transaction committed essentially requires one revolution of the drive with pg_xlog on it, so a 15kRPM drive limits you to 250TPS. BTW, PostgreSQL sees a big speed boost if you mount ext3 with the option data=writeback. Note that doing that probably has a negative impact on data recovery after a crash for non-database files. -- Jim Nasby [EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] 500 requests per second
On Mon, May 21, 2007 at 03:50:27PM -0400, Merlin Moncure wrote: > I work on a system about like you describe400tps constant24/7. > Major challenges are routine maintenance and locking. Autovacuum is > your friend but you will need to schedule a full vaccum once in a > while because of tps wraparound. If you allow AV to do this, none of > your other tables get vacuumed until it completesheh! BTW, that's changed in either 8.2 or 8.3; the change is that freeze information is now tracked on a per-table basis instead of per-database. So autovacuum isn't forced into freezing everything in the database at once. -- Jim Nasby [EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] pg_stats how-to?
On Fri, May 18, 2007 at 04:26:05PM -0700, Y Sidhu wrote: > >To answer your original question, a way to take a look at how bloated > >your tables are would be to ANALYZE, divide reltuples by relpages from > >pg_class (gives how many rows per page you have) and compare that to 8k > >/ average row size. The average row size for table rows would be the sum > >of avg_width from pg_stats for the table + 24 bytes overhead. For > >indexes, it would be the sum of avg_width for all fields in the index > >plus some overhead (8 bytes, I think). > > > >An even simpler alternative would be to install contrib/pgstattuple and > >use the pgstattuple function, though IIRC that does read the entire > >relation from disk. > >-- > >Jim Nasby [EMAIL PROTECTED] > >EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) > > > > Here are my results: > > a. SELECT sum(reltuples)/sum(relpages) as rows_per_page FROM pg_class; > > I get 66 > > b. SELECT (8000/(sum(avg_width)+24)) as table_stat FROM pg_stats; > > I get 1 And those results will be completely meaningless because they're covering the entire database (catalog tables included). You need to compare the two numbers on a table-by-table basis, and you'd also have to ignore any small tables (say smaller than 1000 pages). Also, a page is 8192 bytes in size (though granted there's a page header that's something like 16 bytes). -- Jim Nasby [EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] [doc patch] a slight VACUUM / VACUUM FULL doc improvement proposal
On Wed, May 16, 2007 at 12:09:26PM -0400, Alvaro Herrera wrote: > Guillaume Cottenceau wrote: > > "Jim C. Nasby" writes: > > > > > On Wed, May 16, 2007 at 09:41:46AM +0200, Guillaume Cottenceau wrote: > > > > [...] > > > > > > Come on, I don't suggest to remove several bold warnings about > > > > it, the best one being "Therefore, frequently using VACUUM FULL > > > > can have an extremely negative effect on the performance of > > > > concurrent database queries." My point is to add the few > > > > additional mentions; I don't think the claims that VACUUM FULL > > > > physically compacts the data, and might be useful in case of too > > > > long time with infrequent VACUUM are incorrect, are they? > > > > > > Unfortunately they are, to a degree. VACUUM FULL can create a > > > substantial amount of churn in the indexes, resulting in bloated > > > indexes. So often you have to REINDEX after you VACUUM FULL. > > > > Ok, VACUUM FULL does his job (it physically compacts the data and > > might be useful in case of too long time with infrequent VACUUM), > > but we are going to not talk about it because we often needs a > > REINDEX after it? The natural conclusion would rather be to > > document the fact than REINDEX is needed after VACUUM FULL, isn't > > it? > > Maybe, but we should also mention that CLUSTER is a likely faster > workaround. What this boils down to is that there should probably be a separate subsection that deals with "Oh noes! My tables are too big!" -- Jim Nasby [EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] Disk Fills Up and fsck "Compresses" it
No, it's part of FreeBSD's UFS. google FreeBSD softupdates and you should get plenty of info. As I said, it's probably not worth worrying about. On Wed, May 16, 2007 at 08:21:23AM -0700, Y Sidhu wrote: > What do you mean by "softupdates?" Is that a parameter in what I am guessing > is the conf file? -- Jim Nasby [EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] [doc patch] a slight VACUUM / VACUUM FULL doc improvement proposal
On Wed, May 16, 2007 at 09:41:46AM +0200, Guillaume Cottenceau wrote: > Michael Stone writes: > > > On Tue, May 15, 2007 at 06:43:50PM +0200, Guillaume Cottenceau wrote: > > >patch - basically, I think the documentation under estimates (or > > >sometimes misses) the benefit of VACUUM FULL for scans, and the > > >needs of VACUUM FULL if the routine VACUUM hasn't been done > > >properly since the database was put in production. > > > > It's also possible to overestimate the benefit of vacuum full, leading > > to people vacuum full'ing almost constantly, then complaining about > > performance due to the associated overhead. I think there have been > > more people on this list whose performance problems were caused by > > unnecessary full vacs than by those whose performance problems were > > caused by insufficient full vacs. > > Come on, I don't suggest to remove several bold warnings about > it, the best one being "Therefore, frequently using VACUUM FULL > can have an extremely negative effect on the performance of > concurrent database queries." My point is to add the few > additional mentions; I don't think the claims that VACUUM FULL > physically compacts the data, and might be useful in case of too > long time with infrequent VACUUM are incorrect, are they? Unfortunately they are, to a degree. VACUUM FULL can create a substantial amount of churn in the indexes, resulting in bloated indexes. So often you have to REINDEX after you VACUUM FULL. Long term I think we should ditch 'VACUUM FULL' altogether and create a COMPACT command (it's very easy for users to get confused between "vacuum all the databases in the cluster" or "vacuum the entire database" and "VACUUM FULL"). -- Jim Nasby [EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] Disk Fills Up and fsck "Compresses" it
I'm guessing you're seeing the affect of softupdates. With those enabled it can take some time before the space freed by a delete will actually show up as available. On Tue, May 15, 2007 at 01:18:42PM -0700, Y Sidhu wrote: > Anyone seen PG filling up a 66 GB partition from say 40-ish percentage to > 60-ish percentage in a manner of minutes. When I run a 'fsck' the disk usage > comes down to 40-ish percentage. That's about 10+ GB's variance. > > This is a FreeBSD 6.2 RC2, 4GB memory, Xeon 3.2 GHz '4' of the '8' CPUs in > use - dual cpu, dual core with HTT turned off in the sense that the other 4 > cpu's have been masked out. The drive is a Western Digital 70 GB SATA. > > -- > Yudhvir Singh Sidhu > 408 375 3134 cell -- Jim Nasby [EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] pg_stats how-to?
On Mon, May 14, 2007 at 08:20:49PM -0400, Tom Lane wrote: > "Y Sidhu" <[EMAIL PROTECTED]> writes: > > it may be table fragmentation. What kind of tables? We have 2 of them which > > experience lots of adds and deletes only. No updates. So a typical day > > experiences record adds a few dozen times on the order of 2.5 million. And > > deletes once daily. Each of these tables has about 3 btree indexes. > > With an arrangement like that you should vacuum once daily, shortly > after the deletes --- there's really no point in doing it on any other > schedule. Note "shortly" not "immediately" --- you want to be sure that > any transactions old enough to see the deleted rows have ended. Also, think about ways you might avoid the deletes altogether. Could you do a truncate instead? Could you use partitioning? If you are using deletes then look at CLUSTERing the table some time after the deletes (but be aware that prior to 8.3 CLUSTER doesn't fully obey MVCC). To answer your original question, a way to take a look at how bloated your tables are would be to ANALYZE, divide reltuples by relpages from pg_class (gives how many rows per page you have) and compare that to 8k / average row size. The average row size for table rows would be the sum of avg_width from pg_stats for the table + 24 bytes overhead. For indexes, it would be the sum of avg_width for all fields in the index plus some overhead (8 bytes, I think). An even simpler alternative would be to install contrib/pgstattuple and use the pgstattuple function, though IIRC that does read the entire relation from disk. -- Jim Nasby [EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] 500 requests per second
On Tue, May 15, 2007 at 11:47:29AM +0100, Richard Huxton wrote: > Tarhon-Onu Victor wrote: > >On Mon, 14 May 2007, Richard Huxton wrote: > > > >>1. Is this one client making 500 requests, or 500 clients making one > >>request per second? > > > >Up to 250 clients will make up to 500 requests per second. > > Well, PG is pretty good at handling multiple clients. But if I'm > understanding you here, you're talking about potentially 250*500=125000 > updates per second. If each update writes 1KB to disk, that's 125MB/sec > continuously written. Are these figures roughly correct? I'm guessing it's 500TPS overall, not per connection. It'd be rather challenging just to do 125,000 network round trips per second. > >>2. Do you expect the indexes at least to fit in RAM? > > > >not entirely... or not all of them. > > Hmm - OK. So you're going to have index reads accessing disk as well. > Exactly how big are you looking at here? > Will it be constantly growing? > Can you partition the large table(s) by date or similar? > > Well, the two things I'd expect to slow you down are: > 1. Contention updating index blocks > 2. Disk I/O as you balance updates and selects. > > Since you're constantly updating, you'll want to have WAL on a separate > set of disks from the rest of your database, battery-backed cache on > your raid controller etc. Check the mailing list archives for recent > discussions about good/bad controllers. You'll also want to > substantially increase checkpoint limits, of course. > > If you can cope with the fact that there's a delay, you might want to > look at replication (e.g. slony) to have reads happening on a separate > machine from writes. That may well not be possible in your case. Just as a data point, I've worked with some folks that are doing ~250TPS on a disk array with around 20-30 drives. IIRC a good amount of their working set did fit into memory, but not all of it. Your biggest constraint is really going to be I/O operations per second. If 90% of your data is in cache then you'll need to do a minimum of 50IOPS (realistically you'd probably have to double that). If 50% of your working set fits in cache you'd then be looking at 250IOPS, which is a pretty serious rate. I very strongly encourage you to do benchmarking to get a feel for how your system performs on a given set of hardware so that you have some idea of where you need to get to. You should also be looking hard at your application and system architecture for ways to cut down on your throughput. There may be some things you can do that would reduce the amount of database hardware you need to buy. -- Jim Nasby [EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] pg_stats how-to?
On Mon, May 14, 2007 at 12:02:03PM -0700, Y Sidhu wrote: > I am sorry about this Jim, please understand that I am a newbie and am > trying to solve long vacuum time problems and get a handle on speeding up > queries/reports. I was pointed to pg_stats and that's where I am at now. I Well, I have no idea what that person was trying to convey then. What are you trying to look up? Better yet, what's your actual problem? > have added this into my conf file: > stats_start_collector TRUE stats_reset_on_server_start FALSE > stats_command_string TRUE > However, these being production servers, I have not enabled these: > stats_row_level stats_block_level FYI, stats_command_string has a far larger performance overhead than any of the other stats commands prior to 8.2. > Yes, I have re-started the server(s). It seems like I query tables to get > the info. If so, are there any queries written that I can use? > > Thanks for following up on this with me. > > Yudhvir > > === > On 5/14/07, Jim C. Nasby <[EMAIL PROTECTED]> wrote: > > > >On Mon, May 14, 2007 at 11:09:21AM -0700, Y Sidhu wrote: > >> The stats_block_level and stats_row_level are NOT enabled. The question > >is > >> how to use pg_stats. Do I access/see them via the ANALYZE command? or > >using > >> SQL. I cannot find any document which will get me started on this. > > > >Ok, we're both confused I think... I thought you were talking about the > >pg_stat* views, which depend on the statistics collector (that's what > >the stats_* parameters control). > > > >That actually has nothing at all to do with pg_stats or pg_statistics. > >Those deal with statistics about the data in the database, and not about > >statistics from the engine (which is what the pg_stat* views do...). > > > >If you want to know about pg_stats, take a look at > >http://www.postgresql.org/docs/8.2/interactive/view-pg-stats.html ... > >but normally you shouldn't need to worry yourself about that. Are you > >trying to debug something? > > > >Information about the backend statistics can be found at > >http://www.postgresql.org/docs/8.2/interactive/monitoring.html > >-- > >Jim Nasby [EMAIL PROTECTED] > >EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) > > > > > > -- > Yudhvir Singh Sidhu > 408 375 3134 cell -- Jim Nasby [EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] pg_stats how-to?
On Mon, May 14, 2007 at 11:09:21AM -0700, Y Sidhu wrote: > The stats_block_level and stats_row_level are NOT enabled. The question is > how to use pg_stats. Do I access/see them via the ANALYZE command? or using > SQL. I cannot find any document which will get me started on this. Ok, we're both confused I think... I thought you were talking about the pg_stat* views, which depend on the statistics collector (that's what the stats_* parameters control). That actually has nothing at all to do with pg_stats or pg_statistics. Those deal with statistics about the data in the database, and not about statistics from the engine (which is what the pg_stat* views do...). If you want to know about pg_stats, take a look at http://www.postgresql.org/docs/8.2/interactive/view-pg-stats.html ... but normally you shouldn't need to worry yourself about that. Are you trying to debug something? Information about the backend statistics can be found at http://www.postgresql.org/docs/8.2/interactive/monitoring.html -- Jim Nasby [EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [PERFORM] pg_stats how-to?
Please include the list in your replies... Ok, so you've got stats collection turned on. What's the question then? And are stats_block_level and stats_row_level also enabled? On Mon, May 14, 2007 at 09:28:46AM -0700, Y Sidhu wrote: > yes > > Yudhvir > === > > On 5/14/07, Jim C. Nasby <[EMAIL PROTECTED]> wrote: > > > >Have you either re-loaded the config or restarted the server since > >making those changes? > > > >On Mon, May 14, 2007 at 09:16:54AM -0700, Y Sidhu wrote: > >> I am trying to use them. I have set these values in my conf file: > >> stats_start_collector TRUE stats_reset_on_server_start FALSE > >> stats_command_string TRUE > >> now what? > >> > >> Yudhvir > >> == > >> > >> On 5/13/07, Shoaib Mir <[EMAIL PROTECTED]> wrote: > >> > > >> >Can you be a little more specific? What exactly are you trying to > >achieve > >> >with pg_stats? > >> > > >> >You can always get help for documentation at --> > >> >http://www.postgresql.org/docs/8.2/static/view-pg-stats.html > >> > > >> > > >> >-- > >> >Shoaib Mir > >> >EnterpriseDB (www.enterprisedb.com) > >> > > >> >On 5/13/07, Yudhvir Singh Sidhu < [EMAIL PROTECTED]> wrote: > >> >> > >> >> Anyone know of a pg_stats howto? I'd appreciate any direction. > >> >> > >> >> Yudhvir > >> >> > >> >> ---(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 > >> >> > >> > > >> > > >> > >> > >> -- > >> Yudhvir Singh Sidhu > >> 408 375 3134 cell > > > >-- > >Jim Nasby [EMAIL PROTECTED] > >EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) > > > > > > -- > Yudhvir Singh Sidhu > 408 375 3134 cell -- Jim C. Nasby, Database Architect[EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 Windows: "Where do you want to go today?" Linux: "Where do you want to go tomorrow?" FreeBSD: "Are you guys coming, or what?" ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] pg_stats how-to?
Have you either re-loaded the config or restarted the server since making those changes? On Mon, May 14, 2007 at 09:16:54AM -0700, Y Sidhu wrote: > I am trying to use them. I have set these values in my conf file: > stats_start_collector TRUE stats_reset_on_server_start FALSE > stats_command_string TRUE > now what? > > Yudhvir > == > > On 5/13/07, Shoaib Mir <[EMAIL PROTECTED]> wrote: > > > >Can you be a little more specific? What exactly are you trying to achieve > >with pg_stats? > > > >You can always get help for documentation at --> > >http://www.postgresql.org/docs/8.2/static/view-pg-stats.html > > > > > >-- > >Shoaib Mir > >EnterpriseDB (www.enterprisedb.com) > > > >On 5/13/07, Yudhvir Singh Sidhu < [EMAIL PROTECTED]> wrote: > >> > >> Anyone know of a pg_stats howto? I'd appreciate any direction. > >> > >> Yudhvir > >> > >> ---(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 > >> > > > > > > > -- > Yudhvir Singh Sidhu > 408 375 3134 cell -- Jim Nasby [EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] Kernel cache vs shared_buffers
On Sat, May 12, 2007 at 03:28:45PM +0100, Heikki Linnakangas wrote: > >In the case of it being disk-block based, my > >inclination would be to let the kernel do the buffering. In the case of > >the cache being table-row-based, I would expect it to be much more > >space-efficient and I would be inclined to give the memory to the pg. > >In that case, is it feasible to set shared_buffers to something like > >50 x 8k blocks? We make extensive use of indexes on the larger > >tables and would seldom, if ever, do sequential scans. > > A common rule of thumb people quote here is to set shared_buffers to 1/4 > of available RAM, and leave the rest for OS cache. That's probably a > good configuration to start with. If you really care about performance it would be a good idea to start with that and do your own benchmarking. Much of the consensus about shared_buffers was built up before 8.0, and the shared buffer management we have today looks nothing like what was in 7.4. You might find that shared_buffers = 50% of memory or even higher might perform better for your workload. If you do find results like that, please share them. :) -- Jim Nasby[EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] estimating the need for VACUUM FULL and REINDEX
On Fri, May 11, 2007 at 01:25:04PM -0400, Alvaro Herrera wrote: > Guillaume Cottenceau wrote: > > Guillaume Cottenceau writes: > > > > > With that in mind, I've tried to estimate how much benefit would > > > be brought by running VACUUM FULL, with the output of VACUUM > > > VERBOSE. However, it seems that for example the "removable rows" > > > reported by each VACUUM VERBOSE run is actually reused by VACUUM, > > > so is not what I'm looking for. > > > > I've tried to better understand how autovacuum works (we use 7.4) > > to see if a similar mechanism could be used in 7.4 (e.g. run > > VACUUM ANALYZE often enough to not end up with a need to VACUUM > > FULL). > > > > The autovacuum daemon uses statistics collected thanks to > > stats_row_level. However, inside pg_stat_user_tables, the values > > n_tup_upd and n_tup_del seem to be reported from pg startup and > > never reset, whereas the information from previous VACUUM would > > be needed here, if I understand correctly. Is there anything that > > can be done from that point on with existing pg information, or > > I'd need e.g. to remember the values of my last VACUUM myself? > > In 7.4 there was the pg_autovacuum daemon in contrib, wasn't there? No > need to write one yourself. Correct. But one important note: the default parameters in the 7.4 contrib autovac are *horrible*. They will let your table grow to 3x minimum size, instead of 1.4x in 8.0/8.1 and 1.2x in 8.2. You must specify a different scale if you want anything resembling good results. -- Jim Nasby[EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) ---(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: [PERFORM] Long running transactions again ...
On Wed, Apr 11, 2007 at 12:50:37AM +0200, Tobias Brox wrote: > We had problems again, caused by long running transactions. I'm > monitoring the pg_stat_activity view, checking the query_start of all > requests that are not idle - but this one slipped under the radar as the > application was running frequent queries towards the database. > > That's not what concerns me most. We had two databases running under > postgres at this host - like, main production database (A) and a > separate smaller database for a separate project (B). As far as I > understood postgres philosophy, the databases should be isolated from > each other, i.e. one are not allowed to create a query that goes across > the database borders (select * from A.customers join B.logins ...). So, > I was surprised to see that the application working towards database B > managed to jam up database A, to the extent that we couldn't get A > vacuumed properly. Vacuums do ignore other databases, except for shared relations such as pg_database. If one of the databases wasn't being vacuumed properly it means there was in fact a transaction open. Note that until recently, vacuums wouldn't ignore other vacuums, so a long-running vacuum would prevent repeated vacuums on the same table from accomplishing much. Are you sure that your monitoring doesn't accidentally ignore backends marked as in transaction? -- Jim Nasby[EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] a question about Direct I/O and double buffering
On Thu, Apr 05, 2007 at 03:10:43PM -0500, Erik Jones wrote: > Nope. What we never tracked down was the factor of 10 drop in > database transactions, not disk transactions. The write volume was > most definitely due to the direct io setting -- writes are now being > done in terms of the system's block size where as before they were > being done in terms of the the filesystem's cache page size (as it's > in virtual memory). Basically, we do so many write transactions that > the fs cache was constantly paging. Did you try decreasing the size of the cache pages? I didn't realize that Solaris used a different size for cache pages and filesystem blocks. Perhaps the OS was also being too aggressive with read-aheads? My concern is that you're essentially leaving a lot of your memory unused this way, since shared_buffers is only set to 1.6G. BTW, did you ever increase the parameter that controls how much memory Solaris will use for filesystem caching? -- Jim Nasby[EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] Shared buffers, db transactions commited, and write IO on Solaris
On Fri, Mar 30, 2007 at 11:19:09AM -0500, Erik Jones wrote: > >On Fri, Mar 30, 2007 at 04:25:16PM +0200, Dimitri wrote: > >>The problem is while your goal is to commit as fast as possible - > >>it's > >>pity to vast I/O operation speed just keeping common block size... > >>Let's say if your transaction modification entering into 512K - > >>you'll > >>be able to write much more 512K blocks per second rather 8K per > >>second > >>(for the same amount of data)... Even we rewrite probably several > >>times the same block with incoming transactions - it still costs on > >>traffic, and we will process slower even H/W can do better. Don't > >>think it's good, no? ;) > >> > >>Rgds, > >>-Dimitri > >> > >With block sizes you are always trading off overhead versus space > >efficiency. Most OS write only in 4k/8k to the underlying hardware > >regardless of the size of the write you issue. Issuing 16 512byte > >writes has much more overhead than 1 8k write. On the light > >transaction > >end, there is no real benefit to a small write and it will slow > >performance for high throughput environments. It would be better to, > >and I think that someone is looking into, batching I/O. > > > >Ken > > True, and really, considering that data is only written to disk by > the bgwriter and at checkpoints, writes are already somewhat > batched. Also, Dimitri, I feel I should backtrack a little and point > out that it is possible to have postgres write in 512byte blocks (at > least for UFS which is what's in my head right now) if you set the > systems logical block size to 4K and fragment size to 512 bytes and > then set postgres's BLCKSZ to 512bytes. However, as Ken has just > pointed out, what you gain in space efficiency you lose in > performance so if you're working with a high traffic database this > wouldn't be a good idea. Sorry for the late reply, but I was on vacation... Folks have actually benchmarked filesystem block size on linux and found that block sizes larger than 8k can actually be faster. I suppose if you had a workload that *always* worked with only individual pages it would be a waste, but it doesn't take much sequential reading to tip the scales. -- Jim Nasby[EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[PERFORM] [EMAIL PROTECTED]: Anyone interested in improving postgresql scaling?]
Ok, looks like the FreeBSD community is interested in PostgreSQL performance, or at least enough to investigate it. Anyone here have experience hacking on FreeBSD? - Forwarded message from Kris Kennaway <[EMAIL PROTECTED]> - X-Spam-Checker-Version: SpamAssassin 3.1.6 (2006-10-03) on noel.decibel.org X-Spam-Level: X-Spam-Status: No, score=-0.9 required=5.0 tests=AWL,BAYES_50, FORGED_RCVD_HELO,SPF_PASS autolearn=no version=3.1.6 Date: Sun, 25 Feb 2007 19:22:35 -0500 From: Kris Kennaway <[EMAIL PROTECTED]> To: [EMAIL PROTECTED] User-Agent: Mutt/1.4.2.2i Cc: [EMAIL PROTECTED] Subject: Anyone interested in improving postgresql scaling? Precedence: list Errors-To: [EMAIL PROTECTED] If so, then your task is the following: Make SYSV semaphores less dumb about process wakeups. Currently whenever the semaphore state changes, all processes sleeping on the semaphore are woken, even if we only have released enough resources for one waiting process to claim. i.e. there is a thundering herd wakeup situation which destroys performance at high loads. Fixing this will involve replacing the wakeup() calls with appropriate amounts of wakeup_one(). Kris ___ freebsd-current@freebsd.org mailing list http://lists.freebsd.org/mailman/listinfo/freebsd-current To unsubscribe, send any mail to "[EMAIL PROTECTED]" - End forwarded message - -- Jim C. Nasby, Database Architect[EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 Windows: "Where do you want to go today?" Linux: "Where do you want to go tomorrow?" FreeBSD: "Are you guys coming, or what?" ---(end of broadcast)--- TIP 6: explain analyze is your friend
[PERFORM] [EMAIL PROTECTED]: Progress on scaling of FreeBSD on 8 CPU systems]
Thought I'd pass this along, since the Linux vs FreeBSD performance question comes up fairly regularly... BTW, I've already asked about benchmarking with PostgreSQL, so please don't go over there making trouble. :) - Forwarded message from Kris Kennaway <[EMAIL PROTECTED]> - X-Spam-Checker-Version: SpamAssassin 3.1.6 (2006-10-03) on noel.decibel.org X-Spam-Level: X-Spam-Status: No, score=-0.9 required=5.0 tests=AWL,BAYES_50, FORGED_RCVD_HELO,SPF_PASS autolearn=no version=3.1.6 Date: Sat, 24 Feb 2007 16:31:11 -0500 From: Kris Kennaway <[EMAIL PROTECTED]> To: [EMAIL PROTECTED], [EMAIL PROTECTED], [EMAIL PROTECTED] User-Agent: Mutt/1.4.2.2i Cc: Subject: Progress on scaling of FreeBSD on 8 CPU systems Precedence: list Errors-To: [EMAIL PROTECTED] Now that the goals of the SMPng project are complete, for the past year or more several of us have been working hard on profiling FreeBSD in various multiprocessor workloads, and looking for performance bottlenecks to be optimized. We have recently made significant progress on optimizing for MySQL running on an 8-core amd64 system. The graph of results may be found here: http://www.freebsd.org/~kris/scaling/scaling.png This shows the graph of MySQL transactions/second performed by a multi-threaded client workload against a local MySQL database with varying numbers of client threads, with identically configured FreeBSD and Linux systems on the same machine. The test was run on FreeBSD 7.0, with the latest version of the ULE 2.0 scheduler, the libthr threading library, and an uncommitted patch from Jeff Roberson [1] that addresses poor scalability of file descriptor locking (using a new sleepable mutex primitive); this patch is responsible for almost all of the performance and scaling improvements measured. It also includes some other patches (collected in my kris-contention p4 branch) that have been shown to help contention in MySQL workloads in the past (including a UNIX domain socket locking pushdown patch from Robert Watson), but these were shown to only give small individual contributions, with a cumulative effect on the order of 5-10%. With this configuration we are able to achieve performance that is consistent with Linux at peak (the graph shows Linux 2% faster, but this is commensurate with the margin of error coming from variance between runs, so more data is needed to distinguish them), with 8 client threads (=1 thread/CPU core), and significantly outperforms Linux at higher than peak loads, when running on the same hardware. Specifically, beyond 8 client threads FreeBSD has only minor performance degradation (an 8% drop from peak throughput at 8 clients to 20 clients), but Linux collapses immediately above 8 threads, and above 14 threads asymptotes to essentially single-threaded levels. At 20 clients FreeBSD outperforms Linux by a factor of 4. We see this result as part of the payoff we are seeing from the hard work of many developers over the past 7 years. In particular it is a significant validation of the SMP and locking strategies chosen for the FreeBSD kernel in the post-FreeBSD 4.x world. More configuration details and discussion about the benchmark may be found here: http://people.freebsd.org/~kris/scaling/mysql.html Kris - End forwarded message - -- Jim C. Nasby, Database Architect[EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 Windows: "Where do you want to go today?" Linux: "Where do you want to go tomorrow?" FreeBSD: "Are you guys coming, or what?" ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] long checkpoint_timeout
On Fri, Feb 23, 2007 at 12:23:08PM -0800, Jeff Davis wrote: > On Fri, 2007-02-23 at 14:02 -0600, Jim C. Nasby wrote: > > > say that checkpoints cause extra disk I/O. Is there a good way to > > > measure how much extra I/O (and WAL volume) is caused by the > > > checkpoints? Also, it would be good to know how much total I/O is caused > > > by a checkpoint so that I know if bgwriter is doing it's job. > > > > There's a patch someone just came up with that provides additional debug > > info about both bgwriter operation and checkpoints. I know it will at > > least tell you how much was written out by a checkpoint. > > Excellent, that would answer a lot of my questions. I did some brief > searching and nothing turned up. Do you have a link to the discussion or > the patch? http://archives.postgresql.org/pgsql-hackers/2007-02/msg01083.php -- Jim Nasby[EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) ---(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: [PERFORM] long checkpoint_timeout
On Fri, Feb 23, 2007 at 10:14:29AM -0800, Jeff Davis wrote: > The postgresql.conf says that the maximum checkpoint_timeout is 1 hour. > However, the following messages seem to suggest that it may be useful to > set the value significantly higher to reduce unnecessary WAL volume: > > http://archives.postgresql.org/pgsql-hackers/2006-10/msg00527.php > http://archives.postgresql.org/pgsql-hackers/2006-08/msg01190.php > > Is there a reason for the hour-long limit on checkpoint_timeout? Is > there a cost to doing so, aside from potentially longer recovery time? > > As I understand it, the background writer keeps the I/O more balanced > anyway, avoiding I/O spikes at checkpoint. > > I don't need the checkpoint time to be higher than 1 hour, but I'm > trying to understand the reasoning behind the limit and the implications > of a longer checkpoint_timeout. > > The docs here: > > http://www.postgresql.org/docs/current/static/wal-configuration.html > > say that checkpoints cause extra disk I/O. Is there a good way to > measure how much extra I/O (and WAL volume) is caused by the > checkpoints? Also, it would be good to know how much total I/O is caused > by a checkpoint so that I know if bgwriter is doing it's job. There's a patch someone just came up with that provides additional debug info about both bgwriter operation and checkpoints. I know it will at least tell you how much was written out by a checkpoint. -- Jim Nasby[EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] Recommended Initial Settings
If you're doing much updating at all you'll also want to bump up checkpoint_segments. I like setting checkpoint_warning just a bit under checkpoint_timeout as a way to monitor how often you're checkpointing due to running out of segments. With a large shared_buffers you'll likely need to make the bgwriter more aggressive as well (increase the max_pages numbers), though how important that is depends on how much updating you're doing. If you see periodic spikes in IO corresponding to checkpoints, that's an indication bgwriter isn't doing a good enough job. If everything ends up in memory, it might be good to decrease random_page_cost to 1 or something close to it; though the database should just rely on effective_cache to figure out that everything's in memory. If you're on pre-8.2, you'll want to cut all the autovacuum parameters in half, if you're using it. -- Jim Nasby[EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [PERFORM] Using the 8.2 autovacuum values with 8.1
On Fri, Feb 23, 2007 at 10:13:31AM +0100, Csaba Nagy wrote: > You likely don't need the nightly full vacuum run... we also do here a > nightly vacuum beside autovacuum, but not a full one, only for tables > which are big enough that we don't want autovacuum to touch them in high > business time but they have enough change that we want a vacuum on them > frequent enough. I discover them by checking the stats, for example: Something else I like doing is a periodic vacuumdb -av and capture the output. It's a good way to keep an eye on FSM utilization. Once you've got vacuuming under control you can probably just do that once a month or so. -- Jim Nasby[EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] Query Optimization
It's not necessarily the join order that's an issue; it could also be
due to the merge join that it does in the first case. I've also run into
situations where the cost estimate for a merge join is way off the mark.
Rather than forcing the join order, you might try setting
enable_mergejoin=false.
On Mon, Feb 19, 2007 at 06:03:22PM +0100, Reinhard Vicinus wrote:
> PostgreSQL version: 8.1.6
> OS: Debian etch
>
> The following query needs a lot of time because the query planner
> reorders the joins:
>
> select m.message_idnr, v.headervalue, n.headername from dbmail_messages m
> join dbmail_headervalue v ON v.physmessage_id=m.physmessage_id
> join dbmail_headername n ON v.headername_id=n.id
> where m.mailbox_idnr = 1022 AND message_idnr BETWEEN 698928 AND 1496874
>and lower(n.headername) IN
> ('from','to','cc','subject','date','message-id',
>'priority','x-priority','references','newsgroups','in-reply-to',
>'content-type','x-spam-status','x-spam-flag');
>
> If I prevent the query planner from reordering the joins with 'set
> join_collapse_limit=1;' then the same query is faster. At the end of the
> Mail is the output of a explain analyze for both cases.
>
> The statistics of the database are updated each night. Is there an error
> (in the statistical data) which is responsible for the reordering of the
> joins? And if not are there other alternatives for preventing join
> reordering?
>
> Thanks
> Reinhard
>
>
>
> Explain analyze with set join_collapse_limit=8:
>
> Merge Join (cost=388657.62..391332.20 rows=821 width=127) (actual
> time=82677.950..89103.192 rows=2699 loops=1)
> Merge Cond: ("outer".physmessage_id = "inner".physmessage_id)
> -> Sort (cost=2901.03..2902.61 rows=632 width=16) (actual
> time=247.238..247.578 rows=373 loops=1)
> Sort Key: m.physmessage_id
> -> Bitmap Heap Scan on dbmail_messages m (cost=9.16..2871.63
> rows=632 width=16) (actual time=38.072..246.509 rows=373 loops=1)
> Recheck Cond: (mailbox_idnr = 1022)
> Filter: ((message_idnr >= 698928) AND (message_idnr <=
> 1496874))
> -> Bitmap Index Scan on dbmail_messages_8
> (cost=0.00..9.16 rows=902 width=0) (actual time=25.561..25.561 rows=615
> loops=1)
> Index Cond: (mailbox_idnr = 1022)
> -> Sort (cost=385756.58..387089.35 rows=533108 width=127) (actual
> time=80156.731..85760.186 rows=3278076 loops=1)
> Sort Key: v.physmessage_id
> -> Hash Join (cost=51.00..285787.17 rows=533108 width=127)
> (actual time=34.519..28260.855 rows=3370242 loops=1)
> Hash Cond: ("outer".headername_id = "inner".id)
> -> Seq Scan on dbmail_headervalue v
> (cost=0.00..241200.39 rows=7840939 width=115) (actual
> time=0.006..16844.479 rows=7854485 loops=1)
> -> Hash (cost=50.72..50.72 rows=113 width=28) (actual
> time=34.493..34.493 rows=35 loops=1)
> -> Bitmap Heap Scan on dbmail_headername n
> (cost=28.44..50.72 rows=113 width=28) (actual time=11.796..34.437
> rows=35 loops=1)
> Recheck Cond: ((lower((headername)::text) =
> 'from'::text) OR (lower((headername)::text) = 'to'::text) OR
> (lower((headername)::text) = 'cc'::text) OR (lower((headername)::text) =
> 'subject'::text) OR (lower((headername)::text) = 'date'::text) OR
> (lower((headername)::text) = 'message-id'::text) OR
> (lower((headername)::text) = 'priority'::text) OR
> (lower((headername)::text) = 'x-priority'::text) OR
> (lower((headername)::text) = 'references'::text) OR
> (lower((headername)::text) = 'newsgroups'::text) OR
> (lower((headername)::text) = 'in-reply-to'::text) OR
> (lower((headername)::text) = 'content-type'::text) OR
> (lower((headername)::text) = 'x-spam-status'::text) OR (lower((hea
> dername)::text) = 'x-spam-flag'::text))
> -> BitmapOr (cost=28.44..28.44 rows=116
> width=0) (actual time=11.786..11.786 rows=0 loops=1)
> -> Bitmap Index Scan on
> dbmail_headername_lower_headername (cost=0.00..2.03 rows=8 width=0)
> (actual time=0.037..0.037 rows=3 loops=1)
> Index Cond:
> (lower((headername)::text) = 'from'::text)
> -> Bitmap Index Scan on
> dbmail_headername_lower_headername (cost=0.00..2.03 rows=8 width=0)
> (actual time=0.013..0.013 rows=3 loops=1)
> Index Cond:
> (lower((headername)::text) = 'to'::text)
> -> Bitmap Index Scan on
> dbmail_headername_lower_headername (cost=0.00..2.03 rows=8 width=0)
> (actual time=0.013..0.013 rows=3 loops=1)
> Index Cond:
> (lower((headername)::text) = 'cc'::text)
> -> Bitmap Index Scan on
> dbmail_headername_lower_headername (cost=0.00..2.03 rows=8 width=0)
> (actual time=0.014..0.014 rows=3 l
Re: [PERFORM] how to plan for vacuum?
Please cc the list so others can reply as well... On Thu, Jan 25, 2007 at 08:45:50AM +0100, Tomas Vondra wrote: > > On Wed, Jan 24, 2007 at 02:37:44PM +0900, Galy Lee wrote: > >> 1. How do we know if autovacuum is enough for my application, or should > >> I setup a vacuum manually from cron for my application? > > > > Generally I trust autovac unless there's some tables where it's critical > > that they be vacuumed frequently, such as a queue table or a web session > > table. > > You can tune thresholds and scale factors for that particular table > using pg_autovacuum. If you lower them appropriately, the vacuum will be > fired more often for that table - but don't lower them too much, just go > step by step until you reach values that are fine for you. That doesn't work well if autovac gets tied up vacuuming a very large table. Granted, when that happens there are considerations about the long-running vacuum transaction (prior to 8.2), but in many systems you'll still get some use out of other vacuums. -- Jim C. Nasby, Database Architect [EMAIL PROTECTED] 512.569.9461 (cell) http://jim.nasby.net ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] [PERFORM] how to plan for vacuum?
On Thu, Jan 25, 2007 at 12:52:02AM -0300, Alvaro Herrera wrote: > Jim C. Nasby wrote: > > > I'll generally start with a cost delay of 20ms and adjust based on IO > > utilization. > > I've been considering set a default autovacuum cost delay to 10ms; does > this sound reasonable? For a lightly loaded system, sure. For a heavier load that might be too much, but of course that's very dependent on not only your hardware, but how much you want vacuum to interfere with normal operations. Though, I'd say as a default it's probably better to be more aggressive rather than less. Also, it might be better to only set autovac_cost_delay by default; presumably if someone's running vacuum by hand they want it done pronto. -- Jim Nasby[EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) ---(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: [PERFORM] how to plan for vacuum?
On Wed, Jan 24, 2007 at 02:37:44PM +0900, Galy Lee wrote: > 1. How do we know if autovacuum is enough for my application, or should > I setup a vacuum manually from cron for my application? Generally I trust autovac unless there's some tables where it's critical that they be vacuumed frequently, such as a queue table or a web session table. > 2. How to set the GUC parameters for autovacuum? > There are two sets of parameters for autovacuum: > - vacuum threshold and scale factor (500/0.2) > ?$B!! - analyze threshold and scale factor(250/0.1) > Is there any guideline to set these parameters? When does it need to > change the default values? I find those are generally pretty good starting points; just bear in mind that it means 20% dead space. > 3. How to tune cost-based delay vacuum? > I had searched in performance list; it seems that most of the practices > are based on experience / trial-and-error approach to meet the > requirement of disk utilization or CPU utilization. Is there any other > guild line to set them? Unless you have a means for the database to monitor IO usage on it's own, I don't know that we have a choice... I'll generally start with a cost delay of 20ms and adjust based on IO utilization. -- Jim Nasby[EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] [PERFORM] unusual performance for vac following 8.2 upgrade
On Thu, Jan 11, 2007 at 04:49:28PM -0300, Alvaro Herrera wrote: > Tom Lane wrote: > > > What I think we need to do about this is > > > > (1) fix pgstat_vacuum_tabstats to have non-O(N^2) behavior; I'm thinking > > of using a hash table for the OIDs instead of a linear list. Should be > > a pretty small change; I'll work on it today. > > > > (2) Reconsider whether last-vacuum-time should be sent to the collector > > unconditionally. > > (2) seems a perfectly reasonably answer, but ISTM (1) would be good to > have anyway (at least in HEAD). Actually, I'd rather see the impact #1 has before adding #2... If #1 means we're good for even someone with 10M relations, I don't see much point in #2. BTW, we're now starting to see more users with a large number of relations, thanks to partitioning. It would probably be wise to expand test coverage for that case, especially when it comes to performance. -- Jim Nasby[EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] [PERFORM] table partioning performance
On Thu, Jan 11, 2007 at 12:15:50PM +, Simon Riggs wrote: > On Wed, 2007-01-10 at 16:00 -0500, Steven Flatt wrote: > > On 1/9/07, Simon Riggs <[EMAIL PROTECTED]> wrote: > > If you are doing date range partitioning it should be fairly > > simple to > > load data into the latest table directly. That was the way I > > originally > > intended for it to be used. The rules approach isn't something > > I'd > > recommend as a bulk loading option and its a lot more complex > > anyway. > > The problem we have with blindly loading all data into the latest > > table is that some data (< 5%, possibly even much less) is actually > > delivered "late" and belongs in earlier partitions. So we still > > needed the ability to send data to an arbitrary partition. > > Yes, understand the problem. > > COPY is always going to be faster than INSERTs anyhow and COPY doesn't > allow views, nor utilise rules. You can set up a client-side program to > pre-qualify the data and feed it to multiple simultaneous COPY commands, > as the best current way to handle this. > > -- > Next section aimed at pgsql-hackers, relates directly to above: I'm wondering if you see any issues with COPYing into a partitioned table that's using triggers instead of rules to direct data to the appropriate tables? BTW, I think improved copy error handling would be great, and might perform better than triggers, once we have it... -- Jim Nasby[EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) ---(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: [PERFORM] High inserts, bulk deletes - autovacuum vs scheduled vacuum
On Thu, Jan 11, 2007 at 12:10:34AM -0300, Alvaro Herrera wrote: > Jim C. Nasby wrote: > > > > Is the best way to do that usually to lower the scale factors? Is it > > > ever a good approach to lower the scale factor to zero and just set the > > > thresholds to a pure number of rows? (when setting it for a specific > > > table) > > > > The problem is what happens if autovac goes off and starts vacuuming > > some large table? While that's going on your queue table is sitting > > there bloating. If you have a separate cronjob to handle the queue > > table, it'll stay small, especially in 8.2. > > You mean "at least in 8.2". In previous releases, you could vacuum > that queue table until you were blue on the face, but it would achieve > nothing because it would consider that the dead tuples were visible to a > running transaction: that running the vacuum on the large table. This > is an annoyance that was fixed in 8.2. True, but in many environments there are other transactions that run long enough that additional vacuums while a long vacuum was running would still help. -- Jim Nasby[EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) ---(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: [PERFORM] High inserts, bulk deletes - autovacuum vs scheduled vacuum
On Wed, Jan 10, 2007 at 04:48:42PM -0500, Jeremy Haile wrote: > > BTW, that's the default values for analyze... the defaults for vacuum > > are 2x that. > > Yeah - I was actually more concerned that tables would need to be > analyzed more often than I was about vacuuming too often, so I used > analyze as the example. Since my app is inserting constantly throughout > the day and querying for "recent" data - I want to make sure the query > planner realizes that there are lots of rows with new timestamps on > them. In other words, if I run a query "select * from mytable where > timestamp > '9:00am'" - I want to make sure it hasn't been a day since > the table was analyzed, so the planner thinks there are zero rows > greater than 9:00am today. Well, analyze is pretty cheap. At most it'll read only 30,000 pages, which shouldn't take terribly long on a decent system. So you can be a lot more aggressive with it. > > What's more important > > is to make sure critical tables (such as queue tables) are getting > > vacuumed frequently so that they stay small. > > Is the best way to do that usually to lower the scale factors? Is it > ever a good approach to lower the scale factor to zero and just set the > thresholds to a pure number of rows? (when setting it for a specific > table) The problem is what happens if autovac goes off and starts vacuuming some large table? While that's going on your queue table is sitting there bloating. If you have a separate cronjob to handle the queue table, it'll stay small, especially in 8.2. -- Jim Nasby[EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] table partioning performance
On Wed, Jan 10, 2007 at 04:39:06PM -0500, Steven Flatt wrote: > On 1/10/07, Jim C. Nasby <[EMAIL PROTECTED]> wrote: > > > >Except for the simplest partitioning cases, you'll be much better off > >using a trigger on the parent table to direct inserts/updates/deletes to > >the children. As a bonus, using a trigger makes it a lot more realistic > >to deal with an update moving data between partitions. > > > In our application, data is never moved between partitions. > > The problem I found with triggers is the non-robustness of the PLpgSQL > record data type. For example, in an "on insert" trigger, I can't determine > the fields of the NEW record unless I hard code the column names into the > trigger. This makes it hard to write a generic trigger, which I can use for > all our partitioned tables. It would have been somewhat of a pain to write > a separate trigger for each of our partitioned tables. > > For that and other reasons, we moved some of the insert logic up to the > application level in our product. Yeah, I think the key there would be to produce a function that wrote the function for you. -- Jim Nasby[EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [PERFORM] High inserts, bulk deletes - autovacuum vs scheduled vacuum
Please cc the list so others can help and learn. On Wed, Jan 10, 2007 at 03:43:00PM -0500, Jeremy Haile wrote: > > I'd suggest trying autovacuum and see how it does (though you might want > > to tune it to be more or less aggressive, and you'll probably want to > > enable the cost delay). > > What are some decent default values for the cost delay vacuum settings? > I haven't used these before. I find that simply setting vacuum_cost_delay to 20 is generally a good starting point. I'll usually do that and then run a vacuum while watching disk activity; I try and tune it so that the disk is ~90% utilized with vacuum running. That allows a safety margin without stretching vacuums out forever. > Also - do the default autovacuum settings make sense for tables on the > scale of 10 million rows? For example, using the defaults it would > require about a million rows (250 + 0.1 * 10 million) to be > inserted/updated/deleted before analyzing - which seems high. (about 2 > million for vacuum) Or am I overestimating how often I would need to > vacuum/analyze these tables? Depends on your application... the way I look at it is that a setting of 0.1 means 10% dead space in the table. While 5% or 1% would be better, you hit a point of diminishing returns since you have to read the entire table and it's indexes to vacuum it. BTW, that's the default values for analyze... the defaults for vacuum are 2x that. > Do most people use the default autovacuum settings successfully, or are > they usually modified? I generally use the 8.2 defaults (which are much better than the 8.1 defaults) unless I'm really trying to tune things. What's more important is to make sure critical tables (such as queue tables) are getting vacuumed frequently so that they stay small. (Of course you also need to ensure there's no long running transactions). -- Jim C. Nasby, Database Architect [EMAIL PROTECTED] 512.569.9461 (cell) http://jim.nasby.net ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] Partitioning
On Wed, Jan 10, 2007 at 03:28:00PM -0500, Jeremy Haile wrote: > This seems so much more intuitive and simpler than what is required to > set it up in PostgreSQL. Does PostgreSQL's approach to table > partitioning have any advantage over MySQL? Is a "nicer" syntax planned > for Postgres? The focus was to get the base functionality working, and working correctly. Another consideration is that there's multiple ways to accomplish the partitioning; exposing the basic functionality without enforcing a given interface provides more flexibility (ie: it appears that you can't do list partitioning with MySQL, while you can with PostgreSQL). -- Jim Nasby[EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) ---(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: [PERFORM] High inserts, bulk deletes - autovacuum vs scheduled vacuum
On Tue, Jan 09, 2007 at 12:26:41PM -0500, Jeremy Haile wrote: > I am developing an application that has very predictable database > operations: > -inserts several thousand rows into 3 tables every 5 minutes. (table > contain around 10 million rows each) > -truncates and rebuilds aggregate tables of this data every 5 minutes. > (several thousand rows each) > -regular reads of aggregate table and sometimes large tables by user > interaction > -every night, hundreds of thousands of rows are deleted from these 3 > tables (old data) > -20-30 other tables get inserted/updated slowly throughout the day > > In order to optimize performance of the inserts, I disabled > autovacuum/row-level stats and instead run "vacuum analyze" on the whole > DB every hour. However this operation takes around 20 minutes of each > hour. This means that the database is involved in vacuum/analyzing > tables 33% of the time. > > I'd like any performance advice, but my main concern is the amount of > time vacuum/analyze runs and its possible impact on the overall DB > performance. Thanks! If much of the data in the database isn't changing that often, then why continually re-vacuum the whole thing? I'd suggest trying autovacuum and see how it does (though you might want to tune it to be more or less aggressive, and you'll probably want to enable the cost delay). The only cases where manual vacuum makes sense to me is if you've got a defined slow period and vacuuming during that slow period is still frequent enough to keep up with demand, or if you've got tables that have a very high churn rate and need to be kept small. In the later case, I'll usually setup a cronjob to vacuum those tables once a minute with no cost delay. I'm sure there might be some other cases where not using autovac might make sense, but generally I'd much rather let autovac worry about this so I don't have to. > I am running 8.2 (will be 8.2.1 soon). The box is Windows with 2GB RAM > connected to a SAN over fiber. The data and pg_xlog are on separate > partitions. > > Modified configuration: > effective_cache_size = 1000MB > random_page_cost = 3 > default_statistics_target = 50 > maintenance_work_mem = 256MB > shared_buffers = 400MB > temp_buffers = 10MB > work_mem = 10MB > max_fsm_pages = 150 One other useful manual vacuum to consider is running vacuumdb -av periodically (say, once a month) and looking at the last few lines of output. That will give you a good idea on how large you should set max_fsm_pages. Running the output of vacuumdb -av through pgFouine will give you other useful data. > checkpoint_segments = 30 > stats_row_level = off > stats_start_collector = off Unless you're really trying to get the last ounce of performance out, it's probably not worth turning those stats settings off. -- Jim Nasby[EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) ---(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: [PERFORM] High update activity, PostgreSQL vs BigDBMS
On Tue, Jan 09, 2007 at 09:10:51AM -0800, Jeff Frost wrote: > On Tue, 9 Jan 2007, Jim C. Nasby wrote: > > >On Thu, Dec 28, 2006 at 02:15:31PM -0800, Jeff Frost wrote: > >>When benchmarking various options for a new PG server at one of my > >>clients, > >>I tried ext2 and ext3 (data=writeback) for the WAL and it appeared to be > >>fastest to have ext2 for the WAL. The winning time was 157m46.713s for > >>ext2, 159m47.098s for combined ext3 data/xlog and 158m25.822s for ext3 > >>data=writeback. This was on an 8x150GB Raptor RAID10 on an Areca 1130 w/ > >>1GB BBU cache. This config benched out faster than a 6disk RAID10 + 2 > >>disk > >>RAID1 for those of you who have been wondering if the BBU write back cache > >>mitigates the need for separate WAL (at least on this workload). Those > >>are > >>the fastest times for each config, but ext2 WAL was always faster than the > >>other two options. I didn't test any other filesystems in this go around. > > > >Uh, if I'm reading this correctly, you're saying that WAL on a separate > >ext2 vs. one big ext3 with data=writeback saved ~39 seconds out of > >~158.5 minutes, or 0.4%? Is that even above the noise for your > >measurements? I suspect the phase of the moon might play a bigger role > >;P > > That's what I thought too...cept I ran it 20 times and ext2 won by that > margin every time, so it was quite repeatable. :-/ Even so, you've got to really be hunting for performance to go through the hassle of different filesystems just to gain 0.4%... :) -- Jim Nasby[EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] table partioning performance
On Mon, Jan 08, 2007 at 03:02:24PM -0500, Steven Flatt wrote: > We use partitioned tables extensively and we have observed linear > performance degradation on inserts as the number of rules on the master > table grows (i.e. number of rules = number of partitions). We had to come > up with a solution that didn't have a rule per partition on the master > table. Just wondering if you are observing the same thing. Except for the simplest partitioning cases, you'll be much better off using a trigger on the parent table to direct inserts/updates/deletes to the children. As a bonus, using a trigger makes it a lot more realistic to deal with an update moving data between partitions. -- Jim Nasby[EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [PERFORM] Partitioning
BTW, someone coming up with a set of functions to handle partitioning for the general 'partition by time' case would make a GREAT project on pgFoundry. On Fri, Jan 05, 2007 at 12:47:08PM +0100, Mikael Carneholm wrote: > Take a look at the set of partitioning functions I wrote shortly after > the 8.1 release: > > http://www.studenter.hb.se/~arch/files/part_functions.sql > > You could probably work something out using those functions (as-is, or > as inspiration) together with pgAgent > (http://www.pgadmin.org/docs/1.4/pgagent.html) > > /Mikael > > > -Original Message- > > From: [EMAIL PROTECTED] > [mailto:pgsql-performance- > > [EMAIL PROTECTED] On Behalf Of Arnau > > Sent: den 5 januari 2007 12:02 > > To: pgsql-performance@postgresql.org > > Subject: [PERFORM] Partitioning > > > > Hi all, > > > >I'm not sure if this question fits in the topic of this list. > > > >I'm interested in partitioning and it's the first time I'd use it. > > There is an issue I don't know how you handle it. Lets say I'm > > interested in store monthly based statistical data like the example of > > http://www.postgresql.org/docs/8.2/static/ddl-partitioning.html. What > I > > don't like of this approach is that the monthly tables, rules... must > be > > created "manually" or at least I haven't found any other option. > > > >My question is how do you manage this? do you have a cron task that > > creates automatically these monthly elements (tables, rules, ... ) or > > there is another approach that doesn't require external things like > cron > > only PostgreSQL. > > -- > > Arnau > > > > ---(end of > broadcast)--- > > TIP 5: don't forget to increase your free space map settings > > > ---(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 > -- Jim Nasby[EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) ---(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: [PERFORM] PostgreSQL to host e-mail?
On Fri, Jan 05, 2007 at 01:15:44PM -0500, Reid Thompson wrote: > On Fri, 2007-01-05 at 04:10 +0100, Grega Bremec wrote: > > he main reason I'm writing this mail though, is to suggest you take a > > look > > at Oryx, http://www.oryx.com/; They used to have this product called > > Mailstore, which was designed to be a mail store using PostgreSQL as a > > backend, and has since evolved to a bit more than just that, it seems. > > Perhaps it could be of help to you while building your system, and I'm > > sure > > the people at Oryx will be glad to hear from you while, and after > > you've > > built your system. > > > > Kind regards, > > -- > > ~Grega Bremec > re above... > http://www.archiveopteryx.org/1.10.html You should also look at http://dbmail.org/ , which runs on several databases (PostgreSQL included). -- Jim Nasby[EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] Performance of PostgreSQL on Windows vs Linux
On Wed, Jan 03, 2007 at 12:24:24PM -0500, Jeremy Haile wrote: > I am sure that this has been discussed before, but I can't seem to find > any recent posts. (I am running PostgreSQL 8.2) > > I have always ran PostgreSQL on Linux in the past, but the company I am > currently working for uses Windows on all of their servers. I don't > have the luxury right now of running my own benchmarks on the two OSes, > but wanted to know if anyone else has done a performance comparison. Is > there any significant differences? One thing to consider... I've seen a case or two where pgbench running on windows with HyperThreading enabled was actually faster than with it turned off. (General experience has been that HT hurts PostgreSQL). I suspect that the windows kernel may have features that allow it to better utilize HT than linux. Of course if you don't have HT... it doesn't matter. :) -- Jim Nasby[EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) ---(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: [PERFORM] More 8.2 client issues (Was: [Slow dump?)
On Wed, Jan 03, 2007 at 11:56:20AM -0500, Tom Lane wrote: > Erik Jones <[EMAIL PROTECTED]> writes: > > Tom Lane wrote: > >> I could see this taking an unreasonable amount of time if you had a huge > >> number of pg_class rows or a very long search_path --- is your database > >> at all out of the ordinary in those ways? > >> > > Well, running "select count(*) from pg_class;" returns 524699 rows > > Ouch. > > > our search path is the default. I'd also like to reiterate that \d > > pg_class returns instantly when run from the 8.1.4 psql client connected > > to the 8.2 db. > > I think I know where the problem is: would you compare timing of > > select * from pg_class where c.relname ~ '^(pg_class)$'; > > select * from pg_class where c.relname ~ '^pg_class$'; > > Recent versions of psql put parentheses into the regex pattern for > safety in case it's got "|", but I just realized that that probably > confuses the optimizer's check for an indexable regex :-( > > However, this only explains slowdown in psql's \d commands, which > wasn't your original complaint ... On the other hand, with 500k relations pg_dump is presumably going to be doing a lot of querying of the catalog tables, so if it uses similar queries... -- Jim Nasby[EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) ---(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: [PERFORM] performance implications of binary placement
Are you 100% certain that both builds are using all the same libraries? And to be an apples-apples comparison, you really need to ensure that the datadir is on the same filesystem in both cases (that's the first thing I'd check). Also, that pg_index... error sounds like the second build has been corrupted. On Tue, Dec 26, 2006 at 03:37:47PM -0500, Bob Dusek wrote: > Hello all, > > I've been running performance tests on various incantations of Postgres > on/off for a month or so. And, I've just come across some unexpected > results. > > When I start my Postgres build as such: > > # (Scenario 1) > > ./configure --prefix=/usr --libdir=/usr/lib --bindir=/usr/bin > --includedir=/usr/include/pgsql --datadir=/usr/share/postgresql > --mandir=/usr/share/man --with-docdir=/usr/share/doc/packages > --disable-rpath --enable-thread-safety --enable-integer-datetimes > --without-python --without-perl --without-tcl --without-tk > > It performs significantly worse than when I start my build like this: > > # (Scenario 2) > > ./configure --disable-rpath --enable-thread-safety > --enable-integer-datetimes --without-python --without-perl --without-tcl > --without-tk > > Note: the only differences are that "Scenario 1" includes these > options: > > --prefix=/usr --libdir=/usr/lib --bindir=/usr/bin > --includedir=/usr/include/pgsql --datadir=/usr/share/postgresql > --mandir=/usr/share/man --with-docdir=/usr/share/doc/packages > > And, to be clear, "Scenario 1" performs worse than "Scenario 2". Simple > insert statements are taking significantly longer. > > I did not expect to see a performance hit with these options, especially > since "/usr/" on the test machine is mounted as its own partition, and > in both cases, all of the binaries, include files, etc. are in that > partition. > > Has anyone seen this before? Are hard drive mechanics the only thing in > play here? > > The only difference I'm seeing in logging between the two versions is > that Scenario 2 has several of this message littered throughout the > logfile: > > ERROR: could not open relation "pg_index_indexrelid_index": No such file > or directory > > But, that doesn't seem to be effecting functionality or performance > (especially considering the fact that the logfile that contains that > message is part of the test that is performing better). > > We're using Postgres 7.4.8, building from the SLES9 Postgres 7.4.8 > source rpm. > > Thanks for any help you can provide. I can provide more detail if > needed. > > Thanks again, > > Bob > > > ---(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 > -- Jim Nasby[EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] Postgresql Configutation and overflow
On Thu, Dec 28, 2006 at 10:35:29PM -0500, Dave Cramer wrote: > start with 25% of your 12G as shared buffers, and 75% of 12G for > effective cache I'm curious... why leave 3G for the kernel? Seems like overkill... Granted, as long as you're in the ballpark on effective_cache_size that's all that matters... > You can go higher for shared buffers, but only do so with testing. > > Dave > > > >Features: > > > >- 4 Processsors Intel Xeon Dual 3.0Ghz > >- 12 GB RAM > >- 2 discos en RAID 1 for OS > >- 4 discs RAID 5 for DB > >- S.O Slackware 11.0 Linux 2.6.17.7 > >- Postgres 8.1.4 > > > > > >=In internet i found this: > > > >Tuning PostgreSQL for performance > >2 Some basic parameters > >2.1 Shared buffers > > > ># Start at 4MB (512) for a workstation > ># Medium size data set and 256-512MB available RAM: 16-32MB > >(2048-4096) > ># Large dataset and lots of available RAM (1-4GB): 64-256MB > >(8192-32768) > >== > > > > > >My postgresql.conf configuration is: > > > >#- > >-- > ># FILE LOCATIONS > >#- > >-- > > > ># The default values of these variables are driven from the -D > >command line > ># switch or PGDATA environment variable, represented here as > >ConfigDir. > > > >#data_directory = 'ConfigDir'# use data in another directory > >#hba_file = 'ConfigDir/pg_hba.conf'# host-based authentication > >file > >#ident_file = 'ConfigDir/pg_ident.conf'# IDENT configuration file > > > ># If external_pid_file is not explicitly set, no extra pid file is > >written. > >#external_pid_file = '(none)'# write an extra pid file > > > > > >#- > >-- > ># CONNECTIONS AND AUTHENTICATION > >#- > >-- > > > ># - Connection Settings - > > > >listen_addresses = '*'# what IP address(es) to listen on; > ># comma-separated list of addresses; > ># defaults to 'localhost', '*' = all > >port = 5432 > >max_connections = 3000 > ># note: increasing max_connections costs ~400 bytes of shared > >memory per > ># connection slot, plus lock space (see > >max_locks_per_transaction). You > ># might also need to raise shared_buffers to support more connections. > >#superuser_reserved_connections = 2 > >#unix_socket_directory = '' > >#unix_socket_group = '' > >#unix_socket_permissions = 0777# octal > >#bonjour_name = ''# defaults to the computer name > > > > > > > >#- > >-- > ># RESOURCE USAGE (except WAL) > >#- > >-- > > > ># - Memory - > > > > > >shared_buffers = 81920# min 16 or max_connections*2, > >8KB each > >temp_buffers = 5000# min 100, 8KB each > >max_prepared_transactions = 1000# can be 0 or more > > > ># note: increasing max_prepared_transactions costs ~600 bytes of > >shared memory > > > ># per transaction slot, plus lock space (see > >max_locks_per_transaction). > >work_mem = 10240# min 64, size in KB > >maintenance_work_mem = 253952# min 1024, size in KB > >max_stack_depth = 4096# min 100, size in KB > > > ># - Free Space Map - > > > >#max_fsm_pages = 2# min max_fsm_relations*16, 6 > >bytes each > >#max_fsm_relations = 1000# min 100, ~70 bytes each > > > ># - Kernel Resource Usage - > > > >#max_files_per_process = 1000# min 25 > >#preload_libraries = '' > > > ># - Cost-Based Vacuum Delay - > > > >#vacuum_cost_delay = 0# 0-1000 milliseconds > >#vacuum_cost_page_hit = 1# 0-1 credits > >#vacuum_cost_page_miss = 10# 0-1 credits > >#vacuum_cost_page_dirty = 20# 0-1 credits > >#vacuum_cost_limit = 200# 0-1 credits > > > ># - Background writer - > > > >#bgwriter_delay = 200# 10-1 milliseconds between > >rounds > >#bgwriter_lru_percent = 1.0# 0-100% of LRU buffers scanned/ > >round > >#bgwriter_lru_maxpages = 5# 0-1000 buffers max written/round > >#bgwriter_all_percent = 0.333# 0-100% of all buffers > >scanned/round > >#bgwriter_all_maxpages = 5# 0-1000 buffers max written/round > > > > > >#- > >-- > ># WRITE AHEAD LOG > >#- > >-- > > > ># - Settings - > > > >#fsync = on# turns forced synchronization on or off > >#wal_sync_method = fsync# the default is the first option > ># supported by the operating system: > ># open_datasync > ># fdatasync > >
Re: [PERFORM] High update activity, PostgreSQL vs BigDBMS
On Sun, Jan 07, 2007 at 11:26:01PM -0500, Guy Rouillier wrote: > Ok, I ran with the settings below, but with > > shared_buffers=768MB > effective_cache_size=2048MB > fsync=on > > This run took 29000 seconds. I'm beginning to think configuration > changes are not going to buy significant additional improvement. Time > to look at the app implementation. Very likely, but one thing I haven't seen mentioned is what your bottleneck actually is. Is it CPU? Disk? Something else (ie: neither CPU or IO is at 100%). Additionally, since you have multiple arrays, are you sure they're being utilized equally? Having something like MRTG or cricket will make your tuning much easier. Unlike Oracle, PostgreSQL has no ability to avoid hitting the base table even if an index could cover a query... so compared to Oracle you'll need to dedicate a lot more IO to the base tables. Search around for PostgreSQL on Solaris tuning tips... there's some OS-settings that can make a huge difference. In particular, by default Solaris will only dedicate a fraction of memory to disk caching. That won't bother Oracle much but it's a big deal to PostgreSQL. I think there's some other relevant OS parameters as well. For vacuum, you're going to need to tune the vacuum_cost_* settings so that you can balance the IO impact of vacuums with the need to complete the vacuums in a reasonable time. You'll find this easiest to tune by running manual vacuums and monitoring IO activity. You'll also likely need to tune the bgwriter so that checkpoints aren't killing you. If you're targeting a checkpoint every 5 minutes you'll need to at least up bgwriter_all_maxpages to shared_buffers (in pages) / 300 / 5. I'd round up a bit. As with everything, you'll need to tweak your values from there. If you're using stock bgwriter settings then you'll probably be seeing a big IO spike every time a checkpoint occurs. Speaking of which... how often are checkpoints? If you can tolerate 5 minutes of recovery time, (the default checkpoint_timeout), I suggest setting checkpount_warning to 290 seconds or so; that way if you're getting checkpoints much more often than every 5 minutes you'll be able to see in the logs. Speaking of which, going longer between checkpoints will likely help performance, if you can tolerate longer recovery times. I haven't actually tested the correlation, but I would expect recovery to complete in a maximum of checkpount_timeout seconds. If you can tolerate being in recovery mode for 10 minutes after a crash, try bumping checkpount_timeout, checkpount_warning and checkpoint_segments and see what it does for performance (if you do that you'll also want to tweak bgwriter further... in this case increasing bgwriter_delay would be easiest). Given what sounds like decent IO capabilities, you'll likely get better query plans from decreasing random_page_cost, probably to between 2 and 3. Speaking of IO... if you can switch to RAID10 you'll likely get better preformance since your write load is so heavy. Normally RAID5 is a complete performance killer as soon as you're doing much writing, but I'm guessing that those nice expensive Sun arrays are better than most RAID controllers. All that being said... generally the biggest tuning impact to be had for any database environment is in how the application is using the database. A few sub-optimal things in the application/database design could easily erase every gain you'll get from all your tuning. I suggest running EXPLAIN ANALYZE on the queries that are run most often and seeing what that shows. -- Jim Nasby[EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] High update activity, PostgreSQL vs BigDBMS
On Thu, Dec 28, 2006 at 02:15:31PM -0800, Jeff Frost wrote: > When benchmarking various options for a new PG server at one of my clients, > I tried ext2 and ext3 (data=writeback) for the WAL and it appeared to be > fastest to have ext2 for the WAL. The winning time was 157m46.713s for > ext2, 159m47.098s for combined ext3 data/xlog and 158m25.822s for ext3 > data=writeback. This was on an 8x150GB Raptor RAID10 on an Areca 1130 w/ > 1GB BBU cache. This config benched out faster than a 6disk RAID10 + 2 disk > RAID1 for those of you who have been wondering if the BBU write back cache > mitigates the need for separate WAL (at least on this workload). Those are > the fastest times for each config, but ext2 WAL was always faster than the > other two options. I didn't test any other filesystems in this go around. Uh, if I'm reading this correctly, you're saying that WAL on a separate ext2 vs. one big ext3 with data=writeback saved ~39 seconds out of ~158.5 minutes, or 0.4%? Is that even above the noise for your measurements? I suspect the phase of the moon might play a bigger role ;P -- Jim Nasby[EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [PERFORM] How to determine if my setting for shared_buffers is too high?
Remember that as you increase shared_buffers you might need to make the bgwriter more aggressive too. On Thu, Dec 07, 2006 at 11:42:39AM -0500, Bill Moran wrote: > > I'm gearing up to do some serious investigation into performance for > PostgreSQL with regard to our application. I have two issues that I've > questions about, and I'll address them in two seperate emails. > > This one regards tuning shared_buffers. > > I believe I have a good way to monitor database activity and tell when > a database grows large enough that it would benefit from more > shared_buffers: if I monitor the blks_read column of pg_stat_database, > it should increase very slowly if there is enough shared_buffer > space. When shared buffer space runs out, more disk read requests > will be required and this number will begin to climb. > > If anyone sees a flaw in this approach, I'd be interested to hear it. > > The other tuning issue with shared_buffers is how to tell if I'm > allocating too much. For example, if I allocate 1G of RAM to > shared buffers, and the entire database can fit in 100M, that 900M > might be better used as work_mem, or something else. > > I haven't been able to find anything regarding how much of the > shared buffer space PostgreSQL is actually using, as opposed to > simply allocating. > > -- > Bill Moran > Collaborative Fusion Inc. > > [EMAIL PROTECTED] > Phone: 412-422-3463x4023 > > ---(end of broadcast)--- > TIP 5: don't forget to increase your free space map settings > -- Jim Nasby[EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) ---(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: [PERFORM] Configuration settings for 32GB RAM server
On Mon, Dec 04, 2006 at 09:42:57AM -0800, Jeff Davis wrote: > > fsm_pages = 200,000 ??? Based this on some statistics about the number > > of pages freed from a vacuum on older server. Not sure if its fair > > to calculate this based on vacuum stats of 7.3.4 server? > > > > Might as well make it a higher number because you have a lot of RAM > anyway. It's better than running out of space in the FSM, because to > increase that setting you need to restart the daemon. Increasing this by > 1 only uses 6 bytes. That means you could set it to 10 times the number > you currently have, and it would still be insignificant. You can also run vacuumdb -av and look at the last few lines to see what it says you need. Or you can get that info out of contrib/pg_freespacemap. -- Jim Nasby[EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] 8.2rc1 (much) slower than 8.2dev?
On Mon, Dec 04, 2006 at 05:41:14PM +0100, Arjen van der Meijden wrote: > Since I'd rather not send the entire list of queries to the entire > world, is it OK to send both explain analyze-files to you off list? Can you post them on the web somewhere so everyone can look at them? Also, are you looking at EXPLAIN or EXPLAIN ANALYZE? -- Jim Nasby[EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] shared_buffers > 284263 on OS X
On Mon, Nov 27, 2006 at 07:23:47AM +, Brian Wipf wrote: > On 26-Nov-06, at 11:25 PM, Jim C. Nasby wrote: > >On Sat, Nov 18, 2006 at 08:13:26PM -0700, Brian Wipf wrote: > >>It certainly is unfortunate if Guido's right and this is an upper > >>limit for OS X. The performance benefit of having high shared_buffers > >>on our mostly read database is remarkable. > > > >Got any data about that you can share? People have been wondering > >about > >cases where drastically increasing shared_buffers makes a difference. > > Unfortunately, there are more differences than just the > shared_buffers setting in production right now; it's a completely > different set up, so the numbers I have to compare against aren't > particularly useful. > > When I get the chance, I will try to post data that shows the benefit > of having a higher value of shared_buffers for our usage pattern > (with all other settings being constant -- well, except maybe > effective_cache_size). Basically, in our current configuration, we > can cache all of the data we care about 99% of the time in about 3GB > of shared_buffers. Having shared_buffers set to 512MB as it was > originally, we were needlessly going to disk all of the time. Disk or to the kernel cache? -- Jim Nasby[EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) ---(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: [PERFORM] When to vacuum a table?
On Sun, Nov 26, 2006 at 12:24:17PM +0100, Joost Kraaijeveld wrote: > Hi, > > Are there guidelines (or any empirical data) available how to determine > how often a table should be vacuumed for optimum performance or is this > an experience / trial-and-error thing? Most of the time I just turn autovac on, set the scale factors to 0.2/0.1 and the thresholds to 300/200 and turn on vacuum_cost_delay (usually set to 20). That's a pretty decent setup for most applications. It also doesn't hurt to run a periodic vacuumdb -av and look at the tail end of it's output to make sure you have adequate FSM settings. The exception to that rule is for tables that are very small and have a lot of churn; I'll vacuum those by hand very frequently (every 60 seconds or better). -- Jim Nasby[EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) ---(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: [PERFORM] Priority to a mission critical transaction
On Thu, Nov 23, 2006 at 03:40:15PM -0500, Brad Nicholson wrote: > On Tue, 2006-11-21 at 21:43 -0200, Carlos H. Reimer wrote: > > Hi, > > > > We have an application that is mission critical, normally very fast, > > but when an I/O or CPU bound transaction appears, the mission critical > > application suffers. Is there a way go give some kind of priority to > > this kind of application? > > Reimer > > > Not that I'm aware of. Depending on what the problems transactions are, > setting up a replica on a separate machine and running those > transactions against the replica might be the solution. The BizGres project has been working on resource quotas, which might eventually evolve to what you're looking for. -- Jim Nasby[EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] Postgres server crash
On Sat, Nov 18, 2006 at 05:28:46PM -0800, Richard Troy wrote: > ...I read a large number of articles on this subject and am > absolutely dumbfounded by the -ahem- idiots who think killing a random > process is an appropriate action. I'm just taking their word for it that > there's some kind of impossibility of the existing Linux kernel not > getting itself into a potentially hung situation because it didn't save > itself any memory. Frankly, if it takes a complete kernel rewrite to fix > the problem that the damned operating system can't manage its own needs, > then the kernel needs to be rewritten! > > These kernel hackers could learn something from VAX/VMS. What's interesting is that apparently FreeBSD also has overcommit (and IIRC no way to disable it), yet I never hear people going off on OOM kills in FreeBSD. My theory is that FreeBSD admins are smart enough to dedicate a decent amount of swap space, so that by the time you got to an OOM kill situation you'd be so far into swapping that the box would be nearly unusable. Many linux 'admins' think it's ok to save a few GB of disk space by allocating a small amount of swap (or none at all), and *kaboom*. -- Jim Nasby[EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) ---(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: [PERFORM] availability of SATA vendors
On Wed, Nov 22, 2006 at 04:35:37PM -0500, Bucky Jordan wrote: > While I'm at it, if I have time I'll run pgbench with pg_log on a > separate RAID1, and one with it on a RAID10x6, but I don't know how > useful those results will be. Very, but only if the controller has write-caching enabled. For testing purposes it won't batter if it's actually got a BBU so long as the write cache works (of course you wouldn't run in production like that...) -- Jim Nasby[EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] availability of SATA vendors
On Wed, Nov 22, 2006 at 09:02:04AM -0800, Jeff Frost wrote: > A valid question. Does the caching raid controller negate the desire to > separate pg_xlog from PGDATA? Theoretically, yes. But I don't think I've seen any hard numbers from testing. -- Jim Nasby[EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] shared_buffers > 284263 on OS X
On Sat, Nov 18, 2006 at 08:13:26PM -0700, Brian Wipf wrote: > It certainly is unfortunate if Guido's right and this is an upper > limit for OS X. The performance benefit of having high shared_buffers > on our mostly read database is remarkable. Got any data about that you can share? People have been wondering about cases where drastically increasing shared_buffers makes a difference. -- Jim Nasby[EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] Context switch storm
On Tue, Nov 14, 2006 at 09:17:08AM -0500, Merlin Moncure wrote: > On 11/14/06, Cosimo Streppone <[EMAIL PROTECTED]> wrote: > >I must say I lowered "shared_buffers" to 8192, as it was before. > >I tried raising it to 16384, but I can't seem to find a relationship > >between shared_buffers and performance level for this server. > > My findings are pretty much the same here. I don't see any link > between shared buffers and performance. I'm still looking for hard > evidence to rebut this point. Lower shared buffers leaves more > memory for what really matters, which is sorting. It depends on your workload. If you're really sort-heavy, then having memory available for that will be hard to beat. Otherwise, having a large shared_buffers setting can really help cut down on switching back and forth between the kernel and PostgreSQL. BTW, shared_buffers of 16384 is pretty low by today's standards, so that could be why you're not seeing much difference between that and 8192. Try upping it to 1/4 - 1/2 of memory and see if that changes things. -- Jim Nasby[EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [PERFORM] query slows down drastically with increased number of fields
On Thu, Oct 26, 2006 at 03:03:38PM -0700, George Pavlov wrote: > i have wondered myself. i wouldn't do it through pgAdmin (not sure what > the best test it, but i thought psql from the same machine might be > better--see below). anyway, the funny thing is that if you concatenate > them the time drops: Sure. Take a look at the output and you'll see there's less data to shove around. -- Jim Nasby[EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) ---(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: [PERFORM] VACUUMs take twice as long across all nodes
On Thu, Oct 26, 2006 at 09:35:56PM +0100, Gavin Hamill wrote: > On Thu, 26 Oct 2006 14:17:29 -0500 > "Jim C. Nasby" <[EMAIL PROTECTED]> wrote: > > > Are you sure that there's nothing else happening on the machine that > > could affect the vacuum times? Like, say a backup? Or perhaps updates > > coming in from Slony that didn't used to be there? > > I'm absolutely certain. The backups run from only one slave, given that > it is a full copy of node 1. Our overnight traffic has not increased > any, and the nightly backups show that the overall size of the DB has > not increased more than usual growth. > > Plus, I have fairly verbose logging, and it's not showing anything out > of the ordinary. > > Like I said, it's one of those awful hypothesis/hand-waving problems :) Well, the fact that it's happening on all your nodes leads me to think Slony is somehow involved. Perhaps it suddenly decided to change how often it's issuing syncs? I know it issues vacuums as well, so maybe that's got something to do with it... (though I'm guessing you've already looked in pg_stat_activity/logs to see if anything correlates...) Still, it might be worth asking about this on the slony list... -- Jim Nasby[EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) ---(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: [PERFORM] Stored procedure slower than sql?
The planner has no idea what $1 and $2 are when it plans the query, so that could easily explain why the performance is different. You can prepare statements in psql (at least in 8.1), which would be a good way to verify that theory (compare EXPLAIN for prepared vs. non). On Thu, Oct 26, 2006 at 09:21:37AM -0700, Matthew Peters wrote: > Parameterized. > > IE (very simplified) > > CREATE OR REPLACE FUNCTION my_function(IN param1 BIGINT, IN param2 > INTEGER) > RETURNS my_type > SECURITY DEFINER > AS > $$ > /* my_type = (a,b,c) */ > Select a,b,c > FROM my_table > WHERE indexed_column = $1 > AND partition_constraint_column = $2; > $$ > LANGUAGE SQL; > > > > > Matthew A. Peters > Sr. Software Engineer, Haydrian Corp. > [EMAIL PROTECTED] > (mobile) 425-941-6566 > Haydrian Corp. > -Original Message- > From: Tom Lane [mailto:[EMAIL PROTECTED] > Sent: Thursday, October 26, 2006 9:15 AM > To: Matthew Peters > Cc: pgsql-performance@postgresql.org > Subject: Re: [PERFORM] Stored procedure slower than sql? > Importance: High > > "Matthew Peters" <[EMAIL PROTECTED]> writes: > > How can a stored procedure containing a single query not implement the > > same execution plan (assumption based on the dramatic performance > > difference) that an identical ad-hoc query generates? > > Parameterized vs non parameterized query? > > regards, tom lane > > ---(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 > -- Jim Nasby[EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] VACUUMs take twice as long across all nodes
On Thu, Oct 26, 2006 at 04:06:09PM +0100, Gavin Hamill wrote: > On Thu, 26 Oct 2006 10:47:21 -0400 > Tom Lane <[EMAIL PROTECTED]> wrote: > > > Gavin Hamill <[EMAIL PROTECTED]> writes: > > > Nodes 2 and 3 take only the tables necessary to run our search (10 > > > out of the full 130) and are much lighter (only 7GB on disk cf. > > > 30GB for the full master) , yet the nightly VACUUM FULL has jumped > > > from 2 hours to 4 in the space of one day! > > > > I guess the most useful question to ask is "why are you doing VACUUM > > FULL?" Plain VACUUM should be considerably faster, and for the level > > of row turnover shown by your log, there doesn't seem to be a reason > > to use FULL. > > I do FULL on the 'light' clients simply because 'I can'. The example > posted was a poor choice - the other tables have a larger churn. > > Anyway, once it starts, the load balancer takes it out of rotation so > no love is lost. > > The same behaviour is shown on the 'heavy' clients (master + 2 slaves) > which take all tables - although I cannot afford to VACUUM FULL on > there, the usual VACUUM ANALYZE has begun to take vastly more time > since yesterday than in the many previous months we've been using pg. Are you sure that there's nothing else happening on the machine that could affect the vacuum times? Like, say a backup? Or perhaps updates coming in from Slony that didn't used to be there? -- Jim Nasby[EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] Configuration Issue ?
On Wed, Oct 25, 2006 at 05:31:29PM -0400, Mark Lonsdale wrote: > Set my sort_mem to 8192 You really need to look at what your workload is before trying to tweak sort_mem. With 8G of memory, sort_mem=40 (~400MB) with only 10 active connections might be a good setting. It's usually better to get a sort to fit into memory than spill to disk. Since you never mentioned what kind of workload you have or how many active connections there are, it's pretty much impossible to make a recommendation on that setting. -- Jim Nasby[EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] commit so slow program looks frozen
On Wed, Oct 25, 2006 at 04:32:16PM -0400, Carlo Stonebanks wrote: > >> I have a question for you: did you have a long running query keeping open > a transaction? I've just noticed the same problem here, but things cleaned > up immediately when I aborted the long-running transaction. > > No, the only processes are from those in the import applications themselves: > short transactions never lasting more than a fraction of a second. Do you have a linux/unix machine you could reproduce this on? -- Jim Nasby[EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] Best COPY Performance
On Wed, Oct 25, 2006 at 11:25:01AM -0400, Worky Workerson wrote: > >I'm guessing the high bursts are checkpoints. Can you check your log > >files for pg and see if you are getting warnings about checkpoint > >frequency? You can get some mileage here by increasing wal files. > > Nope, nothing in the log. I have set: > wal_buffers=128 > checkpoint_segments=128 > checkpoint_timeout=3000 > which I thought was rather generous. Perhaps I should set it even > higher for the loads? But depending on your shared_buffer and bgwriter settings (as well as how much WAL traffic you're generating, you could still end up with big slugs of work to be done when checkpoints happen. If you set checkpoint_warning to 3001, you'll see exactly when checkpoints are happening, so you can determine if that's an issue. -- Jim Nasby[EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] Problems using a function in a where clause
On Wed, Oct 25, 2006 at 07:55:38AM -0300, Mara Dalponte wrote:
> On 10/24/06, Jim C. Nasby <[EMAIL PROTECTED]> wrote:
> >On Mon, Oct 23, 2006 at 04:54:00PM -0300, Mara Dalponte wrote:
> >> Hello,
> >>
> >> I have a query with several join operations and applying the same
> >> filter condition over each involved table. This condition is a complex
> >> predicate over an indexed timestamp field, depending on some
> >> parameters.
> >> To factorize code, I wrote the filter into a plpgsql function, but
> >> the resulting query is much more slower than the first one!
> >
> >A view would probably be a better idea... or create some code that
> >generates the code for you.
>
> Thank, but the filter function needs some external parameters, so a
> view wont be appropiate. Anyway, your second possibility could work!
>
> >> The explain command over the original query gives the following info
> >> for the WHERE clause that uses the filter:
> >>
> >> ...
> >> Index Cond: ((_timestamp >= '2006-02-23 03:00:00'::timestamp without
> >> time zone) AND (_timestamp <= '2006-02-27 20:00:00.98'::timestamp
> >> without time zone))
> >> ...
> >>
> >> The explain command for the WHERE clause using the filtering function is:
> >>
> >> ...
> >> Filter: include_time_date('2006-02-23'::date, '2006-02-27'::date,
> >> '03:00:00'::time without time zone, '20:00:00'::time without time
> >> zone, (_timestamp)::timestamp without time zone)
> >> ...
> >>
> >> It seems to not be using the index, and I think this is the reason of
> >> the performance gap between both solutions.
> >
> >Well, it looks like include_time_date just returns a boolean, so how
> >could it use the index?
>
> I mean that in the old query the index is used (because is a
> comparative condition over an indexed timestamp field), but not in the
> new one, where the function is used. Is there some kind of "inline"
> function type?
No, unfortunately. Your best bet is to add the most important filter
criteria by hand, or write code that writes the code (which is what I'd
probably do).
--
Jim Nasby[EMAIL PROTECTED]
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
Re: [PERFORM] Best COPY Performance
On Wed, Oct 25, 2006 at 08:03:38AM -0400, Worky Workerson wrote: > I'm just doing CSV style transformations (and calling a lot of > functions along the way), but the end result is a straight bulk load > of data into a blank database. And we've established that Postgres > can do *way* better than what I am seeing, so its not suprising that > perl is using 100% of a CPU. If you're loading into an empty database, there's a number of tricks that will help you: Turn off fsync Add constraints and indexes *after* you've loaded the data (best to add as much of them as possible on a per-table basis right after the table is loaded so that it's hopefully still in cache) Crank up maintenance_work_mem, especially for tables that won't fit into cache anyway Bump up checkpoint segments and wal_buffers. Disable PITR Create a table and load it's data in a single transaction (8.2 will avoid writing any WAL data if you do this and PITR is turned off) -- Jim Nasby[EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] Best COPY Performance
On Tue, Oct 24, 2006 at 10:36:04PM -0700, Craig A. James wrote:
> Jim C. Nasby wrote:
> >Well, given that perl is using an entire CPU, it sounds like you should
> >start looking either at ways to remove some of the overhead from perl,
> >or to split that perl into multiple processes.
>
> I use Perl for big database copies (usually with some
> processing/transformation along the way) and I've never seen 100% CPU usage
> except for brief periods, even when copying BLOBS and such. My typical
> copy divides operations into blocks, for example doing
>
> N = 0
> while (more rows to go) {
> begin transaction
> select ... where primary_key > N order by primary_key limit 1000
> while (fetch a row)
>insert into ...
> N = (highest value found in last block)
> commit
> }
>
> Doing it like this in Perl should keep Postgres busy, with Perl using only
> moderate resources. If you're seeing high Perl CPU usage, I'd look first
> at the Perl code.
Wait... so you're using perl to copy data between two tables? And using
a cursor to boot? I can't think of any way that could be more
inefficient...
What's wrong with a plain old INSERT INTO ... SELECT? Or if you really
need to break it into multiple transaction blocks, at least don't
shuffle the data from the database into perl and then back into the
database; do an INSERT INTO ... SELECT with that same where clause.
--
Jim Nasby[EMAIL PROTECTED]
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [PERFORM] Copy database performance issue
On Mon, Oct 23, 2006 at 05:51:40PM -0400, Steve wrote: > Hello there; > > I've got an application that has to copy an existing database to a new > database on the same machine. > > I used to do this with a pg_dump command piped to psql to perform the > copy; however the database is 18 gigs large on disk and this takes a LONG > time to do. > > So I read up, found some things in this list's archives, and learned that > I can use createdb --template=old_database_name to do the copy in a much > faster way since people are not accessing the database while this copy > happens. > > > The problem is, it's still too slow. My question is, is there any way I > can use 'cp' or something similar to copy the data, and THEN after that's > done modify the database system files/system tables to recognize the > copied database? AFAIK, that's what initdb already does... it copies the database, essentially doing what cp does. > For what it's worth, I've got fsync turned off, and I've read every tuning > thing out there and my settings there are probably pretty good. It's a > Solaris 10 machine (V440, 2 processor, 4 Ultra320 drives, 8 gig ram) and > here's some stats: I don't think any of the postgresql.conf settings will really come into play when you're doing this... -- Jim Nasby[EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] Problems using a function in a where clause
On Mon, Oct 23, 2006 at 04:54:00PM -0300, Mara Dalponte wrote:
> Hello,
>
> I have a query with several join operations and applying the same
> filter condition over each involved table. This condition is a complex
> predicate over an indexed timestamp field, depending on some
> parameters.
> To factorize code, I wrote the filter into a plpgsql function, but
> the resulting query is much more slower than the first one!
A view would probably be a better idea... or create some code that
generates the code for you.
> The explain command over the original query gives the following info
> for the WHERE clause that uses the filter:
>
> ...
> Index Cond: ((_timestamp >= '2006-02-23 03:00:00'::timestamp without
> time zone) AND (_timestamp <= '2006-02-27 20:00:00.98'::timestamp
> without time zone))
> ...
>
> The explain command for the WHERE clause using the filtering function is:
>
> ...
> Filter: include_time_date('2006-02-23'::date, '2006-02-27'::date,
> '03:00:00'::time without time zone, '20:00:00'::time without time
> zone, (_timestamp)::timestamp without time zone)
> ...
>
> It seems to not be using the index, and I think this is the reason of
> the performance gap between both solutions.
Well, it looks like include_time_date just returns a boolean, so how
could it use the index?
> How can I explicitly use this index? which type of functions shall I
> use (VOLATILE | INMUTABLE | STABLE)?
That depends on what exactly the function does. There's a pretty good
description in the CREATE FUNCTION docs.
--
Jim Nasby[EMAIL PROTECTED]
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)
---(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: [PERFORM] Best COPY Performance
On Tue, Oct 24, 2006 at 09:17:08AM -0400, Worky Workerson wrote: > >http://stats.distributed.net used to use a perl script to do some > >transformations before loading data into the database. IIRC, when we > >switched to using C we saw 100x improvement in speed, so I suspect that > >if you want performance perl isn't the way to go. I think you can > >compile perl into C, so maybe that would help some. > > Like Craig mentioned, I have never seen those sorts of improvements > going from perl->C, and developer efficiency is primo for me. I've > profiled most of the stuff, and have used XS modules and Inline::C on > the appropriate, often used functions, but I still think that it comes > down to my using CSV and Text::CSV_XS. Even though its XS, CSV is > still a pain in the ass. > > >Ultimately, you might be best of using triggers instead of rules for the > >partitioning since then you could use copy. Or go to raw insert commands > >that are wrapped in a transaction. > > Eh, I've put the partition loading logic in the loader, which seems to > work out pretty well, especially since I keep things sorted and am the > only one inserting into the DB and do so with bulk loads. But I'll > keep this in mind for later use. Well, given that perl is using an entire CPU, it sounds like you should start looking either at ways to remove some of the overhead from perl, or to split that perl into multiple processes. -- Jim Nasby[EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] Best COPY Performance
On Mon, Oct 23, 2006 at 03:37:47PM -0700, Craig A. James wrote: > Jim C. Nasby wrote: > >http://stats.distributed.net used to use a perl script to do some > >transformations before loading data into the database. IIRC, when we > >switched to using C we saw 100x improvement in speed, so I suspect that > >if you want performance perl isn't the way to go. I think you can > >compile perl into C, so maybe that would help some. > > I use Perl extensively, and have never seen a performance problem. I > suspect the perl-to-C "100x improvement" was due to some other factor, like > a slight change in the schema, indexes, or the fundamental way the client > (C vs Perl) handled the data during the transformation, or just plain bad > Perl code. > > Modern scripting languages like Perl and Python make programmers far, far > more productive than the bad old days of C/C++. Don't shoot yourself in > the foot by reverting to low-level languages like C/C++ until you've > exhausted all other possibilities. I only use C/C++ for intricate > scientific algorithms. > > In many cases, Perl is *faster* than C/C++ code that I write, because I > can't take the time (for example) to write the high-performance string > manipulation that have been fine-tuned and extensively optimized in Perl. Well, the code is all at http://cvs.distributed.net/viewcvs.cgi/stats-proc/hourly/ (see logmod directory and logmod_*.pl). There have been changes made to the C code since we changed over, but you can find the appropriate older versions in there. IIRC, nothing in the database changed when we went from perl to C (it's likely that was the *only* change that happened anywhere around that time). -- Jim Nasby[EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) ---(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: [PERFORM] Optimizing disk throughput on quad Opteron
On Sat, Oct 21, 2006 at 08:43:05AM -0700, John Philips wrote: > I heard some say that the transaction log should be on > it's own array, others say it doesn't hurt to have it > on the same array as the OS. Is it really worthwhile > to put it on it's own array? It all depends on the controller and how much non-WAL workload there is. Theoretically, with a good enough controller, you can leave WAL on the same partition as your data. With a complex setup like you're looking at, you really will want to do some testing to see what makes the most sense. I can also point you at a company that does modeling of stuff like this; they could actually give you some idea of how well that setup would perform before you buy the hardware. BTW, any test results you can provide back to the community would be most appreciated! -- Jim Nasby[EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] Best COPY Performance
On Mon, Oct 23, 2006 at 11:10:19AM -0400, Worky Workerson wrote: > >> I am most interested in loading two tables, one with about 21 (small) > >> VARCHARs where each record is about 200 bytes, and another with 7 > >> INTEGERs, 3 TIMESTAMPs, and 1 BYTEA where each record is about 350 > >> bytes. > > > >indexes/keys? more memory for sorting during index creation can have > >a dramatic affect on bulk insert performance. check for pg_tmp > >folders popping up during copy run. > > The only index on load is a single IP4 btree primary key, which I > figure should function about like an INTEGER. > > >for table light on indexes, 10-15k for copy is pretty poor. you can > >get pretty close to that with raw inserts on good hardware. I would > >suggest configuirng your perl script to read from stdin and write to > >stdout, and pipe it to psql using copy from stdin. then just > >benchmark your perl script redirecting output to a file. > > So simple and hadn't thought of that ... thanks. When I pre-create a > COPY file, I can load it at about 45K inserts/sec (file was 1.8GB or > 14.5 million records in 331 seconds), which looks like its about 5.5 > MB/s. I'm loading from a local 15K SCSI320 RAID10 (which also > contains the PG log files) to a 10K SCSI320 RAID10 on an FC SAN. Does > this look more consistent with "decent" performance, or should I go > looking into some hardware issues i.e. SAN configuration? I've > currently got several hats including hardware/systems/security admin, > as well as DBA and programmer, and my SAN setup skills could > definitely use some more work. > > Hardware aside, my perl can definitely use some work, and it seems to > be mostly the CSV stuff that I am using, mostly for convenience. I'll > see if I can't redo some of that to eliminate some CSV processing, or, > barring that, multithread the process to utilize more of the CPUs. > Part of the reason that I hadn't used psql in the first place is that > I'm loading the data into partitioned tables, and the loader keeps > several COPY connections open at a time to load the data into the > right table. I guess I could just as easily keep several psql pipes > open, but it seemed cleaner to go through DBI. http://stats.distributed.net used to use a perl script to do some transformations before loading data into the database. IIRC, when we switched to using C we saw 100x improvement in speed, so I suspect that if you want performance perl isn't the way to go. I think you can compile perl into C, so maybe that would help some. Ultimately, you might be best of using triggers instead of rules for the partitioning since then you could use copy. Or go to raw insert commands that are wrapped in a transaction. -- Jim Nasby[EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] New hardware thoughts
On Sat, Oct 21, 2006 at 12:12:59AM +0930, Shane Ambler wrote: > Generally more disks at slower speed - 2 10K disks in raid 0 is faster > than 1 15K disk. More disks also allow more options. Not at writing they're not (unless you're using RAID0... ugh). -- Jim Nasby[EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] Vacuum and Memory Loss
On Mon, Oct 23, 2006 at 09:45:59AM +0100, Richard Huxton wrote: > Mike wrote: > >Hello friends, > > > >I am responsible for maintaining a high volume website using postgresql > >8.1.4. Given the amount of reads and writes, I vacuum full the server a > >few times a week around 1, 2 AM shutting down the site for a few > >minutes. The next day morning around 10 - 11 AM the server slows down > >to death. It used to be that the error 'Too many clients' would be > >recorded, until I increased the number of clients it can handle, and > >now it simply slows down to death having lots and lots of postmaster > >processes running: > > > >Tasks: 665 total, 10 running, 655 sleeping, 0 stopped, 0 zombie > >Cpu(s): 14.9% us, 16.7% sy, 0.0% ni, 0.0% id, 68.4% wa, 0.0% hi, > >0.0% si > >Mem: 2074932k total, 2051572k used,23360k free, 2736k > >buffers > >Swap: 2096440k total, 188k used, 251992k free, 102968k cached > > This seems to be saying you have 1.8GB of swap in use. I'd start by > checking with vmstat whether you're actively swapping. If so, you're > overallocating memory. Which could easily be caused by a combination of trying to handle too many database connections at once and setting work_mem too high. I've often gone into client sites to find they've set the database up to accept hundreds or thousands of connections, even though the hardware they're running on would most likely fall over if they actually had that many simultaneously active connections. In many cases, increasing the number of connections the the database will hurt performance rather than help it, because you're now asking an already overloaded server to do even more work. -- Jim Nasby[EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] DB Performance decreases due to often written/accessed
On Thu, Oct 19, 2006 at 06:19:16PM +0100, Richard Huxton wrote: > OK - these plans look about the same, but the time is greatly different. > Both have rows=140247 as the estimated number of rows in tbl_reg. Either > you have many more rows in the second case (in which case you're not > running ANALYSE enough) or you have lots of gaps in the table (you're > not running VACUUM enough). Look closer... the actual stats show that the sorts in the second case are returning far more rows. And yes, analyze probably needs to happen. > I'd then try putting an index on (attr1,attr2,attr3...attr6) and see if > that helps reduce time. With bitmap index scans, I think it'd be much better to create 6 indexes and see which ones actually get used (and then drop the others). -- Jim Nasby[EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) ---(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: [PERFORM] Swappiness setting on a linux pg server
On Thu, Oct 19, 2006 at 06:53:49PM +0200, Tobias Brox wrote: > [Jim C. Nasby - Thu at 11:45:32AM -0500] > > > > The issue with pg_xlog is you don't need bandwidth... you need super-low > > > > latency. The best way to accomplish that is to get a battery-backed RAID > > > > controller that you can enable write caching on. > > > > > > Sounds a bit risky to me :-) > > > > Well, you do need to understand what happens if the machine does lose > > power... namely you have a limited amount of time to get power back to > > the machine so that the controller can flush that data out. Other than > > that, it's not very risky. > > We have burned ourself more than once due to unreliable raid controllers > ... Well, if you're buying unreliable hardware, there's not much you can do... you're setting yourself up for problems. > > quantities of memory. So in your case, 600M wouldn't be pushing things > > much at all. Even 1G wouldn't be that out of the ordinary. Also remember > > that the more memory for shared_buffers, the less for > > sorting/hashes/etc. (work_mem) > > What do you mean, a high value for the shared_buffers implicates I > can/should lower the work_mem value? Or just that I should remember to > have more than enough memory for both work_mem, shared_buffers and OS > caches? What is a sane value for the work_mem? It's currently set to > 8M. The key is that there's enough memory for shared_buffers and work_mem without going to swapping. If you're consuming that much work_mem I wouldn't worry at all about OS caching. What's reasonable for work_mem depends on your workload. If you've got some reporting queries that you know aren't run very concurrently they might benefit from large values of work_mem. For stats.distributed.net, I set work_mem to something like 2MB in the config file, but the nightly batch routines manually set it up to 256M or more, because I know that those only run one at a time, and having that extra memory means a lot of stuff that would otherwise have to spill to disk now stays in memory. -- Jim Nasby[EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) ---(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: [PERFORM] Swappiness setting on a linux pg server
On Thu, Oct 19, 2006 at 06:39:22PM +0200, Tobias Brox wrote: > [Jim C. Nasby - Thu at 11:31:26AM -0500] > > Yeah, test setups are a good thing to have... > > We would need to replicate the production traffic as well to do reliable > tests. Well, we'll get to that one day ... Marginally reliable tests are usually better than none at all. :) > > The issue with pg_xlog is you don't need bandwidth... you need super-low > > latency. The best way to accomplish that is to get a battery-backed RAID > > controller that you can enable write caching on. > > Sounds a bit risky to me :-) Well, you do need to understand what happens if the machine does lose power... namely you have a limited amount of time to get power back to the machine so that the controller can flush that data out. Other than that, it's not very risky. As for shared_buffers, conventional wisdom has been to use between 10% and 25% of memory, bounding towards the lower end as you get into larger quantities of memory. So in your case, 600M wouldn't be pushing things much at all. Even 1G wouldn't be that out of the ordinary. Also remember that the more memory for shared_buffers, the less for sorting/hashes/etc. (work_mem) -- Jim Nasby[EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] Swappiness setting on a linux pg server
On Thu, Oct 19, 2006 at 06:00:54PM +0200, Tobias Brox wrote: > [Jim C. Nasby - Thu at 10:28:31AM -0500] > > I think it'd be much better to experiment with using much larger > > shared_buffers settings. The conventional wisdom there is from 7.x days > > when you really didn't want a large buffer, but that doesn't really > > apply with the new buffer management we got in 8.0. I know of one site > > that doubled their performance by setting shared_buffers to 50% of > > memory. > > Oh, that's interessting. I will give it a shot. Our config is > "inheritated" from the 7.x-days, so we have a fairly low setting > compared to available memory. From the 7.x-days the logic was that "a > lot of careful thought has been given when designing the OS cache/buffer > subsystem, we don't really want to reinvent the wheel" or something like > that. Yeah, test setups are a good thing to have... > Sadly it's not easy to measure the overall performance impact of such > tunings in a production environment, so such a setting tends to be tuned > by religion rather than science :-) > > > Something else to consider is that many people will put pg_xlog on the > > same drives as the OS (and swap). It's pretty important that those > > drives not have much activity other than pg_xlog, so any swap activity > > would have an even larger than normal impact on performance. > > Hm ... that's actually our current setting, we placed the postgres > database itself on a separate disk, not the xlog. So we should have > done it the other way around? No wonder the performance is badly > affected by backups etc ... Well, typically I see setups where people dedicate say 6 drives to the data and 2 drives for the OS and pg_xlog. The issue with pg_xlog is you don't need bandwidth... you need super-low latency. The best way to accomplish that is to get a battery-backed RAID controller that you can enable write caching on. In fact, if the controller is good enough, you can theoretically get away with just building one big RAID10 and letting the controller provide the low-latency fsyncs that pg_xlog depends on. -- Jim Nasby[EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) ---(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: [PERFORM] Swappiness setting on a linux pg server
On Thu, Oct 19, 2006 at 03:54:28PM +0200, Tobias Brox wrote: > I just came to think about /proc/sys/swappiness ... > > When this one is set to a high number (say, 100 - which is maximum), the > kernel will aggressively swap out all memory that is not beeing > accessed, to allow more memory for caches. For a postgres server, OS > caches are good, because postgres relies on the OS to cache indices, > etc. At the other hand, for any production server it's very bad to > experience bursts of iowait when/if the swapped out memory becomes > needed - particularly if the server is used for interactive queries, > like serving a web application. > > I know there are much religion on this topic in general, I'm just > curious if anyone has done any serious thoughts or (even better!) > experimenting with the swappiness setting on a loaded postgres server. I think it'd be much better to experiment with using much larger shared_buffers settings. The conventional wisdom there is from 7.x days when you really didn't want a large buffer, but that doesn't really apply with the new buffer management we got in 8.0. I know of one site that doubled their performance by setting shared_buffers to 50% of memory. Something else to consider is that many people will put pg_xlog on the same drives as the OS (and swap). It's pretty important that those drives not have much activity other than pg_xlog, so any swap activity would have an even larger than normal impact on performance. > I would assume that the default setting (60) is pretty OK and sane, and > that modifying the setting would have insignificant effect. My > religious belief is that, however insignificant, a higher setting would > have been better :-) > > We're running linux kernel 2.6.17.7 (debian) on the postgres server, and > our memory stats looks like this: > total used free sharedbuffers cached > Mem: 6083M 5846M 236M 0 31M 5448M > -/+ buffers/cache:366M 5716M > Swap: 2643M 2M 2640M > > In addition to the postgres server we're running some few cronscripts > and misc on it - nothing significant though. -- Jim Nasby[EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] index growth problem
On Wed, Oct 18, 2006 at 03:39:56PM -0700, Graham Davis wrote: > So I guess any changes that were made to make VACUUM and FSM include > indexes > does not remove the necessity to reindex (as long as we don't want index > sizes to bloat and grow larger than they need be). > Is that correct? Not in recent releases, no. Remember that any index on a field that gets update activity will naturally have some amount of empty space due to page splits, but this is normal (and actually desireable). So you can't just compare index size before and after a REINDEX and assume something's wrong if REINDEX shrinks the index; that gain is artificial. So long as you are vacuuming frequently enough and keep the free space map large enough, there shouldn't be any need to REINDEX. -- Jim Nasby[EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] Postgresql 8.1.4 - performance issues for select on
On Wed, Oct 18, 2006 at 03:32:15PM -0700, Jeff Davis wrote: > On Wed, 2006-10-18 at 17:10 -0500, Jim C. Nasby wrote: > > Sorry, don't have the earlier part of this thread, but what about... > > > > SELECT greatest(max(a), max(b)) ... > > > > ? > > To fill you in, we're trying to get the max of a union (a view across > two physical tables). UNION or UNION ALL? You definitely don't want to do a plain UNION if you can possibly avoid it. > It can be done if you're creative with the query; I suggested a query > that selected the max of the max()es of the individual tables. Your > query could work too. However, the trick would be getting postgresql to > recognize that it can transform "SELECT max(x) FROM foo" into that, > where foo is a view of a union. > > If PostgreSQL could sort the result of a union by merging the results of > two index scans, I think the problem would be solved. Is there something > preventing this, or is it just something that needs to be added to the > planner? Hrm... it'd be worth trying the old ORDER BY ... LIMIT 1 trick just to see if that worked in this case, but I don't have much hope for that. -- Jim Nasby[EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) ---(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: [PERFORM] index growth problem
On Wed, Oct 18, 2006 at 03:20:19PM -0700, Graham Davis wrote:
> I have a question about index growth.
>
> The way I understand it, dead tuples in indexes were not reclaimed by
> VACUUM commands in the past. However, I've read in a few forum posts
> that this was changed somewhere between 7.4 and 8.0.
There was a change to indexes that made vacuum more effective; I don't
remember the details off-hand.
> I'm having an issue where my GIST indexes are growing quite large, and
> running a VACUUM doesn't appear to remove the dead tuples. For example,
> if I check out the size an index before running any VACUUM :
>
> select pg_relation_size('asset_positions_position_idx');
> pg_relation_size
> --
> 11624448
> (1 row)
>
> The size is about 11Mb. If I run a VACUUM command in verbose, I see
> this about the index:
>
> INFO: index "asset_positions_position_idx" now contains 4373 row
> versions in 68 pages
> DETAIL: 0 index pages have been deleted, 0 are currently reusable.
> CPU 0.00s/0.00u sec elapsed 0.16 sec.
>
> When I run the same command to find the size after the VACUUM, it hasn't
> changed. However, if I drop and then recreate this index, the size
> becomes much smaller (almost half the size):
>
> drop index asset_positions_position_idx;
> DROP INDEX
>
> CREATE INDEX asset_positions_position_idx ON asset_positions USING GIST
> (position GIST_GEOMETRY_OPS);
> CREATE INDEX
>
> select pg_relation_size('asset_positions_position_idx');
> pg_relation_size
> --
> 6225920
> (1 row)
>
> Is there something I am missing here, or is the reclaiming of dead
> tuples for these indexes just not working when I run a VACUUM? Is it
> suppose to work?
That's not really a useful test to see if VACUUM is working. VACUUM can
only trim space off the end of a relation (index or table), where by
'end' I mean the end of the last file for that relation on the
filesystem. This means it's pretty rare for VACUUM to actually shrink
files on-disk for tables. This can be even more difficult for indexes (I
think it's virtually impossible to shrink a B-tree index file).
--
Jim Nasby[EMAIL PROTECTED]
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)
---(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: [PERFORM] Postgresql 8.1.4 - performance issues for select on
On Wed, Oct 18, 2006 at 02:33:49PM -0700, Jeff Davis wrote: > On Wed, 2006-10-18 at 23:19 +0200, Dimitri Fontaine wrote: > > Le mercredi 18 octobre 2006 23:02, Ioana Danes a ??crit : > > > I tried the partitioning scenario but I've got into > > > the same problem. The max function is not using the > > > indexes on the two partitioned tables... > > > > > > Any other thoughts? > > > > Did you make sure your test included table inheritance? > > I'm not sure the planner benefits from constraint_exclusion without > > selecting > > the empty parent table (instead of your own union based view). > > > > constraint exclusion and inheritance won't help him. > > The problem is that he has two indexes, and he needs to find the max > between both of them. PostgreSQL isn't smart enough to recognize that it > can use two indexes, find the max in each one, and find the max of those > two values. Sorry, don't have the earlier part of this thread, but what about... SELECT greatest(max(a), max(b)) ... ? -- Jim Nasby[EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) ---(end of broadcast)--- TIP 6: explain analyze is your friend
