Re: [PERFORM] Poor delete performance AFTER vacuum analyze
Jeremy M. Guthrie [EMAIL PROTECTED] writes: My system will run great after a full vacuum(as I would expect). It will run all day long taking only 3-5 seconds to run and deal with approximately 100megs of new data each day. However, the instant the system finishes only a 'vacuum analyze', the whole thing slows WAY down to where each run can take 10-15 minutes. Could we see EXPLAIN ANALYZE for the deletion query in both the fast and slow states? regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [PERFORM] Poor delete performance AFTER vacuum analyze
On Sat, 19 Jul 2003, Jeremy M. Guthrie wrote: 100megs of new data each day. However, the instant the system finishes only a 'vacuum analyze', the whole thing slows WAY down to where each run can take 10-15 minutes. Have you run EXPLAIN ANALYZE on the delete query before and after the vacuum? Does it explain why it goes slower? -- /Dennis ---(end of broadcast)--- TIP 8: explain analyze is your friend
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] 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. snip 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: link, 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
