Re: [PERFORM] Inconsistent performance

On Mon, 15 Sep 2003, scott.marlowe wrote: > On Mon, 15 Sep 2003, Joseph Bove wrote: > > > Stephan, > > > > I've run explain analyze a number of times and have gotten results between > > 5.5 and 7.5 seconds > > > > Att

Re: [PERFORM] Is there a reason _not_ to vacuum continuously?

On Wed, 17 Sep 2003, Matt Clark wrote: > *** THE QUESTION(S) *** > Is there any reason for me not to run continuous sequential vacuum analyzes? > At least for the 6 tables that see a lot of updates? > I hear 10% of tuples updated as a good time to vac-an, but does my typical > count of 3 indexes p

Re: [PERFORM] advice on raid controller

I've used the megaraid / LSI cards in the past and they were pretty good in terms of reliability, but the last one I used was the 328 model, from 4 years ago or so. that one had a battery backup option for the cache, and could go to 128 Meg. We tested it with 4/16 and 128 meg ram, and it was

Re: [PERFORM] advice on raid controller

On 29 Sep 2003, Will LaShell wrote: > On Mon, 2003-09-29 at 06:48, scott.marlowe wrote: > > I've used the megaraid / LSI cards in the past and they were pretty good > > in terms of reliability, but the last one I used was the 328 model, from 4 > > years ago or so. tha

Re: [PERFORM] inferior SCSI performance

On Wed, 1 Oct 2003, Andrew Sullivan wrote: > On Wed, Sep 17, 2003 at 04:46:00PM -0400, Michael Adler wrote: > > So the quesiton is whether it is ever sensible to use write-caching and > > expect comparable persistence. > > Yes. If and only if you have a battery-backed cache. I know of no > IDE

Re: [PERFORM] TPC-R benchmarks

On Tue, 30 Sep 2003, Oleg Lebedev wrote: > I continue struggling with the TPC-R benchmarks and wonder if anyone > could help me optimize the query below. ANALYZE statistics indicate that > the query should run relatively fast, but it takes hours to complete. I > attached the query plan to this pos

Re: [PERFORM] Optimizing >= and <= for numbers and dates

On Wed, 1 Oct 2003, Dimitri Nagiev wrote: > here goes the EXPLAIN ANALYZE output: > > > template1=# VACUUM analyze mytable; > VACUUM > template1=# explain analyze select * from mytable where > mydate>='2003-09-01'; > QUERY PLAN >

Re: [PERFORM] Optimizing >= and <= for numbers and dates

Oh, to followup on my previously sent post, make sure you've got effective_cache_size set right BEFORE you go trying to set random_page_cost, and you might wanna run a select * from table to load the table into kernel buffer cache before testing, then also test it with the cache cleared out (s

Re: [PERFORM] TPC-R benchmarks

-> Index Scan using > pk_supplier on supplier (cost=0.00..3.01 rows=1 width=8) (actual > time=0.08..0.08 rows=1 loops=348760) >Index Cond: > ("outer".ps_suppkey = supplier.s_suppkey) Total

Re: [PERFORM] TPC-R benchmarks

ent down from 2 > hours to 2 minutes. I attached the new query plan to this posting. > Is there any way to optimize it even further? > What should I do to make this query run fast without hurting the > performance of the other queries? > Thanks. > > Oleg > > -O

[PERFORM] further testing on IDE drives

I was testing to get some idea of how to speed up the speed of pgbench with IDE drives and the write caching turned off in Linux (i.e. hdparm -W0 /dev/hdx). The only parameter that seems to make a noticeable difference was setting wal_sync_method = open_sync. With it set to either fsync, or fd

Re: [PERFORM] TPC-R benchmarks

On Thu, 2 Oct 2003, Oleg Lebedev wrote: > I was trying to get the pg_stats information to Josh and decided to > recreate the indexes on all my tables. After that I ran vacuum full > analyze, re-enabled nestloop and ran explain analyze on the query. It > ran in about 2 minutes. > I attached the new

Re: [PERFORM] further testing on IDE drives

On Thu, 2 Oct 2003, scott.marlowe wrote: > I was testing to get some idea of how to speed up the speed of pgbench > with IDE drives and the write caching turned off in Linux (i.e. hdparm -W0 > /dev/hdx). > > The only parameter that seems to make a noticeable differe

