Re: [PERFORM] Moving postgresql.conf tunables into 2003...
Michael Pohl wrote: > On Sun, 6 Jul 2003, Matthew Nuzum wrote: > > > At the very least, if there is good documentation for these parameters, > > maybe the conf file should provide a link to this info. > > I believe that is what Josh is proposing: > > http://archives.postgresql.org/pgsql-performance/2003-07/msg00102.php > > > [Apache httpd] uses a three phase (if not more) documentation level. > > The .conf file contains detailed instructions in an easy to read and > > not-to-jargon-ish structure. The docs provide detailed tutorials and > > papers that expand on configuration params in an easy to read format. > > Both of these refer to the thorough reference manual that breaks each > > possible option down into it's nitty gritty details so that a user can > > get more information if they so desire. > > I agree that Apache's approach is primo. Often the .conf comments are > enough to jog my memory about a directive I haven't used for a while. Or > the comments are enough to let me know I don't need a directive, or that I > need to go to the manual and read more. I appreciate that. Isn't that what we have now --- isn't postgresql.conf clear enough to jog people's memory. -- 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 4: Don't 'kill -9' the postmaster
Re: [PERFORM] Moving postgresql.conf tunables into 2003...
Keep in mind that if we auto-tune, we will only be able to do it for some platforms, so we will need a table that shows which settings are autotuned for each platform. --- Sean Chittenden wrote: > > I don't have much to add because I'm pretty new to Postgres and have > > been soliciting advice here recently, but I totally agree with > > everything you said. I don't mind if it's in the postgres.conf file > > or in a faq that is easy to find, I just would like it to be in one > > place. A good example of the need for this is when I was tuning > > "effective_cache" I thought that was creating a cache for Postgres > > when in fact as it was pointed out to me, it's just hinting to > > postgres the size of the OS cache. Lots of ways for people to get > > really confused here. > > I looked through the src/doc/runtime.sgml for a good place to stick > this and couldn't find a place that this seemed appropriate, but on > FreeBSD, this can be determined with a great deal of precision in a > programmatic manner: > > echo "effective_cache_size = $((`sysctl -n vfs.hibufspace` / 8192))" > > The same OID is available via C too. It'd be slick if PostgreSQL > could tune itself (on FreeBSD) at initdb time with the above code. If > Linux exports this info via /proc and can whip out the appropriate > magic, even better. An uncommented out good guess that shows up in > postgresql.conf would be stellar and quite possible with the use of > sed. > > Maybe an initdb switch could be added to have initdb tune the config > it generates? If a -n is added, have it generate a config and toss it > to stdout? > > > case `uname` in > "FreeBSD") > echo "effective_cache_size = $((`sysctl -n vfs.hibufspace` / 8192))" > ;; > *) > echo "Unable to automatically determine the effective cache size" >> > /dev/stderr > ;; > esac > > > -sc > > -- > Sean Chittenden > > ---(end of broadcast)--- > TIP 4: Don't 'kill -9' the postmaster > -- 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 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 vs. MySQL
I think the issue with multiple users is that a car is good for moving a few people, but it can't move lots of large boxes. A truck can move large boxes, but it can't move a few people efficiently. PostgreSQL is more like a truck, while MySQL is more like a car. As an aside, I think Solaris is slower than other OS's because it is built to scale efficiently to many CPU's, and that takes a performance hit in a machine with just a few CPU's, though they are working on tuning those cases. Of course, this is all just a generalization. --- scott.marlowe wrote: > On Fri, 4 Jul 2003, Brian Tarbox wrote: > > > I'm actually leaving this list but I can answer this question. Our results > > were with a single user and we were running Inodb. We were running on > > RedHat 8.0 / 9.0 with vanilla linux settings. > > Hi Brian, I just wanted to add that if you aren't testing your setup for > multiple users, you are doing yourself a disservice. The performance of > your app with one user is somewhat interesting, the performance of the > system with a dozen or a hundred users is of paramount importance. > > A server that dies under heavy parallel load is useless, no matter how > fast it ran when tested for one user. Conversely, one would prefer a > server that was a little slow for single users but can hold up under load. > > When I first built my test box a few years ago, I tested postgresql / > apache / php at 100 or more parallel users. That's where things start > getting ugly, and you've got to test for it now, before you commit to a > platform. > > Postgresql is designed to work on anything out of the box, which means > it's not optimized for high performance, but for running on old Sparc 2s > with 128 meg of ram. If you're going to test it against MySQL, be fair to > yourself and performance tune them both before testing, they're > performance on vanilla linux with vanilla configuration tuning teachs you > little about how they'll behave in production on heavy iron. > > Good luck on your testing, and please, don't quit testing at the first > sign one or the other is faster, be throrough and complete, including > heavy parallel load testing with reads AND writes. Know the point at > which each system begins to fail / become unresponsive, and how they > behave in overload. > > > > ---(end of broadcast)--- > TIP 7: don't forget to increase your free space map settings > -- 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 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [PERFORM] PostgreSQL vs. MySQL
Brian Tarbox wrote: > Oddly enough, the particular application in question will have an extremely > small user base...perhaps a few simultainous users at most. > > As to the testing, I neglected to say early in this thread that my manager > instructed me _not_ to do further performance testing...so as a good > consultant I complied. I'm not going to touch if that was a smart > instruction to give :-) Performance is probably 'good enough', and you can revisit it later when you have more time. -- 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 7: don't forget to increase your free space map settings
Re: [PERFORM] Poor delete performance AFTER vacuum analyze
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 I looked back at my code and I also need to reclarify something. The delete at the end is multiple delete statements within a transaction. After full vacuum with 160,000 records in Table: (takes a bit the first time through) Tlog=# explain analyze delete from Tlog where Tlog_ID <= 47766002 and host='tbp-pp'; QUERY PLAN - - Index Scan using shost_idx on tlog (cost=0.00..6281.45 rows=136 width=6) (actual time=64529.43..64529.43 rows=0 loops=1) Index Cond: (host = 'tbp-pp'::character varying) Filter: (tlog_id <= 47766002) Total runtime: 64529.52 msec After zero records in table: ( Tlog=# explain analyze delete from Tlog where Tlog_ID <= 47766002 and host='tbp-pp'; QUERY PLAN - --- Index Scan using shost_idx on tlog (cost=0.00..6281.45 rows=136 width=6) (actual time=84.87..84.87 rows=0 loops=1) Index Cond: (host = 'tbp-pp'::character varying) Filter: (tlog_id <= 47766002) Total runtime: 84.96 msec Slow Explain after vacuum analyze: (this is when it gets bad) TLog=# explain analyze delete from Tlog where Tlog_ID <= 47766002 and shost='tbp-pp'; QUERY PLAN - -- Index Scan using shost_idx on tlog (cost=0.00..6128.52 rows=82 width=6) (actual time=262178.82..262178.82 rows=0 loops=1) Index Cond: (host = 'tbp-pp'::character varying) Filter: (tlog_id <= 47766002) Total runtime: 262178.96 msec - -- Jeremy M. Guthrie Systems Engineer Berbee 5520 Research Park Dr. Madison, WI 53711 Phone: 608-298-1061 Berbee...Decade 1. 1993-2003 -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.1 (GNU/Linux) iD8DBQE/GysLqtjaBHGZBeURAhNTAJ0QA2/eZM/DhSyxmXi89i6kXFQFwgCfacZY UIMUdK95O3N0UpOTxedM6Pw= =laUO -END PGP SIGNATURE- ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PERFORM] [pgsql-advocacy] About the default performance
I can help with this too. --- scott.marlowe wrote: > I'm willing to help too. I'm basically a DBA / developer type, with mild > C hacking skills (I develop in PHP, so my C coding is quite rusty > nowadays.) > > If nothing else testing on different equipment / OSes. > > On Fri, 4 Jul 2003, Josh Berkus wrote: > > > Kaarel: > > > > (cross-posted back to Performance because I don't want to post twice on the > > same topic) > > > > > The problem is that people often benchmark the so called vanilla > > > installation of PostgreSQL. > > > > > I remember a discussion in the general list about having multiple > > > default conf files to choose from. Ala low-end, average and high-end > > > installations. A tool to read some system information and dynamically > > > generating a proper configuration file was also mentioned. > > > > Yes. So far, only Justin, Kevin B., Shridhar and I have volunteered to do any > > work on that task -- and all of us have been swamped with 7.4-related stuff. > > > > I would like to see, before the end of the year, some if not all of the stuff > > that Kaarel is posting about. Obviously, my first task is to set up a > > framework so that everyone can contribute to the project. > > > > > I'm not an expert of PostgreSQL by any means I have just been reading > > > PostgreSQL email lists for only about a month or so. So I believe I have > > > read that there is a auto-vacuum being worked on? In my opinion this > > > should be included in the main installation by default. This is just the > > > kind of job that a machine should do...when a big portion of data has > > > changed do VACUUM ANALYCE automagically. > > > > > > Is these improvements actually being implemented and how far are they? > > > > The auto-vacuum daemon (pgavd) is finished. However, it will still require > > the user to turn it on; we don't want to run potentially RAM-sucking > > background processes without user invitiation. So obviously that needs to be > > part of a comprehensive "quick start" guide. > > > > So, Kaarel you want to write the "quick start" guide for 7.4? All of > > the detail material is available online, you mainly need to provide narrative > > and links of the form of ... first, read this: , then do this ... > > > > > The technical side of these problems is not for this list of course. > > > However the "side-effects" (reputation of being slow) of these problems > > > direclty relate to advocacy and PostgreSQL popularity. Maybe these > > > problems are already worked on or maybe I'm over exaggerating the > > > situation but I do believe solving these issues would only benefit > > > PostgreSQL. > > > > You're absolutely correct so let's do something about it. From my > > perspective, the first step is improved docs, becuase we can have those out > > by 7.4 release. > > > > > > > ---(end of broadcast)--- > TIP 5: Have you checked our extensive FAQ? > >http://www.postgresql.org/docs/faqs/FAQ.html > -- 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] Poor delete performance AFTER vacuum analyze
"Jeremy M. Guthrie" <[EMAIL PROTECTED]> writes: > I looked back at my code and I also need to reclarify something. The delete > at the end is multiple delete statements within a transaction. I think you are going to have to show us all the details of what you're doing in between these queries. I was expecting to see a difference in query plans, but you've got the exact same plan in all three runs --- so it's not the planner making the difference here, nor the ANALYZE statistics. My best conjecture now is something odd about the way you are deleting the old data or loading the new. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PERFORM] Dual Xeon + HW RAID question
Alexandre, I missed your orig. post, but AFAIK multiprocessing kernels will handle HT CPUs as 2 CPUs each. Thus, our dual Xeon 2.4 is recognized as 4 Xeon 2.4 CPUs. This way, I don't think HT would improve any single query (afaik no postgres process uses more than one cpu), but overall multi-query performance has to improve. - Original Message - From: "Nikolaus Dilger" <[EMAIL PROTECTED]> Sent: Saturday, July 12, 2003 8:25 PM Alexandre, Since you want the fastest speed I would do the 2 data disks in RAID 0 (striping) not RAID 1 (mirroring). If you would care about not loosing any transactions you would keep all 3 disks in RAID 5. Don't know the answer to the Hyperthreading question. Why don't you run a test to find out? Regards, Nikolaus On Thu, 10 Jul 2003 14:43:25 -0300 (BRT), "alexandre arruda paes :: aldeia digital" wrote: > > Hi, > > I have this machine with a 10 million records: > * Dual Xeon 2.0 (HyperThreading enabled), 3 7200 SCSI , > Adaptec 2110S, > RAID 5 - 32k chunk size, 1 GB Ram DDR 266 ECC, RH 8.0 - > 2.4.18 > > The database is mirrored with contrib/dbmirror in a P4 > 1 Gb Ram + IDE > > If a disk failure occurs, I can use the server in the > mirror. > > I will format the main server in this weekend and I > have seen in the list > some people that recomends a Software RAID instead HW. > > I think too remove the RAID 5 and turn a RAID 1 for > data in 2 HDs. > SO, WAL and swap in the thrid HD. > > My questions: > > 1) I will see best disk performance changing the disk > layout like above > 2) HyperThreading really improve a procces basead > program, like postgres > > Thank´s for all > > Alexandre ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PERFORM] ugly query slower in 7.3, even slower after vacuum full analyze
Dear Gurus, I have a query discussed here earlier that suffers heavily from "lack of view flattening" in v7.3. Following Tom's guidance, I made a conclusion to that thread (http://archives.postgresql.org/pgsql-performance/2003-05/msg00215.php) and asked it to be confirmed or fixed, but I didn't get any responses. Here are some times, for which I'd like to get some response. Old machine is New machine is * PIII 800, * Dual Xeon 2.4, * IDE 7200, * 5xSCSI 1 HW RAID 5, * psql 7.2.1,* psql 7.3.3, * orig conf * orig and crude conf, as below. * old: 18 sec* new: 24 sec * new w/ vacuum full verbose analyze: 30-31 sec (!!!) 1. Are these times (18 vs 24) believable with such heavy HW change or is there something fishy about it? * I know multiprocessing doesn't come in view with a single query * but cpu and hw speed should * I know 7.3 is slower because of unflattened views 2. What may be the cause of VACUUM slowing the query? 3. Disabling any one of mergejoin, hashjoin, seqscan did no good. Disabling sort prevented query from finishing in several minutes. 4. I have tried to crudely carve optimizer settings as below, but it changed nothing according to this query. Any further ideas? Note that time tests were taken in close succession (test; killall -HUP postmaster; test; ...) If needed, I can attach query, exp-ana outputs before and after vacuum (carved and uncarved conf file), and the vacuum log itself. TIA, G. --- cut here --- shared_bufers = 4096 sort_mem = 4096 effective_cache_size = 2 random_page_cost = 1.5 --- cut here --- ---(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
