Re: [PERFORM] Jdbc/postgres performance
On 17-Oct-06, at 3:05 PM, Behl, Rohit ((Infosys)) wrote: HiWe are facing performance problems in postgres while executing a query. When I execute this query on the server it takes 5-10 seconds. Also I get good performance while executing this query from my code in java with the hard codes values. I face severe performance problems when I run it using a prepared statement. The query is as follows:Select events.event_id, ctrl.real_name, events.tsds, events.value, events.lds, events.correction, ctrl.type, ctrl.freq from iso_midw_data_update_events events, iso_midw_control ctrl where events.obj_id = ctrl.obj_id and events.event_id 68971124 order by events.event_id limit 2000The above query executes in 5-10 seconds.However the below query executes in 8 mins:Select events.event_id, ctrl.real_name, events.tsds, events.value, events.lds, events.correction, ctrl.type, ctrl.freq from table events, iso_midw_control ctrl where events.obj_id = ctrl.obj_id and events.event_id ?::bigint order by events.event_id limit ?setLong(1, 68971124);setInt(2, 2000);The table has close to 5 million rows. The table has the following index:iso_midw_data_update_events_event_id_keyiso_midw_data_update_events_lds_idxiso_midw_data_update_events_obj_id_idx The table is described as follows:Columns_name data_type type_name column_sizelds 2 numeric 13obj_id 2 numeric 6tsds 2 numeric 13value 12 varchar 22correction 2 numeric 1delta_lds_tsds 2 numeric 13event_id -5 bigserial 8Please tell me what I am missing while setting the prepared statement. I am using postgres7.4.2. and postgresql-8.1-407.jdbc3.jar.Try the same query with protocolVersion=2. There are some issues with prepared statements being slower if the parameters are not the same type as the column being compared to.protocol version 2 will issue the query exactly the same as psql does. Also note that your two queries are not identical. In the prepared query you cast to bigint ?Version 8.1.x handles this better I think. Thanks RegardsRohit
Re: [PERFORM] New hardware thoughts
-Original Message- From: [EMAIL PROTECTED] [mailto:pgsql-performance- [EMAIL PROTECTED] On Behalf Of Joshua D. Drake Sent: Friday, October 20, 2006 2:52 PM To: Ben Suffolk Cc: Dave Cramer; pgsql-performance@postgresql.org Subject: Re: [PERFORM] New hardware thoughts Ben Suffolk wrote: You mentioned a Perc controller, so I'll assume this is a Dell. My advice is to find another supplier. check the archives for Dell. Basically you have no idea what the Perc controller is since it is whatever Dell decides to ship that day. In general though you are going down the right path here. Disks first, memory second, cpu third Dave Yes I am looking at either the 2950 or the 6850. I think the only think that the 6850 really offers me over the 2950 is more expandability in the spare processor, and additional memory sockets. In all other respects the config I am looking at would fit either chassis. Although the 2950, being slightly newer has the DRAC 5 (dells implementation of IPMI) management, which may be useful. Get an HP with the 64* series. They are a good, well rounded machine for PostgreSQL. http://h10010.www1.hp.com/wwpc/pscmisc/vac/us/en/ss/proliant/proliant- dl.html?jumpid=re_R295_prodexp/busproducts/computing-server/proliant-dl I hear what you say about the raid card, but how likely are they to change it from the LSI Mega Raid one in reality? But I am open to Heh... very likely. I have a 6 drive Dell machine with a Perc controller (lsi rebrand). If I put it in RAID 5, it refuses to get more than 8 megs a second. If I put it in RAID 10, it get about 50 megs a second. If I get the offshelf LSI Megaraid withe the same configuration? You don't want to know... it will just make you want to cry at the fact that you bought a Dell. I agree there's better platforms out there than Dell, but the above is simply not true for the 2950. Raid 5, dd, on 6 disks, I get about 260Mb/s sustained writes. Granted, this should be faster, but... it's a far cry from 8 or 50MB/s. I posted some numbers here a while back on the 2950, so you might want to dig those out of the archives. For CPU, if that's a concern, make sure you get Woodcrest with 4MB shared cache per socket. These are extremely fast CPU's (Intel's 80% performance improvements over the old Xeons actually seem close). Oh, and I would NOT recommend planning to add CPU's to a dell box after you've purchased it. I've seen too many CPU upgrades go awry. Adding disks, no biggie, adding ram, eh, don't mind, adding CPU, I try to stay away from for reliability purposes. Also, I have had experience with at least half dozen 2850's and 2950's - all have had the LSI controllers re-branded as Perc. If this is a concern, talk with dell, and I believe you get a 30 day money-back guarantee. I've used this before, and yes, they will take the server back. The sales guys aren't too bright, they'll promise anything, but as long as you can give the server back... (true, we buy a lot of dell servers.. so... get confirmation from dell on what return policy applies to your purchase) If you're not concerned about space, go for the 8 2.5 disks. You'll get more raw storage out of 300GB 3.5, but unless you need it, you'd be better served with the additional spindles. As for FreeBSD- I'd advise taking a good look at 6.2, its' in beta and they've fixed quite a few problems with the 2950 (Raid controller and bce nic issues come to mind). Lastly, if you have the money and rack space for an external disk cage, take a look at Dell's MD1000 - not as good as some of the sun offerings, but not too shabby for dell. (Note that I have not tested the MD1000 so I'm just going off of my 2950 experience and the specs for the MD1000). The above comes from being stuck with dell and trying to make the best of it. Turns out it's not as bad as it used to be. Oh, and side note, this may be obvious for some, but if you're running BSD and need support, ask to speak to the Linux guys (or simply tell them you're running Linux). Avoid Dell's windows support at all costs... - Bucky ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] New hardware thoughts
On 20-10-2006 16:58 Dave Cramer wrote: Ben, My option in disks is either 5 x 15K rpm disks or 8 x 10K rpm disks (all SAS), or if I pick a different server I can have 6 x 15K rpm or 8 x 10K rpm (again SAS). In each case controlled by a PERC 5/i (which I think is an LSI Mega Raid SAS 8408E card). You mentioned a Perc controller, so I'll assume this is a Dell. My advice is to find another supplier. check the archives for Dell. Basically you have no idea what the Perc controller is since it is whatever Dell decides to ship that day. As far as I know, the later Dell PERC's have all been LSI Logic-controllers, to my knowledge Dell has been a major contributor to the LSI-Linux drivers... At least the 5/i and 5/e have LSI-logic controller chips. Although the 5/e is not an exact copy of the LSI Mega raid 8480E, its board layout and BBU-memory module are quite different. It does share its functionality however and has afaik the same controller-chip on it. Currently we're using a Dell 1950 with PERC 5/e connecting a MD1000 SAS-enclosure, filled with 15 36GB 15k rpm disks. And the Dell-card easily beats an ICP Vortex-card we also connected to that enclosure. Ow and we do get much more than, say, 8-50 MB/sec out of it. WinBench99 gets about 644MB/sec in sequential reading tops from a 14-disk raid10 and although IOmeter is a bit less dramatic it still gets over 240MB/sec. I have no idea how fast a simple dd would be and have no bonnie++ results (at hand) either. At least in our benchmarks, we're convinced enough that it is a good set-up. There will be faster set-ups, but at this price-point it won't surprise me if its the fastest disk-set you can get. By the way, as far as I know, HP offers the exact same broadcom network chip in their systems as Dell does... So if that broadcom chip is unstable on a Dell in FreeBSD, it might very well be unstable in a HP too. Best regards, Arjen ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] pgBench on Windows
Does the one that ships in the installer not work?//Magnusit does work.*putting ashes on my head*Googled around and only found pgbench.c; never looked in program directory. Sorry, my mistake. Harald-- GHUM Harald Massa persuadere et programmareHarald Armin MassaReinsburgstraße 202b70197 Stuttgart0173/9409607-Python: the only language with more web frameworks than keywords.
Re: [PERFORM] Vacuum and Memory Loss
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: If you are saying that running the vacuum full helps your performance, then you want to make sure you are running plain vacuum and analyze frequently enough. If you have a database which has lots of update and delete statements, and you do not run vacuum regularly enough, you can end up with lots dead blocks slowing down database scans. If you do lots of updates and deletes you should shedule vacuum and analyze more often, or you might want to look into running auto vacuum: http://www.postgresql.org/docs/8.1/interactive/maintenance.html#AUTOVACUUM If you aren't doing lots of updates and deletes, then maybe you just have a busy database. Lots of postmaster processes implies you have lots of clients connecting to your database. You can turn on stats_command_string and then check the pg_stat_activity table to see what these connections are doing. If they are running queries, you can try to optimize them. Try turning on logging of long running queries with log_min_duration_statement. Then use EXPLAIN ANALYZE to see why the query is slow and if anything can be done to speed it up. ---(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] New hardware thoughts
If I get the offshelf LSI Megaraid withe the same configuration? You don't want to know... it will just make you want to cry at the fact that you bought a Dell. I agree there's better platforms out there than Dell, but the above is simply not true for the 2950. Raid 5, dd, on 6 disks, I get about 260Mb/s sustained writes. Granted, this should be faster, but... it's a far cry from 8 or 50MB/s. I posted some numbers here a while back on the 2950, so you might want to dig those out of the archives. Well these are 3 year old machines, they could have improved a bit but it is quite true for the version of the Dells I have. I can duplicate it on both machines. Frankly Dell has a *long* way to go to prove to me that they are a quality vendor for Server hardware. Joshua D. Drake ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