Re: [PERFORM] Is This My Speed Limit?

On Thu, 2 Oct 2003, CN wrote: > Hi! > > It's just my curiosity. I wonder if there is any way to break my speed > limit on AMD 450Mhz: You're most likely I/O bound, not CPU bound here. So, if you want better speed, you'll likely need a better storage subsystem. ---(end

Re: [PERFORM] Postgres low end processing.

On Fri, 3 Oct 2003, Ron Johnson wrote: > On Fri, 2003-10-03 at 12:52, Stef wrote: > > On Fri, 03 Oct 2003 12:32:00 -0400 > > Tom Lane <[EMAIL PROTECTED]> wrote: > > > > => What exactly is failing? And what's the platform, anyway? > > > > Nothing is really failing atm, except the funds for bette

Re: [PERFORM] Shopping for hardware

On Mon, 6 Oct 2003, Jason Hihn wrote: > Ok, I asked this on [novice], but I was told it's be better to post it > here... > > I've got some money to spend on a new servers. The biggest concern is the > PostgreSQL database server that will "be the company." (*Everyone* uses the > database server in

Re: [PERFORM] Linux filesystem shootout

On Thu, 9 Oct 2003, Shridhar Daithankar wrote: > Kaarel wrote: > >>>http://www.ussg.iu.edu/hypermail/linux/kernel/0310.1/0208.html > >>> > >>>Shridhar > >>> > >>> > > I feel incompetent when it comes to file systems. Yet everybody would like to > > have the best file system if given the choic

Re: [pgsql-advocacy] [PERFORM] OFFTOPIC: PostgreSQL vs MySQL

On Thu, 9 Oct 2003, Josh Berkus wrote: > David Griffiths wrote: > > With regards to #1, I'd like to specifically mention tuning - the docs > > at http://www.postgresql.org/docs/7.3/static/runtime-config.html > > give a > > Have you

Re: [PERFORM] PostgreSQL vs MySQL

On Thu, 9 Oct 2003, Jeff wrote: > On Thu, 9 Oct 2003, David Griffiths wrote: > > > 1) the MySQL docs are better (sorry - I found them easier to read, and > > more comprehensive; I had an easier time finding the answers I needed) > > Huh. I had the opposite experience. Each to his own. > I think

Re: [PERFORM] further testing on IDE drives

Nope, write-cache enabled by default. On Thu, 9 Oct 2003, Bruce Momjian wrote: > > How did this drive come by default? Write-cache disabled? > > --- > > scott.marlowe wrote: > > On Thu, 2 Oct 2

Re: [PERFORM] further testing on IDE drives

On Thu, 9 Oct 2003, Bruce Momjian wrote: > scott.marlowe wrote: > > I was testing to get some idea of how to speed up the speed of pgbench > > with IDE drives and the write caching turned off in Linux (i.e. hdparm -W0 > > /dev/hdx). > > > > The only paramete

Re: [PERFORM] further testing on IDE drives

On Fri, 10 Oct 2003, Josh Berkus wrote: > Bruce, > > > Yes. If you were doing multiple WAL writes before transaction fsync, > > you would be fsyncing every write, rather than doing two writes and > > fsync'ing them both. I wonder if larger transactions would find > > open_sync slower? > > Want

Re: [PERFORM] further testing on IDE drives

On Fri, 10 Oct 2003, Josh Berkus wrote: > Bruce, > > > Yes. If you were doing multiple WAL writes before transaction fsync, > > you would be fsyncing every write, rather than doing two writes and > > fsync'ing them both. I wonder if larger transactions would find > > open_sync slower? > > Want

Re: [PERFORM] further testing on IDE drives

On Tue, 14 Oct 2003, Tom Lane wrote: > "scott.marlowe" <[EMAIL PROTECTED]> writes: > > open_sync was WAY faster at this than the other two methods. > > Do you not have open_datasync? That's the preferred method if > available. Nope, when I try to star

Re: [SQL] [PERFORM] sql performance and cache

On Tue, 14 Oct 2003, Wei Weng wrote: > On Sat, 11 Oct 2003, Christopher Kings-Lynne wrote: > > > > > > I have two very similar queries which I need to execute. They both have > > > exactly the same from / where conditions. When I execute the first, it takes > > > about 16 seconds. The second is

Re: [PERFORM] PostgreSQL data on a NAS device ?

