Re: [PERFORM] postgresql-8.0.1 performance tuning
Cosimo Streppone wrote: Mark Kirkwood ha scritto: Cosimo Streppone wrote: # Config /etc/sysctl.conf: kernel.shmall = 786432000 kernel.shmmax = 786432000 I think you have a problem here. kernel.shmmax should *not* be set to an amount of RAM, but Sorry, I thought "shmall" but written "shmmax". Thanks Mark! Hehe - happens to me all the time! On the shmall front - altho there is *probably* no real performance impact setting it to the same as shmmax (i.e. allowing 4096 allocations of size shmmax!), it is overkill. In addition it does allow for a DOS by a program that allocates thousands of segments (or somehow starts thousands of Pg servers on different ports...)! For a dedicated Pg server I would size shmall using a calculation along the lines of: shmall = (no. of postgresql servers) * (shmmax/4096) If there are other daemons on the box that need to use shared memory, then add their likely requirements to shmall too! cheers Mark ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[PERFORM] How to avoid database bloat
Hello, Our database increases in size 2.5 times during the day. What to do to avoid this? Autovacuum running with quite aggressive settings, FSM settings are high enough. Database size should be more or less constant but it has high turnover rate (100+ insert/update/delete per second). Below is "du -sk" of database dir during the day. On 4:05 full vacuum+reindex runs and database size is once again reduced. Thanks, Mindaugas Tue May 31 11:00:01 EEST 2005 533808 /ora/pgsql/base/465436/ Tue May 31 11:30:01 EEST 2005 567344 /ora/pgsql/base/465436/ Tue May 31 12:00:01 EEST 2005 578632 /ora/pgsql/base/465436/ Tue May 31 12:30:01 EEST 2005 586336 /ora/pgsql/base/465436/ Tue May 31 13:00:01 EEST 2005 594716 /ora/pgsql/base/465436/ Tue May 31 13:30:01 EEST 2005 604932 /ora/pgsql/base/465436/ Tue May 31 14:00:01 EEST 2005 613668 /ora/pgsql/base/465436/ Tue May 31 14:30:01 EEST 2005 625752 /ora/pgsql/base/465436/ Tue May 31 15:00:01 EEST 2005 637704 /ora/pgsql/base/465436/ Tue May 31 15:30:01 EEST 2005 649700 /ora/pgsql/base/465436/ Tue May 31 16:00:01 EEST 2005 657392 /ora/pgsql/base/465436/ Tue May 31 16:30:02 EEST 2005 668228 /ora/pgsql/base/465436/ Tue May 31 17:00:01 EEST 2005 676332 /ora/pgsql/base/465436/ Tue May 31 17:30:01 EEST 2005 686376 /ora/pgsql/base/465436/ Tue May 31 18:00:01 EEST 2005 694080 /ora/pgsql/base/465436/ Tue May 31 18:30:02 EEST 2005 705876 /ora/pgsql/base/465436/ Tue May 31 19:00:01 EEST 2005 713916 /ora/pgsql/base/465436/ Tue May 31 19:30:01 EEST 2005 725460 /ora/pgsql/base/465436/ Tue May 31 20:00:01 EEST 2005 733892 /ora/pgsql/base/465436/ Tue May 31 20:30:01 EEST 2005 745344 /ora/pgsql/base/465436/ Tue May 31 21:00:01 EEST 2005 753048 /ora/pgsql/base/465436/ Tue May 31 21:30:02 EEST 2005 768228 /ora/pgsql/base/465436/ Tue May 31 22:00:01 EEST 2005 804796 /ora/pgsql/base/465436/ Tue May 31 22:30:01 EEST 2005 858840 /ora/pgsql/base/465436/ Tue May 31 23:00:02 EEST 2005 902684 /ora/pgsql/base/465436/ Tue May 31 23:30:01 EEST 2005 939796 /ora/pgsql/base/465436/ Wed Jun 1 00:00:02 EEST 2005 990840 /ora/pgsql/base/465436/ Wed Jun 1 00:30:11 EEST 2005 1005316 /ora/pgsql/base/465436/ Wed Jun 1 01:00:02 EEST 2005 1011408 /ora/pgsql/base/465436/ Wed Jun 1 01:30:01 EEST 2005 1010888 /ora/pgsql/base/465436/ Wed Jun 1 02:00:01 EEST 2005 1010872 /ora/pgsql/base/465436/ Wed Jun 1 02:30:01 EEST 2005 1010784 /ora/pgsql/base/465436/ Wed Jun 1 03:00:02 EEST 2005 1003260 /ora/pgsql/base/465436/ Wed Jun 1 03:30:02 EEST 2005 1003372 /ora/pgsql/base/465436/ Wed Jun 1 04:00:01 EEST 2005 1003380 /ora/pgsql/base/465436/ Wed Jun 1 04:30:01 EEST 2005 426508 /ora/pgsql/base/465436/ Wed Jun 1 05:00:01 EEST 2005 429036 /ora/pgsql/base/465436/ Wed Jun 1 05:30:01 EEST 2005 432156 /ora/pgsql/base/465436/ Wed Jun 1 06:00:01 EEST 2005 42 /ora/pgsql/base/465436/ Wed Jun 1 06:30:01 EEST 2005 435052 /ora/pgsql/base/465436/ Wed Jun 1 07:00:02 EEST 2005 439908 /ora/pgsql/base/465436/ Wed Jun 1 07:30:01 EEST 2005 450144 /ora/pgsql/base/465436/ Wed Jun 1 08:00:01 EEST 2005 471120 /ora/pgsql/base/465436/ Wed Jun 1 08:30:02 EEST 2005 490712 /ora/pgsql/base/465436/ Wed Jun 1 09:00:01 EEST 2005 501652 /ora/pgsql/base/465436/ Wed Jun 1 09:30:01 EEST 2005 530128 /ora/pgsql/base/465436/ Wed Jun 1 10:00:01 EEST 2005 541580 /ora/pgsql/base/465436/ Wed Jun 1 10:30:01 EEST 2005 571204 /ora/pgsql/base/465436/ ---(end of broadcast)--- TIP 3: 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-8.0.1 performance tuning
Hi John, thank you very much for the answer :). I moved the pg_xlog to another partition and made a symlink to it. Know the database is much more faster than before. A sample select which was finished in 68seconds before, is now finished in 58seconds :). I will test the other changes today also and will write a feedback after testing. :) Thanks a lot. I'm very confusing to tuning the postgresql-db. #:-) best regards Martin Am Dienstag, den 31.05.2005, 13:46 -0500 schrieb John A Meinel: > Martin Fandel wrote: > > > Hi @ all, > > > > i'm trying to tune my postgresql-db but i don't know if the values are > > right > > set. > > > > I use the following environment for the postgres-db: > > > > # Hardware > > cpu: 2x P4 3Ghz > > ram: 1024MB DDR 266Mhz > > > > partitions: > > /dev/sda3 23G 9,6G 13G 44% / > > /dev/sda1 11G 156M 9,9G 2% /var > > /dev/sdb1 69G 13G 57G 19% /var/lib/pgsql > > > > /dev/sda is in raid 1 (2x 35GB / 1upm / sca) > > /dev/sdb is in raid 10 (4x 35GB / 1upm / sca) > > # /Hardware > > You probably want to put the pg_xlog file onto /dev/sda rather than > having it in /dev/sdb. Having it separate from the data usually boosts > performance a lot. I believe you can just mv it to a different > directory, and then recreate it as a symlink. (Stop the database first :) > > > > > # Config > > /etc/sysctl.conf: > > kernel.shmall = 786432000 > > kernel.shmmax = 786432000 > > > Not really sure about these two. > > > /etc/fstab: > > /dev/sdb1 /var/lib/pgsql reiserfs > > acl,user_xattr,noatime,data=writeback 1 2 > > > Seems decent. > > > /var/lib/pgsql/data/postgresql.conf > > superuser_reserved_connections = 2 > > shared_buffers = 3000 > > work_mem= 131072 > > maintenance_work_mem= 131072 > > These both seem pretty large. But it depends on how many concurrent > connections doing sorting/hashing/etc you expect. If you are only > expecting 1 connection, these are probably fine. Otherwise with 1GB of > RAM I would probably make work_mem more like 4096/8192. > Remember, running out of work_mem means postgres will spill to disk, > slowing that query. Running out of RAM causes the system to swap, making > everything slow. > > > max_stack_depth = 2048 > > max_fsm_pages = 2 > > max_fsm_relations = 1000 > > max_files_per_process = 1000 > > vacuum_cost_delay = 10 > > vacuum_cost_page_hit= 1 > > vacuum_cost_page_miss = 10 > > vacuum_cost_page_dirty = 20 > > vacuum_cost_limit = 200 > > bgwriter_delay = 200 > > bgwriter_percent= 1 > > bgwriter_maxpages = 100 > > fsync = true > > wal_sync_method = fsync > > wal_buffers = 64 > > commit_delay= 0 > > commit_siblings = 5 > > checkpoint_segments = 256 > > checkpoint_timeout = 900 > > checkpoint_warning = 30 > > effective_cache_size= 1 > > random_page_cost= 4 > > cpu_tuple_cost = 0.01 > > cpu_index_tuple_cost= 0.001 > > cpu_operator_cost = 0.0025 > > geqo= true > > geqo_threshold = 12 > > geqo_effort = 5 > > geqo_pool_size = 0 > > geqo_generations= 0 > > geqo_selection_bias = 2.0 > > deadlock_timeout= 1000 > > max_locks_per_transaction = 64 > > # /Config > > > > # Transactions > > we have about 115-300 transactions/min in about 65 tables. > > # /Transactions > > > > I'm really new at using postgres. So i need some experience to set this > > parameters in the postgresql- and the system-config. I can't find standard > > calculations for this. :/ The postgresql-documentation doesn't help me to > > set the best values for this. > > > > The database must be high-availble. I configured rsync to sync the > > complete > > /var/lib/pgsql-directory to my hot-standby. On the hotstandby i will > > make the > > dumps of the database to improve the performance of the master-db. > > > I didn't think an rsync was completely valid. Probably you should look > more into Slony. > http://slony.info > > It is a single-master asynchronous replication system. I believe it is > pretty easy to setup, and does what you really want. > > > In my tests the synchronization works fine. I synchronised the hole > > directory > > and restarted the database of the hotstandby. While restarting, > > postgresql turned > > back the old (not archived) wals and the database of my hotstandby was > > consistent. Is this solution recommended? Or must i use archived w
Re: [PERFORM] very large table
On Tue, 2005-05-31 at 11:37 +0200, Praveen Raja wrote: > I’m trying to move an existing solution from MySQL to PostgreSQL. As > it is now the solution has 4 tables where data in inserted by an > application. At regular intervals (10min) data from these tables is > consolidated and moved to another table for reporting purposes. There > exist many instances of these reporting tables and in total they are > expected to hold about 500 million rows. There are about 200 of these > reporting tables at the moment with data split among them. When a > request comes in all these tables are searched. > While moving to PostgreSQL is it a good idea to move from using > multiple tables to one table for so many rows? No. All of the same reasoning applies. Try to keep each table small enough to fit easily in RAM. Make sure you specify WITHOUT OIDS on the main data tables. Best Regards, Simon Riggs ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [PERFORM] slow queries, possibly disk io
On Fri, 2005-05-27 at 07:52 -0500, Josh Close wrote: > > Setting shared buffers above something like 10-30% of memory is counter > > productive. > > What is the reason behind it being counter productive? If shared > buffers are at 30%, should effective cache size be at 70%? How do > those two relate? They don't relate. shared_buffers = 5 is enough. More than that will give bgwriter issues. effective_cache_size changes whether indexes are selected or not. Higher settings favour indexed access. > > > > Increasing sort_mem can help with various activities, but increasing it > > too much can cause you to swap, which kills performance. The caution is > > that you will likely use at least 1 sort_mem per connection, and can > > likely use more than one if the query is complicated. > > I have a max of 100 connections and 2 gigs of mem. Right now the sort > mem is a 4 megs. How much higher could I put that? > Please post your server hardware config all in one go. You have more than 2 CPUs, yes? Also, mention what bgwriter settings are. You may need to turn those down a bit. Best Regards, Simon Riggs ---(end of broadcast)--- TIP 8: explain analyze is your friend
[PERFORM] TIP 9: the planner will ignore... & datatypes
TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match But INT2, INT4, INT8 and "SERIAL" are considered to be a unique datatype. Am I Right? Thanks, Marc -- Geschenkt: 3 Monate GMX ProMail gratis + 3 Ausgaben stern gratis ++ Jetzt anmelden & testen ++ http://www.gmx.net/de/go/promail ++ ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [PERFORM] postgresql-8.0.1 performance tuning
On Wed, Jun 01, 2005 at 07:30:37AM +0200, Cosimo Streppone wrote: >>fsync = true > false Just setting fsync=false without considering the implications is a _bad_ idea... /* Steinar */ -- Homepage: http://www.sesse.net/ ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PERFORM] postgresql-8.0.1 performance tuning
Steinar wrote: On Wed, Jun 01, 2005 at 07:30:37AM +0200, Cosimo Streppone wrote: > > fsync = true > false Just setting fsync=false without considering the implications is a _bad_ idea... I totally agree on that. -- Cosimo ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PERFORM] postgresql-8.0.1 performance tuning
Yes, i think also that this setting should be enabled :). Am Mittwoch, den 01.06.2005, 11:57 +0200 schrieb Steinar H. Gunderson: > On Wed, Jun 01, 2005 at 07:30:37AM +0200, Cosimo Streppone wrote: > >>fsync = true > > false > > Just setting fsync=false without considering the implications is a _bad_ > idea... > > /* Steinar */ ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PERFORM] TIP 9: the planner will ignore... & datatypes
On Wed, Jun 01, 2005 at 11:45:06AM +0200, Marc Mamin wrote: > TIP 9: the planner will ignore your desire to choose an index scan if your > joining column's datatypes do not match > > > But INT2, INT4, INT8 and "SERIAL" are considered to be a unique datatype. > Am I Right? No, they weren't when this tip was written. As of 8.0 however this tip is no longer the complete truth; we do allow cross-type index scans. -- Alvaro Herrera () "I suspect most samba developers are already technically insane... Of course, since many of them are Australians, you can't tell." (L. Torvalds) ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PERFORM] How to avoid database bloat
"Mindaugas Riauba" <[EMAIL PROTECTED]> writes: > Our database increases in size 2.5 times during the day. > What to do to avoid this? Autovacuum running with quite > aggressive settings, FSM settings are high enough. First thing I'd suggest is to get a more detailed idea of exactly what is bloating --- which tables/indexes are the problem? regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[PERFORM] Moving pg_xlog
Hi All, I have been reading about increasing PostgreSQL performance by relocating the pg_xlog to a disk other than the one where the database resides. I have the following pg_xlogs on my system. /raid02/databases/pg_xlog /raid02/rhdb_databases/pg_xlog /raid02/databases-8.0.0/pg_xlog /var/lib/pgsql/data/pg_xlog The second and third entries are from backups that were made before major upgrades so I am expecting that I can blow them away. The first entry is in the directory where my databases are located. I have no idea why the forth entry is there. It is in the PostgreSQL installation directory. Here is my filesystem. # df -k Filesystem 1K-blocks Used Available Use% Mounted on /dev/sda6 9052552 2605292 5987404 31% / /dev/sda1 101089 32688 63182 35% /boot none 1282880 0 1282880 0% /dev/shm /dev/sdb2 16516084 32836 15644256 1% /raid01 /dev/sdb3 16516084 1156160 14520932 8% /raid02 /dev/sda5 2063504 32916 1925768 2% /tmp /dev/sda3 4127108203136 3714324 6% /var /dev/cdrom 494126494126 0 100% /mnt/cdrom Can I 1) stop the postmaster 2) rm -rf /var/lib/pgsql/data/pg_xlog 3) mv /raid02/databases/pg_xlog /var/lib/pgsql/data/pg_xlog 4) ln -s /var/lib/pgsql/data/pg_xlog /raid02/databases/pg_xlog 5) start postmaster If I can do that and place the pg_xlog in the installation directory will I create any installation issues the next time I upgrade PostgreSQL? TIA Kind Regards, Keith ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PERFORM] Moving pg_xlog
"Keith Worthington" <[EMAIL PROTECTED]> writes: > I have been reading about increasing PostgreSQL performance by relocating the > pg_xlog to a disk other than the one where the database resides. I have the > following pg_xlogs on my system. > /raid02/databases/pg_xlog > /raid02/rhdb_databases/pg_xlog > /raid02/databases-8.0.0/pg_xlog > /var/lib/pgsql/data/pg_xlog > I have no idea why the forth entry is there. It is in the PostgreSQL > installation directory. It's there because the RPM sets up a database under /var/lib/pgsql/data. > 1) stop the postmaster > 2) rm -rf /var/lib/pgsql/data/pg_xlog > 3) mv /raid02/databases/pg_xlog /var/lib/pgsql/data/pg_xlog > 4) ln -s /var/lib/pgsql/data/pg_xlog /raid02/databases/pg_xlog > 5) start postmaster Put the xlog anywhere BUT there! > If I can do that and place the pg_xlog in the installation directory will I > create any installation issues the next time I upgrade PostgreSQL? Oh, the installation will be just fine ... but your database will not be after the upgrade wipes out your WAL. Put the xlog under some non-system-defined directory. regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [PERFORM] Moving pg_xlog
On Wed, 01 Jun 2005 12:19:40 -0400, Tom Lane wrote > "Keith Worthington" <[EMAIL PROTECTED]> writes: > > I have been reading about increasing PostgreSQL performance > > by relocating the pg_xlog to a disk other than the one > > where the database resides. I have the following pg_xlogs > > on my system. > > > > /raid02/databases/pg_xlog > > /raid02/rhdb_databases/pg_xlog > > /raid02/databases-8.0.0/pg_xlog > > /var/lib/pgsql/data/pg_xlog > > > > I have no idea why the forth entry is there. It is in the PostgreSQL > > installation directory. > > It's there because the RPM sets up a database under /var/lib/pgsql/data. > > > 1) stop the postmaster > > 2) rm -rf /var/lib/pgsql/data/pg_xlog > > 3) mv /raid02/databases/pg_xlog /var/lib/pgsql/data/pg_xlog > > 4) ln -s /var/lib/pgsql/data/pg_xlog /raid02/databases/pg_xlog > > 5) start postmaster > > Put the xlog anywhere BUT there! > > > If I can do that and place the pg_xlog in the installation > > directory will I create any installation issues the next > > time I upgrade PostgreSQL? > > Oh, the installation will be just fine ... but your database will not > be after the upgrade wipes out your WAL. Put the xlog under some > non-system-defined directory. > > regards, tom lane > > ---(end of broadcast)--- > TIP 7: don't forget to increase your free space map settings Thanks Tom. I am glad I asked before I leaped. 8-0 Is there a convention that most people follow. It would seem that anywhere in the installation directory is a bad idea. From what I have read on other threads it does not want to be in the database directory since in most cases that would put it on the same disk as the database. I am assuming due to lack of reaction that the symbolic link is not an issue. Is there a cleaner or more appropriate way of moving the pg_xlog. Finally, am I correct in assuming that as long as the postmaster is shut down moving the log is safe? Kind Regards, Keith ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] Moving pg_xlog
Keith Worthington wrote: On Wed, 01 Jun 2005 12:19:40 -0400, Tom Lane wrote "Keith Worthington" <[EMAIL PROTECTED]> writes: I have been reading about increasing PostgreSQL performance by relocating the pg_xlog to a disk other than the one where the database resides. I have the following pg_xlogs on my system. /raid02/databases/pg_xlog /raid02/rhdb_databases/pg_xlog /raid02/databases-8.0.0/pg_xlog /var/lib/pgsql/data/pg_xlog I have no idea why the forth entry is there. It is in the PostgreSQL installation directory. It's there because the RPM sets up a database under /var/lib/pgsql/data. 1) stop the postmaster 2) rm -rf /var/lib/pgsql/data/pg_xlog 3) mv /raid02/databases/pg_xlog /var/lib/pgsql/data/pg_xlog 4) ln -s /var/lib/pgsql/data/pg_xlog /raid02/databases/pg_xlog 5) start postmaster Put the xlog anywhere BUT there! If I can do that and place the pg_xlog in the installation directory will I create any installation issues the next time I upgrade PostgreSQL? Oh, the installation will be just fine ... but your database will not be after the upgrade wipes out your WAL. Put the xlog under some non-system-defined directory. regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings Thanks Tom. I am glad I asked before I leaped. 8-0 Is there a convention that most people follow. It would seem that anywhere in the installation directory is a bad idea. From what I have read on other threads it does not want to be in the database directory since in most cases that would put it on the same disk as the database. We tend to use somthing that associates the WAL with the appropriate cluster, like /var/lib/CLUSTER for the data /var/lib/CLUSTER_WAL for WAL files. I am assuming due to lack of reaction that the symbolic link is not an issue. Is there a cleaner or more appropriate way of moving the pg_xlog. A symbolic link is the standard way to do it. Finally, am I correct in assuming that as long as the postmaster is shut down moving the log is safe? You are correct. Moving the WAL files with the postmaster running would be a very bad thing. -- Brad Nicholson 416-673-4106 Database Administrator, Afilias Canada Corp. ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] Moving pg_xlog
"Keith Worthington" <[EMAIL PROTECTED]> writes: > On Wed, 01 Jun 2005 12:19:40 -0400, Tom Lane wrote >> Put the xlog anywhere BUT there! > Is there a convention that most people follow. It would seem that > anywhere in the installation directory is a bad idea. From what I > have read on other threads it does not want to be in the database > directory since in most cases that would put it on the same disk as > the database. I don't know of any fixed convention. The way I would be inclined to do it, given that I wanted the data on disk 1 (with mount point /disk1) and xlog on disk 2 (with mount point /disk2) is to create postgres-owned directories /disk1/postgres/ and /disk2/postgres/, and then within those put the data directory (thus, /disk1/postgres/data/) and xlog directory (/disk2/postgres/pg_xlog/). Having an extra level of postgres-owned directory is handy since it makes it easier to do database admin work without being root --- once you've created those two directories and chown'd them to postgres, everything else can be done as the postgres user. Now that I think about it, you were (if I understood your layout correctly) proposing to put the xlog on your system's root disk. This is probably a bad idea for performance, because there will always be other traffic to the root disk. What you are really trying to accomplish is to make sure the xlog is on a disk spindle that has no other traffic besides xlog, so that the disk heads never have to move off the current xlog file. The xlog traffic is 100% sequential writes and so you can cut the seeks involved to near nil if you can dedicate a spindle to it. > I am assuming due to lack of reaction that the symbolic link is not an issue. > Is there a cleaner or more appropriate way of moving the pg_xlog. No, that's exactly the way to do it. > Finally, am I correct in assuming that as long as the postmaster is shut down > moving the log is safe? Right. You can move the data directory too if you want. AFAIR the only position-dependent stuff in there is (if you are using tablespaces in 8.0) the tablespace symlinks under data/pg_tblspc/. You can fix those by hand if you have a need to move a tablespace. regards, tom lane ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] Moving pg_xlog
Tom Lane wrote: ... Now that I think about it, you were (if I understood your layout correctly) proposing to put the xlog on your system's root disk. This is probably a bad idea for performance, because there will always be other traffic to the root disk. What you are really trying to accomplish is to make sure the xlog is on a disk spindle that has no other traffic besides xlog, so that the disk heads never have to move off the current xlog file. The xlog traffic is 100% sequential writes and so you can cut the seeks involved to near nil if you can dedicate a spindle to it. I certainly agree with what you wrote. But my understanding is that if you only have 2 arrays, then moving xlog onto the array not on the database is better than having it with the database. It isn't optimum, but it is better. Because that way there isn't as much contention between the database and xlog. John =:-> signature.asc Description: OpenPGP digital signature
[PERFORM] Forcing use of specific index
Is it any way to attempt to force the planner to use some specific index while creating the plan? Other than eventually dropping all the other indices (which is obiously not a solution in production setting anyway)? I have one case where I have added 16 indices to a table, many of them beeing partial indices. The table itself has only 50k of rows, but are frequently used in heavy joins. I imagine there can be exponential order on the number of alternative paths the planner must examinate as function of the number of indices? It seems to me that the planner is quite often not choosing the "best" index, so I wonder if there is any easy way for me to check out what the planner think about a specific index :-) -- Tobias Brox, Beijing ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[PERFORM] Adaptec/LSI/?? RAID
We're in the process of buying another Opteron server to run Postgres, and based on the suggestions in this list I've asked our IT director to get an LSI MegaRaid controller rather than one of the Adaptecs. But when we tried to place our order, our vendor (Penguin Computing) advised us: "we find LSI does not work well with 4GB of RAM. Our engineering find that LSI card could cause system crashes. One of our customer ... has found that Adaptec cards works well on PostGres SQL -- they're using it as a preforce server with xfs and post-gress." Any comments? Suggestions for other RAID controllers? ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [PERFORM] Adaptec/LSI/?? RAID
Stacy White presumably uttered the following on 06/01/05 23:42: We're in the process of buying another Opteron server to run Postgres, and based on the suggestions in this list I've asked our IT director to get an LSI MegaRaid controller rather than one of the Adaptecs. But when we tried to place our order, our vendor (Penguin Computing) advised us: "we find LSI does not work well with 4GB of RAM. Our engineering find that LSI card could cause system crashes. One of our customer ... has found that Adaptec cards works well on PostGres SQL -- they're using it as a preforce server with xfs and post-gress." Any comments? Suggestions for other RAID controllers? ---(end of broadcast)--- We use the LSI MegaRaid 320-2x with the battery-backed cache on a dual opteron system that uses 8G of RAM. OS is FreeBSD amd64 (5.4) and runs without hesitation. Database currently over 100GB and it performs admirably. So chalk one anecdotal item towards the LSI column. To be fair I have not tried an Adaptec card with this setup so I cannot comment positively or negatively on that card. As a side note, we did have issues with this setup with Linux (2.6 kernel - 64bit) and XFS file system (we generally use FreeBSD but I wanted to try other 64bit OSes before committing). Whether the linux issues were due to the LSI, memory, Tyan mobo, or something else was never determined -- FreeBSD ran it and did so without flinching so our choice was easy. HTH Sven ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] Adaptec/LSI/?? RAID
I've used LSI MegaRAIDs successfully in the following systems with both Redhat 9 and FC3 64bit. Arima HDAMA/8GB RAM Tyan S2850/4GB RAM Tyan S2881/4GB RAM I've previously stayed away from Adaptec because we used to run Solaris x86 and the driver was somewhat buggy. For Linux and FreeBSD, I'd be less worried as open source development of drivers usually lead to better testing & bug-fixing. Stacy White wrote: We're in the process of buying another Opteron server to run Postgres, and based on the suggestions in this list I've asked our IT director to get an LSI MegaRaid controller rather than one of the Adaptecs. But when we tried to place our order, our vendor (Penguin Computing) advised us: "we find LSI does not work well with 4GB of RAM. Our engineering find that LSI card could cause system crashes. One of our customer ... has found that Adaptec cards works well on PostGres SQL -- they're using it as a preforce server with xfs and post-gress." Any comments? Suggestions for other RAID controllers? ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [PERFORM] Moving pg_xlog
John A Meinel <[EMAIL PROTECTED]> writes: > Tom Lane wrote: >> Now that I think about it, you were (if I understood your layout >> correctly) proposing to put the xlog on your system's root disk. >> This is probably a bad idea for performance, ... > I certainly agree with what you wrote. But my understanding is that if > you only have 2 arrays, then moving xlog onto the array not on the > database is better than having it with the database. It isn't optimum, > but it is better. Because that way there isn't as much contention > between the database and xlog. If the machine isn't doing much else than running the database, then yeah, that may be the best available option. If there are other things going on then you have to think about how much competition there is for the root disk. But the impression I had from the OP's df listing is that he has several disks available ... so he ought to be able to find one that doesn't need to do anything except xlog. regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [PERFORM] 'Fastest' PC's are slowest in the house
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Justin Davis wrote: | I have five PC's accessing a PG database that is mounted on a | Dell Windows 2003 server. The PC's are accessing the database with a | Fujitsu cobol program via ODBC (all machines have same (newest) ODBC | driver from PG). 2 of the machines are the newest I have and both | pretty identically configured but are very slow by comparison to the | others. My colleagues and I are still in the exploration / decision | process, we have been working with and learning the database about 2 months. | | I'm looking to see if anyone knows of O/S or hardware issues right off | the bat or can recommend a debug method, log checking, etc. path we | might follow. | | The program in question reads the PG database and displays matching | query results on a cobol screen, for the point of this topic that is all | it is doing. We run the same query from each PC which returns 15 | records out of a 6,000 record customer DB. | | The machines: | | - 2 are 2.0 Ghz Dells with 512 Ram & XP SP2 - they take just over 2 minutes | - 1 AMD 2.4 with 256 Ram & XP SP2 - just under 2 secs. | - 1 AMD 900 Mhz with 256 Ram & XP SP 1 - just under 2 secs | - 1 Intel 266 Mhz with 256 Ram & Windows 2000 - 11-13 secs | Hello, Justin. While re-reading your post, I was (still) under the impression that those machines are all client machines and that there is only one database they are all accessing. Is my impression true? If so, then I'm afraid there must be some other issue you've been hitting, because from the viewpoint of a postmaster, it is completely irrelevant who the client is. Unless so, can you please provide some evidence that the issue at hand really has to do with the PostgreSQL query shipping to those Dells (profiling, for example), so we have something to work from? My assertion though is that there's either an issue in the ODBC layer, or the COBOL program you're running (be it your code or the runtime). While at it, and completely unrelated, I'm not sure that, both from the performance and reliability viewpoint, running production PostgreSQL on a Windows machine may be the best possible decision. If you have the luxury of experimenting, and unless your side-goal is to run-proof the Windows version of PostgreSQL, I'd suggest you try a couple of alternatives, such as Linux, BSD or even Solaris, whichever you feel will offer you better future support. If you choose to run it on Windows afterall, I'd kindly advise you to do your best to stay on the safe side of the story with a double-checked backup strategy, solely because the Windows version of PostgreSQL is a new product and not widely used in production environments, so there is not much expertise yet in the specifics of keeping it performant, stable and most of all, how to tackle things after the worst has happened. Kind regards, - -- Grega Bremec gregab at p0f dot net -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.4 (GNU/Linux) iD8DBQFCnqfgfu4IwuB3+XoRApSRAJ0aJYEIEnJZlw2TeLtSO/1+qmoLHACbBAjS LahS3A/YMgVthkvnQ3AJcXg= =Cl6f -END PGP SIGNATURE- ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
