Re: [PERFORM] RAID controllers etc... was: PostgreSQL data on aNAS device ?
I have been searching (www.lsil.com) for this megaraid_2 driver you mentioned. What kind of MegaRaid card does the Perc4/Di match? Elite1600? Elite1650? I picked Elite1600 and the latest driver I found was version 2.05.00. Is this one OK for RedHat 9? The README file present only mentions RedHat8... Kind regards, Alexander. ---(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
[PERFORM] RedHat Enterprise Linux ES 3 ?!?!
Hi guys, This basically continues the other thread about the PERC4 RAID controller, but since it is a bit off-topic I thought to start another thread. Thanks for all your help so far :) Earlier today I read about the newly released RedHat Enterprise Linux ES version 3. This version should include out-of-the-box megaraid_2 drivers, so it would support the Dell PERC4/Di RAID controller. However, it is very much more expensive than RedHat Linux 9. RH Linux 9 is free and the Enterpise ES edition will cost between 400 and several 1.000's of dollars, depending on the support you want to go with it. Do any of you guys have experience with the previous version of Enterprise Linux (that would be version 2.1) or even better, are any of you already using version 3? Would you recommend this over RedHat Linux 9? I think that with RH Linux 9 it would be easier to get all the latest versions of components I need (RPMs for PostgreSQL, Apache, Samba etc.), while my guess would be that Enterprise Linux would be more difficult to upgrade... Also, I cannot find any list of packages included in Enterprise Linux 2.1 / 3. Does anyone know if PostgreSQL is included and if so, what version? Kind regards, Alexander Priem. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PERFORM] vacuum locking
On Wed, Oct 22, 2003 at 09:27:47PM -0400, Tom Lane wrote: > trace. What is causing that? Not VACUUM I don't think. It doesn't have > any huge memory demand. But swapping out processes could account for What about if you've set vacuum_mem too high? A -- Andrew Sullivan 204-4141 Yonge Street Afilias CanadaToronto, Ontario Canada <[EMAIL PROTECTED]> M2P 2A8 +1 416 646 3304 x110 ---(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] vacuum locking
Andrew Sullivan <[EMAIL PROTECTED]> writes: > On Wed, Oct 22, 2003 at 09:27:47PM -0400, Tom Lane wrote: >> trace. What is causing that? Not VACUUM I don't think. It doesn't have >> any huge memory demand. But swapping out processes could account for > What about if you've set vacuum_mem too high? Maybe, but only if it actually had reason to use a ton of memory --- that is, it were recycling a very large number of tuples in a single table. IIRC that didn't seem to be the case here. regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [PERFORM] vacuum locking
Mario Weilguni <[EMAIL PROTECTED]> writes: > I think oracle does not do garbage collect, it overwrites the tuples directly > and stores the old tuples in undo buffers. Since most transactions are > commits, this is a big win. ... if all tuples are the same size, and if you never have any transactions that touch enough tuples to overflow your undo segment (or even just sit there for a long time, preventing you from recycling undo-log space; this is the dual of the VACUUM-can't-reclaim-dead-tuple problem). And a few other problems that any Oracle DBA can tell you about. I prefer our system. 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
pgsql-performance@postgresql.org
Asked and answered on the list probably a thousand times, but what else is there to discuss on the performance list? :) I recently built a rather powerful machine to be used in a heavily accessed database.. the machine is a dual AthlonMP 2800+, 2GB of PC2100 ECC, and a 4x18GB RAID-0 using 15k rpm fujitsu MAS drives on a 4ch u160 ICP-Vortex card with 256MB of cache. The box runs FreeBSD, tracking RELENG_4 (-STABLE) and PostGreSQL 7.3.4 from ports (7.3.4_1) There are a few databases running on the machine, but for now, the one that is the most performance sensitive is also arguably the worst designed. The access pattern on a day to day basis looks basically like this: 1. ~75k rows aggregate are inserted into two different tables, 70/30 split between two tables. The 70% going to the smaller table (containing just two integers) and the 30% going into a larger table containing a rather largish (~4KB) text field and more integer types; no searching of any kind is done on this text field, it appears in no where clauses, and is not indexed. 2. As these rows are inserted, other processes see them and for each row: a. A new row containing just one field is inserted, that row being an FK into the 30% table mentioned above. b. A row in a 3rd table is updated; this table never gets deleted from, and rarely sees inserts, it's just a status table, but it has nearly a million rows. The updated row is an integer. c. The 30% table itself is updated. 3. When these processes finish their processing, the rows in both the 70/30 tables and the table from 2a are deleted; The 2b table has a row again updated. There is only one process that does all the inserting, from a web backend. Steps 2 and 3 are done by several other backend processes on different machines, "fighting" to pick up the newly inserted rows and process them. Not the most efficient design, but modifying the current code isn't an option; rest assured that this is being redesigned and new code is being written, but the developer who wrote the original left us with his spaghetti-python mess and no longer works for us. I run a 'vacuum analyze verbose' on the database in question every hour, and a reindex on every table in the database every six hours, 'vacuum full' is run manually as required perhaps anywhere from once a week to once a month. I realize the analyze may not be running often enough and the reindex more often than need be, but I don't think these are adversely affecting performance very much; degredation over time does not appear to be an issue. So on with the question. Given the above machine with the above database and access pattern, I've configured the system with the following options. I'm just wondering what some of you more experierenced pg tuners have to say. I can provide more information such as ipcs, vmstat, iostat, etc output on request but I figure this message is getting long enough already.. Thanks for any input. Kernel and postgres information follows. Related kernel configuration options: ... cpu I686_CPU maxusers 256 ... options MAXDSIZ="(1024UL*1024*1024)" options MAXSSIZ="(512UL*1024*1024)" options DFLDSIZ="(512UL*1024*1024)" ... options SYSVSHM #SYSV-style shared memory options SYSVMSG #SYSV-style message queues options SYSVSEM #SYSV-style semaphores options SHMMAXPGS=65536 options SHMMAX="(SHMMAXPGS*PAGE_SIZE+1)" options SHMSEG=256 options SEMMNI=384 options SEMMNS=768 options SEMMNU=384 options SEMMAP=384 ... relevant postgresql.conf options: max_connections = 128 shared_buffers = 2 max_fsm_relations = 1 max_fsm_pages = 200 max_locks_per_transaction = 64 wal_buffers = 128 sort_mem = 262144 # we have some large queries running at times vacuum_mem = 131072 checkpoint_segments = 16 checkpoint_timeout = 300 commit_delay = 1000 commit_siblings = 32 fsync = true wal_fsync_method = fsync effective_cache_size = 49152 # 384MB, this could probably be higher random_page_cost = 1.7 cpu_tuble_cost = 0.005 cpu_index_tuple_cost = 0.0005 cpu_operator_cost = 0.0012 geqo_threshold = 20 stats_start_collector = true stats_reset_on_server_start = off stats_command_string = true stats_row_level = true stats_block_level = true ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [PERFORM] vacuum locking
On Thu, Oct 23, 2003 at 09:17:41AM -0400, Tom Lane wrote: > > Maybe, but only if it actually had reason to use a ton of memory --- > that is, it were recycling a very large number of tuples in a single > table. IIRC that didn't seem to be the case here. Ah, that's what I was trying to ask. I didn't know if the memory was actually taken by vacuum at the beginning (like shared memory is) or what-all happened. A -- Andrew Sullivan 204-4141 Yonge Street Afilias CanadaToronto, Ontario Canada <[EMAIL PROTECTED]> M2P 2A8 +1 416 646 3304 x110 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] vacuum locking
Tom Lane writes: > ... if all tuples are the same size, and if you never have any Incorrect. If the tuples smaller, Oracle does the right thing. If there's enough space in the page, it shifts the tuples to make room. That's what pctfree, pctused and pctincrease allow you to control. It's all in memory so its fast, and I don't think it has to update any indices. > transactions that touch enough tuples to overflow your undo segment That's easily configured, and hasn't been a problem in the databases I've managed. > (or even just sit there for a long time, preventing you from recycling That's probably bad software or a batch system--which is tuned differently. Any OLTP system has to be able to partition its problems to keep transactions short and small. If it doesn't, it will not be usable. > undo-log space; this is the dual of the VACUUM-can't-reclaim-dead-tuple > problem). And a few other problems that any Oracle DBA can tell you > about. I prefer our system. Oracle seems to make the assumption that data changes, which is why it manages free space within each page as well as within free lists. The database will be bigger but you get much better performance on DML. It is very good at caching so reads are fast. Postgres seems to make the assumption that updates and deletes are rare. A delete/insert policy for updates means that a highly indexed table requires lots of disk I/O when the update happens and the concomitant garbage collection when vacuum runs. But then MVCC makes the assumption that there's lots of DML. I don't understand the philosphical split here. I guess I don't understand what application profiles/statistics makes you prefer Postgres' approach over Oracle's. > The increased I/O activity is certainly to be expected, but what I find > striking here is that you've got substantial swap activity in the second > trace. What is causing that? Not VACUUM I don't think. It doesn't have > any huge memory demand. But swapping out processes could account for > the perceived slowdown in interactive response. The box is a bit memory starved, and we'll be addressing that shortly. I don't think it accounts for 3 minute queries, but perhaps it might. vacuum_mem is 32mb, btw. Rob ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [PERFORM] RedHat Enterprise Linux ES 3 ?!?!
On Thu, 2003-10-23 at 01:40, Alexander Priem wrote: > Hi guys, > > This basically continues the other thread about the PERC4 RAID controller, > but since it is a bit off-topic I thought to start another thread. Thanks > for all your help so far :) > > Earlier today I read about the newly released RedHat Enterprise Linux ES > version 3. This version should include out-of-the-box megaraid_2 drivers, so > it would support the Dell PERC4/Di RAID controller. > > However, it is very much more expensive than RedHat Linux 9. RH Linux 9 is > free and the Enterpise ES edition will cost between 400 and several 1.000's > of dollars, depending on the support you want to go with it. > > Do any of you guys have experience with the previous version of Enterprise > Linux (that would be version 2.1) or even better, are any of you already > using version 3? > > Would you recommend this over RedHat Linux 9? I think that with RH Linux 9 > it would be easier to get all the latest versions of components I need (RPMs > for PostgreSQL, Apache, Samba etc.), while my guess would be that Enterprise > Linux would be more difficult to upgrade... The reason to get RHEL over RH9 or the upcoming Fedora releases is for stability. They have a -much- longer stability period, release cycle, and support lifetime. You get RHEL if you want a distribution that you can get commercial support for, install the server and then not touch it. For production machines of this nature you'll pretty much never have the latest and greatest packages. Instead you'll have the most completely stable packages. The two distribution types are really apples and oranges. They are both fruit ( they are both linux distros ) but they sure taste different. > Also, I cannot find any list of packages included in Enterprise Linux 2.1 / > 3. Does anyone know if PostgreSQL is included and if so, what version? You have two options as I understand it for PG under RHEL. You can install the PG source from Postgres themselves, or you can use the Postgresql Red Hat Edition. Bruce I think can give you more information on this product. http://sources.redhat.com/rhdb/index.html This is the link to it. > > Kind regards, > Alexander Priem. Hope this helps, Will signature.asc Description: This is a digitally signed message part
Re: [PERFORM] RAID controllers etc... was: PostgreSQL data on aNAS
On Thu, 23 Oct 2003, Alexander Priem wrote: > I have been searching (www.lsil.com) for this megaraid_2 driver you > mentioned. > > What kind of MegaRaid card does the Perc4/Di match? Elite1600? Elite1650? > > I picked Elite1600 and the latest driver I found was version 2.05.00. Is > this one OK for RedHat 9? The README file present only mentions RedHat8... I would guess it's a MegaRaid320-2 card, listed here: http://www.lsilogic.com/products/stor_prod/raid/3202.html Since the Elite1600/1650 seem to be U160 cards and the Perc/4Di would seem to be listed as a U320 card at this page: http://www.domsch.com/linux/ ---(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] RedHat Enterprise Linux ES 3 ?!?!
On Thu, 2003-10-23 at 11:27, Will LaShell wrote:
> > Also, I cannot find any list of packages included in Enterprise Linux
> 2.1 /
> > 3. Does anyone know if PostgreSQL is included and if so, what version?
>
> You have two options as I understand it for PG under RHEL. You can
> install the PG source from Postgres themselves, or you can use the
> Postgresql Red Hat Edition. Bruce I think can give you more information
> on this product. http://sources.redhat.com/rhdb/index.html This is the
> link to it.
>
Bruce works for SRA, not Red Hat, so he's probably not your best option
to talk to on PRHE... While there are Red Hat employees floating around
these lists, I'd first suggest reading over the website and then either
emailing the PRHE lists or one of it's team members depending on the
specifics of any questions.
Robert Treat
--
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
---(end of broadcast)---
TIP 8: explain analyze is your friend
Re: [PERFORM] RedHat Enterprise Linux ES 3 ?!?!
On Thu, 2003-10-23 at 08:44, Robert Treat wrote:
> On Thu, 2003-10-23 at 11:27, Will LaShell wrote:
> > > Also, I cannot find any list of packages included in Enterprise Linux
> > 2.1 /
> > > 3. Does anyone know if PostgreSQL is included and if so, what version?
> >
> > You have two options as I understand it for PG under RHEL. You can
> > install the PG source from Postgres themselves, or you can use the
> > Postgresql Red Hat Edition. Bruce I think can give you more information
> > on this product. http://sources.redhat.com/rhdb/index.html This is the
> > link to it.
> >
>
> Bruce works for SRA, not Red Hat, so he's probably not your best option
> to talk to on PRHE... While there are Red Hat employees floating around
Gah that's right. *beats self*
> these lists, I'd first suggest reading over the website and then either
> emailing the PRHE lists or one of it's team members depending on the
> specifics of any questions.
Don't forget you can always call the RedHat sales people as well. They
usually have good product knowledge especially since you are talking
about the Advanced Server lines.
> Robert Treat
> --
> Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
Will
signature.asc
Description: This is a digitally signed message part
Re: [PERFORM] RedHat Enterprise Linux ES 3 ?!?!
Robert Treat wrote: > On Thu, 2003-10-23 at 11:27, Will LaShell wrote: > > > Also, I cannot find any list of packages included in Enterprise Linux > > 2.1 / > > > 3. Does anyone know if PostgreSQL is included and if so, what version? > > > > You have two options as I understand it for PG under RHEL. You can > > install the PG source from Postgres themselves, or you can use the > > Postgresql Red Hat Edition. Bruce I think can give you more information > > on this product. http://sources.redhat.com/rhdb/index.html This is the > > link to it. > > > > Bruce works for SRA, not Red Hat, so he's probably not your best option > to talk to on PRHE... While there are Red Hat employees floating around > these lists, I'd first suggest reading over the website and then either > emailing the PRHE lists or one of it's team members depending on the > specifics of any questions. Way off topic, but let's do Red Hat a favor for employing PostgreSQL folks --- here is a nice URL I read yesterday on the topic: http://news.com.com/2100-7344-5094774.html?tag=nl -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PERFORM] vacuum locking
> "RN" == Rob Nagler <[EMAIL PROTECTED]> writes: RN> Vivek Khera writes: >> AMI or Adaptec based? RN> Adaptec, I think. AIC-7899 LVD SCSI is what dmidecode says, and RN> Red Hat/Adaptec aacraid driver, Aug 18 2003 is what comes up when it Cool. No need to diddle with it, then. The Adaptec work quite well, especially if you have battery backup. Anyhow, it seems that as Tom mentioned, you are going into swap when your vacuum runs, so I'll suspect you're just at the edge of total memory utilization, and then you go over the top. Another theory is that the disk capacity is near saturation, the vacuum causes it to slow down just a smidge, and then your application opens additional connections to handle the incoming requests which don't complete fast enough, causing more memory usage with the additional postmasters created. Again, you suffer the slow spiral of death due to resource shortage. I'd start by getting full diagnosis of overall what your system is doing during the vacuum (eg, additional processes created) then see if adding RAM will help. Also, how close are you to the capacity of your disk bandwidth? I don't see that in your numbers. I know in freebsd I can run "systat -vmstat" and it gives me a percentage of utilization that lets me know when I'm near the capacity. -- =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Vivek Khera, Ph.D.Khera Communications, Inc. Internet: [EMAIL PROTECTED] Rockville, MD +1-240-453-8497 AIM: vivekkhera Y!: vivek_khera http://www.khera.org/~vivek/ ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [PERFORM] slow select
> "JB" == Josh Berkus <[EMAIL PROTECTED]> writes: JB> Medora, >> Increasing effective_cache_size to 1 did it. JB> That would be 78MB RAM. If you have more than that available, you can JB> increase it further. Ideally, it should be about 2/3 to 3/4 of available JB> RAM. Assuming your OS will use that much RAM for the cache... the whole world's not Linux :-) -- =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Vivek Khera, Ph.D.Khera Communications, Inc. Internet: [EMAIL PROTECTED] Rockville, MD +1-240-453-8497 AIM: vivekkhera Y!: vivek_khera http://www.khera.org/~vivek/ ---(end of broadcast)--- TIP 8: explain analyze is your friend
pgsql-performance@postgresql.org
> "AL" == Allen Landsidel <[EMAIL PROTECTED]> writes: AL> I recently built a rather powerful machine to be used in a heavily AL> accessed database.. the machine is a dual AthlonMP 2800+, 2GB of AL> PC2100 ECC, and a 4x18GB RAID-0 using 15k rpm fujitsu MAS drives on a AL> 4ch u160 ICP-Vortex card with 256MB of cache. The only recommendation I'd make is to switch from RAID0 to RAID10, unless you can afford the downtime (and loss of data) when one of your drives takes a vacation. Also, is your RAID card cache battery backed up? If no, then you lose the ability to use write-back and this costs *dearly* in performance. AL> The box runs FreeBSD, tracking RELENG_4 (-STABLE) and PostGreSQL 7.3.4 AL> from ports (7.3.4_1) An excellent choice. :-) [[ ... ]] AL> I run a 'vacuum analyze verbose' on the database in question every AL> hour, and a reindex on every table in the database every six hours, AL> 'vacuum full' is run manually as required perhaps anywhere from once a AL> week to once a month. I realize the analyze may not be running often AL> enough and the reindex more often than need be, but I don't think AL> these are adversely affecting performance very much; degredation over AL> time does not appear to be an issue. Personally, I don't think you need to reindex that much. And I don't think you need to vacuum full *ever* if you vacuum often like you do. Perhaps reducing the vacuum frequency may let you reach a steady state of disk usage? Depending on how many concurrent actions you process, perhaps you can use a temporary table for each, so you don't have to delete many rows when you're done. On my busy tables, I vacuum every 6 hours. The vacuum analyze is run on the entire DB nightly. I reindex every month or so my most often updated tables that show index bloat. Watch for bloat by monitoring the size of your indexes: SELECT relname,relpages FROM pg_class WHERE relname LIKE 'some_table%' ORDER BY relname; AL> Related kernel configuration options: AL> ... AL> cpu I686_CPU AL> maxusers 256 let the system autoconfigure maxusers... AL> ... AL> options MAXDSIZ="(1024UL*1024*1024)" AL> options MAXSSIZ="(512UL*1024*1024)" AL> options DFLDSIZ="(512UL*1024*1024)" above are ok at defaults. AL> options SHMMAXPGS=65536 perhaps bump this and increase your shared buffers. I find that if you do lots of writes, having a few more shared buffers helps. AL> options SHMMAX="(SHMMAXPGS*PAGE_SIZE+1)" you don't need to explicitly set this... it is automatically set based on the above setting. AL> relevant postgresql.conf options: AL> max_fsm_pages = 200 this may be overkill. I currently run with 100 AL> effective_cache_size = 49152 # 384MB, this could probably be higher the current recommendation for freebsd is to set this to: `sysctl -n vfs.hibufspace` / 8192 where 8192 is the blocksize used by postgres. You may also want to increase the max buffer space used by FreeBSD, which apparently is capped at 200M (I think) by dafault. I'll have to look up how to bump that, as most likely you have plenty of RAM sitting around unused. What does "top" say about that when you're busy? -- =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Vivek Khera, Ph.D.Khera Communications, Inc. Internet: [EMAIL PROTECTED] Rockville, MD +1-240-453-8497 AIM: vivekkhera Y!: vivek_khera http://www.khera.org/~vivek/ ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [PERFORM] vacuum locking
Am Donnerstag, 23. Oktober 2003 15:26 schrieb Tom Lane: > ... if all tuples are the same size, and if you never have any > transactions that touch enough tuples to overflow your undo segment > (or even just sit there for a long time, preventing you from recycling > undo-log space; this is the dual of the VACUUM-can't-reclaim-dead-tuple > problem). And a few other problems that any Oracle DBA can tell you about. > I prefer our system. of course both approaches have advantages, it simply depends on the usage pattern. A case where oracle really rules over postgresql are m<-->n connection tables where each record consist of two foreign keys, the overwrite approach is a big win here. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
