Re: [PERFORM] Help with Query Tuning
Thanks, I understand it know :-
But My one doubt which isn't clear :
*Original Query :-*
select count(*) from page_content where (content like '%Militant%'
OR content like '%jihad%' OR content like '%Mujahid%' OR
content like '%fedayeen%' OR content like '%insurgent%' OR content
like '%terrORist%' OR
content like '%cadre%' OR content like '%civilians%' OR content like
'%police%' OR content like '%defence%' OR content like '%cops%' OR
content like '%crpf%' OR content like '%dsf%' OR content like '%ssb%')
AND (content like '%kill%' OR content like '%injure%');
*Output :-*
count
---
57061
(1 row)
Time: 19726.555 ms
I need to tune it , use full-text searching as :
*Modified Query :-
*SELECT count(*) from page_content
WHERE publishing_date like '%2010%' and content_language='en' and
content is not null and isprocessable = 1 and
to_tsvectOR('english',content) @@ to_tsquery('english','Mujahid' ||
'jihad' || 'Militant' || 'fedayeen' || 'insurgent' || 'terrORist' ||
'cadre' || 'civilians' || 'police' || 'defence' || 'cops' || 'crpf' ||
'dsf' || 'ssb');
*Output :-*
count
---
0
(1 row)
Time: 194685.125 ms
*
*I try, SELECT count(*) from page_content
WHERE publishing_date like '%2010%' and content_language='en' and
content is not null and isprocessable = 1 and
to_tsvectOR('english',content) @@ to_tsquery('english','%Mujahid%' ||
'%jihad%' || '%Militant%' || '%fedayeen%' || '%insurgent%' ||
'%terrORist%' || '%cadre%' || '%civilians%' || '%police%' || '%defence%'
|| '%cops%' || '%crpf%' || '%dsf%' || '%ssb%');
count
---
0
(1 row)
Time: 194722.468 ms
I know I have to create index but index is the next step, first you have
to get the correct result .
CREATE INDEX pgweb_idx ON page_content USING gin(to_tsvector('english',
content));
Please guide me where I am going wrong.
Thanks best Regards,
Adarsh Sharma
Kenneth Marshall wrote:
On Wed, Mar 16, 2011 at 02:43:38PM +0530, Adarsh Sharma wrote:
Dear all,
I am facing a problem while creating the index to make the below query run
faster. My table size is near about 1065 MB and 428467 rows.
explain analyze select count(*) from page_content where publishing_date
like '%2010%' and content_language='en' and content is not null and
isprocessable = 1 and (content like '%Militant%'
OR content like '%jihad%' OR content like '%Mujahid%' OR
content like '%fedayeen%' OR content like '%insurgent%' OR content like
'%terrorist%' OR
content like '%cadre%' OR content like '%civilians%' OR content like
'%police%' OR content like '%defence%' OR content like '%cops%' OR content
like '%crpf%' OR content like '%dsf%' OR content like '%ssb%') AND (content
like '%kill%' or content like '%injure%');
*Output:
* Aggregate (cost=107557.78..107557.79 rows=1 width=0) (actual
time=18564.631..18564.631 rows=1 loops=1)
- Seq Scan on page_content (cost=0.00..107466.82 rows=36381 width=0)
(actual time=0.146..18529.371 rows=59918 loops=1)
Filter: ((content IS NOT NULL) AND (publishing_date ~~
'%2010%'::text) AND (content_language = 'en'::bpchar) AND (isprocessable =
1) AND (((content)
::text ~~ '%kill%'::text) OR ((content)::text ~~ '%injure%'::text)) AND
(((content)::text ~~ '%Militant%'::text) OR ((content)::text ~~
'%jihad%'::text) OR (
(content)::text ~~ '%Mujahid%'::text) OR ((content)::text ~~
'%fedayeen%'::text) OR ((content)::text ~~ '%insurgent%'::text) OR
((content)::text ~~ '%terrori
st%'::text) OR ((content)::text ~~ '%cadre%'::text) OR ((content)::text ~~
'%civilians%'::text) OR ((content)::text ~~ '%police%'::text) OR
((content)::text
~~ '%defence%'::text) OR ((content)::text ~~ '%cops%'::text) OR
((content)::text ~~ '%crpf%'::text) OR ((content)::text ~~ '%dsf%'::text)
OR ((content)::text
~~ '%ssb%'::text)))
Total runtime: 18564.673 ms
*Index on that Table :
*CREATE INDEX idx_page_id
ON page_content
USING btree
(crawled_page_id);
*Index I create :*
CREATE INDEX idx_page_id_content
ON page_content
USING btree
(crawled_page_id,content_language,publishing_date,isprocessable);
*Index that fail to create:
*CREATE INDEX idx_page_id_content1
ON page_content
USING btree
(crawled_page_id,content);
Error :-ERROR: index row requires 13240 bytes, maximum size is 8191
** Error **
ERROR: index row requires 13240 bytes, maximum size is 8191
SQL state: 54000
How to resolve this error
Please give any suggestion to tune the query.
Thanks best Regards,
Adarsh Sharma
You should probably be looking at using full-text indexing:
http://www.postgresql.org/docs/9.0/static/textsearch.html
or limit the size of content for the index.
Cheers,
Ken
Re: [PERFORM] Help with Query Tuning
*Modified Query :-
*SELECT count(*) from page_content
WHERE publishing_date like '%2010%' and content_language='en' and
content is not null and isprocessable = 1 and
to_tsvectOR('english',content) @@ to_tsquery('english','Mujahid' ||
'jihad' || 'Militant' || 'fedayeen' || 'insurgent' || 'terrORist' ||
'cadre' || 'civilians' || 'police' || 'defence' || 'cops' || 'crpf' ||
'dsf' || 'ssb');
I guess there should be spaces between the words. This way it's just one
very long word 'MujahidjihadMilitantfedayeen' and I doubt that's what
you're looking for.
regards
Tomas
--
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] pg_xlog size
Thank you. I had pg_archivecleanup added in recovery.conf, but on second look had a typo in the archive dir path. After this change in recovery.conf and postgres restart, its fine now. Once my archive dir got cleaned up , i noticed my /var/postgres/data/pg_xlog dir on master also got cleaned up On Wed, Mar 16, 2011 at 1:27 PM, Euler Taveira de Oliveira eu...@timbira.com wrote: Em 15-03-2011 12:09, Tech Madhu escreveu: [This is not a performance question, next time post at the appropriate list, that is -general] Everything works fine (w.r.t replication), but the pg_xlog size grows continuously, though i had no operations going on. Also the archiving to the other side filled up the other side FS. ls -l /var/postgres/data/pg_xlog | wc -l 103 Did you consider using pg_archivecleanup [1]? At start, there were only 15 files. The max_wal_segments is 32, but not sure why iam seeing 103 files. Also the archiving dir size doubled (w.r.t number of files archived). and filled up the filesystem. I manually logged into postgres and run checkpoint; did not see any file reduction max_wal_segments [2] is *not* related to archiving activity. [1] http://www.postgresql.org/docs/9.0/static/pgarchivecleanup.html [2] http://www.postgresql.org/docs/9.0/static/runtime-config-wal.html#GUC-WAL-KEEP-SEGMENTS -- Euler Taveira de Oliveira http://www.timbira.com/
Re: [PERFORM] Updating histogram_bounds after a delete
On Wed, Mar 16, 2011 at 5:56 PM, Kevin Grittner kevin.gritt...@wicourts.gov wrote: there is a feature to probe the end of an index's range in situations where data skew was often causing less than optimal plans to be chosen. Was this introduced in 9.0 or was it earlier? My company hasn't introduced integrated support for 9.0 yet, but I can go to 8.4. It was suggested that I change my SQL from: delete from my_table where event_date now() - interval '12 hours'; to: delete from my_table where event_date now() - interval '12 hours' and event_date = (select min(event_date) from my_table); Which, even if the stats are out of date, will be more accurate as it will not consider the histogram buckets that are empty due to previous deletes. Seems like exactly what the feature you mentioned would do, no? Thanks for the help, Derrick
Re: [PERFORM] Updating histogram_bounds after a delete
Derrick Rice derrick.r...@gmail.com wrote: Kevin Grittner kevin.gritt...@wicourts.gov wrote: there is a feature to probe the end of an index's range in situations where data skew was often causing less than optimal plans to be chosen. Was this introduced in 9.0 or was it earlier? I don't remember when it was added. I took a stab at searching for it, but didn't get it figured out; if nobody who knows off-hand jumps in, I'll try again when I have more time. It was suggested that I change my SQL from: delete from my_table where event_date now() - interval '12 hours'; to: delete from my_table where event_date now() - interval '12 hours' and event_date = (select min(event_date) from my_table); That seems like a reasonable workaround. Seems like exactly what the feature you mentioned would do, no? I know it helps with inserts off the end of the range; I'm less certain about deletes. I *think* that's covered, but I'd have to dig into the code or do some testing to confirm. -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] Updating histogram_bounds after a delete
On Thu, Mar 17, 2011 at 09:49:45AM -0500, Kevin Grittner wrote: Derrick Rice derrick.r...@gmail.com wrote: Kevin Grittner kevin.gritt...@wicourts.gov wrote: there is a feature to probe the end of an index's range in situations where data skew was often causing less than optimal plans to be chosen. Was this introduced in 9.0 or was it earlier? I don't remember when it was added. I took a stab at searching for it, but didn't get it figured out; if nobody who knows off-hand jumps in, I'll try again when I have more time. I think this is it: http://archives.postgresql.org/pgsql-committers/2010-01/msg00021.php Regards, Ken -- 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] Updating histogram_bounds after a delete
Kevin Grittner kevin.gritt...@wicourts.gov writes: Derrick Rice derrick.r...@gmail.com wrote: Kevin Grittner kevin.gritt...@wicourts.gov wrote: there is a feature to probe the end of an index's range in situations where data skew was often causing less than optimal plans to be chosen. Was this introduced in 9.0 or was it earlier? I don't remember when it was added. I took a stab at searching for it, but didn't get it figured out; if nobody who knows off-hand jumps in, I'll try again when I have more time. Author: Tom Lane t...@sss.pgh.pa.us Branch: master Release: REL9_0_BR [40608e7f9] 2010-01-04 02:44:40 + When estimating the selectivity of an inequality column constant or column constant, and the comparison value is in the first or last histogram bin or outside the histogram entirely, try to fetch the actual column min or max value using an index scan (if there is an index on the column). If successful, replace the lower or upper histogram bound with that value before carrying on with the estimate. This limits the estimation error caused by moving min/max values when the comparison value is close to the min or max. Per a complaint from Josh Berkus. It is tempting to consider using this mechanism for mergejoinscansel as well, but that would inject index fetches into main-line join estimation not just endpoint cases. I'm refraining from that until we can get a better handle on the costs of doing this type of lookup. 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] Updating histogram_bounds after a delete
Kenneth Marshall k...@rice.edu wrote: I think this is it: http://archives.postgresql.org/pgsql-committers/2010-01/msg00021.php Looks like it. Based on the commit date, that would be a 9.0 change. Based on the description, I'm not sure it fixes Derrick's problem; the workaround of explicitly using min() for the low end of a range may need to be a long-term approach. It does seem odd, though, that the statistics would be off by that much. Unless the query is run immediately after a mass delete, autovacuum should be fixing that. Perhaps the autovacuum improvements in later releases will solve the problem. If not, an explicit ANALYZE (or perhaps better, VACUUM ANALYZE) immediately after a mass delete would be wise. -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] Xeon twice the performance of opteron
hey folks, Running into some odd performance issues between a few of our db boxes. While trying to speed up a query I ran it on another box and it was twice as fast. The plans are identical and various portions of the query run in the same amount of time - it all boils down to most of the time being spent in a join filter. The plan is as good as it is going to get but the thing that is concerning me, which hopefully some folks here may have some insight on, is the very large difference in runtime. three boxes: A: Intel(R) Xeon(R) CPU E5345 @ 2.33GHz (Runs query fastest) 4MB cache B: Quad-Core AMD Opteron(tm) Processor 2352 (2.1GHZ) (Main production box, currently, middle speed) 512k cache C: Quad-Core AMD Opteron(tm) Processor 2378 (2.4GHZ) 512k cache A B are running PG 8.4.2 (yes, I know it desperately need to be upgraded). C was also on 8.4.2 and since it was not in production I upgraded it to 8.4.7 and got the same performance as 8.4.2. Dataset on A B is the same C is mostly the same, but is missing a couple weeks of data (but since this query runs over 3 years of data, it is negligable - plus C runs the slowest!) All three running FC10 with kernel Linux db06 2.6.27.19-170.2.35.fc10.x86_64 #1 SMP Mon Feb 23 13:00:23 EST 2009 x86_64 x86_64 x86_64 GNU/Linux Load is very low on each box. The query is running from shared_buffers - no real IO is occuring. The average timing for the query in question is 90ms on A, 180ms on B and 190ms on C. Now here's where some odd stuff starts piling up: explain analyze overhead on said queries: 20ms on A, 50ms on B and 85ms on C(!!) We had one thought about potential NUMA issues, but doing a series (100) of connect, query, disconnect and looking at the timings reveals them all to be solid... but even still we wouldn't expect it to be that awful. The smaller cache of the opterons is also a valid argument. I know we're running an old kernel, I'm tempted to upgrade to see what will happen, but at the same time I'm afraid it'll upgrade to a kernel with a broken [insert major subsystem here] which has happened before. Anybody have some insight into this or run into this before? btw, little more background on the query: - Nested Loop (cost=5.87..2763.69 rows=9943 width=0) (actual time=0.571..2 74.750 rows=766 loops=1) Join Filter: (ce.eventdate = (md.date - '6 days'::interval)) - Nested Loop (cost=5.87..1717.98 rows=27 width=8) (actual time=0.53 3..8.301 rows=159 loops=1) [stuff removed here] - Index Scan using xxx_date_idx on xx md (cost=0.00..19.50 rows=1099 width=8) (actual time=0.023..0.729 rows=951 loops=15 9) Index Cond: (ce.eventdate = md.date) On all three boxes that inner nestloop completes in about the same amount of time - it is that join filter that is causing the pain and agony. (If you are noticing the timing differences, that is because the numbers above are the actual numbers, not explain analyze). The query is pulling up a rolling window of events that occured on a specific date. This query pulls up al the data for a period of time. ce.eventdate is indexed, and is used in the outer nestloop. Thinking more about what is going on cache thrashing is certainly a possibility. the amazing explain analyze overhead is also very curious - we all know it adds overhead, but 85ms? Yow. -- Jeff Trout j...@jefftrout.com http://www.stuarthamm.net/ http://www.dellsmartexitin.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] Xeon twice the performance of opteron
On Thu, Mar 17, 2011 at 10:13 AM, Jeff thres...@torgo.978.org wrote: hey folks, Running into some odd performance issues between a few of our db boxes. We've noticed similar results both in OLTP and data warehousing conditions here. Opteron machines just seem to lag behind *especially* in data warehousing. Smaller cache for sorting/etc... is what I'd always chalked it up to, but I'm open to other theories if they exist. -- 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] Xeon twice the performance of opteron
On Thu, Mar 17, 2011 at 1:42 PM, J Sisson sisso...@gmail.com wrote: On Thu, Mar 17, 2011 at 10:13 AM, Jeff thres...@torgo.978.org wrote: hey folks, Running into some odd performance issues between a few of our db boxes. We've noticed similar results both in OLTP and data warehousing conditions here. Opteron machines just seem to lag behind *especially* in data warehousing. Smaller cache for sorting/etc... is what I'd always chalked it up to, but I'm open to other theories if they exist. It's my theory as well - you know, this could be solved by JITting complex expressions. Bad cache behavior in application often comes as a side-effect of interpreted execution (in this case, of expressions, conditions, functions). A JIT usually solves this cache inefficiency. I know, adding any kind of JIT to pg could be a major task. -- 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] Xeon twice the performance of opteron
On 3/17/11 9:42 AM, J Sisson wrote: On Thu, Mar 17, 2011 at 10:13 AM, Jeffthres...@torgo.978.org wrote: hey folks, Running into some odd performance issues between a few of our db boxes. We've noticed similar results both in OLTP and data warehousing conditions here. Opteron machines just seem to lag behind *especially* in data warehousing. Smaller cache for sorting/etc... is what I'd always chalked it up to, but I'm open to other theories if they exist. We had a similar result with a different CPU-intensive open-source package, and discovered that if we compiled it on the Opteron it ran almost twice as fast as binaries compiled on Intel hardware. We thought we could compile once, run everywhere, but it's not true. It must have been some specific optimization difference between Intel and AMD that the gcc compiler knows about. I don't know if that's the case here, but it's a thought. Craig -- 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] Adding additional index causes 20,000x slowdown for certain select queries - postgres 9.0.3
Hi all, The bl_number is nearly a unique value per a row (some small portion are duplicated on a handful or rows). We need the unique on pair of bl_number and month, but evaluating current usage we don't make use of selecting on just month currently (though we expect to have usage scenarios that do that in the not too distant future, i.e. pulling out all the records that match a given month date). But for the time being we've gone with the suggestion here of flipping the order of the index columns to (bl_number, month) which rescues the original performance (since the new index can no longer be used with the query). We'd still be interested in other suggestions for convincing the query planner not to pick the bad plan in this case (since we'll eventually need an index on month) without having to use the slower CTE form. To me the problem seems two fold, (1) planner doesn't know there's a correlation between month and particular buyer_ids (some are randomly distributed across month) (2) even in cases where there isn't a correlation (not all of our buyer id's are correlated with month) it still seems really surprising to me the planner thought this plan would be faster, the estimated selectivity of the buyer fields is 48k / 45million ~ 1/1000 so for limit 100 it should expect to backward index scan ~100K rows, vs. looking up the expected 48k rows and doing a top-100 sort on them, I'd expect the latter plan to be faster in almost all situations (unless we're clustered on month perhaps, but we're actually clustered on supplier_id, buyer_id which would favor the latter plan as well I'd think). (an aside) there's also likely some benefit from clustering in the original plan before the new index, since we cluster on supplier_id, buyer_id and a given buyer_id while having up to 100k rows will generally only have a few supplier ids Tim On Wed, Mar 16, 2011 at 1:05 PM, Shaun Thomas stho...@peak6.com wrote: On 03/15/2011 01:23 PM, Timothy Garnett wrote: Column | Type --++ id | integer| bl_number| character varying(16) | month| date | buyer_id | integer| supplier_id | integer| Ok. In your table description, you don't really talk about the distribution of bl_number. But this part of your query: ORDER BY month DESC LIMIT 100 OFFSET 0 Is probably tricking the planner into using that index. But there's the fun thing about dates: we almost always want them in order of most recent to least recent. So you might want to try again with your index_customs_records_on_month_and_bl_number declared like this instead: CREATE INDEX index_customs_records_on_month_and_bl_number ON customs_records (month DESC, bl_number); Or, if bl_number is more selective anyway, but you need both columns for other queries and you want this one to ignore it: CREATE INDEX index_customs_records_on_month_and_bl_number ON customs_records (bl_number, month DESC); Either way, I bet you'll find that your other queries that use this index are also doing a backwards index scan, which will always be slower by about two orders of magnitude, since backwards reads act basically like random reads. The effect you're getting is clearly exaggerated, and I've run into it on occasion for effectively the entire history of PostgreSQL. Normally increasing the statistics on the affected columns and re-analyzing fixes it, but on a composite index, that won't necessarily be the case. -- Shaun Thomas OptionsHouse | 141 W. Jackson Blvd. | Suite 800 | Chicago IL, 60604 312-676-8870 stho...@peak6.com __ See http://www.peak6.com/email_disclaimer.php for terms and conditions related to this email
Re: [PERFORM] Adding additional index causes 20,000x slowdown for certain select queries - postgres 9.0.3
Timothy Garnett tgarn...@panjiva.com wrote: We'd still be interested in other suggestions for convincing the query planner not to pick the bad plan in this case You could try boosting cpu_tuple_cost. I've seen some evidence that the default number is a bit low in general, so it wouldn't necessarily be bad to try your whole load with a higher setting. If that doesn't work you could set it for the one query. If that setting alone doesn't do it, you could either decrease both page cost numbers or multiply all the cpu numbers (again, probably boosting cpu_tuple_cost relative to the others). -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] Adding additional index causes 20,000x slowdown for certain select queries - postgres 9.0.3
Thanks, we'll give these a try. Tim On Thu, Mar 17, 2011 at 2:13 PM, Kevin Grittner kevin.gritt...@wicourts.gov wrote: Timothy Garnett tgarn...@panjiva.com wrote: We'd still be interested in other suggestions for convincing the query planner not to pick the bad plan in this case You could try boosting cpu_tuple_cost. I've seen some evidence that the default number is a bit low in general, so it wouldn't necessarily be bad to try your whole load with a higher setting. If that doesn't work you could set it for the one query. If that setting alone doesn't do it, you could either decrease both page cost numbers or multiply all the cpu numbers (again, probably boosting cpu_tuple_cost relative to the others). -Kevin
[PERFORM] Fastest pq_restore?
Hi, I've been looking around for information on doing a pg_restore as fast as possible. It is for a backup machine so I am not interested in anything like crash recovery or anything else that would impact speed of load. I just want to go from no database to database there as fast as possible. The server is for postgresql only and this is the only database, sp both system at postgres can be set however is required for the fast load. Currently I am using a twin processor box with 2GB of memory and raid 5 disk. I start postgres before my load with these settings, which have been suggested. shared_buffers = 496MB maintenance_work_mem = 160MB checkpoint_segments = 30 autovacuum = false full_page_writes=false maintenance_work_mem and checkpoint_segments were advised to be increased, which I have done, but these are just guess values as I couldn't see any advise for values, other than bigger. I restore like this; pg_restore -Fc -j 4 -i -O -d my_db my_db_dump.tbz Even as this, it is still slower than I would like. Can someone suggest some optimal settings (for postgresql 9) that will get this as quick as it can be? Thanks.
[PERFORM] Request for feedback on hardware for a new database server
Hello, At MusicBrainz we're looking to get a new database server, and are hoping to buy this in the next couple of days. I'm mostly a software guy, but I'm posting this on behalf of Rob, who's actually going to be buying the hardware. Here's a quote of what we're looking to get: I'm working to spec out a bad-ass 1U database server with loads of cores (12), RAM (24GB) and drives (4 SAS) in a hardware RAID-1,0 configuration: 1 * SuperMicro 2016R-URF, 1U, redundant power supply, 4 SATA/SAS drive bays 2 2 * Intel Xeon X5650 Westmere 2.66GHz 12MB L3 Cache LGA 1366 95W Six-Core Server Processor 2 2 * Crucial 24GB (3 x 4GB) DDR3 SDRAM ECC Registered DDR3 1333, CT3KIT51272BV1339 1 1 * LSI MegaRAID SATA/SAS 9260-4i ($379) (linux support [1]) or 1 * HighPoint RocketRAID 4320 PCI-Express x8 ($429) or 1 * Adaptec RAID 3405 controller ($354) 4 * Fujitsu MBA3147RC 147GB 15000 RPM SuperMicro machines have treated us really well over time (better than Dell or Sun boxes), so I am really happy to throw more money in their direction. Redundant power supplies seem like a good idea for a database server. For $400 more we can get hexa core processors as opposed to quad core processors at 2.66Ghz. This seems like a really good deal -- any thoughts on this? Crucial memory has also served us really well, so that is a no-brainer. The RAID controller cards are where I need to most feedback! Of the LSI, Highpoint or Adaptec cards, which one is likely to have native linux support that does not require custom drivers to be installed? The LSI card has great specs at a great price point with Linux support, but installing the custom driver sounds like a pain. Does anyone have any experience with these cards? We've opted to not go for SSD drives in the server just yet -- it doesn't seem clear how well SSDs do in a driver environment. That's it -- anyone have any feedback? Just a quick bit more information. Our database is certainly weighted towards being read heavy, rather than write heavy (with a read-only web service accounting for ~90% of our traffic). Our tables vary in size, with the upperbound being around 10mil rows. I'm not sure exactly what more to say - but any feedback is definitely appreciated. We're hoping to purchase this server on Monday, I believe. Any questions, ask away! Thanks, - Ollie [1]: http://www.lsi.com/storage_home/products_home/internal_raid/megaraid_sas/entry_line/megaraid_sas_9240-4i/index.html -- 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] Fastest pq_restore?
On 03/17/2011 09:25 AM, Michael Andreasen wrote: Hi, I've been looking around for information on doing a pg_restore as fast as possible. It is for a backup machine so I am not interested in anything like crash recovery or anything else that would impact speed of load. I just want to go from no database to database there as fast as possible. The server is for postgresql only and this is the only database, sp both system at postgres can be set however is required for the fast load. Currently I am using a twin processor box with 2GB of memory and raid 5 disk. I start postgres before my load with these settings, which have been suggested. shared_buffers = 496MB maintenance_work_mem = 160MB checkpoint_segments = 30 autovacuum = false full_page_writes=false maintenance_work_mem and checkpoint_segments were advised to be increased, which I have done, but these are just guess values as I couldn't see any advise for values, other than bigger. I restore like this; pg_restore -Fc -j 4 -i -O -d my_db my_db_dump.tbz Even as this, it is still slower than I would like. Can someone suggest some optimal settings (for postgresql 9) that will get this as quick as it can be? Thanks. autovacuum = off fsync = off synchronous_commit = off full_page_writes = off bgwriter_lru_maxpages = 0 -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] Xeon twice the performance of opteron
-Original Message- From: pgsql-performance-ow...@postgresql.org [mailto:pgsql-performance- ow...@postgresql.org] On Behalf Of Jeff Sent: Thursday, March 17, 2011 9:14 AM To: pgsql-performance@postgresql.org Cc: Brian Ristuccia Subject: [PERFORM] Xeon twice the performance of opteron hey folks, Running into some odd performance issues between a few of our db boxes. While trying to speed up a query I ran it on another box and it was twice as fast. The plans are identical and various portions of the query run in the same amount of time - it all boils down to most of the time being spent in a join filter. The plan is as good as it is going to get but the thing that is concerning me, which hopefully some folks here may have some insight on, is the very large difference in runtime. three boxes: A: Intel(R) Xeon(R) CPU E5345 @ 2.33GHz (Runs query fastest) 4MB cache B: Quad-Core AMD Opteron(tm) Processor 2352 (2.1GHZ) (Main production box, currently, middle speed) 512k cache C: Quad-Core AMD Opteron(tm) Processor 2378 (2.4GHZ) 512k cache A B are running PG 8.4.2 (yes, I know it desperately need to be upgraded). C was also on 8.4.2 and since it was not in production I upgraded it to 8.4.7 and got the same performance as 8.4.2. Dataset on A B is the same C is mostly the same, but is missing a couple weeks of data (but since this query runs over 3 years of data, it is negligable - plus C runs the slowest!) All three running FC10 with kernel Linux db06 2.6.27.19-170.2.35.fc10.x86_64 #1 SMP Mon Feb 23 13:00:23 EST 2009 x86_64 x86_64 x86_64 GNU/Linux Load is very low on each box. The query is running from shared_buffers - no real IO is occuring. The average timing for the query in question is 90ms on A, 180ms on B and 190ms on C. Now here's where some odd stuff starts piling up: explain analyze overhead on said queries: 20ms on A, 50ms on B and 85ms on C(!!) We had one thought about potential NUMA issues, but doing a series (100) of connect, query, disconnect and looking at the timings reveals them all to be solid... but even still we wouldn't expect it to be that awful. The smaller cache of the opterons is also a valid argument. I know we're running an old kernel, I'm tempted to upgrade to see what will happen, but at the same time I'm afraid it'll upgrade to a kernel with a broken [insert major subsystem here] which has happened before. Anybody have some insight into this or run into this before? btw, little more background on the query: - Nested Loop (cost=5.87..2763.69 rows=9943 width=0) (actual time=0.571..2 74.750 rows=766 loops=1) Join Filter: (ce.eventdate = (md.date - '6 days'::interval)) - Nested Loop (cost=5.87..1717.98 rows=27 width=8) (actual time=0.53 3..8.301 rows=159 loops=1) [stuff removed here] - Index Scan using xxx_date_idx on xx md (cost=0.00..19.50 rows=1099 width=8) (actual time=0.023..0.729 rows=951 loops=15 9) Index Cond: (ce.eventdate = md.date) On all three boxes that inner nestloop completes in about the same amount of time - it is that join filter that is causing the pain and agony. (If you are noticing the timing differences, that is because the numbers above are the actual numbers, not explain analyze). The query is pulling up a rolling window of events that occured on a specific date. This query pulls up al the data for a period of time. ce.eventdate is indexed, and is used in the outer nestloop. Thinking more about what is going on cache thrashing is certainly a possibility. the amazing explain analyze overhead is also very curious - we all know it adds overhead, but 85ms? Yow. -- Jeff Trout j...@jefftrout.com http://www.stuarthamm.net/ http://www.dellsmartexitin.com/ I am sure you might have already checked for this, but just incase... Did you verify that no power savings stuff is turned on in the BIOS or at the kernel ? I have to set ours to something HP calls static high performance or something like that if I want boxes that are normally pretty idle to execute in a predictable fashion for sub second queries. I assume you checked with a steam benchmark results on the AMD machines to make sure they are getting in the ballpark of where they are supposed to ? -- 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] Xeon twice the performance of opteron
On Thu, Mar 17, 2011 at 9:13 AM, Jeff thres...@torgo.978.org wrote: hey folks, Running into some odd performance issues between a few of our db boxes. While trying to speed up a query I ran it on another box and it was twice as fast. The plans are identical and various portions of the query run in the same amount of time - it all boils down to most of the time being spent in a join filter. The plan is as good as it is going to get but the thing that is concerning me, which hopefully some folks here may have some insight on, is the very large difference in runtime. My experience puts the 23xx series opterons in a same general neighborhood as the E5300 and a little behind the E5400 series Xeons. OTOH, the newer Magny Cours Opterons stomp both of those into the ground. Do any of those machines have zone.reclaim.mode = 1 ??? i.e.: sysctl -a|grep zone.reclaim vm.zone_reclaim_mode = 0 I had a machine that had just high enough interzone communications cost to get it turned on by default and it slowed it right to a crawl under pgsql. -- 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] Request for feedback on hardware for a new database server
On Mar 17, 2011, at 5:51 PM, Oliver Charles wrote: Hello, At MusicBrainz we're looking to get a new database server, and are hoping to buy this in the next couple of days. I'm mostly a software guy, but I'm posting this on behalf of Rob, who's actually going to be buying the hardware. Here's a quote of what we're looking to get: I'm working to spec out a bad-ass 1U database server with loads of cores (12), RAM (24GB) and drives (4 SAS) in a hardware RAID-1,0 configuration: 1 * SuperMicro 2016R-URF, 1U, redundant power supply, 4 SATA/SAS drive bays 2 2 * Intel Xeon X5650 Westmere 2.66GHz 12MB L3 Cache LGA 1366 95W Six-Core Server Processor 2 2 * Crucial 24GB (3 x 4GB) DDR3 SDRAM ECC Registered DDR3 1333, CT3KIT51272BV1339 1 1 * LSI MegaRAID SATA/SAS 9260-4i ($379) (linux support [1]) or 1 * HighPoint RocketRAID 4320 PCI-Express x8 ($429) or 1 * Adaptec RAID 3405 controller ($354) 4 * Fujitsu MBA3147RC 147GB 15000 RPM That's it -- anyone have any feedback? I'm no expert, but... That's very few drives. Even if you turn them into a single array (rather than separating out a raid pair for OS and a raid pair for WAL and raid 10 array for data) that's going to give you very little IO bandwidth, especially for typical random access work. Unless your entire database active set fits in RAM I'd expect your cores to sit idle waiting on disk IO much of the time. Don't forget that you need a BBU for whichever RAID controller you need, or it won't be able to safely do writeback caching, and you'll lose a lot of the benefit. 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] Request for feedback on hardware for a new database server
On Thu, Mar 17, 2011 at 6:51 PM, Oliver Charles postgresql-p...@ocharles.org.uk wrote: Hello, At MusicBrainz we're looking to get a new database server, and are hoping to buy this in the next couple of days. I'm mostly a software guy, but I'm posting this on behalf of Rob, who's actually going to be buying the hardware. Here's a quote of what we're looking to get: I'm working to spec out a bad-ass 1U database server with loads of cores (12), RAM (24GB) and drives (4 SAS) in a hardware RAID-1,0 configuration: 1 * SuperMicro 2016R-URF, 1U, redundant power supply, 4 SATA/SAS drive bays 2 2 * Intel Xeon X5650 Westmere 2.66GHz 12MB L3 Cache LGA 1366 95W Six-Core Server Processor 2 2 * Crucial 24GB (3 x 4GB) DDR3 SDRAM ECC Registered DDR3 1333, CT3KIT51272BV1339 1 1 * LSI MegaRAID SATA/SAS 9260-4i ($379) (linux support [1]) or 1 * HighPoint RocketRAID 4320 PCI-Express x8 ($429) or 1 * Adaptec RAID 3405 controller ($354) 4 * Fujitsu MBA3147RC 147GB 15000 RPM SuperMicro machines have treated us really well over time (better than Dell or Sun boxes), so I am really happy to throw more money in their direction. Redundant power supplies seem like a good idea for a database server. For $400 more we can get hexa core processors as opposed to quad core processors at 2.66Ghz. This seems like a really good deal -- any thoughts on this? Crucial memory has also served us really well, so that is a no-brainer. The RAID controller cards are where I need to most feedback! Of the LSI, Highpoint or Adaptec cards, which one is likely to have native linux support that does not require custom drivers to be installed? The LSI card has great specs at a great price point with Linux support, but installing the custom driver sounds like a pain. Does anyone have any experience with these cards? We've opted to not go for SSD drives in the server just yet -- it doesn't seem clear how well SSDs do in a driver environment. That's it -- anyone have any feedback? Just a quick bit more information. Our database is certainly weighted towards being read heavy, rather than write heavy (with a read-only web service accounting for ~90% of our traffic). Our tables vary in size, with the upperbound being around 10mil rows. I'm not sure exactly what more to say - but any feedback is definitely appreciated. We're hoping to purchase this server on Monday, I believe. Any questions, ask away! I order my boxes from a white box builder called Aberdeen. They'll test whatever hardware you want with whatever OS you want to make sure it works before sending it out. As far as I know the LSI card should just work with linux, if not, the previous rev should work fine (the LSI ). I prefer Areca RAID 1680/1880 cards, they run cooler and faster than the LSIs. Another point. My experience with 1U chassis and cooling is that they don't move enough air across their cards to make sure they stay cool. You'd be better off ordering a 2U chassis with 8 3.5 drive bays so you can add drives later if you need to, and it'll provide more cooling air across the card. Our current big 48 core servers are running plain LSI SAS adapters without HW RAID because the LSI s we were using overheated and cooked themselves to death after about 3 months. Those are 1U chassis machines, and our newer machines are all 2U boxes now. BTW, if you ever need more than 2 sockets, right now the Magny Cours AMDs are the fastest in that arena. For 2 sockets the Nehalem based machines are about equal to them. The high point RAID controllers are toys (or at least they were last I checked). If you have to go with 4 drives just make it one big RAID-10 array and then partition that out into 3 or 4 partitions. It's important to put pg_xlog on a different partition even if it's on the same array, as it allows the OS to fsync it separately. -- 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] Help with Query Tuning
Thanks , it works now .. :-)
Here is the output :
pdc_uima=# SELECT count(*) from page_content WHERE publishing_date like
'%2010%' and
pdc_uima-# content_language='en' and content is not null and
isprocessable = 1 and
pdc_uima-# to_tsvector('english',content) @@
to_tsquery('english','Mujahid' || ' | '
pdc_uima(# || 'jihad' || ' | ' || 'Militant' || ' | ' || 'fedayeen' || ' | '
pdc_uima(# || 'insurgent' || ' | ' || 'terrORist' || ' | ' || 'cadre' ||
' | '
pdc_uima(# || 'civilians' || ' | ' || 'police' || ' | ' || 'cops' ||
'crpf' || ' | '
pdc_uima(# || 'defence' || ' | ' || 'dsf' || ' | ' || 'ssb' );
count
137193
(1 row)
Time: 195441.894 ms
But my original query is to use AND also i.e
select count(*) from page_content where publishing_date like '%2010%'
and content_language='en' and content is not null and isprocessable = 1
and (content like '%Militant%'
OR content like '%jihad%' OR content like '%Mujahid%' OR
content like '%fedayeen%' OR content like '%insurgent%' OR content
like '%terrORist%' OR
content like '%cadre%' OR content like '%civilians%' OR content like
'%police%' OR content like '%defence%' OR content like '%cops%' OR
content like '%crpf%' OR content like '%dsf%' OR content like '%ssb%')
AND (content like '%kill%' OR content like '%injure%');
count
---
57061
(1 row)
Time: 19423.087 ms
Now I have to add AND condition ( AND (content like '%kill%' OR content
like '%injure%') ) also.
Thanks Regards,
Adarsh Sharma
t...@fuzzy.cz wrote:
t...@fuzzy.cz wrote:
Yes , I think we caught the problem but it results in the below error :
SELECT count(*) from page_content
WHERE publishing_date like '%2010%' and content_language='en' and
content is not null and isprocessable = 1 and
to_tsvector('english',content) @@ to_tsquery('english','Mujahid ' ||
'jihad ' || 'Militant ' || 'fedayeen ' || 'insurgent ' || 'terrORist '
|| 'cadre ' || 'civilians ' || 'police ' || 'defence ' || 'cops ' ||
'crpf ' || 'dsf ' || 'ssb');
ERROR: syntax error in tsquery: Mujahid jihad Militant fedayeen
insurgent terrORist cadre civilians police defence cops crpf dsf ssb
The text passed to to_tsquery has to be a proper query, i.e. single
tokens
separated by boolean operators. In your case, you should put there '|'
(which means OR) to get something like this
'Mujahid | jihad | Militant | ...'
or you can use plainto_tsquery() as that accepts simple text, but it
puts
'' (AND) between the tokens and I guess that's not what you want.
Tomas
What to do to make it satisfies the OR condition to match any of the
to_tsquery values as we got it right through like '%Mujahid' or .
or
You can't force the plainto_tsquery to somehow use the OR instead of AND.
You need to modify the piece of code that produces the search text to put
there '|' characters. So do something like this
SELECT count(*) from page_content WHERE publishing_date like '%2010%' and
content_language='en' and content is not null and isprocessable = 1 and
to_tsvector('english',content) @@ to_tsquery('english','Mujahid' || ' | '
|| 'jihad' || ' | ' || 'Militant' || ' | ' || 'fedayeen);
Not sure where does this text come from, but you can do this in a higher
level language, e.g. in PHP. Something like this
$words = implode(' | ', explode(' ',$text));
and then pass the $words into the query. Or something like that.
Tomas
