Re: [PERFORM] System Requirement
On 6/13/05, Saranya Sivakumar <[EMAIL PROTECTED]> wrote: > 2 x 2.4 Ghz Intel Xeon CPU with HT(4 virtual CPUs) switch to amd opteron (dual cpu). for the same price you get 2x performance - comparing to xeon boxes. > RAM - 1GB you'd definitelly could use more ram. the more the better. > HDD - 34GB SCSI is it one drive of 34G? if yes, buy another one and setup raid1 over them. should boost performance as well. > Production DB size: 10.89 GB not much. you could even consider buing 12 or 16g of ram to make it fit in memory. depesz ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] System Requirement
Hi, Thanks for the advice on increasing RAM and switching to AMD processors. To tell more about our DB: Our DB is transaction intensive. Interaction to the DB is through a web based application. Typically at any instant there are over 100 users using the application and we have customers worldwide. The DB size is 10.89 GB with 250+ tables. Also, regarding upgrading to 8.0, it is better to first upgrade to 7.4 (from 7.3.2--current version on production) and then upgrade to 8.0. Am I right? Thanks, Saranya__Do You Yahoo!?Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
[PERFORM] regular expression search
I search for particular strings using regular expressions (e.g. where column ~* $query) through a text data type column which contains notes (some html code like bold is included). It works but my question is whether there would be a way to speed up searches? >From my limited investigation, I see the features "CREATE INDEX" and "tsearch2" but I'm not clear on how these work, whether they would be appropriate, and whether there would be a better approach. I'd appreciate being pointed in the right direction. Pierre ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [PERFORM] Most effective tuning choices for busy website?
Neil Conway wrote: > Mark Stosberg wrote: >> I've used PQA to analyze my queries and happy overall with how they are >> running. About 55% of the query time is going to variations of the pet >> searching query, which seems like where it should be going. The query is >> frequent and complex. It has already been combed over for appropriate >> indexing. > > It might be worth posting the EXPLAIN ANALYZE and relevant schema > definitions for this query, in case there is additional room for > optimization. > >>Our hardware: Dual 3 Ghz processors 3 GB RAM, running on FreeBSD. > > Disk? > > You are presumably using Xeon processors, right? If so, check the list > archives for information on the infamous "context switching storm" that > causes performance problems for some people using SMP Xeons. I wanted to follow-up to report a positive outcome to tuning this Xeon SMP machine on FreeBSD. We applied the following techniques, and saw the average CPU usage drop by about 25%. - in /etc/sysctl.conf, we set it to use raw RAM for shared memory: kern.ipc.shm_use_phys=1 - We updated our kernel config and postmaster.conf to set shared_buffers to about 8000. - We disabled hyperthreading in the BIOS, which had a label like "Logical Processors? : Disabled". I recall there was tweak my co-worker made that's not on my list. I realize it's not particularly scientific because we changed several things at once...but at least it is working well enough for now. Mark ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] regular expression search
Just read the docs in contrib/tsearch2 in the PostgreSQL distribution. Pierre A. Fortier wrote: I search for particular strings using regular expressions (e.g. where column ~* $query) through a text data type column which contains notes (some html code like bold is included). It works but my question is whether there would be a way to speed up searches? From my limited investigation, I see the features "CREATE INDEX" and "tsearch2" but I'm not clear on how these work, whether they would be appropriate, and whether there would be a better approach. I'd appreciate being pointed in the right direction. Pierre ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[PERFORM] SELECT LIMIT 1 VIEW Performance Issue
Hi All, I previously posted the following as a sequel to my SELECT DISTINCT Performance Issue question. We would most appreciate any clue or suggestions on how to overcome this show-stopping issue. We are using 8.0.3 on Windows. Is it a known limitation when using a view with SELECT ... LIMIT 1? Would the forthcoming performance enhancement with MAX help when used within a view, as in: create or replace view VCurPlayer as select * from Player a where a.AtDate = (select Max(b.AtDate) from Player b where a.PlayerID = b.PlayerID); select PlayerID,AtDate from VCurPlayer where PlayerID='0'; Thanks and regards, KC. - At 19:45 05/06/06, PFC wrote: Previously, we have also tried to use LIMIT 1 instead of DISTINCT, but the performance was no better: select PlayerID,AtDate from Player where PlayerID='0' order by PlayerID desc, AtDate desc LIMIT 1 The DISTINCT query will pull out all the rows and keep only one, so the one with LIMIT should be faster. Can you post explain analyze of the LIMIT query ? Actually the problem with LIMIT 1 query is when we use views with the LIMIT 1 construct. The direct SQL is ok: esdt=> explain analyze select PlayerID,AtDate from Player where PlayerID='0' order by PlayerID desc, AtDate desc LIMIT 1; Limit (cost=0.00..1.37 rows=1 width=23) (actual time=0.000..0.000 rows=1 loops=1) -> Index Scan Backward using pk_player on player (cost=0.00..16074.23 rows=11770 width=23) (actual time=0.000..0.000 rows=1 loops=1) Index Cond: ((playerid)::text = '0'::text) Total runtime: 0.000 ms esdt=> create or replace view VCurPlayer3 as select * from Player a where AtDate = (select b.AtDate from Player b where a.PlayerID = b.PlayerID order by b.PlayerID desc, b.AtDate desc LIMIT 1); esdt=> explain analyze select PlayerID,AtDate,version from VCurPlayer3 where PlayerID='0'; Index Scan using pk_player on player a (cost=0.00..33072.78 rows=59 width=27) (actual time=235.000..235.000 rows=1 loops=1) Index Cond: ((playerid)::text = '0'::text) Filter: ((atdate)::text = ((subplan))::text) SubPlan -> Limit (cost=0.00..1.44 rows=1 width=23) (actual time=0.117..0.117 rows=1 loops=1743) -> Index Scan Backward using pk_player on player b (cost=0.00..14023.67 rows=9727 width=23) (actual time=0.108..0.108 rows=1 loops=1743) Index Cond: (($0)::text = (playerid)::text) Total runtime: 235.000 ms The problem appears to be in the loops=1743 scanning all 1743 data records for that player. Regards, KC. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