On Mon, 20 Oct 2003, Alexander Priem wrote: > Hi all, > > Does anyone have any experience with putting PostgreSQL data on a NAS > device? > > I am asking this because a NAS device is much cheaper to set up than a > couple of SCSI disks. I would like to use a relatively cheap NAS device > which u

Re: [PERFORM] PostgreSQL data on a NAS device ?

On Tue, 21 Oct 2003, Alexander Priem wrote: > The machine is going to be used for a pretty large database (well over 100 > tables with some of them containing over a million records from the start, > number of tables and records will grow (much?) larger in the future). This > database is going to

Re: [PERFORM] Tuning for mid-size server

On Tue, 21 Oct 2003, Josh Berkus wrote: > Anjan, > > > Pretty soon, a PowerEdge 6650 with 4 x 2Ghz XEONs, and 8GB Memory, with > > internal drives on RAID5 will be delivered. Postgres will be from RH8.0. > > How many drives? RAID5 sucks for heavy read-write databases, unless you have > 5+ dri

Re: [PERFORM] Tuning for mid-size server

On Tue, 21 Oct 2003, Josh Berkus wrote: > Scott, > > > Also, if it's a read only environment, RAID5 with n drives equals the > > performance of RAID0 with n-1 drives. > > True. > > > Josh, you gotta get out more. IA32 has supported >4 gig ram for a long > > time now, and so has the linux kerne

Re: [PERFORM] Tuning for mid-size server

On Tue, 21 Oct 2003, Josh Berkus wrote: > Anjan, > > > From what I know, there is a cache-row-set functionality that doesn't > > exist with the newer postgres... > > What? PostgreSQL has always used the kernel cache for queries. > > > Concurrent users will start from 1 to a high of 5000 or mor

[PERFORM] RAID controllers etc... was: PostgreSQL data on a NAS device ?

On 21 Oct 2003, Will LaShell wrote: > On Tue, 2003-10-21 at 08:40, scott.marlowe wrote: > > > So that brings up my question, which is better, the Perc4 or Perc3 > > controllers, and what's the difference between them? I find Dell's > > tendency to hide other

Re: [PERFORM] Tuning for mid-size server

On Tue, 21 Oct 2003, Andrew Sullivan wrote: > On Tue, Oct 21, 2003 at 11:51:02AM -0700, Josh Berkus wrote: > > > Of course, if you have anecdotal evidence to the contrary, then the > > only way to work this would be to have OSDL help us sort it out. > > Nope. I too have such anecdotal evidence

Re: [PERFORM] Processors vs Memory

