Re: [PERFORM] High load average in 64-core server , no I/O wait and CPU is idle
| | Load avg is the number of processes in the running queue, which can | be either waiting to be run or actually running. | | So if you had 100% CPU usage, then you'd most definitely have a load | avg of 64, which is neither good or bad. It may simply mean that | you're using your hardware's full potential. Dear Claudio , Thanks for the reply and clarifying on the actually running part. below is a snapshot of the top output while the system was loaded. top - 12:15:13 up 101 days, 19:01, 1 user, load average: 23.50, 18.89, 21.74 Tasks: 650 total, 11 running, 639 sleeping, 0 stopped, 0 zombie Cpu(s): 26.5%us, 5.7%sy, 0.0%ni, 67.2%id, 0.0%wa, 0.0%hi, 0.6%si, 0.0%st Mem: 131971752k total, 122933996k used, 9037756k free, 251544k buffers Swap: 33559780k total, 251916k used, 33307864k free, 116356252k cached Our applications does slowdown when loads are at that level. Can you please tell what else can be metered? | | If your processes are waiting but not using CPU or I/O time... all I | can think of is mcelog (it's the only application I've ever witnessed | doing that). Do check ps/top and try to find out which processes are | in a waiting state to have a little more insight. I will read more on the processes status and try to keep a close eye over it. I shall be responding after a few hours on it. regds mallah. | | -- | Sent via pgsql-performance mailing list | (pgsql-performance@postgresql.org) | To make changes to your subscription: | http://www.postgresql.org/mailpref/pgsql-performance -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] High load average in 64-core server , no I/O wait and CPU is idle
Dear Andy , Following the discussion on load average we are now investigating on some other parts of the stack (other than db). Essentially we are bumping up the limits (on appserver) so that more requests goes to the DB server. | | Maybe you are hitting some locks? If its not IO and not CPU then | maybe something is getting locked and queries are piling up. | | -Andy -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] High load average in 64-core server , no I/O wait and CPU is idle
| From: Steve Crawford scrawf...@pinpointresearch.com | To: Rajesh Kumar. Mallah mal...@tradeindia.com | Cc: Andy Colson a...@squeakycode.net, Claudio Freire klaussfre...@gmail.com, pgsql-performance@postgresql.org | Sent: Thursday, May 24, 2012 9:23:47 PM | Subject: Re: [PERFORM] High load average in 64-core server , no I/O wait and CPU is idle | | On 05/24/2012 05:58 AM, Rajesh Kumar. Mallah wrote: | Dear Andy , | | Following the discussion on load average we are now investigating | on some | other parts of the stack (other than db). | | Essentially we are bumping up the limits (on appserver) so that more | requests | goes to the DB server. | Which leads to the question: what, other than the db, runs on this | machine? No nothing else runs on *this* machine. We are lucky to have such a beefy hardware dedicated to postgres :) We have a separate machine for application server that has 2 tiers. I am trying to reach to the point to max out the db machine , for that to happen we need to work on the other parts. regds mallah. | | Cheers, | Steve -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] High load average in 64-core server , no I/O wait and CPU is idle
- Stephen Frost sfr...@snowman.net wrote: | From: Stephen Frost sfr...@snowman.net | To: Rajesh Kumar. Mallah mal...@tradeindia.com | Cc: pgsql-performance@postgresql.org | Sent: Thursday, May 24, 2012 9:27:37 PM | Subject: Re: [PERFORM] High load average in 64-core server , no I/O wait and CPU is idle | | Rajesh, | | * Rajesh Kumar. Mallah (mal...@tradeindia.com) wrote: | We are puzzled why the CPU and DISK I/O system are not being | utilized | fully and would seek lists' wisdom on that. | | What OS is this? What kernel version? Dear Frost , We are running linux with kernel 3.2.X (which has the lseek improvements) | | just a thought, will it be a good idea to partition the host | hardware | to 4 equal virtual environments , ie 1 for master (r/w) and 3 | slaves r/o | and distribute the r/o load on the 3 slaves ? | | Actually, it might help with 9.1, if you're really running into some | scalability issues in our locking area.. You might review this: | | http://rhaas.blogspot.com/2012/04/did-i-say-32-cores-how-about-64.html | | That's a pretty contrived test case, but I suppose it's possible your | case is actually close enough to be getting affected also.. Thanks for the reference , even i thought so (LockManager) , but we are actually also running out db max connections (also) ( which is currently at 600) , when that happens something at the beginning of the application stack also gets dysfunctional and it changes the very input to the system. ( think of negative feedback systems ) It is sort of complicated but i will definitely update list , when i get to the point of putting the blame on DB :-) . Regds Mallah. | | Thanks, | | Stephen -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
[PERFORM] High load average in 64-core server , no I/O wait and CPU is idle
Dear List , We are having scalability issues with a high end hardware The hardware is CPU = 4 * opteron 6272 with 16 cores ie Total = 64 cores. RAM = 128 GB DDR3 Disk = High performance RAID10 with lots of 15K spindles and a working BBU Cache. normally the 1 min load average of the system remains between 0.5 to 1.0 . The problem is that sometimes there are spikes of load avg which jumps to 50 very rapidly ( ie from 0.5 to 50 within 10 secs) and it remains there for sometime and slowly reduces to normal value. During such times of high load average we observe that there is no IO wait in system and even CPU is 50% idle. In any case the IO Wait always remains 1.0 % and is mostly 0. Hence the load is not due to high I/O wait which was generally the case with our previous hardware. We are puzzled why the CPU and DISK I/O system are not being utilized fully and would seek lists' wisdom on that. We have setup sar to poll the system parameters every minute and the data of which is graphed with cacti. If required any of the system parameters or postgresql parameter can easily be put under cacti monitoring and can be graphed. The query load is mostly read only. It is also possible to replicate the problem with pg_bench to some extent . I choose -s = 100 and -t=1 , the load does shoot but not that spectacularly as achieved by the real world usage. any help shall be greatly appreciated. just a thought, will it be a good idea to partition the host hardware to 4 equal virtual environments , ie 1 for master (r/w) and 3 slaves r/o and distribute the r/o load on the 3 slaves ? regds mallah -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] High load average in 64-core server , no I/O wait and CPU is idle
- Claudio Freire klaussfre...@gmail.com wrote: | From: Claudio Freire klaussfre...@gmail.com | To: Rajesh Kumar. Mallah mal...@tradeindia.com | Cc: pgsql-performance@postgresql.org | Sent: Thursday, May 24, 2012 9:23:43 AM | Subject: Re: [PERFORM] High load average in 64-core server , no I/O wait and CPU is idle | | On Thu, May 24, 2012 at 12:39 AM, Rajesh Kumar. Mallah | mal...@tradeindia.com wrote: | The problem is that sometimes there are spikes of load avg which | jumps to 50 very rapidly ( ie from 0.5 to 50 within 10 secs) and | it remains there for sometime and slowly reduces to normal value. | | During such times of high load average we observe that there is no | IO wait | in system and even CPU is 50% idle. In any case the IO Wait always | remains 1.0 % and | is mostly 0. Hence the load is not due to high I/O wait which was | generally | the case with our previous hardware. | | Do you experience decreased query performance? Yes we do experience substantial application performance degradations. | | Load can easily get to 64 (1 per core) without reaching its capacity. | So, unless you're experiencing decreased performance I wouldn't think | much of it. I far as i understand , Load Avg is the average number of processes waiting to be run in past 1 , 5 or 15 mins. A number 1 would mean that countable number of processes were waiting to be run. how can load of more than 1 and upto 64 be OK for a 64 core machine ? | | Do you have mcelog running? as a cron or a daemon? No we do not have mcelog. BTW the Postgresql version is : 9.1.3 which i forgot to mention in my last email. regds mallah. | Sometimes, mcelog tends to crash in that way. We had to disable it in | our servers because it misbehaved like that. It only makes load avg | meaningless, no performance impact, but being unable to accurately | measure load is bad enough. -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Pooling in Core WAS: Need help in performance tuning.
Hi, Sorry, if posting here was not proper instead of starting new thread (I am really not sure if its bad thing to do) I would like to share my recent experience on implementation of client side pooling using pgbouncer. By client side i mean that the the pgbouncer process in not on same machine as postgresql server. In first trial pgbouncer and postgresql were in same machine phbouncer was connecting to postgresql using unix domain sockets. But i shifted it laters owing to high CPU usage 50%. ( using top) Now i have shifted pgbouncer into a virtual machine (openvz container) in the application server hardware and all my applications on other virtual machines (web applications) connect to pgbouncer on this virtual machine. I tested the setup with pgbench in two scenarios 1. connecting to DB server directly 2. connecting to DB via pgbouncer the no of clients was 10 ( -c 10) carrying out 1 transactions each (-t 1) . pgbench db was initilised with scaling factor -s 100. since client count was less there was no queuing of requests in pgbouncer i would prefer to say it was in 'passthrough' mode. the result was that 1. direct ~ 2000 tps 2. via pgbouncer ~ 1200 tps -- Experience on deploying to production environment with real world load/usage pattern -- Pgbouncer was put in same machine as postgresql connecting via unix domain to server and tcp sockets with clients. 1. There was drastic reduction in CPU loads from 30 to 10 ldavg 2. There were no clients waiting, pool size was 150 and number of active connections was 100-120. 3. Application performance was worse (inspite of 0 clients waiting ) I am still waiting to see what is the effect of shifting out pgbounce from dbserver to appserver, but with pgbench results i am not very hopeful. I am curious why inspite of 0 clients waiting pgbounce introduces a drop in tps. Warm Regds Rajesh Kumar Mallah. CTO - tradeindia.com. Keywords: pgbouncer performance On Mon, Jul 12, 2010 at 6:11 PM, Kevin Grittner kevin.gritt...@wicourts.gov wrote: Craig Ringer cr...@postnewspapers.com.au wrote: So rather than asking should core have a connection pool perhaps what's needed is to ask what can an in-core pool do that an external pool cannot do? (1) It can prevent the most pessimal performance problems resulting from lack of an external connection pool (or a badly configured one) by setting a single GUC. Configuration tools could suggest a good value during initial setup. (2) It can be used without installing and configuring a more sophisticated and complex product. (3) It might reduce latency because it avoids having to receive, parse, and resend data in both directions -- eliminating one hop. I know the performance benefit would usually accrue to the external connection pooler, but there might be some circumstances where a built-in pool could win. (4) It's one more checkbox which can be ticked off on some RFPs. That said, I fully agree that if we can include good documentation on the external poolers and we can get packagers to include poolers in their distribution, that gets us a much bigger benefit. A built-in solution would only be worthwhile if it was simple enough and lightweight enough not to be a burden on execution time or maintenance. Maybe that's too big an if. -Kevin -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Pooling in Core WAS: Need help in performance tuning.
note: my postgresql server pgbouncer were not in virtualised environment in the first setup. Only application server has many openvz containers.
Re: [PERFORM] Pooling in Core WAS: Need help in performance tuning.
Nice suggestion to try , I will put pgbouncer on raw hardware and run pgbench from same hardware. regds rajesh kumar mallah. Why in VM (openvz container) ? Did you also try it in the same OS as your appserver ? Perhaps even connecting from appserver via unix seckets ? and all my applications on other virtual machines
Re: [PERFORM] Pooling in Core WAS: Need help in performance tuning.
ok , now the question is , is it possible to dig out from from postgresql database server if connection pooling is needed ? In our case eg i have kept max_connections = 300 if i reduce below 250 i get error max connection reached. on connecting to db directly, if i put pgbouncer i get less performance (even if no clients waiting) without pooling the dbserver CPU usage increases but performance of apps is also become good. Regds Rajesh Kumar Mallah. On Sun, Jul 18, 2010 at 10:55 PM, Greg Smith g...@2ndquadrant.com wrote: Rajesh Kumar Mallah wrote: the no of clients was 10 ( -c 10) carrying out 1 transactions each (-t 1) . pgbench db was initilised with scaling factor -s 100. since client count was less there was no queuing of requests in pgbouncer i would prefer to say it was in 'passthrough' mode. Of course pgbouncer is going decrease performance in this situation. You've added a whole layer to things that all traffic has to pass through, without a setup that gains any benefit from the connection pooling. Try making the client count 1000 instead if you want a useful test. -- Greg Smith 2ndQuadrant US Baltimore, MD PostgreSQL Training, Services and Support g...@2ndquadrant.com www.2ndQuadrant.us
Re: [PERFORM] Pooling in Core WAS: Need help in performance tuning.
On Sun, Jul 18, 2010 at 10:55 PM, Greg Smith g...@2ndquadrant.com wrote: Rajesh Kumar Mallah wrote: the no of clients was 10 ( -c 10) carrying out 1 transactions each (-t 1) . pgbench db was initilised with scaling factor -s 100. since client count was less there was no queuing of requests in pgbouncer i would prefer to say it was in 'passthrough' mode. Of course pgbouncer is going decrease performance in this situation. You've added a whole layer to things that all traffic has to pass through, without a setup that gains any benefit from the connection pooling. Try making the client count 1000 instead if you want a useful test. Dear Greg, my max_client is 300 shall i test with client count 250 ? if so what should be the scaling factor while initializing the pgbench db? -- Greg Smith 2ndQuadrant US Baltimore, MD PostgreSQL Training, Services and Support g...@2ndquadrant.com www.2ndQuadrant.us
Re: [PERFORM] Pooling in Core WAS: Need help in performance tuning.
Looks like , pgbench cannot be used for testing with pgbouncer if number of pgbench clients exceeds pool_size + reserve_pool_size of pgbouncer. pgbench keeps waiting doing nothing. I am using pgbench of postgresql 8.1. Are there changes to pgbench in this aspect ? regds Rajesh Kumar Mallah. On Sun, Jul 18, 2010 at 11:38 PM, Rajesh Kumar Mallah mallah.raj...@gmail.com wrote: On Sun, Jul 18, 2010 at 10:55 PM, Greg Smith g...@2ndquadrant.com wrote: Rajesh Kumar Mallah wrote: the no of clients was 10 ( -c 10) carrying out 1 transactions each (-t 1) . pgbench db was initilised with scaling factor -s 100. since client count was less there was no queuing of requests in pgbouncer i would prefer to say it was in 'passthrough' mode. Of course pgbouncer is going decrease performance in this situation. You've added a whole layer to things that all traffic has to pass through, without a setup that gains any benefit from the connection pooling. Try making the client count 1000 instead if you want a useful test. Dear Greg, my max_client is 300 shall i test with client count 250 ? if so what should be the scaling factor while initializing the pgbench db? -- Greg Smith 2ndQuadrant US Baltimore, MD PostgreSQL Training, Services and Support g...@2ndquadrant.com www.2ndQuadrant.us
Re: [PERFORM] Pooling in Core WAS: Need help in performance tuning.
Thanks for the thought but it (-C) does not work . BTW, I think you should use -C option with pgbench for this kind of testing. -C establishes connection for each transaction, which is pretty much similar to the real world application which do not use connection pooling. You will be supprised how PostgreSQL connection overhead is large. -- Tatsuo Ishii SRA OSS, Inc. Japan English: http://www.sraoss.co.jp/index_en.php Japanese: http://www.sraoss.co.jp
Re: [PERFORM] how to (temporarily) disable/minimize benefits of disk block cache or postgresql shared buffer
Dear Criag, Thanks for thinking about it.I do not understand why u feel OpenVz is weird. at the most its not very popular. But lets not get into that debate as its not the proper forum. From your reply i understand that there is not a easy and clean way of doing it. Since performance related profiling requires multiple iterations it is not feasible to reboot the machine. I think i will try to profile my code using new and unique input parameters in each iteration, this shall roughly serve my purpose. On Fri, Jul 2, 2010 at 8:30 AM, Craig Ringer cr...@postnewspapers.com.auwrote: On 02/07/10 01:59, Rajesh Kumar Mallah wrote: I had set it to 128kb it does not really work , i even tried your next suggestion. I am in virtualized environment particularly OpenVz. where echo 3 /proc/sys/vm/drop_caches does not work inside the virtual container, i did it in the hardware node but still does not give desired result. Yeah, if you're in a weird virtualized environment like that you're likely to have problems, because caching can be done at multiple levels. In the case of OpenVZ, it's hard to know what the guest and what the host even is sometimes, and I wouldn't trust it to respect things like the Linux VM cache management. You might have to fall back on the classic method: a program that tries to allocate as much RAM as it can. On Linux this is EXTREMELY unsafe unless you ensure you have vm overcommit disabled (see the postgresql docs) because by default Linux systems will never fail a memory allocation - instead they'll go looking for a big process to kill to free some memory. In theory this should be your memory gobbler program, but in reality the OOM killer isn't so predictable. So: try turning vm overcommit off, then writing (or finding) a simple program that keeps on malloc()ing memory until an allocation call fails. That should force any caches out, freeing you for another cold run. Note that this method won't just force out the obvious caches like postgresql data files. It also forces out things like caches of running binaries. Things will grind to an absolute crawl for a minute or two before resuming normal speed, because *everything* has to come back from disk at once. The same is true of using /proc/sys/vm/drop_caches to drop all caches. I guess, in the end, nothing really subtitutes for a good reboot. -- Craig Ringer Tech-related writing: http://soapyfrogs.blogspot.com/
Re: [PERFORM] Extremely high CPU usage when building tables
Dear Deb, i feel three distinct steps are present 1. CREATE TABLE 2. LOAding of data in tables 3. Creation of indexes It is still not clear when you are seeing your system becoming unresponsive to other application. Is it during loading of data or creation of indexes? 1. can you give any idea about how much data you are loading ? rows count or GB data etc 2. how many indexes are you creation ? regds Rajesh Kumar Mallah.
[PERFORM] how to (temporarily) disable/minimize benefits of disk block cache or postgresql shared buffer
Hi, this is not really a performance question , sorry if its bit irrelevant to be posted here. We have a development environment and we want to optimize the non-database parts of the application. The problem is that subsequent run of queries are execute very fast and makes the performance analysis a trivial problem. We want that the subsequent runs of query should take similar times as the first run so that we can work on the optimizing the calling patterns to the database. regds Rajesh Kumar Mallah.
Re: [PERFORM] Extremely high CPU usage when building tables
Hi, 1. Did you also check vmstat output , from sar output the i/o wait is not clear. 2. i gues you must be populating the database between creating tables and creating indexes. creating indexes require sorting of data that may be cpu intensive, loading/populating the data may saturate the i/o bandwidth . I think you should check when the max cpu utilisation is taking place exactly. regds Rajesh Kumar Mallah. On Sat, Jun 26, 2010 at 3:55 AM, Deborah Fuentes dfuen...@eldocomp.comwrote: Hello, When I run an SQL to create new tables and indexes is when Postgres consumes all CPU and impacts other users on the server. We are running Postgres 8.3.7 on a Sun M5000 with 2 x quad core CPUs (16 threads) running Solaris 10. I've attached the sar data at the time of the run- here's a snip-it below. Any ideas would be greatly appreciated. Thanks! Deb Here, note the run queue, the left column. That is the number of processes waiting to run. 97 processes waiting to run at any time with only eight CPU cores looks very busy. r...@core2 # sar -q 5 500 SunOS core2 5.10 Generic_142900-11 sun4u06/17/2010 12:01:50 runq-sz %runocc swpq-sz %swpocc 12:01:55 1.8 80 0.0 0 12:02:00 1.0 20 0.0 0 12:02:05 1.0 20 0.0 0 12:02:10 0.0 0 0.0 0 12:02:15 0.0 0 0.0 0 12:02:21 3.3 50 0.0 0 12:02:26 1.0 20 0.0 0 12:02:31 1.0 60 0.0 0 12:02:36 1.0 20 0.0 0 12:02:4227.0 50 0.0 0 12:02:4932.8 83 0.0 0 12:02:5576.0 100 0.0 0 12:03:0166.1 100 0.0 0 12:03:0743.8 100 0.0 0 12:03:1352.0 100 0.0 0 12:03:1991.2 100 0.0 0 12:03:2697.8 83 0.0 0 12:03:3363.7 100 0.0 0 12:03:3967.4 100 0.0 0 12:03:4741.5 100 0.0 0 12:03:5382.0 83 0.0 0 12:03:5988.7 100 0.0 0 12:04:0687.7 50 0.0 0 12:04:1241.3 100 0.0 0 12:04:1794.3 50 0.0 0 12:04:22 1.0 20 0.0 0 12:04:27 3.3 60 0.0 0 12:04:32 1.0 20 0.0 0 12:04:38 0.0 0 0.0 0 -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] What is the best way to optimize this query
Dear Sri, Please post at least the Explain Analyze output . There is a nice posting guideline also regarding on how to post query optimization questions. http://wiki.postgresql.org/wiki/SlowQueryQuestions On Thu, Jul 1, 2010 at 10:49 AM, Srikanth Kata srika...@inventum.netwrote: Please tell me What is the best way to optimize this query select s.*,a.actid,a.phone,d.domid,d.domname,d.domno,a.actno,a.actname,p.descr as svcdescr from vwsubsmin s inner join packages p on s.svcno=p.pkgno inner join account a on a.actno=s.actno inner join ssgdom d on a.domno=d.domno inner join (select subsno from getexpiringsubs($1,cast($2 as integer),cast($3 as double precision), $4) as (subsno int,expirydt timestamp without time zone,balcpt double precision)) as e on s.subsno=e.subsno where s.status =15 and d.domno=$5 order by d.domname,s.expirydt,a.actname -- View this message in context: http://old.nabble.com/What-is-the-best-way-to-optimize-this-query-tp29041515p29041515.html Sent from the PostgreSQL - performance mailing list archive at Nabble.com. -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] how to (temporarily) disable/minimize benefits of disk block cache or postgresql shared buffer
On Thu, Jul 1, 2010 at 10:07 PM, Craig Ringer cr...@postnewspapers.com.auwrote: On 01/07/10 17:41, Rajesh Kumar Mallah wrote: Hi, this is not really a performance question , sorry if its bit irrelevant to be posted here. We have a development environment and we want to optimize the non-database parts of the application. The problem is that subsequent run of queries are execute very fast and makes the performance analysis a trivial problem. We want that the subsequent runs of query should take similar times as the first run so that we can work on the optimizing the calling patterns to the database. You can get rid of PostgreSQL's caches in shared_buffers by restarting the PostgreSQL server. I don't know if there's any more convenient way. Alternately, just set a really minimal shared_buffers that's just enough for your connections so there's not much room for cached data. I had set it to 128kb it does not really work , i even tried your next suggestion. I am in virtualized environment particularly OpenVz. where echo 3 /proc/sys/vm/drop_caches does not work inside the virtual container, i did it in the hardware node but still does not give desired result. regds Rajesh Kumar Mallah. If you are running a Linux server (as you didn't mention what you're running on) you can drop the OS disk cache quite easily: http://linux-mm.org/Drop_Caches http://www.linuxinsight.com/proc_sys_vm_drop_caches.html AFAIK for most other platforms you have to use a tool that gobbles memory to force caches out. On Windows, most of those garbage tools that claim to free memory do this - it's about the only time you'd ever want to use one, since they do such horrid things to performance. -- Craig Ringer
[PERFORM] order by slowing down a query by 80 times
Dear List,
just by removing the order by co_name reduces the query time dramatically
from ~ 9 sec to 63 ms. Can anyone please help.
Regds
Rajesh Kumar Mallah.
explain analyze SELECT * from ( SELECT
a.profile_id,a.userid,a.amount,a.category_id,a.catalog_id,a.keywords,b.co_name
from general.catalogs a join general.profile_master using(profile_id) where
1=1 and co_name_vec @@ to_tsquery('manufacturer') and b.co_name is not
null and a.ifmain is true ) as c order by co_name
limit 25 offset 0;
Limit (cost=0.00..3659.13 rows=25 width=129) (actual time=721.075..9241.105
rows=25 loops=1)
- Nested Loop (cost=0.00..1215772.28 rows=8307 width=476) (actual
time=721.073..9241.050 rows=25 loops=1)
- Nested Loop (cost=0.00..1208212.37 rows=8307 width=476)
(actual time=721.052..9240.037 rows=25 loops=1)
- Nested Loop (cost=0.00..1204206.26 rows=6968 width=472)
(actual time=721.032..9239.516 rows=25 loops=1)
- Nested Loop (cost=0.00..1154549.19 rows=6968
width=471) (actual time=721.012..9236.523 rows=25 loops=1)
- Index Scan using profile_master_co_name on
profile_master b (cost=0.00..1125295.59 rows=6968 width=25) (actual
time=0.097..9193.154 rows=2212 loops=1)
Filter: ((co_name IS NOT NULL) AND
((co_name_vec)::tsvector @@ to_tsquery('manufacturer'::text)))
- Index Scan using
catalog_master_profile_id_fkindex on catalog_master (cost=0.00..4.19 rows=1
width=446) (actual time=0.016..0.016 rows=0 loops=2212)
Index Cond: (catalog_master.profile_id =
b.profile_id)
Filter: ((catalog_master.hide IS FALSE) AND
((catalog_master.hosting_status)::text = 'ACTIVE'::text))
- Index Scan using profile_master_profile_id_pkey on
profile_master (cost=0.00..7.11 rows=1 width=9) (actual time=0.105..0.105
rows=1 loops=25)
Index Cond: (profile_master.profile_id =
catalog_master.profile_id)
- Index Scan using
catalog_categories_pkey_catalog_id_category_id on catalog_categories
(cost=0.00..0.56 rows=1 width=8) (actual time=0.014..0.015 rows=1 loops=25)
Index Cond: (catalog_categories.catalog_id =
catalog_master.catalog_id)
Filter: (catalog_categories.ifmain IS TRUE)
- Index Scan using web_category_master_pkey on
web_category_master (cost=0.00..0.90 rows=1 width=4) (actual
time=0.034..0.036 rows=1 loops=25)
Index Cond: (web_category_master.category_id =
catalog_categories.category_id)
Filter: ((web_category_master.status)::text = 'ACTIVE'::text)
Total runtime: 9241.304 ms
explain analyze SELECT * from ( SELECT
a.profile_id,a.userid,a.amount,a.category_id,a.catalog_id,a.keywords,b.co_name
from general.catalogs a join general.profile_master b using(profile_id)
where 1=1 and co_name_vec @@ to_tsquery('manufacturer') and b.co_name
is not null and a.ifmain is true ) as c limit 25 offset 0;
QUERY PLAN
--
Limit (cost=0.00..358.85 rows=25 width=476) (actual time=0.680..63.176
rows=25 loops=1)
- Nested Loop (cost=0.00..119238.58 rows=8307 width=476) (actual
time=0.677..63.139 rows=25 loops=1)
- Nested Loop (cost=0.00..111678.66 rows=8307 width=476) (actual
time=0.649..62.789 rows=25 loops=1)
- Nested Loop (cost=0.00..107672.56 rows=6968 width=472)
(actual time=0.626..62.436 rows=25 loops=1)
- Nested Loop (cost=0.00..58015.49 rows=6968
width=471) (actual time=0.606..62.013 rows=25 loops=1)
- Index Scan using profile_master_co_name_vec
on profile_master b (cost=0.00..28761.89 rows=6968 width=25) (actual
time=0.071..50.576 rows=1160 loops=1)
Index Cond: ((co_name_vec)::tsvector @@
to_tsquery('manufacturer'::text))
Filter: (co_name IS NOT NULL)
- Index Scan using
catalog_master_profile_id_fkindex on catalog_master (cost=0.00..4.19 rows=1
width=446) (actual time=0.008..0.008 rows=0 loops=1160)
Index Cond: (catalog_master.profile_id =
b.profile_id)
Filter: ((catalog_master.hide IS FALSE) AND
((catalog_master.hosting_status)::text = 'ACTIVE'::text))
- Index Scan using profile_master_profile_id_pkey on
profile_master (cost=0.00..7.11 rows=1 width=9) (actual time=0.012..0.012
rows=1 loops=25)
Index Cond: (profile_master.profile_id =
catalog_master.profile_id)
- Index Scan using
catalog_categories_pkey_catalog_id_category_id on catalog_categories
(cost=0.00..0.56 rows=1 width=8) (actual time=0.010..0.011 rows=1 loops=25)
Index Cond: (catalog_categories.catalog_id
Re: [PERFORM] order by slowing down a query by 80 times
On Mon, Jun 28, 2010 at 5:09 PM, Yeb Havinga yebhavi...@gmail.com wrote:
Rajesh Kumar Mallah wrote:
Dear List,
just by removing the order by co_name reduces the query time dramatically
from ~ 9 sec to 63 ms. Can anyone please help.
The 63 ms query result is probably useless since it returns a limit of 25
rows from an unordered result. It is not surprising that this is fast.
The pain is here:
Index Scan using profile_master_co_name on profile_master b
(cost=0.00..1125295.59 rows=6968 width=25) (actual time=0.097..9193.154
rows=2212 loops=1)
Filter: ((co_name IS NOT NULL) AND
((co_name_vec)::tsvector @@ to_tsquery('manufacturer'::text)))
It looks like seq_scans are disabled, since the index scan has only a
filter expression but not an index cond.
seq_scans is NOT explicitly disabled. The two queries just differed in the
order by clause.
regds
Rajesh Kumar Mallah.
regards,
Yeb Havinga
Regds
Rajesh Kumar Mallah.
Re: [PERFORM] order by slowing down a query by 80 times
Dear Tom/Kevin/List thanks for the insight, i will check the suggestion more closely and post the results. regds Rajesh Kumar Mallah.
Re: [PERFORM] order by slowing down a query by 80 times
The way to make this go faster is to set up the actually recommended
infrastructure for full text search, namely create an index on
(co_name_vec)::tsvector (either directly or using an auxiliary tsvector
column). If you don't want to maintain such an index, fine, but don't
expect full text search queries to be quick.
regards, tom lane
Dear Tom/List ,
co_name_vec is actually the auxiliary tsvector column that is mantained via
a
an update trigger. and the index that you suggested is there . consider
simplified
version. When we order by co_name the index on co_name_vec is not used
some other index is used.
tradein_clients= explain analyze SELECT profile_id from
general.profile_master b where 1=1 and co_name_vec @@ to_tsquery
('manufacturer') order by co_name limit 25;
QUERY PLAN
---
Limit (cost=0.00..3958.48 rows=25 width=25) (actual time=0.045..19.847
rows=25 loops=1)
- Index Scan using profile_master_co_name on profile_master b
(cost=0.00..1125315.59 rows=7107 width=25) (actual time=0.043..19.818
rows=25 loops=1)
Filter: ((co_name_vec)::tsvector @@
to_tsquery('manufacturer'::text))
Total runtime: 19.894 ms
(4 rows)
tradein_clients= explain analyze SELECT profile_id from
general.profile_master b where 1=1 and co_name_vec @@ to_tsquery
('manufacturer')limit 25;
QUERY PLAN
---
Limit (cost=0.00..101.18 rows=25 width=4) (actual time=0.051..0.632
rows=25 loops=1)
- Index Scan using profile_master_co_name_vec on profile_master b
(cost=0.00..28761.89 rows=7107 width=4) (actual time=0.049..0.593 rows=25
loops=1)
Index Cond: ((co_name_vec)::tsvector @@
to_tsquery('manufacturer'::text))
Total runtime: 0.666 ms
(4 rows)
tradein_clients=
Re: [PERFORM] sudden spurt in swap utilization (was:cpu bound postgresql setup.)
Dear List, Today has been good since morning. Although it is a lean day for us but the indications are nice. I thank everyone who shared the concern. I think the most significant change has been to reduce shared_buffers from 10G to 4G , this has lead to reduced memory usage and some breathing space to the OS. Although i am yet to incorporate the suggestions from pgtune but i think the issue of max_connection needs to be addressed first. I am investigating application issues and about the mechanism that puts many backend to 'IDLE in transaction ' mode for significant times. I thank Tom for the script he sent. Once that resolves i shall check pooling as suggested by Kevin, then eventually max_connections can be reduced. I shall also check pgpool and pgbouncer if they are helpful in this regard. I observed that the number of simultaneous connection today (lean day) hovers between 1 to 10 , occasionally shooting to 15 but never more than 20 i would say. I am happy that i/o waits are negligible and cpu is idling also for a while. procs ---memory-- ---swap-- -io --system-- -cpu-- r b swpd free buff cache si sobibo in cs us sy id wa st 22 0 18468 954120 13460 2849177200 568 1558 13645 18355 62 10 27 2 0 16 0 18468 208100 13476 2846908400 580 671 14039 17055 67 13 19 1 0 10 2 18812 329032 13400 283569720 46 301 1768 13848 17884 68 10 20 1 0 16 2 18812 366596 13416 2836162000 325 535 13957 16649 72 11 16 1 0 50 1 18812 657048 13432 2836654800 416 937 13823 16667 62 9 28 1 0 6 1 18812 361040 13452 2837190800 323 522 14352 16789 74 12 14 0 0 33 0 18812 162760 12604 2821015200 664 1544 14701 16315 66 11 22 2 0 5 0 18812 212028 10764 2792180000 552 648 14567 17737 67 10 21 1 0 6 0 18796 279920 10548 2789038830 359 562 12635 15976 60 9 30 1 0 8 0 18796 438820 10564 2789444000 289 2144 12234 15770 57 8 34 1 0 5 0 18796 531800 10580 2790170000 514 394 12169 16005 59 8 32 1 0 17 0 18796 645868 10596 2789070400 423 948 13369 16554 67 10 23 1 0 9 1 18796 1076540 10612 27898604 00 598 403 12703 17363 71 10 18 1 0 8 0 18796 1666508 10628 27904748 00 430 1123 13314 17421 57 9 32 1 0 9 1 18776 1541444 10644 27913092 10 653 954 13194 16822 75 11 12 1 0 8 0 18776 1526728 10660 27921380 00 692 788 13073 16987 74 9 15 1 0 8 0 18776 1482304 10676 27933176 00 966 2029 13017 16651 76 12 11 1 0 21 0 18776 1683260 10700 27937492 00 298 663 13110 15796 67 10 23 1 0 18 0 18776 2087664 10716 27943512 00 406 622 12399 17072 62 9 28 1 0 With 300 connections, I think that either of these could lead you to experience intermittent bursts of extreme swapping. I'd drop it to somewhere in the 16MB to 32MB range until I had a connection pool configured such that it was actually keeping the number of active connections much lower. (*) wal_buffers = 8MB # pgtune wizard 2010-06-25 (64kb , via default) Sure, I'd boost this. checkpoint_segments = 16 # pgtune wizard 2010-06-25 (30 , specified) If you have the disk space for the 30 segments, I wouldn't reduce it. shared_buffers = 7680MB # pgtune wizard 2010-06-25 (4096 MB , specified) This one is perhaps the most sensitive to workload. Anywhere between 1GB and 8GB might be best for you. Greg Smith has some great advice on how to tune this for your workload. (*) max_connections = 80 # pgtune wizard 2010-06-25 (300 , ;-) specified) when i reduce max_connections i start getting errors, i will see again concurrent connections during business hours. That's probably a good number to get to, but you have to reduce the number of actual connections before you set the limit that low. lot of our connections are in IDLE in transaction state If any of these stay in that state for more than a minute or two, you need to address that if you want to get your connection count under control. If any of them persist for hours or days, you need to fix it to avoid bloat which can kill performance. -Kevin
Re: [PERFORM] sudden spurt in swap utilization (was:cpu bound postgresql setup.)
Dear List , A simple (perl) script was made to 'watch' the state transitions of back ends. On startup It captures a set of pids for watching and displays a visual representation of the states for next 30 intervals of 1 seconds each. The X axis is interval cnt, Y axis is pid and the origin is on top-left. The state value can be Active Query (*) , or IDLE indicated by '.' or 'IDLE in transaction' indicated by '?' . for my server below is a random output (during lean hours and on a lean day). PID 01 02 03 04 05 06 07 08 09 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 4334 ? ? ? ? * ? ? ? ? ? * ? ? ? ? ? ? ? ? 6904 ? ? . . . * ? . . . . . . ? ? . 6951 ? ? ? . . . . ? ? ? ? ? . . . ? ? ? . . . ? . . . . . ? ? . 7009 ? * ? ? . . . . . . . . . * * . * ? ? . . . * ? ? ? . . . ? 7077 ? . ? . . . * . ? . . . . ? . . . . . . . ? . . ? . . . ? ? 7088 ? . . ? . ? ? ? . . . . . . ? . . ? ? * . . . . . ? . ? . * 7091 ? . . * ? ? ? ? ? ? ? * ? . . ? * . * . . . . . . . . . . . 7093 ? ? . ? . . . . ? . ? * . . . . . . . . . ? ? ? . ? ? ? . . 7112 * * . . . ? ? ? . . . . . . . . ? ? . ? . ? . . ? . . . . . 7135 ? . . * . ? ? ? . ? ? . . . ? . . . . . . . ? . . . ? ? . . 7142 ? . ? . . . . . . * . . . ? . . . . . . . . . . . . . . 7166 ? . ? ? ? * * . ? * . ? . . . ? . ? ? . . . * . . . ? . . . 8202 ? ? . . . * . ? . . . . . . . * ? . . . ? ? . . . . ? ? ? . 8223 ? . . . . . . ? 8237 ? ? ? . ? ? ? ? . . . . . . . ? . . . . . ? . . * ? . . . . 8251 ? . ? . . . . . ? ? . . . * ? . . . ? . . . . . . . . . . . 8278 ? ? . . . . ? . . . . . . . ? . . . . . . ? ? . . * . . . . 8290 ? . . 8294 ? ? . . . . . . . . . . . . ? . . . ? ? . . . . . . . . * * 8303 ? * ? . ? ? ? . ? ? ? . . . . * . . . . . . . . . . . . . . 8306 ? ? . . . ? . . . ? . . . . . . * . . . 8309 * ? ? ? ? . . . ? . . . 8329 ? . * * . . . . . . . * . ? . * . ? . * . * ? . . . (*) Active Query , (.) Idle , (?) Idle in transaction,blank backend over. Looks like most of the graph space is filled with (.) or (?) and very less active queries (long running queries 1s). on a busy day and busi hour i shall check the and post again. The script is presented which depends only on perl , DBI and DBD::Pg. script pasted here: http://pastebin.com/mrjSZfLB Regds mallah. On Sat, Jun 26, 2010 at 3:23 PM, Rajesh Kumar Mallah mallah.raj...@gmail.com wrote: Dear List, Today has been good since morning. Although it is a lean day for us but the indications are nice. I thank everyone who shared the concern. I think the most significant change has been to reduce shared_buffers from 10G to 4G , this has lead to reduced memory usage and some breathing space to the OS. Although i am yet to incorporate the suggestions from pgtune but i think the issue of max_connection needs to be addressed first. I am investigating application issues and about the mechanism that puts many backend to 'IDLE in transaction ' mode for significant times. I thank Tom for the script he sent. Once that resolves i shall check pooling as suggested by Kevin, then eventually max_connections can be reduced. I shall also check pgpool and pgbouncer if they are helpful in this regard. I observed that the number of simultaneous connection today (lean day) hovers between 1 to 10 , occasionally shooting to 15 but never more than 20 i would say. I am happy that i/o waits are negligible and cpu is idling also for a while. procs ---memory-- ---swap-- -io --system-- -cpu-- r b swpd free buff cache si sobibo in cs us sy id wa st 22 0 18468 954120 13460 2849177200 568 1558 13645 18355 62 10 27 2 0 16 0 18468 208100 13476 2846908400 580 671 14039 17055 67 13 19 1 0 10 2 18812 329032 13400 283569720 46 301 1768 13848 17884 68 10 20 1 0 16 2 18812 366596 13416 2836162000 325 535 13957 16649 72 11 16 1 0 50 1 18812 657048 13432
[PERFORM] sudden spurt in swap utilization (was:cpu bound postgresql setup.)
A scary phenomenon is being exhibited by the server , which is the server is slurping all the swap suddenly , some of the relevant sar -r output are: 10:30:01 AM kbmemfree kbmemused %memused kbbuffers kbcached kbswpfree kbswpused %swpused kbswpcad 10:40:01 AM979068 31892208 97.02 10588 28194876 1781568314872 15.02 66500 10:50:01 AM 1791536 31079740 94.55 10480 27426512 1782848313592 14.96 43880 11:00:01 AM 4678768 28192508 85.77 9692 27213312 1784888311552 14.86 33296 11:10:01 AM179208 32692068 99.45 3180 27569008 1725136371304 17.71 65444 11:20:01 AM225604 32645672 99.31 2604 29817192 1693672402768 19.21 78312 --- 11:30:01 AM520224 32351052 98.42 1780 26863576 0 2096440100.00 1585772 -- within 10mins 11:40:02 AM483532 32387744 98.53 2672 27220404 0 2096440100.00 43876 11:50:01 AM162700 32708576 99.51 3316 27792540 0 2096440100.00 43708 12:00:01 PM420176 32451100 98.72 3772 28181316 0 2096440100.00 43708 12:10:01 PM331624 32539652 98.99 3236 27857760 0 2096440100.00 0 12:20:01 PM 1023428 31847848 96.89 4632 27450504 0 2096440100.00 0 12:30:01 PM763296 32107980 97.68 4988 28270704 0 2096440100.00 0 12:40:01 PM770280 32100996 97.66 5260 28423292 0 2096440100.00 0 Then i added more swap made it 4GB from 2GB 02:10:05 PM 8734144 24137132 73.43 5532 21219972 2096788 2096124 49.9952 02:12:01 PM 5989044 26882232 81.78 6108 23606680 2096788 2096124 49.9952 02:14:01 PM 1517724 31353552 95.38 6320 26988280 2096788 2096124 49.9952 02:16:01 PM316692 32554584 99.04 6516 28840264 1844856 2348056 56.00251984 02:18:01 PM450672 32420604 98.63 7748 27238712 0 4192912100.00 2096840 all swap gone. 02:20:01 PM164388 32706888 99.50 7556 27118104 0 4192912100.00 2096840 02:22:01 PM848544 32022732 97.42 6212 26718712 0 4192912100.00 2096840 02:24:01 PM231332 32639944 99.30 6136 27276720 0 4192912100.00 2096840 02:26:01 PM639560 32231716 98.05 5608 27029372 0 4192912100.00 2096840 02:28:01 PM868824 32002452 97.36 4648 26253996 0 4192912100.00 2096840 ... 03:04:01 PM854408 32016868 97.40 4976 27182140 0 4192912100.00 0 03:06:01 PM 1571904 31299372 95.22 5184 27513232 0 4192912100.00 0 03:08:02 PM304600 32566676 99.07 5420 27850780 0 4192912100.00 0 03:10:01 PM915352 31955924 97.22 5632 28076320 0 4192912100.00 0 03:12:01 PM705132 32166144 97.85 5680 28057444 0 4192912100.00 0 03:14:01 PM369516 32501760 98.88 6136 27684364 0 4192912100.00 0 in vmstat the system does not seems to be swapping vmstat 5 procs ---memory-- ---swap-- -io --system-- -cpu-- r b swpd free buff cache si sobibo in cs us sy id wa st 24 2 4192912 947796 6036 2778532410 451 20800 50 6 39 5 0 22 3 4192912 1028956 6044 2779572800 1730 555 13445 14736 67 12 17 4 0 24 0 4192912 877508 6052 2780617200 1595 2292 13334 15666 67 9 19 5 0 14 8 4192912 820432 6068 2781975600 2331 1351 13208 16192 66 9 14 11 0 23 1 4192912 925960 6076 2783164400 1932 1584 13144 16291 71 9 14 5 0 2 3 4192912 895288 6084 2784643200 2496 991 13450 16303 70 9 13 8 0 17 0 4192912 936252 6092 2785986800 2122 826 13438 16233 69 9 17 5 0 8 1 4192912 906164 6100 2787364000 2277 858 13440 16235 63 8 19 10 0 I reduced work_mem from 4GB to 2GB to 512MB (now). I clearly remember that this abnormal consumption of swap was NOT there even when work_mem was 4GB. eg during happier times swap utilisation was: http://pastebin.com/bnE1pFZ9 the question is whats making postgres slurp the swap? i am posting my current postgresql.conf once again. # cat postgresql.conf | grep -v ^\s*# | grep -v ^\s*$ listen_addresses = '*' # what IP address(es) to listen on; port = 5432 # (change requires restart) max_connections = 300 # (change requires restart) shared_buffers = 10GB # min 128kB work_mem = 512MB# min 64kB fsync = on # turns forced synchronization on or off synchronous_commit = on # immediate fsync at commit checkpoint_segments = 30# in logfile segments, min 1, 16MB each archive_mode = on
Re: [PERFORM] sudden spurt in swap utilization (was:cpu bound postgresql setup.)
Dear List, Hmmm , lemme test efficacy of pg_tune. I would reduce shared buffers also. regarding concurrent queries: its now non business hours and SELECT procpid,current_query from pg_stat_activity where current_query not ilike '%idle%' ; is just 5-10, i am yet to measure it during business hours. Warm Regds Rajesh Kumar Mallah. On Fri, Jun 25, 2010 at 4:58 PM, Yeb Havinga yebhavi...@gmail.com wrote: Rajesh Kumar Mallah wrote: A scary phenomenon is being exhibited by the server , which is the server is slurping all the swap suddenly 8 1 4192912 906164 6100 2787364000 2277 858 13440 16235 63 8 19 10 0 I reduced work_mem from 4GB to 2GB to 512MB (now). I clearly remember that this abnormal consumption of swap was NOT there even when work_mem was 4GB. eg during happier times swap utilisation was: http://pastebin.com/bnE1pFZ9 the question is whats making postgres slurp the swap? i am posting my current postgresql.conf once again. # cat postgresql.conf | grep -v ^\s*# | grep -v ^\s*$ listen_addresses = '*' # what IP address(es) to listen on; port = 5432 # (change requires restart) max_connections = 300 # (change requires restart) Hello Rajesh, In constrast with e.g. shared_buffers and effective_cache_size, work_mem is amount of memory per 'thing' (e.g. order/group by) that wants some working memory, so even a single backend can use several pieces of work_mem memory. Looking at your postgresql.conf, other memory values seem a bit too high as well for a 32GB ram server. It is probably a good idea to use pgtune (on pgfoundry) to get some reasonable ball park settings for your hardware. regards, Yeb Havinga -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] sudden spurt in swap utilization (was:cpu bound postgresql setup.)
I changed shared_buffers from 10G to 4G , swap usage has almost become nil. # free total used free sharedbuffers cached Mem: 32871276 245758248295452 0 11064 22167324 -/+ buffers/cache:2397436 30473840 Swap: 41929123524192560 I also observed that there was a huge IO wait and load spike initially which gradually reduced to normal levels. Now things seems to be fine. but real test shall be during business hours. vmstat output: http://pastebin.com/ygu8gUhS the iowait now is very respectable 10% and CPU is idling most of the time. # vmstat 10 procs ---memory-- ---swap-- -io --system-- -cpu-- r b swpd free buff cache si sobibo in cs us sy id wa st 2 1352 8482444 11336 2229910010 450 20800 50 6 39 5 0 4 0352 8393840 11352 2230448400 480 163 9260 12717 32 4 62 3 0 5 1352 8474788 11360 2230898000 304 445 8295 12358 28 4 67 2 0 3 0352 8370672 11376 2231667600 648 158 8760 13214 38 4 55 3 0 11 0352 8193824 11392 2232357200 621 577 8800 13163 37 4 56 3 0 2 0352 8229012 11408 2232666400 169 405 9588 13696 34 4 61 1 0 6 1352 8319176 11424 2233314400 559 170 8830 12929 32 4 61 3 0 I shall also try pgtune in a while. -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] sudden spurt in swap utilization (was:cpu bound postgresql setup.)
Dear List, pgtune suggests the following: (current value are in braces via reason) , (*) indicates significant difference from current value. default_statistics_target = 50 # pgtune wizard 2010-06-25 (current 100 via default) (*) maintenance_work_mem = 1GB # pgtune wizard 2010-06-25 (16MB via default) checkpoint_completion_target = 0.9 # pgtune wizard 2010-06-25 (0.5 via default) (*) effective_cache_size = 22GB # pgtune wizard 2010-06-25 (18GB , specified) work_mem = 192MB # pgtune wizard 2010-06-25 (256MB , specified) (*) wal_buffers = 8MB # pgtune wizard 2010-06-25 ( 64kb , via default) checkpoint_segments = 16 # pgtune wizard 2010-06-25 (30 , specified) shared_buffers = 7680MB # pgtune wizard 2010-06-25 ( 4096 MB , specified) (*) max_connections = 80 # pgtune wizard 2010-06-25 ( 300 , ;-) specified ) when i reduce max_connections i start getting errors, i will see again concurrent connections during business hours. lot of our connections are in IDLE in transaction state during business this peculiar behavior of mod_perl servers have been discussed in past i think. dont' remember if there was any resolution.
Re: [PERFORM] Occasional giant spikes in CPU load
Dear Criag, also check for the possibility of installing sysstat in our system. it goes a long way in collecting the system stats. you may consider increasing the frequency of data collection by changing the interval of cron job manually in /etc/cron.d/ normally its */10 , you may make it */2 for time being. the software automatically maintains historical records of data for 1 month.
[PERFORM] Re: sudden spurt in swap utilization (was:cpu bound postgresql setup.)
Dear tom, we have autocommit off in dbi. Any commit or rollback from
the persistent modperl process immediately issues begin work; if the
modperl process is waiting for request the database backend remains in
idle in transaction state. Unless we modify data in a http request we
neighter issue a commit nor rollback.
On 6/25/10, Tom Molesworth t...@audioboundary.com wrote:
On 25/06/10 16:59, Rajesh Kumar Mallah wrote:
when i reduce max_connections i start getting errors, i will see again
concurrent connections
during business hours. lot of our connections are in IDLE in
transaction state during business
this peculiar behavior of mod_perl servers have been discussed in
past i think. dont' remember
if there was any resolution.
If connections spend any significant amount of time in IDLE in
transaction state, that might indicate you're not committing/rolling
back after running queries - can you show an example of the code you're
using?
e.g. something like my $dbh = DBI-connect(...); my $sth =
$dbh-prepare(q{select ... }); $sth-fetchall_arrayref; $sth-rollback;
Tom
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
--
Sent from Gmail for mobile | mobile.google.com
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] sudden spurt in swap utilization (was:cpu bound postgresql setup.)
Dear Greg/Kevin/List , Many thanks for the comments regarding the params, I am however able to change an experiment on production in a certain time window , when that arrives i shall post my observations. Rajesh Kumar Mallah. Tradeindia.com - India's Largest B2B eMarketPlace.
Re: [PERFORM] cpu bound postgresql setup.
Dear List, 1. It was found that too many stray queries were getting generated from rouge users and bots we controlled using some manual methods. 2. We have made application changes and some significant changes have been done. 3. we use xfs and our controller has BBU , we changed barriers=1 to barriers=0 as i learnt that having barriers=1 on xfs and fsync as the sync method, the advantage of BBU is lost unless barriers is = 0 (correct me if my understanding is wrong) 4. We had implemented partitioning using exclusion constraints , parent relnship was removed from quite a lot of old partition tables. our postgresql.conf -- # cat postgresql.conf | grep -v ^\s*# | grep -v ^\s*$ listen_addresses = '*' # what IP address(es) to listen on; port = 5432 # (change requires restart) max_connections = 300 # (change requires restart) shared_buffers = 10GB # min 128kB work_mem = 4GB # min 64kB fsync = on # turns forced synchronization on or off synchronous_commit = on # immediate fsync at commit checkpoint_segments = 30# in logfile segments, min 1, 16MB each archive_mode = on # allows archiving to be done archive_command = '/opt/scripts/archive_wal.sh %p %f ' archive_timeout = 600 # force a logfile segment switch after this effective_cache_size = 18GB constraint_exclusion = on # on, off, or partition logging_collector = on # Enable capturing of stderr and csvlog log_directory = '/var/log/postgresql' # directory where log files are written, log_filename = 'postgresql.log' # log file name pattern, log_truncate_on_rotation = on # If on, an existing log file of the log_rotation_age = 1d # Automatic rotation of logfiles will log_error_verbosity = verbose # terse, default, or verbose messages log_min_duration_statement = 5000 # -1 is disabled, 0 logs all statements datestyle = 'iso, mdy' lc_messages = 'en_US.UTF-8' # locale for system error message lc_monetary = 'en_US.UTF-8' # locale for monetary formatting lc_numeric = 'en_US.UTF-8' # locale for number formatting lc_time = 'en_US.UTF-8' # locale for time formatting default_text_search_config = 'pg_catalog.english' add_missing_from = on custom_variable_classes = 'general' # list of custom variable class names general.report_level = '' general.disable_audittrail2 = '' general.employee='' Also i would like to apologize that some of the discussions on this problem inadvertently became private between me kevin. On Thu, Jun 24, 2010 at 12:10 AM, Rajesh Kumar Mallah mallah.raj...@gmail.com wrote: It was nice to go through the interesting posting guidelines. i shall be analyzing the slow queries more objectively tomorrow during the peak hours. I really hope it sould be possible to track down the problem. On 6/23/10, Kevin Grittner kevin.gritt...@wicourts.gov wrote: Rajesh Kumar Mallah mallah.raj...@gmail.com wrote: did you suggest at some point that number of backend per core should be preferebly 3 ? I've found the number of *active* backends is optimal around (2 * cores) + spindles. You said you had eight cores and eight or ten spindles, so I figure a connection pool limited to somewhere around 24 active connections is ideal. (Depending on how you set up your pool, you may need a higher total number of connections to keep 24 active.) -Kevin -- Sent from Gmail for mobile | mobile.google.com -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] cpu bound postgresql setup.
On Thu, Jun 24, 2010 at 8:57 PM, Kevin Grittner kevin.gritt...@wicourts.gov wrote: I'm not clear whether you still have a problem, or whether the changes you mention solved your issues. I'll comment on potential issues that leap out at me. It shall require more observation to know if the problem is solved. my problem was high load average in the server . We find that when ldavg is between 10-20 responses of applications were acceptable ldavg 40 makes things slower. What prompted me to post to list is that the server transitioned from being IO bound to CPU bound and 90% of syscalls being lseek(XXX, 0, SEEK_END) = YYY Rajesh Kumar Mallah mallah.raj...@gmail.com wrote: 3. we use xfs and our controller has BBU , we changed barriers=1 to barriers=0 as i learnt that having barriers=1 on xfs and fsync as the sync method, the advantage of BBU is lost unless barriers is = 0 (correct me if my understanding is wrong) We use noatime,nobarrier in /etc/fstab. I'm not sure where you're setting that, but if you have a controller with BBU, you want to set it to whichever disables write barriers. as per suggestion in discussions on some other thread I set it in /etc/fstab. max_connections = 300 As I've previously mentioned, I would use a connection pool, in which case this wouldn't need to be that high. We do use connection pooling provided to mod_perl server via Apache::DBI::Cache. If i reduce this i *get* too many connections from non-superuser ... error. Will pgpool - I/II still applicable in this scenario ? work_mem = 4GB That's pretty high. That much memory can be used by each active connection, potentially for each of several parts of the active query on each connection. You should probably set this much lower in postgresql.conf and boost it if necessary for individual queries. hmmm.. it was 8GB for many months ! i shall reduce it further, but will it not result in usage of too many temp files and saturate i/o? effective_cache_size = 18GB With 32GB RAM on the machine, I would probably set this higher -- somewhere in the 24GB to 30GB range, unless you have specific reasons to believe otherwise. It's not that critical, though. i do not remember well but there is a system view that (i think) guides at what stage the marginal returns of increasing it starts disappearing , i had set it a few years back. add_missing_from = on Why? There has been discussion of eliminating this option -- do you have queries which rely on the non-standard syntax this enables? unfortunately yes. Also i would like to apologize that some of the discussions on this problem inadvertently became private between me kevin. Oops. I failed to notice that. Thanks for bringing it back to the list. (It's definitely in your best interest to keep it in front of all the other folks here, some of whom regularly catch things I miss or get wrong.) If you still do have slow queries, please follow up with details. I have now set log_min_duration_statement = 5000 and there are few queries that come to logs. please comment on the connection pooling aspect. Warm Regards Rajesh Kumar Mallah. -Kevin -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] cpu bound postgresql setup.
i do not remember well but there is a system view that (i think) guides at what stage the marginal returns of increasing it starts disappearing , i had set it a few years back. Sorry the above comment was regarding setting shared_buffers not effective_cache_size. On Thu, Jun 24, 2010 at 10:55 PM, Rajesh Kumar Mallah mallah.raj...@gmail.com wrote: On Thu, Jun 24, 2010 at 8:57 PM, Kevin Grittner kevin.gritt...@wicourts.gov wrote: I'm not clear whether you still have a problem, or whether the changes you mention solved your issues. I'll comment on potential issues that leap out at me. It shall require more observation to know if the problem is solved. my problem was high load average in the server . We find that when ldavg is between 10-20 responses of applications were acceptable ldavg 40 makes things slower. What prompted me to post to list is that the server transitioned from being IO bound to CPU bound and 90% of syscalls being lseek(XXX, 0, SEEK_END) = YYY Rajesh Kumar Mallah mallah.raj...@gmail.com wrote: 3. we use xfs and our controller has BBU , we changed barriers=1 to barriers=0 as i learnt that having barriers=1 on xfs and fsync as the sync method, the advantage of BBU is lost unless barriers is = 0 (correct me if my understanding is wrong) We use noatime,nobarrier in /etc/fstab. I'm not sure where you're setting that, but if you have a controller with BBU, you want to set it to whichever disables write barriers. as per suggestion in discussions on some other thread I set it in /etc/fstab. max_connections = 300 As I've previously mentioned, I would use a connection pool, in which case this wouldn't need to be that high. We do use connection pooling provided to mod_perl server via Apache::DBI::Cache. If i reduce this i *get* too many connections from non-superuser ... error. Will pgpool - I/II still applicable in this scenario ? work_mem = 4GB That's pretty high. That much memory can be used by each active connection, potentially for each of several parts of the active query on each connection. You should probably set this much lower in postgresql.conf and boost it if necessary for individual queries. hmmm.. it was 8GB for many months ! i shall reduce it further, but will it not result in usage of too many temp files and saturate i/o? effective_cache_size = 18GB With 32GB RAM on the machine, I would probably set this higher -- somewhere in the 24GB to 30GB range, unless you have specific reasons to believe otherwise. It's not that critical, though. i do not remember well but there is a system view that (i think) guides at what stage the marginal returns of increasing it starts disappearing , i had set it a few years back. add_missing_from = on Why? There has been discussion of eliminating this option -- do you have queries which rely on the non-standard syntax this enables? unfortunately yes. Also i would like to apologize that some of the discussions on this problem inadvertently became private between me kevin. Oops. I failed to notice that. Thanks for bringing it back to the list. (It's definitely in your best interest to keep it in front of all the other folks here, some of whom regularly catch things I miss or get wrong.) If you still do have slow queries, please follow up with details. I have now set log_min_duration_statement = 5000 and there are few queries that come to logs. please comment on the connection pooling aspect. Warm Regards Rajesh Kumar Mallah. -Kevin -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
[PERFORM] Re: cpu bound postgresql setup. Firstly many thanks for responding. I am concerned because the load averages have increased and users complaining of slowness. I do not change settings freq
On 6/23/10, Kevin Grittner kevin.gritt...@wicourts.gov wrote: Rajesh Kumar Mallah mallah.raj...@gmail.com wrote: PasteBin for the vmstat output http://pastebin.com/mpHCW9gt On Wed, Jun 23, 2010 at 8:22 PM, Rajesh Kumar Mallah mallah.raj...@gmail.com wrote: Dear List , I observe that my postgresql (ver 8.4.2) dedicated server has turned cpu bound and there is a high load average in the server 50 usually. The server has 2 Quad Core CPUs already and there are 6 or 8 drives in raid 10 , there is negligable i/o wait. There is 32GB ram and no swapping. When i strace processes at random i see lot of lseek (XXX,0,SEEK_END) calls which i feel were not that frequent before. can any pointers be got for investigating the high cpu usage by postgresql processes. I'm not clear on what problem you are experiencing. Using a lot of your hardware's capacity isn't a problem in itself -- are you getting poor response time? Poor throughput? Some other problem? Is it continuous, or only when certain queries run? One thing that is apparent is that you might want to use a connection pool, or if you're already using one you might want to configure it to reduce the maximum number of active queries. With eight cores and eight drives, your best throughput is going to be at somewhere around 24 active connections, and you appear to be going to at least twice that. If you can provide a copy of your postgresql.conf settings (without comments) and an EXPLAIN ANALYZE of a slow query, along with the schema information for the tables used by the query, you'll probably get useful advice on how to adjust your configuration, indexing, or query code to improve performance. -Kevin -- Sent from Gmail for mobile | mobile.google.com -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Entry point for Postgresql Performance
Databases are usually IO bound , vmstat results can confirm individual cases and setups. In case the server is IO bound the entry point should be setting up properly performing IO. RAID10 helps a great extent in improving IO bandwidth by parallelizing the IO operations, more spindles the better. Also write caches helps in great deal in caching the writes and making commits faster. In my opinion system level tools (like vmstat) at peak load times can be an entry point in understanding the bottlenecks of a particular setup. if there is swapping u absolutely need to double the ram . ( excess ram can be used in disk block caching) if its cpu bound add more cores or high speed cpus if its io bound put better raid arrays controller. regds mallah. On Thu, Mar 12, 2009 at 4:22 PM, Nagalingam, Karthikeyan karthikeyan.nagalin...@netapp.com wrote: Hi, Can you guide me, Where is the entry point to get the documentation for Postgresql performance tuning, Optimization for Postgresql with Storage controller. Your recommendation and suggestion are welcome. Regards Karthikeyan.N -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] suggestions for postgresql setup on Dell 2950 , PERC6i controller
Effect of ReadAhead Settings disabled,256(default) , 512,1024 SEQUENTIAL xfs_ra0 414741 , 66144 xfs_ra256403647, 545026 all tests on sda6 xfs_ra512411357, 564769 xfs_ra1024 404392, 431168 looks like 512 was the best setting for this controller That's only known for sequential access. How did it perform under the random access, or did the numbers not change too much? RANDOM SEEKS /sec xfs_ra0 6341.0 xfs_ra256 14642.7 xfs_ra512 14415.6 xfs_ra102414541.6 the value does not seems to be having much effect unless its totally disabled. regds mallah. -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] suggestions for postgresql setup on Dell 2950 , PERC6i controller
On Wed, Feb 18, 2009 at 2:27 PM, Grzegorz Jaśkiewicz gryz...@gmail.com wrote: have you tried hanging bunch of raid1 to linux's md, and let it do raid0 for you ? Hmmm , i will have only 3 bunches in that case as system has to boot from first bunch as system has only 8 drives. i think reducing spindles will reduce perf. I also have a SATA SAN though from which i can boot! but the server needs to be rebuilt in that case too. I (may) give it a shot. regds -- mallah. I heard plenty of stories where this actually sped up performance. One noticeable is case of youtube servers. -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] suggestions for postgresql setup on Dell 2950 , PERC6i controller
There has been an error in the tests the dataset size was not 2*MEM it was 0.5*MEM i shall redo the tests and post results. -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] suggestions for postgresql setup on Dell 2950 , PERC6i controller
On Tue, Feb 17, 2009 at 5:15 PM, Matthew Wakeling matt...@flymine.org wrote: On Tue, 17 Feb 2009, Rajesh Kumar Mallah wrote: sda6 -- xfs with default formatting options. sda7 -- mkfs.xfs -f -d sunit=128,swidth=512 /dev/sda7 sda8 -- ext3 (default) it looks like mkfs.xfs options sunit=128 and swidth=512 did not improve io throughtput as such in bonnie++ tests . it looks like ext3 with default options performed worst in my case. Of course, doing comparisons using a setup like that (on separate partitions) will skew the results, because discs' performance differs depending on the portion of the disc being accessed. You should perform the different filesystem tests on the same partition one after the other instead. point noted . will redo the test on ext3. Matthew -- We did a risk management review. We concluded that there was no risk of any management.-- Hugo Mills h...@carfax.nildram.co.uk -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] suggestions for postgresql setup on Dell 2950 , PERC6i controller
the raid10 voulme was benchmarked again taking in consideration above points # fdisk -l /dev/sda Disk /dev/sda: 290.9 GB, 290984034304 bytes 255 heads, 63 sectors/track, 35376 cylinders Units = cylinders of 16065 * 512 = 8225280 bytes Device Boot Start End Blocks Id System /dev/sda1 * 1 12 96358+ 83 Linux /dev/sda2 13131710482412+ 83 Linux /dev/sda313181578 2096482+ 83 Linux /dev/sda41579 35376 2714824355 Extended /dev/sda515791839 2096451 82 Linux swap / Solaris /dev/sda61840791948837568+ 83 Linux /dev/sda7 29297 3537648837600 83 Linux CASE writesreads KB/s KB/s ext3(whole disk) 244194 , 352093 one part whole disk xfs(whole disk) 402352 , 547674 25ext3 260132 , 420905 partition only first 25% 25xfs 404291 , 547672(/dev/sda6) ext3_25 227307, 348237 partition specifically last 25% xfs25 350661, 474481 (/dev/sda7) Effect of ReadAhead Settings disabled,256(default) , 512,1024 xfs_ra0 414741 , 66144 xfs_ra256403647, 545026 all tests on sda6 xfs_ra512411357, 564769 xfs_ra1024 404392, 431168 looks like 512 was the best setting for this controller Considering these two figures xfs25 350661, 474481(/dev/sda7) 25xfs 404291 , 547672(/dev/sda6) looks like the beginning of the drives are 15% faster than the ending sections , considering this is it worth creating a special tablespace at the begining of drives if at all done what kind of data objects should be placed towards begining , WAL , indexes , frequently updated tables or sequences ? regds mallah. On Tue, Feb 17, 2009 at 9:49 PM, Scott Carey sc...@richrelevance.com wrote: Generally speaking, you will want to use a partition that is 25% or less the size of the whole disk as well. If it is the whole thing, one file system can place the file you are testing in a very different place on disk and skew results as well. My own tests, using the first 20% of an array for all, showed that xfs with default settings beat out or equalled 'tuned' settings with hardware raid 10, and was far faster than ext3 in sequential transfer rate. same here. If testing STR, you will also want to tune the block device read ahead value (example: /sbin/blockdev -getra /dev/sda6). This has very large impact on sequential transfer performance (and no impact on random access). How large of an impact depends quite a bit on what kernel you're on since the readahead code has been getting better over time and requires less tuning. But it still defaults out-of-the-box to more optimal settings for a single drive than RAID. For SAS, try 256 or 512 * the number of effective spindles (spindles * 0.5 for raid 10). For SATA, try 1024 or 2048 * the number of effective spindles. The value is in blocks (512 bytes). There is documentation on the blockdev command, and here is a little write-up I found with a couple web searches: http://portal.itauth.com/2007/11/20/howto-linux-double-your-disk-read-performance-single-command From: pgsql-performance-ow...@postgresql.org [pgsql-performance-ow...@postgresql.org] On Behalf Of Rajesh Kumar Mallah [mallah.raj...@gmail.com] Sent: Tuesday, February 17, 2009 5:25 AM To: Matthew Wakeling Cc: pgsql-performance@postgresql.org Subject: Re: [PERFORM] suggestions for postgresql setup on Dell 2950 , PERC6i controller On Tue, Feb 17, 2009 at 5:15 PM, Matthew Wakeling matt...@flymine.org wrote: On Tue, 17 Feb 2009, Rajesh Kumar Mallah wrote: sda6 -- xfs with default formatting options. sda7 -- mkfs.xfs -f -d sunit=128,swidth=512 /dev/sda7 sda8 -- ext3 (default) it looks like mkfs.xfs options sunit=128 and swidth=512 did not improve io throughtput as such in bonnie++ tests . it looks like ext3 with default options performed worst in my case. Of course, doing comparisons using a setup like that (on separate partitions) will skew the results, because discs' performance differs depending on the portion of the disc being accessed. You should perform the different filesystem tests on the same partition one after the other instead. point noted . will redo the test on ext3. Matthew -- We did a risk management review. We concluded that there was no risk of any management.-- Hugo Mills h...@carfax.nildram.co.uk -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref
Re: [PERFORM] suggestions for postgresql setup on Dell 2950 , PERC6i controller
Detailed bonnie++ figures. http://98.129.214.99/bonnie/report.html On Wed, Feb 18, 2009 at 1:22 PM, Rajesh Kumar Mallah mallah.raj...@gmail.com wrote: the raid10 voulme was benchmarked again taking in consideration above points # fdisk -l /dev/sda Disk /dev/sda: 290.9 GB, 290984034304 bytes 255 heads, 63 sectors/track, 35376 cylinders Units = cylinders of 16065 * 512 = 8225280 bytes Device Boot Start End Blocks Id System /dev/sda1 * 1 12 96358+ 83 Linux /dev/sda2 13131710482412+ 83 Linux /dev/sda313181578 2096482+ 83 Linux /dev/sda41579 35376 2714824355 Extended /dev/sda515791839 2096451 82 Linux swap / Solaris /dev/sda61840791948837568+ 83 Linux /dev/sda7 29297 3537648837600 83 Linux CASE writesreads KB/s KB/s ext3(whole disk) 244194 , 352093 one part whole disk xfs(whole disk) 402352 , 547674 25ext3 260132 , 420905 partition only first 25% 25xfs 404291 , 547672(/dev/sda6) ext3_25 227307, 348237 partition specifically last 25% xfs25 350661, 474481 (/dev/sda7) Effect of ReadAhead Settings disabled,256(default) , 512,1024 xfs_ra0 414741 , 66144 xfs_ra256403647, 545026 all tests on sda6 xfs_ra512411357, 564769 xfs_ra1024 404392, 431168 looks like 512 was the best setting for this controller Considering these two figures xfs25 350661, 474481(/dev/sda7) 25xfs 404291 , 547672(/dev/sda6) looks like the beginning of the drives are 15% faster than the ending sections , considering this is it worth creating a special tablespace at the begining of drives if at all done what kind of data objects should be placed towards begining , WAL , indexes , frequently updated tables or sequences ? regds mallah. On Tue, Feb 17, 2009 at 9:49 PM, Scott Carey sc...@richrelevance.com wrote: Generally speaking, you will want to use a partition that is 25% or less the size of the whole disk as well. If it is the whole thing, one file system can place the file you are testing in a very different place on disk and skew results as well. My own tests, using the first 20% of an array for all, showed that xfs with default settings beat out or equalled 'tuned' settings with hardware raid 10, and was far faster than ext3 in sequential transfer rate. same here. If testing STR, you will also want to tune the block device read ahead value (example: /sbin/blockdev -getra /dev/sda6). This has very large impact on sequential transfer performance (and no impact on random access). How large of an impact depends quite a bit on what kernel you're on since the readahead code has been getting better over time and requires less tuning. But it still defaults out-of-the-box to more optimal settings for a single drive than RAID. For SAS, try 256 or 512 * the number of effective spindles (spindles * 0.5 for raid 10). For SATA, try 1024 or 2048 * the number of effective spindles. The value is in blocks (512 bytes). There is documentation on the blockdev command, and here is a little write-up I found with a couple web searches: http://portal.itauth.com/2007/11/20/howto-linux-double-your-disk-read-performance-single-command From: pgsql-performance-ow...@postgresql.org [pgsql-performance-ow...@postgresql.org] On Behalf Of Rajesh Kumar Mallah [mallah.raj...@gmail.com] Sent: Tuesday, February 17, 2009 5:25 AM To: Matthew Wakeling Cc: pgsql-performance@postgresql.org Subject: Re: [PERFORM] suggestions for postgresql setup on Dell 2950 , PERC6i controller On Tue, Feb 17, 2009 at 5:15 PM, Matthew Wakeling matt...@flymine.org wrote: On Tue, 17 Feb 2009, Rajesh Kumar Mallah wrote: sda6 -- xfs with default formatting options. sda7 -- mkfs.xfs -f -d sunit=128,swidth=512 /dev/sda7 sda8 -- ext3 (default) it looks like mkfs.xfs options sunit=128 and swidth=512 did not improve io throughtput as such in bonnie++ tests . it looks like ext3 with default options performed worst in my case. Of course, doing comparisons using a setup like that (on separate partitions) will skew the results, because discs' performance differs depending on the portion of the disc being accessed. You should perform the different filesystem tests on the same partition one after the other instead. point noted . will redo the test on ext3. Matthew -- We did a risk management review. We concluded that there was no risk of any management
Re: [PERFORM] suggestions for postgresql setup on Dell 2950 , PERC6i controller
BTW our Machine got build with 8 15k drives in raid10 , from bonnie++ results its looks like the machine is able to do 400 Mbytes/s seq write and 550 Mbytes/s read. the BB cache is enabled with 256MB sda6 -- xfs with default formatting options. sda7 -- mkfs.xfs -f -d sunit=128,swidth=512 /dev/sda7 sda8 -- ext3 (default) it looks like mkfs.xfs options sunit=128 and swidth=512 did not improve io throughtput as such in bonnie++ tests . it looks like ext3 with default options performed worst in my case. regds -- mallah NOTE: observations made in this post are interpretations by the poster only which may or may not be indicative of the true suitablity of the filesystem. On Mon, Feb 16, 2009 at 7:01 PM, Gregory Stark st...@enterprisedb.com wrote: Arjen van der Meijden acmmail...@tweakers.net writes: When we purchased our Perc 5/e with MD1000 filled with 15 15k rpm sas disks, my colleague actually spend some time benchmarking the PERC and a ICP Vortex (basically a overclocked Adaptec) on those drives. Unfortunately he doesn't have too many comparable results, but it basically boiled down to quite good scores for the PERC and a bit less for the ICP Vortex. IOMeter sequential reads are above 300MB/s for the RAID5 and above 240MB/s for a RAID10 (and winbench99 versions range from 400+ to 600+MB/s). FWIW those are pretty terrible numbers for fifteen 15k rpm drives. They're about what you would expect if for a PCI-X card which was bus bandwidth limited. A PCI-e card should be able to get about 3x that from the drives. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's RemoteDBA services! -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] scheduling autovacuum at lean hours only.
Its nice to know the evolution of autovacuum and i understand that the suggestion/requirement of autovacuum at lean hours only was defeating the whole idea. regds --rajesh kumar mallah. On Fri, Feb 13, 2009 at 11:07 PM, Chris Browne cbbro...@acm.org wrote: mallah.raj...@gmail.com (Rajesh Kumar Mallah) writes: why is it not a good idea to give end users control over when they want to run it ? It's not a particularly good idea to give end users things that they are likely then to *immediately* use to shoot themselves in the foot. Turning off vacuuming all day is the sort of thing that is indeed pretty certain to hurt you when you imagined it was going to help you. In particular, if you shut off autovac all day, heavily updated tables with certain sorts of (pretty common!) update patterns are certain to bloat up, to the point that you'll need to do CLUSTER/VACUUM FULL on them. In effect, the practical effect of autovacuum at lean hours only is more reasonably described as cancel autovacuum and revert to the elder policy of requiring users to do manual vacuuming. It's worth looking at how autovacuum has been evolving over time... - When it was introduced, 8.0-ish (maybe it was 8.1 when it became official), it was pretty primitive. Autovac was a single process, where you had three controls over behaviour: - You could run autovac, or not; - You could exclude specific tables from being processed by autovac - There is a capability to vacuum less aggressively by using delays to reduce autovac I/O usage - In 8.3, it was enhanced to add the notion of having multiple vacuum workers There was discussion about having one of those workers restrict itself to small tables, so that you'd never have the scenario where the workers were all busy and a small table that needed vacuuming was left unvacuumed for a long time. It appears that didn't happen, which seems unfortunate, but that's life... You should look at all the knobs that *are* offered before deciding a policy that may be harmful to performance. As things stand now, there are a couple of ways I could see tuning this: - You might check on the GUC variables autovacuum_vacuum_cost_delay and autovacuum_vacuum_cost_limit, which would allow you to restrict the I/O cost. This might allow you to run autovacuum all the time without adversely affecting performance. - You might come up with a list of the *LARGE* tables that you don't want vacuumed during the day, and set up a cron job that adds/drops them from the pg_autovacuum table at the appropriate times. This is definitely going to be more finicky, and requires a great deal more awareness of the tables being updated by your applications. It makes autovacuum a whole lot less automatic. There are known enhancements coming up: - In 8.4, there is a capability for VACUUM to only process the portions of the table known to have been altered. That ought to be a better answer than *any* of the fiddling suggested, to date. Right now, a VACUUM on public.my_huge_table, a table 18GB in size, will walk through the entire table, even though there were only a handful of pages where tuples were invalidated. This is almost certainly the single best improvement possible to resolve your issue; it seems likely to *directly* address the problem, and has the considerable merit of not requiring much if any configuration/reconfiguration/scheduling. -- (reverse (concatenate 'string gro.mca @ enworbbc)) http://linuxdatabases.info/info/ what would we do without C? we would have PASAL, BASI, OBOL, and Ommon Lisp. -- #Erik -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
[PERFORM] dissimilar drives in Raid10 , does it make difference ?
I have received Dell Poweredge 2950 MIII with 2 kind of drives. I cant' make out the reason behind it , does it make any difference in long run or in performance the drives are similar in overall characteristics but does the minor differences if will cause any problem ? scsi0 : LSI Logic SAS based MegaRAID driver Vendor: SEAGATE Model: ST973451SSRev: SM04 Type: Direct-Access ANSI SCSI revision: 05 Vendor: FUJITSU Model: MBC2073RC Rev: D506 Type: Direct-Access ANSI SCSI revision: 05 Vendor: FUJITSU Model: MBC2073RC Rev: D506 Type: Direct-Access ANSI SCSI revision: 05 Vendor: SEAGATE Model: ST973451SSRev: SM04 Type: Direct-Access ANSI SCSI revision: 05 Vendor: SEAGATE Model: ST973451SSRev: SM04 Type: Direct-Access ANSI SCSI revision: 05 Vendor: FUJITSU Model: MBC2073RC Rev: D506 Type: Direct-Access ANSI SCSI revision: 05 Vendor: SEAGATE Model: ST973451SSRev: SM04 Type: Direct-Access ANSI SCSI revision: 05 Vendor: FUJITSU Model: MBC2073RC Rev: D506 Type: Direct-Access ANSI SCSI revision: 05 thanks regds -- mallah -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
[PERFORM] please help with the explain analyze plan
Dear friends,
I have explain analyze of two queries
explain analyze SELECT count(*) from general.rfis where 1=1 and
inquiry_type = 'BUY'and receiver_uid=1320721;
(7 ms)
http://pastebin.com/m5297e03c
explain analyze SELECT count(*) from general.rfis where 1=1 and
inquiry_type = 'BUY'and receiver_uid=1320721 generated_date =
2251 and ;
(80 secs)
http://pastebin.com/d1e4bdea7
The table general.rfis is partitioned on generated_date and the
condition generated_date = 2251
was added with the intention to limit the number of (date based)
partitions that would be searched
by the query using the constraint exclusion facility. however as
evident the query has become very
slow as a result of this condition (even on primed caches).
can anyone kindly explain why the result was so counter intuitive ?
In particular where is most of the (80828.438 ms) spent on the plan
http://pastebin.com/d1e4bdea7 (reference to actual line number is appreciated)
structure of a typical partition (abridged)
Table rfi_partitions.rfis_part_2009_01
Column | Type |
Modifiers
---++---
rfi_id| integer| not null default
nextval('general.rfis_rfi_id_seq'::regclass)
sender_uid| integer| not null
receiver_uid | integer| not null
subject | character varying(100) | not null
message | text | not null
inquiry_type | character varying(50) | default
'BUY'::character varying
inquiry_source| character varying(30) | not null
generated_date| integer| not null default
general.current_date_id()
Indexes:
rfis_part_2009_01_pkey PRIMARY KEY, btree (rfi_id)
rfis_part_2009_01_generated_date btree (generated_date)
rfis_part_2009_01_receiver_uid btree (receiver_uid) CLUSTER
rfis_part_2009_01_sender_uid btree (sender_uid)
Check constraints:
rfis_part_2009_01_generated_date_check CHECK (generated_date =
3289 AND generated_date = 3319)
rfis_part_2009_01_rfi_id_check CHECK (rfi_id = 12344252 AND
rfi_id = 12681399)
Inherits: rfis
regds
rajesh kumar mallah.
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] please help with the explain analyze plan
thanks for the hint, now the peak hour is over and the same scan is taking 71 ms in place of 8 ms and the total query time is also acceptable. But it is surprising that the scan was taking so long consistently at that point of time. I shall test again under similar circumstance tomorrow. Is it possible to enable block level statistics from the psql prompt for a particular query and see the results on the psql prompt ? explain analyze SELECT count(*) from rfi_partitions.rfis_part_2006_02 where generated_date = 2251 and receiver_uid=1320721 ; QUERY PLAN Aggregate (cost=6.44..6.45 rows=1 width=0) (actual time=71.513..71.513 rows=1 loops=1) - Index Scan using rfis_part_2006_02_generated_date on rfis_part_2006_02 (cost=0.00..6.43 rows=1 width=0) (actual time=71.508..71.508 rows=0 loops=1) Index Cond: (generated_date = 2251) Filter: (receiver_uid = 1320721) Total runtime: 71.553 ms (5 rows) -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] please help with the explain analyze plan
hurray! ANALYZING changed the plan I was not expecting the plan to change because the partition of 2006_02 is supposed to be dormant. maybe the partition was never analyzed. But still question remains, why the time taken was in orders of magnitude higher in loaded condition. tradein_clients= explain SELECT count(*) from rfi_partitions.rfis_part_2006_02 where generated_date = 2251 and receiver_uid=1320721 ; QUERY PLAN Aggregate (cost=6.44..6.45 rows=1 width=0) - Index Scan using rfis_part_2006_02_generated_date on rfis_part_2006_02 (cost=0.00..6.43 rows=1 width=0) Index Cond: (generated_date = 2251) Filter: (receiver_uid = 1320721) (4 rows) tradein_clients= ANALYZE rfi_partitions.rfis_part_2006_02; ANALYZE tradein_clients= explain SELECT count(*) from rfi_partitions.rfis_part_2006_02 where generated_date = 2251 and receiver_uid=1320721 ; QUERY PLAN -- Aggregate (cost=8.78..8.79 rows=1 width=0) - Index Scan using rfis_part_2006_02_receiver_uid on rfis_part_2006_02 (cost=0.00..8.77 rows=1 width=0) Index Cond: (receiver_uid = 1320721) Filter: (generated_date = 2251) (4 rows) tradein_clients= explain analyze SELECT count(*) from rfi_partitions.rfis_part_2006_02 where generated_date = 2251 and receiver_uid=1320721 ; QUERY PLAN Aggregate (cost=8.78..8.79 rows=1 width=0) (actual time=0.045..0.045 rows=1 loops=1) - Index Scan using rfis_part_2006_02_receiver_uid on rfis_part_2006_02 (cost=0.00..8.77 rows=1 width=0) (actual time=0.042..0.042 rows=0 loops=1) Index Cond: (receiver_uid = 1320721) Filter: (generated_date = 2251) Total runtime: 0.082 ms (5 rows) tradein_clients= On Wed, Feb 11, 2009 at 6:07 PM, Rajesh Kumar Mallah mallah.raj...@gmail.com wrote: thanks for the hint, now the peak hour is over and the same scan is taking 71 ms in place of 8 ms and the total query time is also acceptable. But it is surprising that the scan was taking so long consistently at that point of time. I shall test again under similar circumstance tomorrow. Is it possible to enable block level statistics from the psql prompt for a particular query and see the results on the psql prompt ? explain analyze SELECT count(*) from rfi_partitions.rfis_part_2006_02 where generated_date = 2251 and receiver_uid=1320721 ; QUERY PLAN Aggregate (cost=6.44..6.45 rows=1 width=0) (actual time=71.513..71.513 rows=1 loops=1) - Index Scan using rfis_part_2006_02_generated_date on rfis_part_2006_02 (cost=0.00..6.43 rows=1 width=0) (actual time=71.508..71.508 rows=0 loops=1) Index Cond: (generated_date = 2251) Filter: (receiver_uid = 1320721) Total runtime: 71.553 ms (5 rows) -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
[PERFORM] scheduling autovacuum at lean hours only.
Hi, Is it possible to configure autovacuum to run only during certain hours ? We are forced to keep it off because it pops up during the peak query hours. Regds rajesh kumar mallah. -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] scheduling autovacuum at lean hours only.
On Wed, Feb 11, 2009 at 7:11 PM, Guillaume Cottenceau g...@mnc.ch wrote: Rajesh Kumar Mallah mallah.rajesh 'at' gmail.com writes: Hi, Is it possible to configure autovacuum to run only during certain hours ? We are forced to keep it off because it pops up during the peak query hours. You'd rather configure the delaying process to not alter too much performance. Autovacuum is really not targeted at running once a day - I think it is partly because the old vacuuming was too weak (because too seldom in many cases) that autovaccum was added. A delaying configuration that works nicely for us without impacting performance much (tested at the time of 8.2 to cause a +40% response time during autovacuuming, compared to +300% with more default values): vacuum_cost_delay = 150 vacuum_cost_page_hit = 1 vacuum_cost_page_miss = 10 vacuum_cost_page_dirty = 20 vacuum_cost_limit = 1000 autovacuum_vacuum_cost_delay = 300 why is it not a good idea to give end users control over when they want to run it ? (Disclaimer: IIRC, Alvaro doesn't like these figures at all) Of course, these are good for us (bloat is very, very low and performance impact is not experienced in production), not necessarily for you. You should conduct your own tests. Be sure to also consider http://developer.postgresql.org/~wieck/vacuum_cost/ -- Guillaume Cottenceau -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] scheduling autovacuum at lean hours only.
On Wed, Feb 11, 2009 at 10:03 PM, Grzegorz Jaśkiewicz gryz...@gmail.com wrote: On Wed, Feb 11, 2009 at 2:57 PM, Rajesh Kumar Mallah mallah.raj...@gmail.com wrote: vacuum_cost_delay = 150 vacuum_cost_page_hit = 1 vacuum_cost_page_miss = 10 vacuum_cost_page_dirty = 20 vacuum_cost_limit = 1000 autovacuum_vacuum_cost_delay = 300 why is it not a good idea to give end users control over when they want to run it ? Effectively, you have control over autovacuum via these params. You have to remember, that autovacuum doesn't cost much, and it makes planner know more about data. It's not there to clean up databases, as you might imagine - it is there to update stats, and mark pages as free. So make sure you tweak that config fist, because I have a funny feeling that you just think that vacuuming bogs down your machine, and _can_ be turned off without any bad consequences, which is simply not true. our usage pattern is such that peak activity (indicated by load average) during day time is 10 times during night hours. Autovacuum just puts more pressure to the system. If less stressing version is used then it shall take longer to complete one cycle, which would mean less performance for longer time . Less performance queues up queries and encourages people to re submit their queries which again adds to bogging up the system. In our case i feel the hardware is bit underscaled as compared to load thats why i think running in lean hours is best of both worlds no performance sacrifices and intelligent vacuuming. regds -- mallah. -- GJ -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] scheduling autovacuum at lean hours only.
On Wed, Feb 11, 2009 at 11:30 PM, Brad Nicholson bnich...@ca.afilias.info wrote: On Wed, 2009-02-11 at 22:57 +0530, Rajesh Kumar Mallah wrote: On Wed, Feb 11, 2009 at 10:03 PM, Grzegorz Jaśkiewicz gryz...@gmail.com wrote: On Wed, Feb 11, 2009 at 2:57 PM, Rajesh Kumar Mallah mallah.raj...@gmail.com wrote: vacuum_cost_delay = 150 vacuum_cost_page_hit = 1 vacuum_cost_page_miss = 10 vacuum_cost_page_dirty = 20 vacuum_cost_limit = 1000 autovacuum_vacuum_cost_delay = 300 why is it not a good idea to give end users control over when they want to run it ? Effectively, you have control over autovacuum via these params. You have to remember, that autovacuum doesn't cost much, and it makes planner know more about data. It's not there to clean up databases, as you might imagine - it is there to update stats, and mark pages as free. So make sure you tweak that config fist, because I have a funny feeling that you just think that vacuuming bogs down your machine, and _can_ be turned off without any bad consequences, which is simply not true. our usage pattern is such that peak activity (indicated by load average) during day time is 10 times during night hours. Autovacuum just puts more pressure to the system. If less stressing version is used then it shall take longer to complete one cycle, which would mean less performance for longer time . Less performance queues up queries and encourages people to re submit their queries which again adds to bogging up the system. That's not exactly how it works in practise, if tuned properly. It may take longer, but it is less intensive while running. We had one system that had spikes happening due to the exact case you described - there were noticeably high IO wait times while certain tables were being vacuumed. We set the cost delay and the wait times dropped to the point where it was non-issue. I think i can take this route and monitor the io activity during vacuums. thanks everyone for their suggestions. -- mallah. Vacuums take twice as long, but there is no measurable impact to the performance. In our case i feel the hardware is bit underscaled as compared to load thats why i think running in lean hours is best of both worlds no performance sacrifices and intelligent vacuuming. That is a different issue altogether. Not vacuuming a running system at all during peak hours is not considered intelligent vacuuming IMHO. There are plenty of use cases where small, frequent vacuums keep tables under control at a very low cost. Letting them go for extended periods of time without vacuuming causes bloat and eventual slowdowns to table access which manifest in higher IO usage across the board. If you really are dead set on vacuuming only at night, you may want to do a careful analysis of which tables need to be vacuumed and when, and trigger manual vacuums from cron. -- Brad Nicholson 416-673-4106 Database Administrator, Afilias Canada Corp. -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
[PERFORM] query becomes fas on 'SET enable_hashjoin TO off;'
Hi ,
I have a query in which two huge tables (A,B) are joined using an indexed
column and a search is made on tsvector on some column on B. Very limited
rows of B are expected to match the query on tsvector column.
With default planner settings the query takes too long ( 100 secs) , but
with hashjoin off it returns almost immediately. The question is , is
it is advisable to
tweak planner settings for specific queries in application ?
The plans are as follows.
1. With default settings
explainselect lead_id from general.trade_leads join
general.profile_master as pm using(profile_id) where status ='m' and
co_name_vec @@ to_tsquery('plastictubes') limit 20;
QUERY PLAN
---
Limit (cost=4109.11..11127.78 rows=20 width=4)
- Hash Join (cost=4109.11..90789.72 rows=247 width=4)
Hash Cond: (trade_leads.profile_id = pm.profile_id)
- Seq Scan on trade_leads (cost=0.00..85752.52 rows=246832 width=8)
Filter: ((status)::text = 'm'::text)
- Hash (cost=4095.68..4095.68 rows=1074 width=4)
- Bitmap Heap Scan on profile_master pm
(cost=40.89..4095.68 rows=1074 width=4)
Filter: (co_name_vec @@ '''plastic'' ''tube'''::tsquery)
- Bitmap Index Scan on
profile_master_co_name_vec (cost=0.00..40.62 rows=1074 width=0)
Index Cond: (co_name_vec @@ '''plastic''
''tube'''::tsquery)
(10 rows)
2. with SET enable_hashjoin TO off;
explain analyze select lead_id from general.trade_leads join
general.profile_master as pm using(profile_id) where status ='m' and
co_name_vec @@ to_tsquery('plastictubes') limit 20;
QUERY PLAN
-
Limit (cost=3.42..13080.44 rows=20 width=4) (actual
time=1530.039..1530.039 rows=0 loops=1)
- Nested Loop (cost=3.42..161504.56 rows=247 width=4) (actual
time=1530.037..1530.037 rows=0 loops=1)
- Index Scan using profile_master_co_name_vec on
profile_master pm (cost=0.00..4335.36 rows=1074 width=4) (actual
time=220.821..1014.501 rows=7 loops=1)
Index Cond: (co_name_vec @@ '''plastic'' ''tube'''::tsquery)
Filter: (co_name_vec @@ '''plastic'' ''tube'''::tsquery)
- Bitmap Heap Scan on trade_leads (cost=3.42..145.75
rows=47 width=8) (actual time=73.640..73.640 rows=0 loops=7)
Recheck Cond: (trade_leads.profile_id = pm.profile_id)
Filter: ((status)::text = 'm'::text)
- Bitmap Index Scan on trade_leads_profile_id
(cost=0.00..3.41 rows=47 width=0) (actual time=73.579..73.579 rows=0
loops=7)
Index Cond: (trade_leads.profile_id = pm.profile_id)
Total runtime: 1530.137 ms
regds
mallah.
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] query becomes fas on 'SET enable_hashjoin TO off;'
Dear Robert,
thanks for ur interest. Our server was too loaded
what i posted my last observation, now the
other explain analyze can also be run and i am posting
both the result , as you can see latter is 55ms versus
3000 ms .
explain analyze select lead_id from general.trade_leads join
general.profile_master as pm using(profile_id) where status ='m' and
co_name_vec @@ to_tsquery('plastictubes') limit 20;
QUERY PLAN
---
Limit (cost=4109.11..11127.78 rows=20 width=4) (actual
time=3076.059..3076.059 rows=0 loops=1)
- Hash Join (cost=4109.11..90789.72 rows=247 width=4) (actual
time=3076.057..3076.057 rows=0 loops=1)
Hash Cond: (trade_leads.profile_id = pm.profile_id)
- Seq Scan on trade_leads (cost=0.00..85752.52 rows=246832
width=8) (actual time=0.020..2972.446 rows=127371 loops=1)
Filter: ((status)::text = 'm'::text)
- Hash (cost=4095.68..4095.68 rows=1074 width=4) (actual
time=42.368..42.368 rows=7 loops=1)
- Bitmap Heap Scan on profile_master pm
(cost=40.89..4095.68 rows=1074 width=4) (actual time=42.287..42.360
rows=7 loops=1)
Filter: (co_name_vec @@ '''plastic'' ''tube'''::tsquery)
- Bitmap Index Scan on
profile_master_co_name_vec (cost=0.00..40.62 rows=1074 width=0)
(actual time=42.252..42.252 rows=7 loops=1)
Index Cond: (co_name_vec @@ '''plastic''
''tube'''::tsquery)
Total runtime: 3076.121 ms
(11 rows)
tradein_clients= SET enable_hashjoin TO off;
SET
tradein_clients= explain analyze select lead_id from
general.trade_leads join general.profile_master as pm
using(profile_id) where status ='m' and co_name_vec @@
to_tsquery('plastictubes') limit 20;
QUERY PLAN
--
Limit (cost=3.42..13080.44 rows=20 width=4) (actual
time=55.233..55.233 rows=0 loops=1)
- Nested Loop (cost=3.42..161504.56 rows=247 width=4) (actual
time=55.232..55.232 rows=0 loops=1)
- Index Scan using profile_master_co_name_vec on
profile_master pm (cost=0.00..4335.36 rows=1074 width=4) (actual
time=16.578..46.175 rows=7 loops=1)
Index Cond: (co_name_vec @@ '''plastic'' ''tube'''::tsquery)
Filter: (co_name_vec @@ '''plastic'' ''tube'''::tsquery)
- Bitmap Heap Scan on trade_leads (cost=3.42..145.75
rows=47 width=8) (actual time=1.287..1.287 rows=0 loops=7)
Recheck Cond: (trade_leads.profile_id = pm.profile_id)
Filter: ((status)::text = 'm'::text)
- Bitmap Index Scan on trade_leads_profile_id
(cost=0.00..3.41 rows=47 width=0) (actual time=1.285..1.285 rows=0
loops=7)
Index Cond: (trade_leads.profile_id = pm.profile_id)
Total runtime: 55.333 ms
(11 rows)
SELECT SUM(1) FROM general.trade_leads WHERE status = 'm';
sum
127371
this constitutes 90% of the total rows.
regds
mallah.
On Tue, Feb 10, 2009 at 6:36 PM, Robert Haas robertmh...@gmail.com wrote:
On Tue, Feb 10, 2009 at 5:31 AM, Rajesh Kumar Mallah
mallah.raj...@gmail.com wrote:
I have a query in which two huge tables (A,B) are joined using an indexed
column and a search is made on tsvector on some column on B. Very limited
rows of B are expected to match the query on tsvector column.
With default planner settings the query takes too long ( 100 secs) , but
with hashjoin off it returns almost immediately. The question is , is
it is advisable to
tweak planner settings for specific queries in application ?
The ones that start with enable_ usually shouldn't be changed.
They're mostly for debugging and finding problems.
The plans are as follows.
It's a little hard to figure out what's gone wrong here because you've
only included EXPLAIN ANALYZE output for one of the plans - the other
is just regular EXPLAIN. Can you send that, along with the output of
the following query:
SELECT SUM(1) FROM trade_leads WHERE status = 'm'
I'm guessing that the problem is that the selectivity estimate for
co_name_vec @@ to_tsquery('plastictubes') is not very good, but I'm
not real familiar with full text search, so I'm not sure whether
there's anything sensible you can do about it.
...Robert
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] ERROR: Can't use an undefined value as an ARRAY reference at /usr/lib/perl5/site_perl/5.8.8/Test/Parser/Dbt2.pm line 521.
Can't use an undefined value as an ARRAY reference at
/usr/lib/perl5/site_perl/5.8.8/Test/Parser/Dbt2.pm line 521.
Can someone please give inputs to resolve this issue? Any help on this will
be appreciated.
519 sub transactions {
520 my $self = shift;
521 return @{$self-{data}-{transactions}-{transaction}};
522 }
the stuff in $self-{data}-{transactions}-{transaction} is not defined
so it cannot be dereferenced. If you want to just escape this error you
may modify the code as:
if ($self-{data}-{transactions}-{transaction}) {
return @{$self-{data}-{transactions}-{transaction}};
} else {
return ();
}
however understanding the root cause is recommended.
Thanks in advance.
Thanks,
Rohan
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] query becomes fas on 'SET enable_hashjoin TO off;'
On Tue, Feb 10, 2009 at 9:09 PM, Tom Lane t...@sss.pgh.pa.us wrote:
Rajesh Kumar Mallah mallah.raj...@gmail.com writes:
On Tue, Feb 10, 2009 at 6:36 PM, Robert Haas robertmh...@gmail.com wrote:
I'm guessing that the problem is that the selectivity estimate for
co_name_vec @@ to_tsquery('plastictubes') is not very good, but I'm
not real familiar with full text search, so I'm not sure whether
there's anything sensible you can do about it.
Yeah, the bad selectivity estimate seems to be the entire problem ---
if that were even slightly closer to reality the planner would've
preferred the nestloop.
I don't think there's a good solution to this in 8.3,
this is 8.2 server at the moment.
because its
estimator for @@ is just a stub. There will be a non-toy estimator
in 8.4, fwiw.
A possibility that seems a bit less crude than turning off hashjoins
is to reduce random_page_cost, so as to bias things toward nestloop
indexscans in general.
reducing random_page_cost from 4 (default) to 3 does switch the plan
in favour of nested loop thanks for the suggestion.
SET random_page_cost TO 4;
SET
tradein_clients= explain select lead_id from general.trade_leads
join general.profile_master as pm using(profile_id) where status ='m'
and co_name_vec @@ to_tsquery('plastictubes') limit 20;
QUERY PLAN
---
Limit (cost=4109.11..11127.78 rows=20 width=4)
- Hash Join (cost=4109.11..90789.72 rows=247 width=4)
Hash Cond: (trade_leads.profile_id = pm.profile_id)
- Seq Scan on trade_leads (cost=0.00..85752.52 rows=246832 width=8)
Filter: ((status)::text = 'm'::text)
- Hash (cost=4095.68..4095.68 rows=1074 width=4)
- Bitmap Heap Scan on profile_master pm
(cost=40.89..4095.68 rows=1074 width=4)
Filter: (co_name_vec @@ '''plastic'' ''tube'''::tsquery)
- Bitmap Index Scan on
profile_master_co_name_vec (cost=0.00..40.62 rows=1074 width=0)
Index Cond: (co_name_vec @@ '''plastic''
''tube'''::tsquery)
(10 rows)
tradein_clients= SET random_page_cost TO 3;
SET
tradein_clients= explain select lead_id from general.trade_leads
join general.profile_master as pm using(profile_id) where status ='m'
and co_name_vec @@ to_tsquery('plastictubes') limit 20;
QUERY PLAN
--
Limit (cost=0.00..9944.78 rows=20 width=4)
- Nested Loop (cost=0.00..122818.07 rows=247 width=4)
- Index Scan using profile_master_co_name_vec on
profile_master pm (cost=0.00..3256.28 rows=1074 width=4)
Index Cond: (co_name_vec @@ '''plastic'' ''tube'''::tsquery)
Filter: (co_name_vec @@ '''plastic'' ''tube'''::tsquery)
- Index Scan using trade_leads_profile_id on trade_leads
(cost=0.00..110.76 rows=45 width=8)
Index Cond: (trade_leads.profile_id = pm.profile_id)
Filter: ((status)::text = 'm'::text)
(8 rows)
regards, tom lane
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] suggestions for postgresql setup on Dell 2950 , PERC6i controller
On Thu, Feb 5, 2009 at 6:10 PM, Matt Burke mattbli...@icritical.com wrote: Arjen van der Meijden wrote: Afaik the Perc 5/i and /e are more or less rebranded LSI-cards (they're not identical in layout etc), so it would be a bit weird if they performed much less than the similar LSI's wouldn't you think? I've recently had to replace a PERC4/DC with the exact same card made by LSI (320-2) because the PERCs firmware was crippled. Its idea of RAID10 actually appears to be concatenated RAID1 arrays. Since replacing it and rebuilding the array on the LSI card, performance has been considerably better (14 disk SCSI shelf) Areca may be the fastest around right now, but if you'd like to get it all from one supplier, its not too bad to be stuck with Dell's perc 5 or 6 series. The PERC6 isn't too bad, however it grinds to a halt when the IO queue gets large and it has the serious limitation of not supporting more than 8 spans, so trying to build a RAID10 array greater than 16 disks is pointless if you're not just after the extra capacity. Are there any reasonable choices for bigger (3+ shelf) direct-connected RAID10 arrays, or are hideously expensive SANs the only option? I've checked out the latest Areca controllers, but the manual available on their website states there's a limitation of 32 disks in an array... Where exactly is there limitation of 32 drives. the datasheet of 1680 states support upto 128drives using enclosures. regds rajesh kumar mallah. -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
[PERFORM] suggestions for postgresql setup on Dell 2950 , PERC6i controller
Hi, I am going to get a Dell 2950 with PERC6i with 8 * 73 15K SAS drives + 300 GB EMC SATA SAN STORAGE, I seek suggestions from users sharing their experience with similar hardware if any. I have following specific concerns. 1. On list i read that RAID10 function in PERC5 is not really striping but spanning and does not give performance boost is it still true in case of PERC6i ? 2. I am planning for RAID10 array of 8 drives for entrire database ( including pg_xlog) , the controller has a write back cache (256MB) is it a good idea ? or is it better to have 6 drives in HW RAID1 and RAID0 of 3 mirrors in s/w and leave 2 drives (raid1) for OS ? 3. Is there any preferred Stripe Size for RAID0 for postgresql usage ? 4. Although i would benchmark (with bonnie++) how would the EMC SATA SAN storage compare with locally attached SAS storage for the purpose of hosting the data , i am hiring the storage primarily for storing base base backups and log archives for PITR implementation. as retal of separate machine was higher than SATA SAN. Regds mallah. -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] setting up raid10 with more than 4 drives
Sorry for posting and disappearing. i am still not clear what is the best way of throwing in more disks into the system. does more stripes means more performance (mostly) ? also is there any thumb rule about best stripe size ? (8k,16k,32k...) regds mallah On 5/30/07, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: On Wed, May 30, 2007 at 08:51:45AM -0700, Luke Lonergan wrote: This is standard stuff, very well proven: try googling 'self healing zfs'. The first hit on this search is a demo of ZFS detecting corruption of one of the mirror pair using checksums, very cool: http://www.opensolaris.org/os/community/zfs/demos/selfheal/;jsessionid=52508 D464883F194061E341F58F4E7E1 The bad drive is pointed out directly using the checksum and the data integrity is preserved. One part is corruption. Another is ordering and consistency. ZFS represents both RAID-style storage *and* journal-style file system. I imagine consistency and ordering is handled through journalling. Cheers, mark -- [EMAIL PROTECTED] / [EMAIL PROTECTED] / [EMAIL PROTECTED] __ . . _ ._ . . .__. . ._. .__ . . . .__ | Neighbourhood Coder |\/| |_| |_| |/|_ |\/| | |_ | |/ |_ | | | | | | \ | \ |__ . | | .|. |__ |__ | \ |__ | Ottawa, Ontario, Canada One ring to rule them all, one ring to find them, one ring to bring them all and in the darkness bind them... http://mark.mielke.cc/ ---(end of broadcast)--- TIP 6: explain analyze is your friend ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] setting up raid10 with more than 4 drives
On 5/31/07, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: On Thu, May 31, 2007 at 01:28:58AM +0530, Rajesh Kumar Mallah wrote: i am still not clear what is the best way of throwing in more disks into the system. does more stripes means more performance (mostly) ? also is there any thumb rule about best stripe size ? (8k,16k,32k...) It isn't that simple. RAID1 should theoretically give you the best read performance. If all you care about is read, then best performance would be to add more mirrors to your array. For write performance, RAID0 is the best. I think this is what you mean by more stripes. This is where RAID 1+0/0+1 come in. To reconcile the above. Your question seems to be: I have a RAID 1+0/0+1 system. Should I add disks onto the 0 part of the array? Or the 1 part of the array? My conclusion to you would be: Both, unless you are certain that you load is scaled heavily towards read, in which case the 1, or if scaled heavily towards write, then 0. thanks . this answers to my query. all the time i was thinking of 1+0 only failing to observe the 0+1 part in it. Then comes the other factors. Do you want redundancy? Then you want 1. Do you want capacity? Then you want 0. Ok. There is no single answer for most people. For me, stripe size is the last decision to make, and may be heavily sensitive to load patterns. This suggests a trial and error / benchmarking requirement to determine the optimal stripe size for your use. thanks. mallah. Cheers, mark -- [EMAIL PROTECTED] / [EMAIL PROTECTED] / [EMAIL PROTECTED] __ . . _ ._ . . .__. . ._. .__ . . . .__ | Neighbourhood Coder |\/| |_| |_| |/|_ |\/| | |_ | |/ |_ | | | | | | \ | \ |__ . | | .|. |__ |__ | \ |__ | Ottawa, Ontario, Canada One ring to rule them all, one ring to find them, one ring to bring them all and in the darkness bind them... http://mark.mielke.cc/ ---(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
[PERFORM] setting up raid10 with more than 4 drives
hi, this is not really postgresql specific, but any help is appreciated. i have read more spindles the better it is for IO performance. suppose i have 8 drives , should a stripe (raid0) be created on 2 mirrors (raid1) of 4 drives each OR should a stripe on 4 mirrors of 2 drives each be created ? also does single channel or dual channel controllers makes lot of difference in raid10 performance ? regds mallah. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] setting up raid10 with more than 4 drives
On 5/30/07, Luke Lonergan [EMAIL PROTECTED] wrote: Stripe of mirrors is preferred to mirror of stripes for the best balance of protection and performance. nooo! i am not aksing raid10 vs raid01 . I am considering stripe of mirrors only. the question is how are more number of disks supposed to be BEST utilized in terms of IO performance for 1. for adding more mirrored stripes OR 2. for adding more harddrives to the mirrors. say i had 4 drives in raid10 format D1 raid1 D2 -- MD0 D3 raid1 D4 -- MD1 MD0 raid0 MD1 -- MDF (final) now i get 2 drives D5 and D6 the i got 2 options 1. create a new mirror D5 raid1 D6 -- MD2 MD0 raid0 MD1 raid0 MD2 -- MDF final OR D1 raid1 D2 raid1 D5 -- MD0 D3 raid1 D4 raid1 D6 -- MD1 MD0 raid0 MD1 -- MDF (final) thanks , hope my question is clear now. Regds mallah. In the stripe of mirrors you can lose up to half of the disks and still be operational. In the mirror of stripes, the most you could lose is two drives. The performance of the two should be similar - perhaps the seek performance would be different for high concurrent use in PG. - Luke On 5/29/07 2:14 PM, Rajesh Kumar Mallah [EMAIL PROTECTED] wrote: hi, this is not really postgresql specific, but any help is appreciated. i have read more spindles the better it is for IO performance. suppose i have 8 drives , should a stripe (raid0) be created on 2 mirrors (raid1) of 4 drives each OR should a stripe on 4 mirrors of 2 drives each be created ? also does single channel or dual channel controllers makes lot of difference in raid10 performance ? regds mallah. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match ---(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] Insertion to temp table deteriorating over time
On 12/13/06, Steven Flatt [EMAIL PROTECTED] wrote: Hi, Our application is using Postgres 7.4 and I'd like to understand the root cause of this problem: To speed up overall insert time, our application will write thousands of rows, one by one, into a temp table 1. how frequently are you commiting the transaction ? if you commit less frequetly it will be faster. 2. If you use COPY instead of INSERT it will be faster. using COPY is easy with DBD::Pg (perl). In versions 8.x i think there has been major speed improvements in COPY. I do not know the root cause of slowdown though. Regds mallah. ---(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 to PostgreSQL, performance considerations
[offtopic]; hmm quite a long thread below is stats of posting Total Messages:87Total Participants: 27 - 19 Daniel van Ham Colchete 12 Michael Stone 9 Ron 5 Steinar H. Gunderson 5 Alexander Staubo 4 Tom Lane 4 Greg Smith 3 Luke Lonergan 3 Christopher Browne 2 Merlin Moncure 2 Guido Neitzer 2 Dave Cramer 2 Cosimo Streppone 2 Bucky Jordan 1 Tatsuo Ishii 1 Sven Geisler 1 Shane Ambler 1 Michael Glaesemann 1 Mark Kirkwood 1 Gene 1 Florian Weimer 1 David Boreham 1 Craig A. James 1 Chris Browne 1 Brad Nicholson 1 Bill Moran 1 Alvaro Herrera --- ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] Postgresql - Threshold value.
On 12/11/06, Ravindran G - TLS, Chennai. [EMAIL PROTECTED] wrote: Hello, How to get Postgresql Threshold value ?. Any commands available ?. What is meant my threshold value ? ---(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] Postgresql - Threshold value.
On 12/11/06, Ravindran G - TLS, Chennai. [EMAIL PROTECTED] wrote: Thanks. I am using Postgres 8.1.4 in windows 2000 and i don't get the proper response for threshold. what is the response you get ? please be specific about the issues. also the footer that comes with your emails are not appreciated by many people. if possible pls avoid it. Regds mallah. - ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] really quick multiple inserts can use COPY?
So, my questions:
Is it possible to use COPY FROM STDIN with JDBC?
Should be. Its at least possible using DBI and DBD::Pg (perl)
my $copy_sth = $dbh - prepare( COPY
general.datamining_mailing_lists (query_id,email_key) FROM STDIN;) ;
$copy_sth - execute();
while (my ($email_key ) = $fetch_sth - fetchrow_array ()) {
$dbh - func($query_id\t$email_key\n, 'putline');
}
$fetch_sth - finish();
$dbh - func(\\.\n, 'putline');
$dbh - func('endcopy');
$copy_sth-finish();
Some JDBC expert would tell better how its done with JDBC.
Will it bring performance improvement compared to SELECT UNION solution?
COPY is quite faast.
Regds
mallah.
many thanks in advance,
Jens Schipkowski
--
**
APUS Software GmbH
---(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
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [PERFORM] VACUUM FULL does not works.......
On 12/6/06, asif ali [EMAIL PROTECTED] wrote: Hi, I have a product table having 350 records. It takes approx 1.8 seconds to get all records from this table. I copies this table to a product_temp table and run the same query to select all records; and it took 10ms(much faster). I did VACUUM FULL on product table but It did not work. I checked the file size of these two tables. product table's file size is 32mb and product_temp table's file size is 72k. So, it seems that VACUUM FULL is not doing anything. Please suggest. It is desirable that PostgreSQL version be reported in problem descriptions. Older versions of pgsql had problem of index bloat. It is interesting to find out why VACUUM FULL does not work in your case(wait for the experts) , but most probably CLUSTERING the table on primary key is going to solve the query performance problem (temporarily) asif ali icrossing inc. Have a burning question? Go to Yahoo! Answers and get answers from real people who know. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] Restart time
On 12/5/06, Tom Lane [EMAIL PROTECTED] wrote: Jean Arnaud [EMAIL PROTECTED] writes: Is there a relation between database size and PostGreSQL restart duration ? No. Does anyone now the behavior of restart time ? It depends on how many updates were applied since the last checkpoint before the crash. If you're talking about startup of a cleanly-shut-down database, it should be pretty much constant time. Dear Sir, Startup time of a clean shutdown database is constant. But we still face problem when it comes to shutting down. PostgreSQL waits for clients to finish gracefully. till date i have never been able to shutdown quickly (web application scenerio) and i tend to do pg_ctl -m immediate stop mostly. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] Restart time
On 12/6/06, Tom Lane [EMAIL PROTECTED] wrote: Rajesh Kumar Mallah [EMAIL PROTECTED] writes: Startup time of a clean shutdown database is constant. But we still face problem when it comes to shutting down. PostgreSQL waits for clients to finish gracefully. till date i have never been able to shutdown quickly (web application scenerio) and i tend to do pg_ctl -m immediate stop mostly. RTFM ... you should be using -m fast not -m immediate. -m immediate is for emergency situations not routine. Thanks for correcting , -m fast works fine for me. I shall RTFM. :) Regds mallah.regards, tom lane
Re: [PERFORM] Stored Procedure Performance
On 4/11/06, Simon Dale [EMAIL PROTECTED] wrote: Hi, I'm trying to evaluate PostgreSQL as a database that will have to store a high volume of data and access that data frequently. One of the features on our wish list is to be able to use stored procedures to access the data and I was wondering if it is usual for stored procedures to perform slower on PostgreSQL than raw SQL? No. RETURN NEXT keeps accumulating the data before returning. I am not sure if any optimisations have been done to that effect. In general functions are *NOT* slower than RAW SQL. Regds mallah. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] Restore performance?
On 4/10/06, Jesper Krogh [EMAIL PROTECTED] wrote: HiI'm currently upgrading a Posgresql 7.3.2 database to a8.1.something-goodI'd run pg_dump | gzip sqldump.gzon the old system. That took about30 hours and gave me an 90GB zipped file. Running cat sqldump.gz | gunzip | psqlinto the 8.1 database seems to take about the same time. Are thereany tricks I can use to speed this dump+restore process up?was the last restore successfull ? if so why do you want to repeat ?some tips1. run new version of postgres in a different port and pipe pg_dump to psqlthis may save the CPU time of compression , there is no need for a temporary dump file.pg_dump | /path/to/psql813 -p 54XX newdb2. use new version of pg_dump to dump the old database as new version is supposed to be wiser.3. make sure you are trapping the restore errors properly psql newdb 21 | cat | tee err works for me.The database contains quite alot of BLOB, thus the size. Jesper--./Jesper Krogh, [EMAIL PROTECTED], Jabber ID: [EMAIL PROTECTED]---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] Restore performance?
sorry for the post , i didn' saw the other replies only after posting.On 4/10/06, Rajesh Kumar Mallah [EMAIL PROTECTED] wrote: On 4/10/06, Jesper Krogh [EMAIL PROTECTED] wrote: HiI'm currently upgrading a Posgresql 7.3.2 database to a8.1.something-goodI'd run pg_dump | gzip sqldump.gzon the old system. That took about30 hours and gave me an 90GB zipped file. Running cat sqldump.gz | gunzip | psqlinto the 8.1 database seems to take about the same time. Are thereany tricks I can use to speed this dump+restore process up? was the last restore successfull ? if so why do you want to repeat ?some tips1. run new version of postgres in a different port and pipe pg_dump to psqlthis may save the CPU time of compression , there is no need for a temporary dump file.pg_dump | /path/to/psql813 -p 54XX newdb2. use new version of pg_dump to dump the old database as new version is supposed to be wiser.3. make sure you are trapping the restore errors properly psql newdb 21 | cat | tee err works for me. The database contains quite alot of BLOB, thus the size. Jesper--./Jesper Krogh, [EMAIL PROTECTED], Jabber ID: [EMAIL PROTECTED]---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] Takes too long to fetch the data from database
what is the query ?use LIMIT or a restricting where clause.regdsmallah.On 4/10/06, soni de [EMAIL PROTECTED] wrote:Hello, I have difficulty in fetching the records from the database. Database table contains more than 1 GB data. For fetching the records it is taking more the 1 hour and that's why it is slowing down the performance. please provide some help regarding improving the performance and how do I run query so that records will be fetched in a less time.
Re: [PERFORM] Restore performance?
4. fsync can also be turned off while loading huge dataset , but seek others comments too (as study docs) as i am not sure about the reliability. i think it can make a lot of difference. On 4/10/06, Jesper Krogh [EMAIL PROTECTED] wrote: Rajesh Kumar Mallah wrote: I'd run pg_dump | gzip sqldump.gzon the old system. That took about 30 hours and gave me an 90GB zipped file. Running cat sqldump.gz | gunzip | psql into the 8.1 database seems to take about the same time. Are there any tricks I can use to speed this dump+restore process up? was the last restore successfull ? if so why do you want to repeat ? about the same time == Estimated guess from restoring a few tablesI was running a testrun, without disabling updates to the productiondatabase, the real run is scheduled for easter where there hopefully is no users on the system. So I need to repeat, I'm just trying to get afeelingabout how long time I need to allocate for the operation. 1. run new version of postgres in a different port and pipe pg_dump to psql this may save the CPU time of compression , there is no need for a temporary dump file. pg_dump | /path/to/psql813-p 54XX newdbI'll do that. It is a completely different machine anyway. 2. use new version of pg_dump to dump the old database as new version is supposed to be wiser.Check. 3. make sure you are trapping the restore errors properly psql newdb 21 | cat | tee err works for me. Thats noted.--Jesper Krogh, [EMAIL PROTECTED]
Re: [PERFORM] pls reply ASAP
On 4/9/06, Chethana, Rao (IE10) [EMAIL PROTECTED] wrote: Hello! Kindly go through the following , I wanted to know whether, the command line arguments(function arguments) --$1 $2 $3 --can be usedas in the following , like, CREATE TYPE TT AS(something,something,……etc……); CREATE OR REPLACE FUNCTION f1(varchar,varchar,varchar,varchar) RETURNS ………..(something). the overall idea expressed is doable. following are comments 1. you have to put RETURNS setof TT (if you plan to return TT) since you used RETURN NEXT 2. you have to use SELECT INTO rec in the function where rec is rowtype TT hope it helps --- non technical comments -- 3. its not a performance question , it shud have been marked more appropriately to pgsql-sql i think. 4. its not a good etiquette to address email to someone and mark Cc to a list. kind regds mallah. BEGIN SELECT a1,a2,a3,a4,a5,a6 FROM (SELECT * FROM T1, T2……WHERE etc… Flag = 0 $1 $2 $3 $4) ORDER BY ……. RETURN NEXT ………; END LOOP; RETURN; END; ' LANGUAGE 'plpgsql'; NOTE :The values for$1 $2 $3 $4will be passed when the function is invoked(called)from the command prompt. Itried implementing the above, butthis type of usage is not supported , how should use it? I am convertingfrom (sprintf, "SELECT query stmts (which uses %s %s %s %s…… )to functions.Any help will be deeply appreciated.Thank you.Kind regards, Chethana.
Re: [PERFORM] optimizing db for small table with tons of updates
Dear Kenji, we had similar issuse with a banner impression update system, that had high concurrency. we modfied the system to use insert instead of update of the same row. performance wise things are much better , but you have to keep deleting old data. hope you extrapolate what i mean if its applicable to your case. Regds Rajesh Kumar Mallah On 4/3/06, Kenji Morishige [EMAIL PROTECTED] wrote: I am using postgresql to be the central database for a variety of tools for our testing infrastructure. We have web tools and CLI tools that require access to machine configuration and other states for automation. We have one tool that uses a table that looks like this: systest_live=# \d cuty Table public.cuty Column| Type | Modifiers -+--+--- resource_id | integer | not null lock_start | timestamp with time zone | lock_by | character varying(12)| frozen | timestamp with time zone | freeze_end | timestamp with time zone | freeze_by | character varying(12)| state | character varying(15)| Indexes: cuty_pkey PRIMARY KEY, btree (resource_id) cuty_main_idx btree (resource_id, lock_start) Foreign-key constraints: cuty_resource_id_fkey FOREIGN KEY (resource_id) REFERENCES resource(resource_id) ON UPDATE CASCADE ON DELETE CASCADE Various users run a tool that updates this table to determine if the particular resource is available or not. Within a course of a few days, this table can be updated up to 200,000 times. There are only about 3500 records in this table, but the update and select queries against this table start to slow down considerablly after a few days. Ideally, this table doesn't even need to be stored and written to the filesystem. After I run a vacuum against this table, the overall database performance seems to rise again. When database is running with recent vacuum the average server load is about .40, but after this table is updated 200,000+ times, the server load can go up to 5.0. here is a typical update query: 2006-04-03 10:53:39 PDT testtool systest_live kyoto.englab.juniper.net(4888) LOG: duration: 2263.741 ms statement: UPDATE cuty SET lock_start = NOW(), lock_by = 'tlim' WHERE resource_id='2262' and (lock_start IS NULL OR lock_start (NOW() - interval '3600 second')) We used to use MySQL for these tools and we never had any issues, but I believe it is due to the transactional nature of Postgres that is adding an overhead to this problem. Are there any table options that enables the table contents to be maintained in ram only or have delayed writes for this particular table? Thanks in advance, Kenji ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] PostgreSQL and Kernel 2.6.x
V i s h a l Kashyap @ [Sai Hertz And Control Systems] wrote: Dear all, Have anyone compiled PostgreSQL with kernel 2.6.x if YES 1. Was their any performance gains Else 1. Is it possible 2. What problems would keeping us away from compiling on kernel 2.6 We run pgsql on 2.6.6 there was upto 30% improvement in performance for certain queries. None, everything works just fine. Regds Mallah. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [PERFORM] seeking consultant for high performance, complex searching
Have you checked Tsearch2 http://www.sai.msu.su/~megera/postgres/gist/tsearch/V2/ is the most feature rich Full text Search system available for postgresql. We are also using the same system in the revamped version of our website. Regds Mallah. Mark Stosberg wrote: Hello, I work for Summersault, LLC. We've been using Postgres since the days of Postgres 6.5. We're focused on building database-driven websites using Perl and Postgres. We are currently seeking help developing a search system that needs to perform complex queries with high performance. Although we have strong skills in Perl and Postgres, we are new to the arena of complex, high-performance search systems. We are seeking to hire a consultant to help this as part of the re-vamp of the 1-800-Save-A-Pet.com website. 1-800-Save-A-Pet.com is a not-for-profit organization whose website finds homes for homeless pets, promoting pet adoption and saving thousands of animal lives. Summersault, LLC is a website development firm focused on creating highly customized database driven websites. The ideal consultant has expert experience with the PostgreSQL RDBMS and the Perl programming language, and is intimately familiar with the architecture and implementation of complex database queries for high-traffic web applications. The consultant should also have a strong background in creating solutions complementary to this work, e.g. assessing hardware requirements, designing a hosting and network infrastructure, and optimizing the algorithm based on real-world feedback. The consultant will work with Summersault developers as a part of a larger application development process. Interested persons or organizations should contact Chris Hardie of Summersault, LLC at [EMAIL PROTECTED] for more information. Thanks! Mark ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PERFORM] [ SOLVED ] select count(*) very slow on an already
I am running an update on the same table update rfis set inquiry_status='APPROVED' where inquiry_status='a'; Its running for past 20 mins. and top output is below. The PID which is executing the query above is 6712. Can anyone tell me why it is in an uninterruptable sleep and does it relate to the apparent poor performance? Is it problem with the disk hardware. I know at nite this query will run reasonably fast. I am running on a decent hardware . Regds mallah. 1:41pm up 348 days, 21:10, 1 user, load average: 11.59, 13.69, 11.49 85 processes: 83 sleeping, 1 running, 0 zombie, 1 stopped CPU0 states: 8.1% user, 2.3% system, 0.0% nice, 89.0% idle CPU1 states: 3.3% user, 2.3% system, 0.0% nice, 93.2% idle CPU2 states: 7.4% user, 1.4% system, 0.0% nice, 90.0% idle CPU3 states: 9.3% user, 7.4% system, 0.0% nice, 82.2% idle Mem: 2064796K av, 2053964K used, 10832K free, 0K shrd, 22288K buff Swap: 2048244K av, 88660K used, 1959584K free 1801532K cached PID USER PRI NI SIZE RSS SHARE STAT %CPU %MEM TIME COMMAND 6712 postgres 16 0 86592 84M 83920 D 11.1 4.1 1:36 postmaster 13103 postgres 15 0 54584 53M 52556 S 3.5 2.6 0:01 postmaster 13034 root 16 0 1072 1072 848 R 2.1 0.0 0:02 top 13064 postgres 15 0 67256 65M 64516 D 2.1 3.2 0:01 postmaster 13088 postgres 16 0 43324 42M 40812 D 2.1 2.0 0:00 postmaster 13076 postgres 15 0 49016 47M 46628 S 1.9 2.3 0:00 postmaster 26931 postgres 15 0 84880 82M 83888 S 1.7 4.1 3:52 postmaster 13107 postgres 15 0 18400 17M 16488 S 1.5 0.8 0:00 postmaster 13068 postgres 15 0 44632 43M 42324 D 1.3 2.1 0:00 postmaster 13074 postgres 15 0 68852 67M 66508 D 1.3 3.3 0:00 postmaster 13108 postgres 15 0 11692 11M 10496 S 1.3 0.5 0:00 postmaster 13075 postgres 15 0 50860 49M 47680 S 1.1 2.4 0:04 postmaster 13066 postgres 15 0 56112 54M 53724 S 0.9 2.7 0:01 postmaster 13109 postgres 15 0 14528 14M 13272 S 0.9 0.7 0:00 postmaster 24454 postgres 15 0 2532 2380 1372 S 0.7 0.1 11:58 postmaster 12 root 15 0 0 0 0 SW 0.5 0.0 816:30 bdflush 24455 postgres 15 0 1600 1476 1380 S 0.5 0.0 9:11 postmaster 12528 postgres 15 0 84676 82M 79920 S 0.3 4.0 0:02 postmaster 12575 postgres 15 0 76660 74M 75796 D 0.3 3.7 0:09 postmaster 13038 postgres 15 0 48952 47M 46436 D 0.3 2.3 0:00 postmaster 13069 postgres 15 0 57464 56M 54852 S 0.3 2.7 0:00 postmaster 13102 postgres 15 0 17864 17M 16504 D 0.3 0.8 0:00 postmaster Richard Huxton wrote: On Thursday 15 April 2004 17:19, Rajesh Kumar Mallah wrote: Bill Moran wrote: Rajesh Kumar Mallah wrote: Hi, The problem was solved by reloading the Table. the query now takes only 3 seconds. But that is not a solution. If dropping/recreating the table improves things, then we can reasonably assume that the table is pretty active with updates/inserts. Correct? Yes the table results from an import process and under goes lots of inserts and updates , but thats before the vacuum full operation. the table is not accessed during vacuum. What i want to know is is there any wat to automate the dumping and reload of a table individually. will the below be safe and effective: Shouldn't be necessary assuming you vacuum (not full) regularly. However, looking back at your original posting, the vacuum output doesn't seem to show any rows that need removing. # VACUUM full verbose eyp_rfi; INFO: vacuuming "public.eyp_rfi" INFO: "eyp_rfi": found 0 removable, 505960 nonremovable row versions in 71987 pages DETAIL: 0 dead row versions cannot be removed yet. Since your select count(*) showed 505960 rows, I can't see how dropping/replacing could make a difference on a sequential scan. Since we're not using any indexes I don't see how it could be related to that. begin work; create table new_tab AS select * from tab; truncate table tab; insert into tab select * from new_tab; drop table new_tab; commit; analyze tab; i havenot tried it but plan to do so. but i feel insert would take ages to update the indexes if any. It will have to update them, which will take time. BTW is there any way to disable checks and triggers on a table temporarily while loading data (is updating reltriggers in pg_class safe?) You can take a look at pg_restore and copy how it does it.
Re: [PERFORM] select count(*) very slow on an already vacuumed table.
Richard Huxton wrote: On Thursday 15 April 2004 08:10, Rajesh Kumar Mallah wrote: The problem is that i want to know if i need a Hardware upgrade at the moment. Eg i have another table rfis which contains ~ .6 million records. SELECT count(*) from rfis where sender_uid 0; Time: 117560.635 ms Which is approximate 4804 records per second. Is it an acceptable performance on the hardware below: RAM: 2 GB DISKS: ultra160 , 10 K , 18 GB Processor: 2* 2.0 Ghz Xeon Hmm - doesn't seem good, does it? If you run it again, is it much faster (since the data should be cached then)? What does vmstat 10 show while you're running the query? One thing you should have done is read the performance tuning guide at: http://www.varlena.com/varlena/GeneralBits/Tidbits/index.php The default values are very conservative, and you will need to change them. Hi, Thanks for the interest . my config are not the default ones. i was running iostat while running the query. Looks like one of the disks doesnt' go past a read performance of 20 ,000 KBytes/sec while the other disk it goes as high as 40,000 . What i am ding currently is loading the table in both the disks and compare the table scan speeds. The performance is definitely better in the newly loaded table in the other disk . the load in server is 13 because i am simultaneously re-loading the data in other table. rt2=# SELECT count(*) from rfis where sender_uid 0; ++ | count | ++ | 564870 | ++ (1 row) Time: 10288.359 ms rt2=# shall post the comparitive details under normal load soon regds mallah. What kind of upgrades shoud be put on the server for it to become reasonable fast. If you've only got one disk, then a second disk for OS/logging. Difficult to say more without knowing numbers of users/activity etc. ---(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] Deleting certain duplicates
Shea,Dan [CIS] wrote: The index is Indexes: forecastelement_rwv_idx btree (region_id, wx_element, valid_time) -Original Message- From: Shea,Dan [CIS] [mailto:[EMAIL PROTECTED] Sent: Monday, April 12, 2004 10:39 AM To: Postgres Performance Subject: [PERFORM] Deleting certain duplicates We have a large database which recently increased dramatically due to a change in our insert program allowing all entries. PWFPM_DEV=# select relname,relfilenode,reltuples from pg_class where relname = 'forecastelement'; relname | relfilenode | reltuples -+-+- forecastelement | 361747866 | 4.70567e+08 Column |Type | Modifiers +-+--- version| character varying(99) | origin | character varying(10) | timezone | character varying(99) | region_id | character varying(20) | wx_element | character varying(99) | value | character varying(99) | flag | character(3)| units | character varying(99) | valid_time | timestamp without time zone | issue_time | timestamp without time zone | next_forecast | timestamp without time zone | reception_time | timestamp without time zone | The program is supposed to check to ensure that all fields but the reception_time are unique using a select statement, and if so, insert it. Due an error in a change, reception time was included in the select to check for duplicates. The reception_time is created by a program creating the dat file to insert. Essentially letting all duplicate files to be inserted. I tried the delete query below. PWFPM_DEV=# delete from forecastelement where oid not in (select min(oid) from forecastelement group by version,origin,timezone,region_id,wx_element,value,flag,units,valid_time,iss ue_time,next_forecast); It ran for 3 days creating what I assume is an index in pgsql_tmp of the group by statement. The query ended up failing with dateERROR:write failed. Well the long weekend is over and we do not have the luxury of trying this again. So I was thinking maybe of doing the deletion in chunks, perhaps based on reception time. its more of an sql question though. to deduplicate on basis of version,origin,timezone,region_id,wx_element,value,flag,units,valid_time, issue_time,next_forecast You could do this. begin work; create temp_table as select distinct on (version,origin,timezone,region_id,wx_element,value,flag,units,valid_time, issue_time,next_forecast) * from forecastelement ; truncate table forecastelement ; drop index index on forecastelement ; insert into forecastelement select * from temp_table ; commit; create indexes Analyze forecastelement ; note that distinct on will keep only one row out of all rows having distinct values of the specified columns. kindly go thru the distinct on manual before trying the queries. regds mallah. Are there any suggestions for a better way to do this, or using multiple queries to delete selectively a week at a time based on the reception_time. I would say there are a lot of duplicate entries between mid march to the first week of April. ---(end of broadcast)--- TIP 8: explain analyze is your friend ---(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 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[PERFORM] Effect of too many columns
Greetings, Is there any performance penalty of having too many columns in a table in terms of read and write speeds. To order to keep operational queries simple (avoid joins) we plan to add columns in the main customer dimension table. Adding more columns also means increase in concurrency in the table as more and more applications will access the same table. Any ideas if its better to split the table application wise or is it ok? Regds mallah. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [PERFORM] COUNT Pagination
scott.marlowe wrote: On Tue, 13 Jan 2004, David Shadovitz wrote: We avert the subsequent execution of count(*) by passing the value of count(*) as a query parameter through the link in page numbers. Mallah, and others who mentioned caching the record count: Yes, I will certainly do this. I can detect whether the query's filter has been changed, or whether the user is merely paging through the results or sorting* the results. I'd love to completely eliminate the cost of the COUNT(*) query, but I guess that I cannot have everything. * My HTML table column headers are hyperlinks which re-execute the query, sorting the results by the selected column. The first click does an ASC sort; a second click does a DESC sort. another useful trick is to have your script save out the count(*) result in a single row table with a timestamp, and every time you grab if, check to see if x number of minutes have passed, and if so, update that row with a count(*). Greetings! The count(*) can get evaluated with any arbitrary combination in whre clause how do you plan to store that information ? In a typical application pagination could be required in n number of contexts . I would be interested to know more about this trick and its applicability in such situations. Offtopic: Does PostgreSQL optimise repeated execution of similar queries ie queries on same table or set of tables (in a join) with same where clause and only differing in LIMIT and OFFSET. I dont know much about MySQL, Is their "Query Cache" achieving better results in such cases? and do we have anything similar in PostgreSQL ? I think the most recently accessed tables anyways get loaded in shared buffers in PostgreSQL so that its not accessed from the disk. But is the "Query Cache" really different from this. Can anyone knowing a little better about the working of MySQLs' query cache throw some light? Regds Mallah. You can even have a cron job do it so your own scripts don't incur the cost of the count(*) and delay output to the user. ---(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
[PERFORM] VACUUM problems with 7.4
I am sure there is no transaction open with the table banner_stats2. Still VACUUM FULL does not seems to effective in removing the dead rows. Can any one please help? Regds mallah tradein_clients=# VACUUM FULL verbose banner_stats2 ; INFO: vacuuming public.banner_stats2 INFO: banner_stats2: found 0 removable, 741912 nonremovable row versions in 6710 pages DETAIL: 737900 dead row versions cannot be removed yet. Nonremovable row versions range from 61 to 72 bytes long. There were 120 unused item pointers. Total free space (including removable row versions) is 246672 bytes. 0 pages are or will become empty, including 0 at the end of the table. 557 pages containing 61344 free bytes are potential move destinations. CPU 0.15s/1.23u sec elapsed 1.38 sec. INFO: index banner_stats_pkey now contains 741912 row versions in 2173 pages DETAIL: 0 index row versions were removed. 0 index pages have been deleted, 0 are currently reusable. CPU 0.03s/0.05u sec elapsed 0.09 sec. INFO: banner_stats2: moved 0 row versions, truncated 6710 to 6710 pages DETAIL: CPU 0.00s/0.00u sec elapsed 0.00 sec. VACUUM tradein_clients=# ---(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
[PERFORM] problem with select count(*) ..
Ever Since i upgraded to 7.4RC2 i am facing problem with select count(*) . In 7.3 the problem was not there select count(*) from data_bank.profiles used to return almost instantly , but in 7.4 explain analyze SELECT count(*) from data_bank.profiles; QUERY PLAN --- Aggregate (cost=48361.30..48361.30 rows=1 width=0) (actual time=23456.870..23456.871 rows=1 loops=1) - Seq Scan on profiles (cost=0.00..47431.84 rows=371784 width=0) (actual time=12174.999..23262.823 rows=123928 loops=1) Total runtime: 23458.460 ms (3 rows) tradein_clients=# If i dump and reload the performance improves and it takes 1 sec. This is what i have been doing since the upgrade. But its not a solution. The Vacuum full is at the end of a loading batch SQL file which makes lot of insert , deletes and updates. Regds Mallah. VACUUM FULL VERBOSE ANALYZE data_bank.profiles; INFO: vacuuming data_bank.profiles INFO: profiles: found 430524 removable, 371784 nonremovable row versions in 43714 pages INFO: index profiles_pincode now contains 371784 row versions in 3419 pages INFO: index profiles_city now contains 371784 row versions in 3471 pages INFO: index profiles_branch now contains 371784 row versions in 2237 pages INFO: index profiles_area_code now contains 371784 row versions in 2611 pages INFO: index profiles_source now contains 371784 row versions in 3165 pages INFO: index co_name_index_idx now contains 371325 row versions in 3933 pages INFO: index address_index_idx now contains 371490 row versions in 4883 pages INFO: index profiles_exp_cat now contains 154836 row versions in 2181 pages INFO: index profiles_imp_cat now contains 73678 row versions in 1043 pages INFO: index profiles_manu_cat now contains 87124 row versions in 1201 pages INFO: index profiles_serv_cat now contains 19340 row versions in 269 pages INFO: index profiles_pid now contains 371784 row versions in 817 pages INFO: index profiles_pending_branch_id now contains 0 row versions in 1 pages INFO: profiles: moved 0 row versions, truncated 43714 to 43714 pages INFO: vacuuming pg_toast.pg_toast_67748379 INFO: pg_toast_67748379: found 0 removable, 74 nonremovable row versions in 17 pages INFO: index pg_toast_67748379_index now contains 74 row versions in 2 pages INFO: pg_toast_67748379: moved 1 row versions, truncated 17 to 17 pages INFO: index pg_toast_67748379_index now contains 74 row versions in 2 pages INFO: analyzing data_bank.profiles INFO: profiles: 43714 pages, 3000 rows sampled, 3634 estimated total rows VACUUM Time: 1001525.19 ms ---(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
[PERFORM] Help with count(*)
Hi , my database seems to be taking too long for a select count(*) i think there are lot of dead rows. I do a vacuum full it improves bu again the performance drops in a short while , can anyone please tell me if anything worng with my fsm settings current fsm=55099264 (not sure how i calculated it) Regds Mallah tradein_clients=# SELECT count(*) from data_bank.profiles ; ++ | count | ++ | 123065 | ++ (1 row) Time: 49756.969 ms tradein_clients=# tradein_clients=# tradein_clients=# VACUUM full verbose analyze data_bank.profiles ; INFO: vacuuming data_bank.profiles INFO: profiles: found 0 removable, 369195 nonremovable row versions in 43423 pages DETAIL: 246130 dead row versions cannot be removed yet. Nonremovable row versions range from 136 to 2036 bytes long. There were 427579 unused item pointers. Total free space (including removable row versions) is 178536020 bytes. 15934 pages are or will become empty, including 0 at the end of the table. 38112 pages containing 178196624 free bytes are potential move destinations. CPU 1.51s/0.63u sec elapsed 23.52 sec. INFO: index profiles_pincode now contains 369195 row versions in 3353 pages DETAIL: 0 index row versions were removed. 379 index pages have been deleted, 379 are currently reusable. CPU 0.20s/0.24u sec elapsed 22.73 sec. INFO: index profiles_city now contains 369195 row versions in 3411 pages DETAIL: 0 index row versions were removed. 1030 index pages have been deleted, 1030 are currently reusable. CPU 0.17s/0.21u sec elapsed 20.67 sec. INFO: index profiles_branch now contains 369195 row versions in 2209 pages DETAIL: 0 index row versions were removed. 783 index pages have been deleted, 783 are currently reusable. CPU 0.07s/0.14u sec elapsed 6.38 sec. INFO: index profiles_area_code now contains 369195 row versions in 2606 pages DETAIL: 0 index row versions were removed. 856 index pages have been deleted, 856 are currently reusable. CPU 0.11s/0.17u sec elapsed 19.62 sec. INFO: index profiles_source now contains 369195 row versions in 3137 pages DETAIL: 0 index row versions were removed. 1199 index pages have been deleted, 1199 are currently reusable. CPU 0.14s/0.12u sec elapsed 9.95 sec. INFO: index co_name_index_idx now contains 368742 row versions in 3945 pages DETAIL: 0 index row versions were removed. 0 index pages have been deleted, 0 are currently reusable. CPU 0.19s/0.69u sec elapsed 11.56 sec. INFO: index address_index_idx now contains 368898 row versions in 4828 pages DETAIL: 0 index row versions were removed. 0 index pages have been deleted, 0 are currently reusable. CPU 0.16s/0.61u sec elapsed 9.17 sec. INFO: index profiles_exp_cat now contains 153954 row versions in 2168 pages DETAIL: 0 index row versions were removed. 0 index pages have been deleted, 0 are currently reusable. CPU 0.07s/0.25u sec elapsed 3.14 sec. INFO: index profiles_imp_cat now contains 73476 row versions in 1030 pages DETAIL: 0 index row versions were removed. 0 index pages have been deleted, 0 are currently reusable. CPU 0.05s/0.11u sec elapsed 8.73 sec. INFO: index profiles_manu_cat now contains 86534 row versions in 1193 pages DETAIL: 0 index row versions were removed. 0 index pages have been deleted, 0 are currently reusable. CPU 0.03s/0.13u sec elapsed 1.44 sec. INFO: index profiles_serv_cat now contains 19256 row versions in 267 pages DETAIL: 0 index row versions were removed. 0 index pages have been deleted, 0 are currently reusable. CPU 0.01s/0.01u sec elapsed 0.25 sec. INFO: index profiles_pid now contains 369195 row versions in 812 pages DETAIL: 0 index row versions were removed. 0 index pages have been deleted, 0 are currently reusable. CPU 0.03s/0.12u sec elapsed 0.41 sec. INFO: index profiles_pending_branch_id now contains 0 row versions in 1 pages DETAIL: 0 index row versions were removed. 0 index pages have been deleted, 0 are currently reusable. CPU 0.00s/0.00u sec elapsed 0.00 sec. INFO: profiles: moved 0 row versions, truncated 43423 to 43423 pages DETAIL: CPU 1.76s/3.01u sec elapsed 60.39 sec. INFO: vacuuming pg_toast.pg_toast_39873340 INFO: pg_toast_39873340: found 0 removable, 65 nonremovable row versions in 15 pages DETAIL: 0 dead row versions cannot be removed yet. Nonremovable row versions range from 47 to 2034 bytes long. There were 0 unused item pointers. Total free space (including removable row versions) is 17672 bytes. 0 pages are or will become empty, including 0 at the end of the table. 14 pages containing 17636 free bytes are potential move destinations. CPU 0.00s/0.00u sec elapsed 0.77 sec. INFO: index pg_toast_39873340_index now contains 65 row versions in 2 pages DETAIL: 0 index pages have been deleted, 0 are currently reusable. CPU 0.00s/0.00u sec elapsed 0.46 sec. INFO: pg_toast_39873340: moved 0 row versions, truncated 15 to 15 pages DETAIL: CPU 0.00s/0.00u sec elapsed 0.00 sec. INFO: analyzing data_bank.profiles INFO: profiles: 43423 pages, 123065 rows sampled,
[PERFORM] IN surpasses NOT EXISTS in 7.4RC2 ??
Hi, NOT EXISTS is taking almost double time than NOT IN . I know IN has been optimised in 7.4 but is anything wrong with the NOT EXISTS? I have vaccumed , analyze and run the query many times still not in is faster than exists : Regds Mallah. NOT IN PLAN tradein_clients=# explain analyze SELECT count(*) from general.profile_master where profile_id not in (select profile_id from general.account_profiles ) ; QUERY PLAN - Aggregate (cost=32238.19..32238.19 rows=1 width=0) (actual time=5329.206..5329.207 rows=1 loops=1) - Seq Scan on profile_master (cost=4458.25..31340.38 rows=359125 width=0) (actual time=1055.496..4637.908 rows=470386 loops=1) Filter: (NOT (hashed subplan)) SubPlan - Seq Scan on account_profiles (cost=0.00..3817.80 rows=256180 width=4) (actual time=0.061..507.811 rows=256180 loops=1) Total runtime: 5337.591 ms (6 rows) tradein_clients=# explain analyze SELECT count(*) from general.profile_master where not exists (select profile_id from general.account_profiles where profile_id=general.profile_master.profile_id ) ; QUERY PLAN --- Aggregate (cost=1674981.97..1674981.97 rows=1 width=0) (actual time=14600.386..14600.387 rows=1 loops=1) - Seq Scan on profile_master (cost=0.00..1674084.16 rows=359125 width=0) (actual time=13.687..13815.798 rows=470386 loops=1) Filter: (NOT (subplan)) SubPlan - Index Scan using account_profiles_profile_id on account_profiles (cost=0.00..4.59 rows=2 width=4) (actual time=0.013..0.013 rows=0 loops=718250) Index Cond: (profile_id = $0) Total runtime: 14600.531 ms ---(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] IN surpasses NOT EXISTS in 7.4RC2 ??
Robert Treat wrote: It is believed that the IN optimization can lead to faster IN times than EXIST times on some queries, the extent of which is still a bit of an unknown. (Incidentally is there an FAQ item on this that needs updating?) Thanks every one for clarifying. Its really a nice thing to see IN working so well becoz its easier to read the SQL using IN. looks like NOT IN is indifferent to indexes where is IN uses indexes , is it true? does indexes affect the new manner in which IN works in 7.4 ? Does the not exist query produce worse results in 7.4 than it did in 7.3? Will surely post the overvation sometime. Regards Mallah. Robert Treat On Thu, 2003-11-13 at 02:53, Rajesh Kumar Mallah wrote: Hi, NOT EXISTS is taking almost double time than NOT IN . I know IN has been optimised in 7.4 but is anything wrong with the NOT EXISTS? I have vaccumed , analyze and run the query many times still not in is faster than exists : Regds Mallah. NOT IN PLAN tradein_clients=# explain analyze SELECT count(*) from general.profile_master where profile_id not in (select profile_id from general.account_profiles ) ; QUERY PLAN - Aggregate (cost=32238.19..32238.19 rows=1 width=0) (actual time=5329.206..5329.207 rows=1 loops=1) - Seq Scan on profile_master (cost=4458.25..31340.38 rows=359125 width=0) (actual time=1055.496..4637.908 rows=470386 loops=1) Filter: (NOT (hashed subplan)) SubPlan - Seq Scan on account_profiles (cost=0.00..3817.80 rows=256180 width=4) (actual time=0.061..507.811 rows=256180 loops=1) Total runtime: 5337.591 ms (6 rows) tradein_clients=# explain analyze SELECT count(*) from general.profile_master where not exists (select profile_id from general.account_profiles where profile_id=general.profile_master.profile_id ) ; QUERY PLAN --- Aggregate (cost=1674981.97..1674981.97 rows=1 width=0) (actual time=14600.386..14600.387 rows=1 loops=1) - Seq Scan on profile_master (cost=0.00..1674084.16 rows=359125 width=0) (actual time=13.687..13815.798 rows=470386 loops=1) Filter: (NOT (subplan)) SubPlan - Index Scan using account_profiles_profile_id on account_profiles (cost=0.00..4.59 rows=2 width=4) (actual time=0.013..0.013 rows=0 loops=718250) Index Cond: (profile_id = $0) Total runtime: 14600.531 ms ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[PERFORM] Suggestions for benchmarking 7.4RC2 against 7.3
Hi, I plan to put 7.4-RC2 in our production servers in next few hours. Since the hardware config the performance related GUCs parameter are going to remain the same i am interested in seeing the performance improvements in 7.4 as compared 7.3 . For this i plan to use the OSDB 0.14 and compare the results for both the cases. Does any one has suggestions for comparing 7.4 against 7.3 ? Since i am using OSDB for second time only any tips/guidance on usage of that is also appreciated. H/W config: CPU: 4 X Intel(R) Xeon(TM) CPU 2.00GHz MEM : 2 GB I/O config : PGDATA on 1 RPM Ultra160 scsi , pg_xlog on a similar seperate SCSI GUC: shared_buffers = 1 max_fsm_relations = 5000 max_fsm_pages = 55099264 sort_mem = 16384 vacuum_mem = 8192 All other performance related parameter have default value eg: #effective_cache_size = 1000# typically 8KB each #random_page_cost = 4 # units are one sequential page fetch cost #cpu_tuple_cost = 0.01 # (same) #cpu_index_tuple_cost = 0.001 # (same) #cpu_operator_cost = 0.0025 # (same) BTW i get following error at the moment: - /usr/local/bin/osdb-pg-ui --postgresql=no_hash_index osdb Invoked: /usr/local/bin/osdb-pg-ui --postgresql=no_hash_index create_tables()0.78 secondsreturn value = 0 load()1.02 secondsreturn value = 0 create_idx_uniques_key_bt()0.64 secondsreturn value = 0 create_idx_updates_key_bt()0.61 secondsreturn value = 0 create_idx_hundred_key_bt()0.61 secondsreturn value = 0 create_idx_tenpct_key_bt()0.62 secondsreturn value = 0 create_idx_tenpct_key_code_bt()0.45 secondsreturn value = 0 create_idx_tiny_key_bt()0.46 secondsreturn value = 0 create_idx_tenpct_int_bt()0.46 secondsreturn value = 0 create_idx_tenpct_signed_bt()0.45 secondsreturn value = 0 create_idx_uniques_code_h()0.46 secondsreturn value = 0 create_idx_tenpct_double_bt()0.46 secondsreturn value = 0 create_idx_updates_decim_bt()0.45 secondsreturn value = 0 create_idx_tenpct_float_bt()0.46 secondsreturn value = 0 create_idx_updates_int_bt()0.46 secondsreturn value = 0 create_idx_tenpct_decim_bt()0.46 secondsreturn value = 0 create_idx_hundred_code_h()0.45 secondsreturn value = 0 create_idx_tenpct_name_h()0.46 secondsreturn value = 0 create_idx_updates_code_h()0.46 secondsreturn value = 0 create_idx_tenpct_code_h()0.45 secondsreturn value = 0 create_idx_updates_double_bt()0.46 secondsreturn value = 0 create_idx_hundred_foreign()0.41 secondsreturn value = 0 populateDataBase()11.54 seconds return value = 0 Error in test Counting tuples at (6746)osdb.c:294: ... empty database -- empty results perror() reports: Resource temporarily unavailable someone sighup'd the parent Any clue? -- Regards Mallah. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [PERFORM] Suggestions for benchmarking 7.4RC2 against 7.3
the error mentioned in first email has been overcome by running osdb on the same machine hosting the DB server. regds mallah. Rajesh Kumar Mallah wrote: Hi, I plan to put 7.4-RC2 in our production servers in next few hours. Since the hardware config the performance related GUCs parameter are going to remain the same i am interested in seeing the performance improvements in 7.4 as compared 7.3 . For this i plan to use the OSDB 0.14 and compare the results for both the cases. Does any one has suggestions for comparing 7.4 against 7.3 ? Since i am using OSDB for second time only any tips/guidance on usage of that is also appreciated. H/W config: CPU: 4 X Intel(R) Xeon(TM) CPU 2.00GHz MEM : 2 GB I/O config : PGDATA on 1 RPM Ultra160 scsi , pg_xlog on a similar seperate SCSI GUC: shared_buffers = 1 max_fsm_relations = 5000 max_fsm_pages = 55099264 sort_mem = 16384 vacuum_mem = 8192 All other performance related parameter have default value eg: #effective_cache_size = 1000# typically 8KB each #random_page_cost = 4 # units are one sequential page fetch cost #cpu_tuple_cost = 0.01 # (same) #cpu_index_tuple_cost = 0.001 # (same) #cpu_operator_cost = 0.0025 # (same) BTW i get following error at the moment: - /usr/local/bin/osdb-pg-ui --postgresql=no_hash_index osdb Invoked: /usr/local/bin/osdb-pg-ui --postgresql=no_hash_index create_tables()0.78 secondsreturn value = 0 load()1.02 secondsreturn value = 0 create_idx_uniques_key_bt()0.64 secondsreturn value = 0 create_idx_updates_key_bt()0.61 secondsreturn value = 0 create_idx_hundred_key_bt()0.61 secondsreturn value = 0 create_idx_tenpct_key_bt()0.62 secondsreturn value = 0 create_idx_tenpct_key_code_bt()0.45 secondsreturn value = 0 create_idx_tiny_key_bt()0.46 secondsreturn value = 0 create_idx_tenpct_int_bt()0.46 secondsreturn value = 0 create_idx_tenpct_signed_bt()0.45 secondsreturn value = 0 create_idx_uniques_code_h()0.46 secondsreturn value = 0 create_idx_tenpct_double_bt()0.46 secondsreturn value = 0 create_idx_updates_decim_bt()0.45 secondsreturn value = 0 create_idx_tenpct_float_bt()0.46 secondsreturn value = 0 create_idx_updates_int_bt()0.46 secondsreturn value = 0 create_idx_tenpct_decim_bt()0.46 secondsreturn value = 0 create_idx_hundred_code_h()0.45 secondsreturn value = 0 create_idx_tenpct_name_h()0.46 secondsreturn value = 0 create_idx_updates_code_h()0.46 secondsreturn value = 0 create_idx_tenpct_code_h()0.45 secondsreturn value = 0 create_idx_updates_double_bt()0.46 secondsreturn value = 0 create_idx_hundred_foreign()0.41 secondsreturn value = 0 populateDataBase()11.54 seconds return value = 0 Error in test Counting tuples at (6746)osdb.c:294: ... empty database -- empty results perror() reports: Resource temporarily unavailable someone sighup'd the parent Any clue? -- Regards Mallah. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [PERFORM] Suggestions for benchmarking 7.4RC2 against 7.3
Josh Berkus wrote: Rajesh, Chris, I got the osdb benchmark running last week, and had to separate client from server. I had to jump through a fair number of hoops including copying data files over to the server. The benchmark software needs a bit more work... What about the OSDL's TPC-derivative benchmarks? That's a much more respected database test, and probably less buggy than OSDB. Hmm... really sorry! my pg_dump | psql is almost finishing in next 20 mins. creating indexes at the moment :) Really sorry can't rollback and delay anymore becoz my website is *unavailable* for past 30 mins. I ran OSDB .15 version and pg_bench . Regds Mallah.
Re: [PERFORM] Suggestions for benchmarking 7.4RC2 against 7.3
RC2 is running in production without any apparent problems till now. Well its difficult to say at the moment how much speed gain is there unless the heavy duty batch SQL scripts are run by cron. Count(*) and group by on large tables are significantly (5x) faster and better error reporting has made it easier to spot the faulty data. eg in fkey violation. Will post the OSDB .15 versions' results on 7.3 7.4 soon. Regds Mallah. Christopher Browne wrote: After a long battle with technology,[EMAIL PROTECTED] (Rajesh Kumar Mallah), an earthling, wrote: the error mentioned in first email has been overcome by running osdb on the same machine hosting the DB server. Yes, it seems unrealistic to try to run the "client" on a separate host from the database. I got the osdb benchmark running last week, and had to separate client from server. I had to jump through a fair number of hoops including copying data files over to the server. The benchmark software needs a bit more work...
[ PROBLEM SOLVED ] Re: [PERFORM] Query puts 7.3.4 on endless loop but 7.4beta5 is fine.
Well Sorry everyone , The problem was tracked down to a silly datatype mismatch between two join columns in table Groups(instance) and Tickets(id) (int vs varchar ) 7.4b5 is automatically taking care of this mismatch hence it was getting executed there. But , The problem is will this behaviour not allow to go such mistakes unnoticed? Regards Mallah. On Friday 31 Oct 2003 4:08 am, Greg Stark wrote: Well, you might want to try the EXISTS version. I'm not sure if it'll be faster or slower though. In theory it should be the same. Hum, I didn't realize the principals table was the largest table. But Postgres knew that so one would expect it to have found a better plan. The IN/EXISTS handling was recently much improved but perhaps there's still room :) SELECT * FROM tickets WHERE EXISTS ( SELECT 1 FROM groups JOIN principals ON (groups.id = principals.objectid) JOIN cachedgroupmembers ON (principals.id = cachedgroupmembers.groupid) JOIN users ON (cachedgroupmembers.memberid = users.id) WHERE lower(users.emailaddress) = '[EMAIL PROTECTED]' AND groups.domain = 'RT::Ticket-Role' AND groups.type = 'Requestor' AND principals.principaltype = 'group' AND groups.instance = tickets.id ) AND type = 'ticket' AND effectiveid = tickets.id AND (status = 'new' OR status = 'open') ORDER BY priority DESC LIMIT 10; ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PERFORM] PostgreSQL 7.4beta5 vs MySQL 4.0.16 with RT(DBIx::SearchBuilder)
On Thursday 30 Oct 2003 4:53 am, you wrote: [EMAIL PROTECTED] writes: Actually PostgreSQL is at par with MySQL when the query is being Properly Written(simplified) These are not the same query, though. Your original looks like Yes that was an optimisation on haste the simplification was not accurate. I will work on it again. But incidently both the SQLs produced the same results which *may* mean that the query could have been done in a simpler manner. SELECT DISTINCT main.* FROM Groups main , Principals Principals_1, ACL ACL_2 WHERE ((ACL_2.RightName = 'OwnTicket') OR (ACL_2.RightName = 'SuperUser')) AND ((ACL_2.PrincipalId = Principals_1.id AND ACL_2.PrincipalType = 'Group' AND (main.Domain = 'SystemInternal' OR main.Domain = 'UserDefined' OR main.Domain = 'ACLEquivalence') AND main.id = Principals_1.id) OR (((main.Domain = 'RT::Queue-Role' AND main.Instance = 25) OR (main.Domain = 'RT::Ticket-Role' AND main.Instance = 6973)) AND main.Type = ACL_2.PrincipalType AND main.id = Principals_1.id)) AND (ACL_2.ObjectType = 'RT::System' OR (ACL_2.ObjectType = 'RT::Queue' AND ACL_2.ObjectId = 25)) ORDER BY main.Name ASC where the replacement is SELECT DISTINCT main.* FROM Groups main join Principals Principals_1 using(id) join ACL ACL_2 on (ACL_2.PrincipalId = Principals_1.id) WHERE ((ACL_2.RightName = 'OwnTicket') OR (ACL_2.RightName = 'SuperUser')) AND ((ACL_2.PrincipalType = 'Group' AND (main.Domain = 'SystemInternal' OR main.Domain = 'UserDefined' OR main.Domain = 'ACLEquivalence')) OR (((main.Domain = 'RT::Queue-Role' AND main.Instance = 25) OR (main.Domain = 'RT::Ticket-Role' AND main.Instance = 6973)) AND main.Type = ACL_2.PrincipalType)) AND (ACL_2.ObjectType = 'RT::System' OR (ACL_2.ObjectType = 'RT::Queue' AND ACL_2.ObjectId = 25)) ORDER BY main.Name ASC ; You have made the condition ACL_2.PrincipalId = Principals_1.id required for all cases, where before it appeared in only one arm of an OR condition. If the second query is correct, then the first one is wrong, and your real problem is that your SQL generator is broken. Yes the SQL generator is not doing the best things at the moment and the author(Jesse) is aware of it and looking forward to our help in optimising it. (I'd argue that the SQL generator is broken anyway ;-) if it generates such horrible conditions as that. Or maybe the real problem is that the database schema is a mess and needs rethinking.) I do not think the database schema is a mess. The ACL system in RT and RT itself is quite comprehensive. The problem is with the Query Generator. Apologies for delayed response to your email. Regards Mallah. 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] Query puts 7.3.4 on endless loop but 7.4beta5 is fine. [ with better indenting ]
Dear Tom, Can you please have a Look at the below and suggest why it apparently puts 7.3.4 on an infinite loop . the CPU utilisation of the backend running it approches 99%. Query: I have tried my best to indent it :) SELECT DISTINCT main.* FROM ( ( ( ( Tickets main JOIN Groups as Groups_1 ON ( main.id = Groups_1.Instance) ) JOIN Principals as Principals_2 ON ( Groups_1.id = Principals_2.ObjectId) ) JOIN CachedGroupMembers as CachedGroupMembers_3 ON ( Principals_2.id = CachedGroupMembers_3.GroupId) ) JOIN Users as Users_4 ON ( CachedGroupMembers_3.MemberId = Users_4.id) ) WHERE ( (main.EffectiveId = main.id) ) AND ( (main.Type = 'ticket') ) AND ( ( ( (Users_4.EmailAddress = '[EMAIL PROTECTED]')AND (Groups_1.Domain = 'RT::Ticket-Role')AND (Groups_1.Type = 'Requestor')AND (Principals_2.PrincipalType = 'Group') ) ) AND ( (main.Status = 'new')OR(main.Status = 'open') ) ) ORDER BY main.Priority DESC LIMIT 10 On Thursday 30 Oct 2003 2:17 am, [EMAIL PROTECTED] wrote: ok this time it constructs a query which puts 7.3.4 on a infinite loop but 7.4b5 is able to come out of it. since it may be of interest to the pgsql people i am Ccing it to the pgsql-performance list i hope its ok. Pgsql 7.3.4 on an endless loop: SELECT DISTINCT main.* FROM Tickets main JOIN Groups as Groups_1 ON ( main.id = Groups_1.Instance)) JOIN Principals as Principals_2 ON ( Groups_1.id = Principals_2.ObjectId)) JOIN CachedGroupMembers as CachedGroupMembers_3 ON ( Principals_2.id = CachedGroupMembers_3.GroupId)) JOIN Users as Users_4 ON ( CachedGroupMembers_3.MemberId = Users_4.id)) WHERE ((main.EffectiveId = main.id)) AND ((main.Type = 'ticket')) AND ( ( ( (Users_4.EmailAddress = '[EMAIL PROTECTED]')AND(Groups_1.Domain = 'RT::Ticket-Role')AND(Groups_1.Type = 'Requestor')AND(Principals_2.PrincipalType = 'Group') ) ) AND ( (main.Status = 'new')OR(main.Status = 'open') ) ) ORDER BY main.Priority DESC LIMIT 10 But 7.4 beta5 seems to be able to handle it: SELECT DISTINCT main.* FROM Tickets main JOIN Groups as Groups_1 ON ( main.id = Groups_1.Instance)) JOIN Principals as Principals_2 ON ( Groups_1.id = Principals_2.ObjectId)) JOIN CachedGroupMembers as CachedGroupMembers_3 ON ( Principals_2.id = CachedGroupMembers_3.GroupId)) JOIN Users as Users_4 ON ( CachedGroupMembers_3.MemberId = Users_4.id)) WHERE ((main.EffectiveId = main.id)) AND ((main.Type = 'ticket')) AND ( ( ( (Users_4.EmailAddress = '[EMAIL PROTECTED]')AND(Groups_1.Domain = 'RT::Ticket-Role')AND(Groups_1.Type = 'Requestor')AND(Principals_2.PrincipalType = 'Group') ) ) AND ( (main.Status = 'new')OR(main.Status = 'open') ) ) ORDER BY main.Priority DESC LIMIT 10; id | effectiveid | queue | type | issuestatement | resolution | owner | subject | initialpriority | finalpriority | priority | timeestimated | | timeworked | status | timeleft | | told | starts| started | due | | resolved | lastupdatedby | lastupdated | creator | created | disabled--+-+---+++ +---+-+-+---+--- ---+---+++--+--+ -+-+-+-+ ---+-+-+-+-- 13 | 13 |23 | ticket | 0 | 0 | 31122 | General Discussion | 0 | 0 |0 | 0 | | 0 | new|0 | | | 1970-01-01 00:00:00 | 1970-01-01 00:00:00 | 1970-01-01 00:00:00 | | | 1970-01-01 00:00:00 | | 31122 | 2001-11-22 04:19:10 | 31122 | 2001-11-22 04:19:07 | | 0 6018 |6018 |19 | ticket | 0 | | 0 |10 | EYP Prospective Clients | 0 | 0 |0 | 0 | 0 | new| 0 | | 1970-01-01 00:00:00 | 1970-01-01 00:00:00 | 2002-09-11 18:29:37 | 1970-01-01 00:00:00 | 31122 | 2002-09-11 18:29:39 | 31122 | 2002-09-11 18:29:37 |0 6336 |6336 | 19 | ticket | 0 | 0 |10 | EYP Prospective Clients | 0 | 0 |0 | 0 | 0 | new| 0 | | 1970-01-01 00:00:00 | 1970-01-01 00:00:00 | 2002-09-20 12:31:02 | 1970-01-01 00:00:00 | 31122 | 2002-09-20 12:31:09 | 31122 | 2002-09-20 12:31:02 |0 6341 |6341 |19 | ticket | 0 | 0 |10 | IP Prospective Clients | 0 | 0 |0 | 0 | 0 | new| 0 | | 1970-01-01
Re: [PERFORM] Query puts 7.3.4 on endless loop but 7.4beta5 is fine.
by, main.lastupdated, main.creator, main.created, main.disabled
- Hash Join (cost=476.18..582.26 rows=1 width=164)
Hash Cond: ("outer".groupid = "inner".id)
- Nested Loop (cost=0.00..105.97 rows=21 width=4)
- Index Scan using users4 on users users_4 (cost=0.00..3.99 rows=2 width=4)
Index Cond: ((emailaddress)::text = '[EMAIL PROTECTED]'::text)
- Index Scan using cachedgroupmembers2 on cachedgroupmembers cachedgroupmembers_3 (cost=0.00..50.81 rows=14 width=8)
Index Cond: (cachedgroupmembers_3.memberid = "outer".id)
- Hash (cost=476.17..476.17 rows=1 width=168)
- Nested Loop (cost=0.00..476.17 rows=1 width=168)
- Nested Loop (cost=0.00..471.54 rows=1 width=168)
- Seq Scan on tickets main (cost=0.00..465.62 rows=1 width=164)
Filter: ((effectiveid = id) AND (("type")::text = 'ticket'::text) AND (((status)::text = 'new'::text) OR ((status)::text = 'open'::text)))
- Index Scan using groups1 on groups groups_1 (cost=0.00..5.90 rows=1 width=12)
Index Cond: (((groups_1."domain")::text = 'RT::Ticket-Role'::text) AND (("outer".id)::text = (groups_1.instance)::text) AND ((groups_1."type")::text = 'Requestor'::text))
- Index Scan using principals2 on principals principals_2 (cost=0.00..4.62 rows=1 width=8)
Index Cond: ("outer".id = principals_2.objectid)
Filter: ((principaltype)::text = 'Group'::text)
(21 rows)
rt3=#
Christopher Browne wrote:
In the last exciting episode, [EMAIL PROTECTED] wrote:
[EMAIL PROTECTED] (Rajesh Kumar Mallah) wrote:
Can you please have a Look at the below and suggest why it
apparently puts 7.3.4 on an infinite loop . the CPU utilisation of the backend running it
approches 99%.
What would be useful, for this case, would be to provide the query plan, perhaps via
EXPLAIN [Big Long Query].
The difference between that EXPLAIN and what you get on 7.4 might be quite interesting.
I would think it quite unlikely that it is truly an "infinite" loop; it is rather more likely
that the plan winds up being pretty bad and doing something [a bunch of nested loops, maybe?]
that run longer than your patience will permit.
:-) ok i will leave it running and try to get it.
No, if you just do EXPLAIN (and not EXPLAIN ANALYZE), that returns
without executing the query.
If the query runs for a really long time, then we _know_ that there is
something troublesome. EXPLAIN (no ANALYZE) should provide some
insight without having anything run for a long time.
If EXPLAIN [big long query] turns into what you are terming an
"infinite loop," then you have a quite different problem, and it would
be very useful to know that.
Re: [PERFORM] Query puts 7.3.4 on endless loop but 7.4beta5 is fine.
Tom Lane wrote: Rajesh Kumar Mallah [EMAIL PROTECTED] writes: SELECT DISTINCT main.* FROM ( ( ( ( Tickets main JOIN Groups as Groups_1 ON ( main.id = Groups_1.Instance) ) JOIN Principals as Principals_2 ON ( Groups_1.id = Principals_2.ObjectId) ) JOIN CachedGroupMembers as CachedGroupMembers_3 ON ( Principals_2.id = CachedGroupMembers_3.GroupId) ) JOIN Users as Users_4 ON ( CachedGroupMembers_3.MemberId = Users_4.id) ) WHERE ... I think the reason for the performance difference is that 7.3 treats JOIN syntax as forcing a particular join order, while 7.4 doesn't. Just out of curiosity , how does 7.4 determine the optimal Join Order? is it GEQO in case of 7.4 although i did not enable it explicitly? Thanks for the reply , I sent the EXPLAINs also just now. What i really want is to help improving the Pg specific Component for DBIx::SearchBuilder. The module is being widely used in the mod_perl world and has impact on the performance perception of PostgreSQL. regards, tom lane
Re: [PERFORM] Query puts 7.3.4 on endless loop but 7.4beta5 is fine.
ailaddress = '[EMAIL PROTECTED]' rt3(# AND groups.domain = 'RT::Ticket-Role' rt3(# AND groups.type = 'Requestor' rt3(# AND principals.principaltype = 'group' rt3(# ) rt3-# AND type = 'ticket' rt3-# AND effectiveid = tickets.id rt3-# AND (status = 'new' OR status = 'open') rt3-# ORDER BY priority DESC rt3-# LIMIT 10; id | effectiveid | queue | type | issuestatement | resolution | owner | subject | initialpriority | finalpriority | priority | timeestimated | timeworked | status | timeleft | told | starts | started | due | resolved | lastupdatedby | lastupdated | creator | created | disabled +-+---+--+++---+-+-+---+--+---+++--+--++-+-+--+---+-+-+-+-- (0 rows) Time: 2670.85 ms rt3=# Well it may be of interest to write the query in best possible way but i am not sure if it really helps the RT application becoz i do not know whether DBIx::SearchBuilder would currently allow auto generation of such arbitrary SQLs. Regds Mallah. Greg Stark wrote: Rajesh Kumar Mallah [EMAIL PROTECTED] writes: rt3=# explain SELECT DISTINCT main.* FROM ((( (Tickets main JOIN Groups as Groups_1 ON ( main.id = Groups_1.Instance)) JOIN Principals as Principals_2 ON ( Groups_1.id = Principals_2.ObjectId) ) JOIN CachedGroupMembers as CachedGroupMembers_3 ON ( Principals_2.id = CachedGroupMembers_3.GroupId) ) JOIN Users as Users_4 ON ( CachedGroupMembers_3.MemberId = Users_4.id) ) WHERE ((main.EffectiveId = main.id)) AND ((main.Type = 'ticket')) AND ((((Users_4.EmailAddress = '[EMAIL PROTECTED]') AND (Groups_1.Domain = 'RT::Ticket-Role') AND (Groups_1.Type = 'Requestor') AND (Principals_2.PrincipalType = 'Group') )) AND ((main.Status = 'new') OR (main.Status = 'open')) ) ORDER BY main.Priority DESC LIMIT 10; So this query seems to be going the long way around to do the equivalent of an IN clause. Presumably because as far as I know mysql didn't support IN subqueries until recently. Can you do an "explain analyze" on the above query and the following rewritten one in 7.4? The "analyze" is important because it'll give real timing information. And it's important that it be on 7.4 as there were improvements in this area specifically in 7.4. SELECT * FROM tickets WHERE id IN ( SELECT groups.instance FROM groups JOIN principals ON (groups.id = principals.objectid) JOIN cachedgroupmembers ON (principals.id = cachedgroupmembers.groupid) JOIN users ON (cachedgroupmembers.memberid = users.id) WHERE users.emailaddress = '[EMAIL PROTECTED]' AND groups.domain = 'RT::Ticket-Role' AND groups.type = 'Requestor' AND principals.principaltype = 'group' ) AND type = 'ticket' AND effectiveid = tickets.id AND (status = 'new' OR status = 'open') ORDER BY priority DESC LIMIT 10;
