Re: [PERFORM] mysql to postgresql, performance questions

On 03/18/2010 09:31 AM, Corin wrote: Hi all, I'm running quite a large social community website (250k users, 16gb database). We are currently preparing a complete relaunch and thinking about switching from mysql 5.1.37 innodb to postgresql 8.4.2. The database server is a dual dualcore operton 22

Re: [PERFORM] Database size growing over time and leads to performance impact

On 03/27/2010 08:00 AM, Gnanakumar wrote: Hi, We're using PostgreSQL 8.2. Recently, in our production database, there was a severe performance impact.. Even though, we're regularly doing both: 1. VACUUM FULL ANALYZE once in a week during low-usage time and 2. ANALYZE everyday at low-usage time

Re: [PERFORM] Performance regarding LIKE searches

On 3/29/2010 12:23 PM, randa...@bioinfo.wsu.edu wrote: Tom, We are using perl 5.10 with postgresql DBD. Can you point me in the right direction in terms of unamed and named prepared statements? Thanks, Randall Svancara Systems Administrator/DBA/Developer Main Bioinformatics Laboratory ---

Re: [PERFORM] Database size growing over time and leads to performance impact

On 3/30/2010 6:17 AM, Gnanakumar wrote: We're using pgpool-II version 2.0.1 for PostgreSQL connection management. pgpool configurations are: num_init_children = 450 child_life_time = 300 connection_life_time = 120 child_max_connections = 30 As you recommended, I ran "ps -ax|grep postgres" at al

Re: [PERFORM] REINDEXing database-wide daily

On 3/30/2010 4:32 AM, Gnanakumar wrote: Hi, We're using PostgreSQL 8.2. I have a question in connection to this question posted by me earlier: http://archives.postgresql.org/pgsql-performance/2010-03/msg00343.php In our application, DML operations (INSERT/UPDATE/DELETE) are heavily performed

Re: [PERFORM] How check execution plan of a function

On Wed Apr 7 2010 7:47 AM, Sabin Coanda wrote: Hi there, I have a function which returns setof record based on a specific query. I try to check the execution plan of that query, so I write EXPLAIN ANALYZE before my select, I call the function and I see the result which shows an actual time about

Re: [PERFORM] How check execution plan of a function

On Fri Apr 9 2010 8:18 AM, Sabin Coanda wrote: I have just a function returning a cursor based on a single coplex query. When I check the execution plan of that query it takes about 3 seconds. Just when it is used inside the function it freezes. This is the problem, and this is the reason I cann

Re: [PERFORM] Slow Bulk Delete

On 05/08/2010 06:39 AM, thilo wrote: Hi all! We moved from MySQL to Postgresql for some of our projects. So far we're very impressed with the performance (especially INSERTs and UPDATEs), except for a strange problem with the following bulk delete query: DELETE FROM table1 WHERE table2_id = ?

Re: [PERFORM] slow query performance

On 6/3/2010 12:47 PM, Anj Adu wrote: I cant seem to pinpoint why this query is slow . No full table scans are being done. The hash join is taking maximum time. The table dev4_act_action has only 3 rows. box is a 2 cpu quad core intel 5430 with 32G RAM... Postgres 8.4.0 1G work_mem 20G effective_

Re: [PERFORM] How to insert a bulk of data with unique-violations very fast

On 06/01/2010 10:03 AM, Torsten Zühlsdorff wrote: Hello, i have a set of unique data which about 150.000.000 rows. Regullary i get a list of data, which contains multiple times of rows than the already stored one. Often around 2.000.000.000 rows. Within this rows are many duplicates and often th

Re: [PERFORM] Analysis Function

On 06/10/2010 07:41 PM, David Jarvis wrote: Hi, I found a slow part of the query: SELECT * date(extract(YEAR FROM m.taken)||'-1-1') d1,* * date(extract(YEAR FROM m.taken)||'-1-31') d2* FROM climate.city c, climate.station s, climate.station_category sc, climate.measurement m WHERE

Re: [PERFORM] query tuning help