On Wed, 22 Oct 2003, Hilary Forbes wrote: > If I have a fixed amount of money to spend as a general rule is it > better to buy one processor and lots of memory or two processors and > less memory for a system which is transactional based (in this case > it's handling reservations). I realise t

Re: [PERFORM] RAID controllers etc... was: PostgreSQL data on aNAS

On Thu, 23 Oct 2003, Alexander Priem wrote: > I have been searching (www.lsil.com) for this megaraid_2 driver you > mentioned. > > What kind of MegaRaid card does the Perc4/Di match? Elite1600? Elite1650? > > I picked Elite1600 and the latest driver I found was version 2.05.00. Is > this one OK

pgsql-performance@postgresql.org

On Fri, 24 Oct 2003, Vivek Khera wrote: > > "AL" == Allen Landsidel <[EMAIL PROTECTED]> writes: > > AL> Well I had the vacuums running every 15 minutes for a while.. via a > AL> simple cron script I wrote just to make sure no more than one vacuum > AL> ran at once, and to 'nice' the job.. but

Re: [PERFORM] PostgreSQL 7.4beta5 vs MySQL 4.0.16 with

On Thu, 30 Oct 2003 [EMAIL PROTECTED] wrote: > >> So its not just PostgreSQL that is suffering from the bad SQL but MySQL also. > >> But the > >> question is my does PostgreSQL suffer so badly ?? I think not all developers > >> write very nice > >> SQLs. > >> > >> Its really sad to see that a

Re: [PERFORM] vacuum locking

On Wed, 29 Oct 2003, Rob Nagler wrote: > Greg Stark writes: > > > > SELECT a, (SELECT name FROM t2 WHERE t2.f2 = t1.f2) > > > > FROM t1 > > > > GROUP BY f2 > > > > > > This doesn't solve the problem. It's the GROUP BY that is doing the > > > wrong thing. It's grouping, then aggregating. > >

Re: [PERFORM] vacuum locking

On Thu, 30 Oct 2003, Rob Nagler wrote: > The vacuum problem is very serious for the problematic database to the > point that one of my customer's customers said: > > However, I am having a hard time understanding why the system is so > slow... from my perspective it seems like you have so

Re: [PERFORM] vacuum locking

On Thu, 30 Oct 2003, Rob Nagler wrote: > scott.marlowe writes: > > t2 was 'vacuum full'ed and analyzed, right? Just guessing. > > Fresh import. I've been told this includes a ANALYZE. You should probably run analyze by hand just to be sure. If the planner is

Re: [PERFORM] Pg+Linux swap use

On Thu, 30 Oct 2003, alexandre :: aldeia digital wrote: > Hi, > > Old: Post 7.3.2, P4 1.8, 1 MB RAM, 2 x IDE SW RAID 1, RedHat 8 > New: Post 7.3.4, Xeon 2.4, 1 MB RAM, 2 x SCSI 15k SW RAID 1, RedHat 9 > > Both use: Only postgresql on server. Buffers = 8192, effective cache = 10 > > In old p

Re: [PERFORM] Pg+Linux swap use

We had a problem at work that when a windows box would connect to a samba share with a lot of files in it, the kswapd was going nuts, even though we weren't low on memory at all. Updating to the 2.4.18 or so of the later redhats fixed that issue. It might be related. I think the kflush daemon

Re: [PERFORM] *very* slow query to summarize data for a month ...

On 11 Nov 2003, Greg Stark wrote: > "Marc G. Fournier" <[EMAIL PROTECTED]> writes: > > > On Tue, 11 Nov 2003, Greg Stark wrote: > > > > > Actually you might be able to get the same effect using function indexes > > > like: > > > > > > create index i on traffic_log (month_trunc(runtime), company_

Re: [PERFORM] Value of Quad vs. Dual Processor machine

On Tue, 11 Nov 2003, Rod Taylor wrote: > On Tue, 2003-11-11 at 18:32, Chris Field wrote: > > We are getting ready to spec out a new machine and are wondering about > > the wisdom of buying a quad versus a dual processor machine. Seing as > > how postgres in not a threaded application, and this se

Re: [PERFORM] Value of Quad vs. Dual Processor machine

As another post pointed out, you need to set cflags to get optimization under Solaris on that flavor of Postgresql. Also, Postgresql tends to get its best performance from the free unixes, Linux and BSD. those are available for Sun Sparcs, but postgresql in 64 bit mode on those boxes is still

Re: [PERFORM] Minimum hardware requirements for Postgresql db

On Wed, 3 Dec 2003 [EMAIL PROTECTED] wrote: > Dear all > > We would be recommending to our ct. on the use of Postgresql db as compared > to MS SQL Server. We are targetting to use Redhat Linux ES v2.1, Postgresql > v7.3.4 and Postgresql ODBC 07.03.0100. > > We would like to know the minimum spec

Re: [PERFORM] tuning questions

On Thu, 4 Dec 2003, Jack Coates wrote: > Another problem is that performance of the 6 million row job is decent > if I stop the job and run a vacuumdb --analyze before letting it > continue; is this something that 7.4 will help with? vacuumb --analyze > doesn't seem to have much effect on the 18 m

Re: [PERFORM] tuning questions

On Thu, 4 Dec 2003, Josh Berkus wrote: > Scott, > > > Just to add to what the others have said here, you probably want to run > > the pg_autovacuum daemon in the background. It comes with 7.4 but will > > work fine with 7.3. > > I don't recommend using pg_autovacuum with a data transformati

Re: [PERFORM] tuning questions

On Thu, 4 Dec 2003, Jack Coates wrote: > On Thu, 2003-12-04 at 12:27, Richard Huxton wrote: > > On Thursday 04 December 2003 19:50, Jack Coates wrote: > > > > > > I'm trying to set Postgres's shared memory usage in a fashion that > > > allows it to return requested results quickly. Unfortunately,

Re: [PERFORM] Hardware suggestions for Linux/PGSQL server

Just one more piece of advice, you might want to look into a good battery backed cache hardware RAID controller. They work quite well for heavily updated databases. The more drives you throw at the RAID array the faster it will be. ---(end of broadcast)--

Re: [PERFORM] [ADMIN] ODBC Driver generates a too big "windows swap file" and

On Fri, 12 Dec 2003, Rhaoni Chiu Pereira wrote: Hi, is there a switch in your pgsql/odbc connector to enable cursors? If so, try turning that on. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.post

Re: [PERFORM] a lot of problems with pg 7.4

On Sat, 13 Dec 2003, Kari Lavikka wrote: > > Hi! > > We have been running a rather busy website using pg 7.3 as the database. > Peak hitrate is something like 120 request / second without images and > other static stuff. The site is a sort of image gallery for IRC users. > > I evaluated pg 7.4

Re: [PERFORM] general peformance question

On Thu, 18 Dec 2003, Conny Thimren wrote: > Hi, > This a kind of newbie-question. I've been using Postgres for a long time in a low > transction environment - and it is great. > > Now I've got an inquiry for using Postgresql in a heavy-load on-line system. This > system must handle something li

Re: [PERFORM] Index creation

On Wed, 7 Jan 2004, Eric Jain wrote: > Any tips for speeding up index creation? > > I need to bulk load a large table with 100M rows and several indexes, > some of which span two columns. > > By dropping all indexes prior to issuing the 'copy from' command, the > operation completes 10x as fast

Re: [PERFORM] COUNT & Pagination

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 d

Re: [PERFORM] 100 simultaneous connections, critical limit?

On Wed, 14 Jan 2004, Jón Ragnarsson wrote: > I am writing a website that will probably have some traffic. > Right now I wrap every .php page in pg_connect() and pg_close(). > Then I read somewhere that Postgres only supports 100 simultaneous > connections (default). Is that a limitation? Should I

Re: [PERFORM] 100 simultaneous connections, critical limit?

On Wed, 14 Jan 2004, Adam Alkins wrote: > scott.marlowe wrote: > > >A few tips from an old PHP/Apache/Postgresql developer. > > > >1: Avoid pg_pconnect unless you are certain you have load tested the > >system and it will behave properly. pg_pconnect often cr

Re: [PERFORM] COUNT & Pagination

On Wed, 14 Jan 2004, Rajesh Kumar Mallah wrote: > 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

Re: [PERFORM] shared_buffer value

On Fri, 16 Jan 2004, Anjan Dave wrote: > 68 processes: 67 sleeping, 1 running, 0 zombie, 0 stopped > CPU0 states: 3.1% user 4.4% system0.0% nice 0.0% iowait 92.0% > idle > CPU1 states: 0.0% user 3.2% system0.0% nice 0.0% iowait 96.3% > idle > CPU2 states: 0.4% user 0.3% s

Re: [PERFORM] "Overlaping" indexes

On Mon, 2 Feb 2004, Tomasz Myrta wrote: > Dnia 2004-02-02 15:46, U?ytkownik Rigmor Ukuhe napisa3: > > Hi, > > > > I have many indexes somehow overlaping like: > > ... btree ("STATUS", "VISIBLE", "NP_ID"); > > ... btree ("STATUS", "VISIBLE"); > > > > is perfomance gained by "more exact" index wor

Re: [PERFORM] "Overlaping" indexes

On Mon, 2 Feb 2004, Tomasz Myrta wrote: > Dnia 2004-02-02 19:30, U¿ytkownik scott.marlowe napisa³: > > Not entirely, since it only has to sort two columns, it will be smaller, > > and will therefore be somewhat faster. > > Can you say something more about it? Will it be

inserting large number of rows was: Re: [PERFORM] Increasing number of PG connections.

On Mon, 2 Feb 2004, Qing Zhao wrote: > I am new here. I have a question related to this in some way. > > Our web site needs to upload a large volume of data into Postgres at a > time. The performance deterioates as number of rows becomes larger. > When it reaches 2500 rows, it never come back

Re: [PERFORM] Increasing number of PG connections.

On Mon, 2 Feb 2004, Kevin Barnard wrote: > I am running a Dual Xeon hyperthreaded server with 4GB RAM RAID-5. The only > thing running on the server is Postgres running under Fedora. I have a 700 > connection limit. > > The DB is setup as a backend for a very high volume website. Most of the

Re: [PERFORM] Increasing number of PG connections.

On Mon, 2 Feb 2004, Kevin Barnard wrote: > On 2 Feb 2004 at 13:58, scott.marlowe wrote: > > > what do you mean at 2 GB? Is that how much is in kernel cache plus > > buffer, plus used, plus etc??? Could you give us the top of top output to > > make sure? If most of th

Re: [PERFORM] [pgsql-advocacy] MySQL+InnoDB vs. PostgreSQL test?

On Tue, 3 Feb 2004, Christopher Kings-Lynne wrote: > > One more thing that annoyed me. If you started a process, such as a > > large DDL operation, or heaven forbid, a cartesian join (what? I never > > do that!). > > I believe InnoDB also has O(n) rollback time. eg. if you are rolling > bac

Re: [PERFORM] Database conversion woes...

On Tue, 3 Feb 2004, Kevin Carpenter wrote: > Hello everyone, > > I am doing a massive database conversion from MySQL to Postgresql for a > company I am working for. This has a few quirks to it that I haven't > been able to nail down the answers I need from reading and searching > through previou

Re: [PERFORM] Compile Vs RPMs

On Tue, 3 Feb 2004, Christopher Browne wrote: > [EMAIL PROTECTED] ("Anjan Dave") writes: > > I would like to know whether there are any significant performance > > advantages of compiling (say, 7.4) on your platform (being RH7.3, 8, > > and 9.0, and Fedora especially) versus getting the relevant b

Re: [PERFORM] select count(*) from anIntColumn where int_value = 0;

On Wed, 11 Feb 2004, David Teran wrote: > Hi, > > > Is your int_value data type int4? If not then use "... from > > job_property > > where int_value = '0'" > > Indexes are used only if datatypes matches. > > > tried those variations already. Strange enough, after dropping and > recreating the i

Re: [PERFORM] update performance

On Wed, 11 Feb 2004, stefan bogdan wrote: > hello > i have postgres 7.3.2.,linux redhat 9.0 > a database,and 20 tables > a lot of fields are char(x) > when i have to make update for all the fields except index > postgres works verry hard > what should i've changed in configuration to make it work

Re: [PERFORM] slow database

If my boss came to me and asked me to make my database server run as slowly as possible, I might come up with the exact same postgresql.conf file as what you posted. Just installing the default postgresql.conf that came with postgresql should make this machine run faster. Read this: http://ww

Re: [PERFORM] update performance

On Wed, 11 Feb 2004, scott.marlowe wrote: > On Wed, 11 Feb 2004, stefan bogdan wrote: > > > hello > > i have postgres 7.3.2.,linux redhat 9.0 > > a database,and 20 tables > > a lot of fields are char(x) > > when i have to make update for all the fields except

Re: [PERFORM] slow database

On Wed, 11 Feb 2004 [EMAIL PROTECTED] wrote: > I already came back the old conditions and I continue slow in the same > way! OK, we need some things from you to help troubleshoot this problem. Postgresql version schema of your tables output of "explain analyze your query here" a chicken foot (ha

Re: [PERFORM] slow database

ENCES public.fabricante > (cd_fabricante) ON UPDATE NO ACTION ON DELETE NO ACTION, > CONSTRAINT "$7" FOREIGN KEY (cd_produto, cd_fornecedor) REFERENCES > public.fornecedor_produto (cd_produto, cd_fornecedor) ON UPDATE NO ACTION ON > DELETE NO ACTION, > CONSTRAINT "$8"

Re: [PERFORM] Disappointing performance in db migrated from MS SQL

On Thu, 12 Feb 2004, Leon Out wrote: > Hello all. I am in the midst of porting a large web application from a > MS SQL Server backend to PostgreSQL. The migration work is basically > complete, and we're at the testing and optimization phase of the > project. The results so far have been disappo

Re: [PERFORM] Slow response of PostgreSQL

Easy two step procedure for speeding this up: 1: Upgrade to 7.4.1 2: Read this: http://www.varlena.com/varlena/GeneralBits/Tidbits/perf.html ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.po

Re: [PERFORM] Tables on multiple disk drives

On Tue, 17 Feb 2004, Konstantin Tokar wrote: > Hi! > Does PostgreSQL allow to create tables and indices of a single > database on multiple disk drives with a purpose of increase > performance as Oracle database does? If a symbolic reference is the > only method then the next question is: how can i

Re: [PERFORM] Tables on multiple disk drives

On Tue, 17 Feb 2004, Craig Thomas wrote: > > On Tue, 17 Feb 2004, Craig Thomas wrote: > > > >> > On Tue, 17 Feb 2004, Konstantin Tokar wrote: > >> > > >> >> Hi! > >> >> Does PostgreSQL allow to create tables and indices of a single > >> database on multiple disk drives with a purpose of increase >

Re: [PERFORM] Slow response of PostgreSQL

On Wed, 18 Feb 2004, Christopher Kings-Lynne wrote: > >>> 1- How can I lock a single record so that other users can only read > >>> it. ?? > >> > >> > >> You cannot do that in PostgreSQL. > > > > > > How about SELECT ... FOR UPDATE? > > No, because users cannot read the locked row in that case

Re: [PERFORM] General performance questions about postgres on Apple

On Sun, 22 Feb 2004, Sean Shanny wrote: > Tom, > > We have the following setting for random page cost: > > random_page_cost = 1# units are one sequential page fetch cost > > Any suggestions on what to bump it up to? > > We are waiting to hear back from Apple on the speed issues, so

Re: [PERFORM] FreeBSD config

On Thu, 26 Feb 2004, Dror Matalon wrote: > Hi, > > We have postgres running on freebsd 4.9 with 2 Gigs of memory. As per > repeated advice on the mailing lists we configured effective_cache_size > = 25520 which you get by doing `sysctl -n vfs.hibufspace` / 8192 > > Which results in using 200Megs

Re: [PERFORM] A cache for the results of queries ?

On Thu, 26 Feb 2004, David Pradier wrote: > Hi everybody, > > i'd like to know if it exists a system of cache for the results of > queries. I believe there are some external libs that provide this at the application level. PHP's adodb is purported to do so. ---(end of

Re: [PERFORM] FreeBSD config

On Thu, 26 Feb 2004, Dror Matalon wrote: > On Thu, Feb 26, 2004 at 11:55:31AM -0700, scott.marlowe wrote: > > On Thu, 26 Feb 2004, Dror Matalon wrote: > > > > > Hi, > > > > > > We have postgres running on freebsd 4.9 with 2 Gigs of memory. As per >

Re: [PERFORM] FreeBSD config

On Fri, 27 Feb 2004, Shridhar Daithankar wrote: > Dror Matalon wrote: > > > Let me try and say it again. I know that setting effective_cache_size > > doesn't affect the OS' cache. I know it just gives Postgres the *idea* > > of how much cache the OS is using. I know that. I also know that a > > c

Re: [PERFORM] Scaling further up

On Tue, 2 Mar 2004, Anjan Dave wrote: > "By lots I mean dozen(s) in a raid 10 array with a good controller." > > I believe, for RAID-10, I will need even number of drives. Correct. > Currently, > the size of the database is about 13GB, and is not expected to grow > exponentially with thousands

Re: [PERFORM] Scaling further up

On Tue, 2 Mar 2004, Anjan Dave wrote: > That was part of my original question - whether it makes sense to go for > a mid-range SunFire machine (64bit HW, 64bit OS), which is scalable to > high amounts of memory, and shouldn't have any issues addressing it all. > I've had that kind of setup once te

Re: [PERFORM] Scaling further up

On Wed, 3 Mar 2004, Paul Thomas wrote: > > On 02/03/2004 23:25 johnn wrote: > > [snip] > > random_page_cost should be set with the following things taken into > > account: > > - seek speed > > Which is not exactly the same thing as spindle speed as it's a combination > of spindle speed an

Re: [PERFORM] Scaling further up

On Thu, 4 Mar 2004, Paul Thomas wrote: > On 03/03/2004 18:23 scott.marlowe wrote: > > [snip] > > There are three factors that affect how fast you can get to the next > > sector: > > > > seek time > > settle time > > rotational latency > > >

Re: [PERFORM] optimizing large query with IN (...)

On Wed, 10 Mar 2004, Marcus Andree S. Magalhaes wrote: > > Guys, > > I got a Java program to tune. It connects to a 7.4.1 postgresql server > running Linux using JDBC. > > The program needs to update a counter on a somewhat large number of > rows, about 1200 on a ~130k rows table. The query is

<    1   2