On 06/14/2010 05:41 AM, AI Rumman wrote: Can any one please help me in tuning the query? explain select * from (select * from crmentity where deleted = 0 and createdtime between (now() - interval '6 month') and now() ) as crmentity inner join (select * from activity where activitytype = 'Emails

Re: [PERFORM] Dead lock

On 06/14/2010 06:50 AM, Elias Ghanem wrote: Hi all, I have 2 data bases trying to perform an update query at the same time on a same table in a third data base using db link. I'm getting a dead lock exception: ERROR: deadlock detected DETAIL: Process 27305 waits for ShareLock on transaction 55575

Re: [PERFORM] performance on new linux box

On 07/07/2010 06:06 PM, Ryan Wexler wrote: Postgresql was previously running on a single cpu linux machine with 2 gigs of memory and a single sata drive (v8.3). Basically a desktop with linux on it. I experienced slow performance. So, I finally moved it to a real server. A dually zeon cento

Re: [PERFORM] Queries with conditions using bitand operator

On 07/13/2010 06:48 AM, Elias Ghanem wrote: Hi, I have table "ARTICLE" containing a String a field "STATUS" that represents a number in binary format (for ex: 10011101). My application issues queries with where conditions that uses BITAND operator on this field (for ex: select * from article wh

Re: [PERFORM] performance on new linux box

On 07/11/2010 03:02 PM, Ryan Wexler wrote: Well I got me a new raid card, MegaRAID 8708EM2, fully equipped with BBU and read and write caching are enabled. It completely solved my performance problems. Now everything is way faster than the previous server. Thanks for all the help everyone.

Re: [PERFORM] Identical query slower on 8.4 vs 8.3

FULL is usually bad. Stick to "vacuum analyze" and drop the full. Do you have indexes on: test.tid, testresult.fk_tid, questionresult.fk_trid and testresult.trid -Andy On 7/15/2010 10:12 AM, Patrick Donlin wrote: I'll read over that wiki entry, but for now here is the EXPLAIN ANALYZE outp

Re: [PERFORM] Using more tha one index per table

On 7/21/2010 2:31 AM, Elias Ghanem wrote: Hi, I have a question concerning the uses of indexes in Postgresql. I red that in PG a query can not use more than one index per table: "a query or data manipulation command can use at most one index per table". Actually I found this a little weird and un

Re: [PERFORM] Simple (hopefully) throughput question?

On 11/3/2010 10:52 AM, Nick Matheson wrote: Hello We have an application that needs to do bulk reads of ENTIRE Postgres tables very quickly (i.e. select * from table). We have observed that such sequential scans run two orders of magnitude slower than observed raw disk reads (5 MB/s versus 100 M

Re: [PERFORM] Huge overestimation in rows expected results in bad plan

On 11/9/2010 3:26 PM, bricklen wrote: Hi, I have a query that is getting a pretty bad plan due to a massively incorrect count of expected rows. All tables in the query were vacuum analyzed right before the query was tested. Disabling nested loops gives a significantly faster result (4s vs 292s).

Re: [PERFORM] MVCC performance issue

On 11/12/2010 7:47 AM, Kyriacos Kyriacou wrote: SUGGESTION -- 1) When a raw UPDATE is performed, store all "new raw versions" either in separate temporary table space or in a reserved space at the end of each table (can be allocated dynamically) etc Your use of "raw" is confusi

Re: [PERFORM] Running PostgreSQL as fast as possible no matter the consequences

On 11/15/2010 9:06 AM, Robert Haas wrote: In 9.1, I'm hopeful that we'll have unlogged tables, which will even better than turning these parameters off, and for which I just posted a patch to -hackers. Instead of generating WAL and writing WAL to the OS and then NOT trying to make sure it hits t

Re: [PERFORM] Compared MS SQL 2000 to Postgresql 9.0 on Windows

On 12/7/2010 11:34 AM, Tom Polak wrote: We are in the process of deciding on how to proceed on a database upgrade. We currently have MS SQL 2000 running on Windows 2003 (on my test server). I was shocked at the cost for MS SQL 2008 R2 for a new server (2 CPU license). I started comparing DB’s

Re: [PERFORM] Compared MS SQL 2000 to Postgresql 9.0 on Windows

On 12/7/2010 1:22 PM, Justin Pitts wrote: Also, as a fair warning: mssql doesn't really care about transactions, but PG really does. Make sure all your code is properly starting and commiting transactions. -Andy I do not understand that statement. Can you explain it a bit better? In mssql

Re: [PERFORM] Compared MS SQL 2000 to Postgresql 9.0 on Windows

On 12/7/2010 2:10 PM, Kenneth Marshall wrote: On Tue, Dec 07, 2010 at 11:56:51AM -0800, Richard Broersma wrote: On Tue, Dec 7, 2010 at 11:43 AM, Andy Colson wrote: In PG the first statement you fire off (like an "insert into" for example) will start a transaction. ?If you dont com

Re: [PERFORM] Help with bulk read performance

On 11/1/2010 9:15 AM, Dan Schaffer wrote: Hello We have an application that needs to do bulk reads of ENTIRE Postgres tables very quickly (i.e. select * from table). We have observed that such sequential scans run two orders of magnitude slower than observed raw disk reads (5 MB/s versus 100 MB/

Re: [PERFORM] Help with bulk read performance

On 12/14/2010 9:41 AM, Jim Nasby wrote: On Dec 14, 2010, at 9:27 AM, Andy Colson wrote: Is this the same thing Nick is working on? How'd he get along? http://archives.postgresql.org/message-id/4cd1853f.2010...@noaa.gov So it is. The one I replied to stood out because no one had repli

Re: [PERFORM] Compared MS SQL 2000 to Postgresql 9.0 on Windows

On 12/17/2010 11:08 AM, Tom Polak wrote: So, I am back on this topic again. I have a related question, but this might be the correct thread (and please let me know that). The boss is pressing the issue because of the cost of MSSQL. What kind of performance can I expect out of Postgres compare t

Re: [PERFORM] Compared MS SQL 2000 to Postgresql 9.0 on Windows

On 12/17/2010 11:37 AM, Robert Haas wrote: On Fri, Dec 17, 2010 at 12:08 PM, Tom Polak wrote: other direction to get good performance, too. You're not going to compare two major database systems across the board and find that one of them is just twice as fast, across the board. They have d

Re: [PERFORM] queries with lots of UNIONed relations

On 1/13/2011 4:42 PM, Robert Haas wrote: On Thu, Jan 13, 2011 at 5:41 PM, Robert Haas wrote: On Thu, Jan 13, 2011 at 5:26 PM, Tom Lane wrote: Robert Haas writes: On Thu, Jan 13, 2011 at 3:12 PM, Jon Nelson wrote: I still think that having UNION do de-duplication of each contributory relat

Re: [PERFORM] queries with lots of UNIONed relations

On 1/13/2011 4:49 PM, Robert Haas wrote: On Thu, Jan 13, 2011 at 5:47 PM, Andy Colson wrote: I don't believe there is any case where hashing each individual relation is a win compared to hashing them all together. If the optimizer were smart enough to be considering the situation as a

Re: [PERFORM] Possible to improve query plan?

On 01/16/2011 09:21 PM, Jeremy Palmer wrote: Hi all, I've come to a dead end in trying to get a commonly used query to perform better. The query is against one table with 10 million rows. This table has been analysed. The table definition is: CREATE TABLE version_crs_coordinate_revision (

Re: [PERFORM] Possible to improve query plan?

-Original Message- From: Andy Colson [mailto:a...@squeakycode.net] Sent: Monday, 17 January 2011 5:22 p.m. To: Jeremy Palmer Cc: pgsql-performance@postgresql.org Subject: Re: [PERFORM] Possible to improve query plan? First, wow, those are long names... I had a hard time keeping track

Re: [PERFORM] Migrating to Postgresql and new hardware

On 1/18/2011 4:56 AM, Lars wrote: Hi, We are in the process of moving a web based application from a MySql to Postgresql database. Our main reason for moving to Postgresql is problems with MySql (MyISAM) table locking. We will buy a new set of servers to run the Postgresql databases. The curren

Re: [PERFORM] Migrating to Postgresql and new hardware

oops, call them database 'a' and database 'b'. -- 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] Migrating to Postgresql and new hardware

On 1/19/2011 6:42 PM, Craig Ringer wrote: On 01/19/2011 05:09 PM, Lars wrote: Thanks for the reply! As others have mentioned, how are you going to be doing your "shards"? Hmm... shards might not have been a good word to describe it. I'll paste what I wrote in another reply: I used sharding a

Re: [PERFORM] Fun little performance IMPROVEMENT...

On 1/21/2011 12:12 PM, gr...@amadensor.com wrote: I was doing a little testing to see how machine load affected the performance of different types of queries, index range scans, hash joins, full scans, a mix, etc. In order to do this, I isolated different performance hits, spinning only CPU, loa

Re: [PERFORM] Queries becoming slow under heavy load

On 1/25/2011 3:37 PM, Anne Rosset wrote: Hi, We are running some performances tests. With a lot of concurrent access, queries get very slow. When there is no load, those queries run fast. We kind of see a trend about these queries: it seems like the ones that become very slow have an ORDER BY o

Re: [PERFORM] High load,

On 1/27/2011 4:31 AM, Michael Kohl wrote: Hi all, we are running a fairly big Ruby on Rails application on Postgres 8.4. Our traffic grew quite a bit lately, and since then we are facing DB performance issues. System load occasionally explodes (around 170 yesterday on a 16 core system), which se

Re: [PERFORM] High load,

On 1/27/2011 9:09 AM, Michael Kohl wrote: On Thu, Jan 27, 2011 at 4:06 PM, Andy Colson wrote: Have you run each of your queries through explain analyze lately? A code review including checking of queries is on our agenda. You are vacuuming/autovacuuming, correct? Sure :-) Thank you

Re: [PERFORM] Get master-detail relationship metadata

On 2/3/2011 5:40 AM, Laszlo Nagy wrote: Hi All, I'm working on a client program that iterates over master-detail relationships in a loop chain. Pseudo code: for row_1 in table_1: table_2 = get_details(row_1,"table2") for row_2 in table_2: row_3 = get_details(row_2,"table3") etc. process_

Re: [PERFORM] getting the most of out multi-core systems for repeated complex SELECT statements

On 2/3/2011 9:08 AM, Mark Stosberg wrote: Each night we run over a 100,000 "saved searches" against PostgreSQL 9.0.x. These are all complex SELECTs using "cube" functions to perform a geo-spatial search to help people find adoptable pets at shelters. All of our machines in development in produc

Re: [PERFORM] getting the most of out multi-core systems for repeated complex SELECT statements

On 02/03/2011 04:56 PM, Greg Smith wrote: Scott Marlowe wrote: On Thu, Feb 3, 2011 at 8:57 AM, wrote: Time for my pet meme to wiggle out of its hole (next to Phil's, and a day later). For PG to prosper in the future, it has to embrace the multi-core/processor/SSD machine at the query level

Re: [PERFORM] getting the most of out multi-core systems for repeated complex SELECT statements

On 02/03/2011 10:00 PM, Greg Smith wrote: Andy Colson wrote: Cpu's wont get faster, but HD's and SSD's will. To have one database connection, which runs one query, run fast, it's going to need multi-core support. My point was that situations where people need to

Re: [PERFORM] Performance trouble finding records through related records

On 03/01/2011 06:14 PM, sverhagen wrote: Hi, appreciated mailing list. Thanks already for taking your time for my performance question. Regards, Sander. ===POSTGRESQL VERSION AND ORIGIN=== PostgreSQL 8.3.9 on i486-pc-linux-gnu, compiled by GCC cc (GCC) 4.2.4 (Ubuntu 4.2.4-1ubuntu3) Installed u

Re: [PERFORM] Performance trouble finding records through related records

On 03/02/2011 06:12 PM, sverhagen wrote: Thanks for your help already! Hope you're up for some more :-) Andy Colson wrote: First off, excellent detail. Second, your explain analyze was hard to read... but since you are not really interested in your posted query, I wont worry about lo

Re: [PERFORM] Performance trouble finding records through related records

On 3/3/2011 3:19 AM, sverhagen wrote: Andy Colson wrote: For your query, I think a join would be the best bet, can we see its explain analyze? Here is a few variations: SELECT events_events.* FROM events_events WHERE transactionid IN ( SELECT transactionid FROM

Re: [PERFORM] Performance issues

I have seen really complex geometries cause problems. If you have thousands of points, when 10 would do, try ST_Simplify and see if it doesnt speed things up. -Andy On 3/8/2011 2:42 AM, Andreas Forø Tollefsen wrote: Hi. Thanks for the comments. My data is right, and the result is exactly wh

Re: [PERFORM] Performance issues

startdate <= '1946/1/1'; 2011/3/8 Andy Colson mailto:a...@squeakycode.net>> I have seen really complex geometries cause problems. If you have thousands of points, when 10 would do, try ST_Simplify and see if it doesnt speed things up. -Andy On 3/8/2011 2:4

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 n

Re: [PERFORM] Fastest pq_restore?

On 3/18/2011 9:38 AM, Kevin Grittner wrote: Andy Colson wrote: On 03/17/2011 09:25 AM, Michael Andreasen wrote: I've been looking around for information on doing a pg_restore as fast as possible. bgwriter_lru_maxpages = 0 I hadn't thought much about that last one -- d

Re: [PERFORM] Performance on AIX

On 03/19/2011 04:00 AM, phb07 wrote: Hi all, At Bull company, we want to answer a call for tender from a large company. And we are asked for information about PostgreSQL performance under AIX on Power 7 servers. By chance, has someone some data about this ? Has someone performed a benchmark u

Re: [PERFORM] Used computers?

Craig James wrote: Apologies for a slightly off-topic question ... a friend is overseeing the demise of a company and has several computers that they need to get rid of. She's an attorney and knows little about them except that they're IBM and cost >$50K originally. Where does one go to sell

Re: [PERFORM] PG 8.3 and server load

Phoenix Kiula wrote: I'm on a CentOS 5 OS 64 bit, latest kernel and all of that. PG version is 8.3.7, compiled as 64bit. The memory is 8GB. It's a 2 x Dual Core Intel 5310. Hard disks are Raid 1, SCSI 15 rpm. The server is running just one website. So there's Apache 2.2.11, MySQL (for some small

Re: [PERFORM] PG 8.3 and server load

ate && vmstat Wed Aug 19 10:01:23 CDT 2009 procs ---memory-- ---swap-- -io --system-- cpu r b swpd free buff cache si sobibo incs us sy id wa 0 0 20920 106376 59220 75310160074 1530 3 10 5 74 12 On Wed, Aug 19, 2

Re: [PERFORM] PG 8.3 and server load

Kevin Grittner wrote: Andy Colson wrote: Phoenix Kiula wrote: It's a 2 x Dual Core Intel 5310. you have average 74% idle cpu. So your not cpu bound either? Or one CPU is pegged and the other three are idle -Kevin Ahh, yeah... Phoenix: run top again, and hit the &#

Re: [PERFORM] PG 8.3 and server load

Phoenix Kiula wrote: On Wed, Aug 19, 2009 at 11:37 PM, Andy Colson<mailto:a...@squeakycode.net>> wrote: > > Phoenix: run top again, and hit the '1' key. It'll show you stats for each > cpu. Is one pegged and the others idle? > top - 10:38:53 up 29

Re: [PERFORM] Seeking performance advice and explanation for high I/O on 8.3

Scott Otis wrote: Would love to get some advice on how to change my conf settings / setup to get better I/O performance. Total I/O (these number are pretty constant throughout the day): Reads: ~ 100 / sec for about 2.6 Mb/sec Writes: ~ 400 /sec for about 46.1Mb/sec Most of the SQL happening i

Re: [PERFORM] Seeking performance advice and explanation for high I/O on 8.3

ector and autovacuum ones. Both of those are using a lot of 1 core each. I am not familiar with a dd test - what is that? Thanks, Scott On Sep 3, 2009, at 8:03 AM, "Andy Colson" wrote: Scott Otis wrote: Would love to get some advice on how to change my conf settings / setup to

Re: [PERFORM] Seeking performance advice and explanation for high I/O on 8.3

Scott Otis wrote: Sorry about not responding to the whole list earlier - this is my first time posting to a mailing list. Would providing more information about the size and complexities of the databases help? I measure I/O stats with iostat - here is the command I use: iostat -d -x mfid0 -t

Re: [PERFORM] Seeking performance advice and explanation for high I/O on 8.3

Robert Haas wrote: On Thu, Sep 3, 2009 at 4:16 PM, Scott Otis wrote: Sorry about not responding to the whole list earlier - this is my first time posting to a mailing list. Would providing more information about the size and complexities of the databases help? I measure I/O stats with iostat

Re: [PERFORM] Possible causes of sometimes slow single-row UPDATE with trivial indexed condition?

Vlad Romascanu wrote: Problem occurs when running (in production) Postgres 8.3.7 64-bit (from RPM) on Ubuntu 8.04.2, on an Amazon EC2 (xen) "Large" instance (8GB RAM), with the DB on a 50GB EC2 block device. Problem does not occur when running (in staging/pre-production) Postgres 8.3.5 32-bit (

Re: [PERFORM] High CPU load on Postgres Server during Peak times!!!!

Shiva Raman wrote: Dear all I am having a problem of high cpu loads in my postgres server during peak time. Following are the details of my setup (details as per the postgres wiki) . *Following is the output of TOP command during offpeak time.* top - 18:36:56 up 77 days, 20:33, 1 user,

Re: [PERFORM] High CPU load on Postgres Server during Peak times!!!!

Andy Colson wrote: Shiva Raman wrote: Dear all I am having a problem of high cpu loads in my postgres server during peak time. Following are the details of my setup (details as per the postgres wiki) . *Following is the output of TOP command during offpeak time.* top - 18:36:56 up 77

Re: [PERFORM] Hunting Unused Indexes .. is it this simple ?

Stef Telford wrote: Hey Everyone, So, I have a nice postgreSQL server (8.4) up and running our database. I even managed to get master->slave going without trouble using the excellent skytools.. however, I want to maximize speed and the hot updates where possible, so, I am wanting to prune u

Re: [PERFORM] High CPU load on Postgres Server during Peak times!!!!

Shiva Raman wrote: /If you run a 'ps ax|grep post' do you see anything that says 'idle in transaction'? (I hope that old of version will show it. my processes show up as postgres not postmaster)/ Lots of requests shows as 'idle in transaction'. Eww. I think that's bad. A connection that

Re: [PERFORM] High CPU load on Postgres Server during Peak times!!!!

SAN is like HD right? I assume waiting on the SAN would show up as %iowait... yes? Andy Colson Wrote : , /Eww. I think that's bad. A connection that has a transaction open will cause lots of row versions, which use up ram, and make it slower to step through the table (even with an inde

Re: [PERFORM] Postgres performance

std pik wrote: Hi all.. please, how can i tune postgres performance? Thanks. Thats a very generic question. Here are some generic answers: You can tune the hardware underneath. Faster hardware = faster pg. You can tune the memory usage, and other postgres.conf setting to match your hardwa

Re: [PERFORM] Load experimentation

On 12/07/2009 12:12 PM, Ben Brehmer wrote: Hello All, I'm in the process of loading a massive amount of data (500 GB). After some initial timings, I'm looking at 260 hours to load the entire 500GB. 10 days seems like an awfully long time so I'm searching for ways to speed this up. The load is ha

Re: [PERFORM] pg_connect takes 3.0 seconds

On 1/4/2010 8:12 PM, Dmitri Girski wrote: Hi everybody, I am running a PostgreSQL server 8.3.5 with a pretty much standard config. The web application server which runs Apache 1.3/PHP2.9 has an intermittent problem: pg_connect takes exactly 3.0 seconds. The usual connection time is 0.0045. The

Re: [PERFORM] a heavy duty operation on an "unused" table kills my server

On 1/13/2010 11:36 PM, Craig Ringer wrote: Robert Haas wrote: I'm kind of surprised that there are disk I/O subsystems that are so bad that a single thread doing non-stop I/O can take down the whole server. Is that normal? No. Does it happen on non-Windows operating systems? Yes. My 3war

Re: [PERFORM] a heavy duty operation on an "unused" table kills my server

On 1/14/2010 12:07 PM, Greg Smith wrote: Andy Colson wrote: On 1/13/2010 11:36 PM, Craig Ringer wrote: Yes. My 3ware 8500-8 on a Debian Sarge box was so awful that launching a terminal would go from a 1/4 second operation to a 5 minute operation under heavy write load by one writer. I landed

Re: [PERFORM] Slow-ish Query Needs Some Love

On 2/2/2010 1:03 PM, Matt White wrote: On Feb 2, 6:06 am, Edgardo Portal wrote: On 2010-02-02, Matt White wrote: I have a relatively straightforward query that by itself isn't that slow, but we have to run it up to 40 times on one webpage load, so it needs to run much faster than it does. He

Re: [PERFORM] System overload / context switching / oom, 8.3

On 2/2/2010 1:11 PM, Rob wrote: Linux 2.6.18-6-686-bigmem #1 SMP Thu Nov 5 17:30:05 UTC 2009 i686 GNU/Linux (Debian Etch) 8 MB RAM 4 Quad Core Intel(R) Xeon(R) CPU E5440 @ 2.83GHz stepping 06 L1 I cache: 32K, L1 D cache: 32K, L2 cache: 6144K Well _there's_ your problem! Ya need

Re: [PERFORM] System overload / context switching / oom, 8.3

On 2/2/2010 1:11 PM, Rob wrote: postgresql.conf: max_connections = 1100 shared_buffers = 800MB max_prepared_transactions = 0 work_mem = 32MB maintenance_work_mem = 64MB max_fsm_pages = 330 max_fsm_relations = 1 vacuum_cost_delay = 50ms bgwriter_delay = 150ms bgwriter_lru_maxpages = 250 b

Re: [PERFORM] Optimizing Postgresql server and FreeBSD for heavy read and writes

On 2/3/2010 9:10 AM, Amitabh Kant wrote: Hello I have a server dedicated for Postgres with the following specs: RAM 16GB, 146GB SAS (15K) x 4 - RAID 10 with BBU, Dual Xeon E5345 @ 2.33GHz OS: FreeBSD 8.0 It runs multiple (approx 10) databases ranging from 500MB to over 24 GB in size. All of

Re: [PERFORM] Slow-ish Query Needs Some Love

On 2/3/2010 11:17 AM, Matt White wrote: On Feb 2, 1:11 pm, a...@squeakycode.net (Andy Colson) wrote: On 2/2/2010 1:03 PM, Matt White wrote: On Feb 2, 6:06 am, Edgardo Portalwrote: On 2010-02-02, Matt Whitewrote: I have a relatively straightforward query that by itself isn&#

Re: [PERFORM] Performance

On 4/29/2011 1:55 PM, Greg Smith wrote: James Mansion wrote: Does the server know which IO it thinks is sequential, and which it thinks is random? Could it not time the IOs (perhaps optionally) and at least keep some sort of statistics of the actual observed times? It makes some assumptions ba

Re: [PERFORM] Postgres performance on Linux and Windows

On 8/3/2011 11:37 AM, Dusan Misic wrote: I had done some testing for my application (WIP) and I had executed same SQL script and queries on real physical 64-bit Windows 7 and on virtualized 64-bit CentOS 6. Both database servers are tuned with real having 8 GB RAM and 4 cores, virtualized having

Re: [PERFORM] Parameters for PostgreSQL

On 8/3/2011 11:03 PM, Craig Ringer wrote: great gobs of battery backed write cache DRAM. Now I know what I'm asking Santa for Christmas this year! -Andy -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.

Re: [PERFORM] Tuning Tips for a new Server

On 8/16/2011 8:35 PM, Ogden wrote: Hope all is well. I have received tremendous help from this list prior and therefore wanted some more advice. I bought some new servers and instead of RAID 5 (which I think greatly hindered our writing performance), I configured 6 SCSI 15K drives with RAID 10

Re: [PERFORM] DBT-5 & Postgres 9.0.3

On 8/17/2011 10:29 AM, bobbyw wrote: Hi, I know this is an old thread, but I wanted to chime in since I am having problems with this as well. I too am trying to run dbt5 against Postgres. Specifically I am trying to run it against Postgres 9.1beta3. After jumping through many hoops I ultimatel

Re: [PERFORM] Raid 5 vs Raid 10 Benchmarks Using bonnie++

On 8/17/2011 1:35 PM, k...@rice.edu wrote: On Wed, Aug 17, 2011 at 01:32:41PM -0500, Ogden wrote: On Aug 17, 2011, at 1:31 PM, k...@rice.edu wrote: On Wed, Aug 17, 2011 at 01:26:56PM -0500, Ogden wrote: I am using bonnie++ to benchmark our current Postgres system (on RAID 5) with the new on

Re: [PERFORM] Raid 5 vs Raid 10 Benchmarks Using bonnie++

On 8/17/2011 1:55 PM, Ogden wrote: On Aug 17, 2011, at 1:48 PM, Andy Colson wrote: On 8/17/2011 1:35 PM, k...@rice.edu wrote: On Wed, Aug 17, 2011 at 01:32:41PM -0500, Ogden wrote: On Aug 17, 2011, at 1:31 PM, k...@rice.edu wrote: On Wed, Aug 17, 2011 at 01:26:56PM -0500, Ogden wrote: I

Re: [PERFORM] Variable versus constrant size tuples

On 8/19/2011 4:03 AM, Krzysztof Chodak wrote: Is there any performance benefit of using constant size tuples? If you are referring to varchar(80) vs text, then no, there is no benefit. -Andy -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your

[PERFORM] IN or EXISTS

Hi all, I have read things someplace saying not exists was better than not in... or something like that. Not sure if that was for in/exists and not in/not exists, and for a lot of records or not. Here is my setup: My website has a general table, let say 60k rows. Its mostly read-only. Ev

Re: [PERFORM] IN or EXISTS

On 8/30/2011 8:33 PM, Craig Ringer wrote: On 31/08/2011 4:30 AM, Andy Colson wrote: Hi all, I have read things someplace saying not exists was better than not in... or something like that. Not sure if that was for in/exists and not in/not exists, and for a lot of records or not. `EXISTS'

Re: [PERFORM] Slow performance

When you ran it, did it really feel like 30 seconds? Or did it come right back real quick? Because your report says: > 35.833 ms Thats ms, or milliseconds, or 0.035 seconds. -Andy On 8/31/2011 8:04 AM, Kai Otto wrote: Hi all, I am running a simple query: SELECT * FROM public.“Frame” Ti

Re: [PERFORM] Slow performance

On 8/31/2011 1:51 PM, Alan Hodgson wrote: On August 31, 2011 11:26:57 AM Andy Colson wrote: When you ran it, did it really feel like 30 seconds? Or did it come right back real quick? Because your report says: > 35.833 ms Thats ms, or milliseconds, or 0.035 seconds. I think the "

Re: [PERFORM] Rather large LA

On 09/05/2011 05:28 AM, Richard Shaw wrote: Hi, I have a database server that's part of a web stack and is experiencing prolonged load average spikes of up to 400+ when the db is restarted and first accessed by the other parts of the stack and has generally poor performance on even simple se

Re: [PERFORM] Sudden drop in DBb performance

On 09/05/2011 02:48 AM, Tomas Vondra wrote: On 3 Září 2011, 9:26, Gerhard Wohlgenannt wrote: Dear list, we are encountering serious performance problems with our database. Queries which took around 100ms or less last week now take several seconds. Results of Bonnie++ Version 1.96 ---

Re: [PERFORM] Sudden drop in DBb performance

On 09/05/2011 09:39 AM, Tomas Vondra wrote: On 5 Září 2011, 16:08, Gerhard Wohlgenannt wrote: Below please find the results of vmstat 2 over some periode of time .. with normal database / system load. What does a "normal load" mean? Does that mean a time when the queries are slow? Are you sur

Re: [PERFORM] Rather large LA

On 09/05/2011 08:57 AM, Richard Shaw wrote: Hi Andy, It's not a new issue no, It's a legacy system that is in no way ideal but is also not in a position to be overhauled. Indexes are correct, tables are up to 25 million rows. On startup, it hits CPU more than IO, I'll provide some additiona

Re: [PERFORM] Sudden drop in DBb performance

On 09/05/2011 01:45 PM, Scott Marlowe wrote: On Mon, Sep 5, 2011 at 8:08 AM, Gerhard Wohlgenannt wrote: Below please find the results of vmstat 2 over some periode of time .. with normal database / system load. 2 1 1344204 240924 104156 31462484 3500 1906 234 3687 4512 12 3 77 9

Re: [PERFORM] should i expected performance degradation over time

On 09/10/2011 11:55 AM, Anibal David Acosta wrote: Sometimes I read that postgres performance is degraded over the time and something people talk about backup and restore database solve the problem. It is really true? I have postgres 9.0 on a windows machine with The autovacuum is ON I have s

Re: [PERFORM] Postgres for a "data warehouse", 5-10 TB

On 09/11/2011 07:35 AM, Igor Chudov wrote: I have been a MySQL user for years, including owning a few multi-gigabyte databases for my websites, and using it to host algebra.com (about 12 GB database). I have had my ups and downs with MySQL. The ups were ease of use and decen

Re: [PERFORM] Postgres for a "data warehouse", 5-10 TB

On 09/11/2011 08:59 AM, Igor Chudov wrote: I do not plan to do a lot of random writing. My current design is that my perl scripts write to a temporary table every week, and then I do INSERT..ON DUPLICATE KEY UPDATE. By the way, does that INSERT UPDATE functionality or something like this exi

Re: [PERFORM] Postgres for a "data warehouse", 5-10 TB

On 09/11/2011 09:21 AM, Igor Chudov wrote: On Sun, Sep 11, 2011 at 9:16 AM, Claudio Freire mailto:klaussfre...@gmail.com>> wrote: On Sun, Sep 11, 2011 at 3:59 PM, Igor Chudov mailto:ichu...@gmail.com>> wrote: > Well, right now, my server has twelve 7,200 RPM 2TB hard drives in a RAID

Re: [PERFORM] Postgres for a "data warehouse", 5-10 TB

On 9/12/2011 12:15 PM, Robert Klemme wrote: On 11.09.2011 19:02, Marti Raudsepp wrote: On Sun, Sep 11, 2011 at 17:23, Andy Colson wrote: On 09/11/2011 08:59 AM, Igor Chudov wrote: By the way, does that INSERT UPDATE functionality or something like this exist in Postgres? You have two options

Re: [PERFORM] Allow sorts to use more available memory

On 9/12/2011 12:33 PM, Robert Schnabel wrote: The recent "data warehouse" thread made me think about how I use work_mem for some of my big queries. So I tried SET work_mem = '4GB' for a session and got ERROR: 4194304 is outside the valid range for parameter "work_mem" (64 .. 2097151) A bit of s

  1   2   >