Re: [PERFORM] Sort-of replication for reporting purposes

On 13 January 2017 at 12:00, Stuart Bishop wrote: > > > On 7 January 2017 at 02:33, Ivan Voras wrote: > >> >> >> >> I forgot to add one more information, the databases are 50G+ each so >> doing the base backup on demand over the network is not a great o

Re: [PERFORM] Sort-of replication for reporting purposes

On 6 Jan 2017 8:30 p.m., "Scott Marlowe" wrote: On Fri, Jan 6, 2017 at 12:24 PM, Ivan Voras wrote: > Hello, > > I'm investigating options for an environment which has about a dozen servers > and several dozen databases on each, and they occasionally need to run hug

[PERFORM] Sort-of replication for reporting purposes

Hello, I'm investigating options for an environment which has about a dozen servers and several dozen databases on each, and they occasionally need to run huge reports which slow down other services. This is of course "legacy code". After some discussion, the idea is to offload these reports to se

Re: [PERFORM] Understanding BRIN index performance

On 3 October 2016 at 12:05, Simon Riggs wrote: > On 3 October 2016 at 10:58, Ivan Voras wrote: > > > I get that, my question was more about why the index scan returned 25 mil > > rows, when the pages are sequentially filled by timestamps? In my > > understading of BRIN,

Re: [PERFORM] Understanding BRIN index performance

On 3 October 2016 at 11:40, Simon Riggs wrote: > On 3 October 2016 at 10:00, Ivan Voras wrote: > > > My first idea is to create a default BRIN index on dateAdded since the > above > > query is not run frequently. To my surprise, the planner refused to use > the >

[PERFORM] Understanding BRIN index performance

Hi, I have a table of around 20 G, more than 220 million records, and I'm running this query on it: explain analyze SELECT MAX(id) - (SELECT id FROM expl_transactions WHERE dateAdded < (now() - INTERVAL '10 MINUTES') ORDER BY dateAdded DESC LIMIT 1) FROM expl_transactions; "id" is SERIAL, "dateA

[PERFORM] Logging queries using sequential scans

Hello, Is it possible to log queries using sequential scans? Or possibly every query in a way which allows grepping for those with sequential scans?

Re: [PERFORM] Indexes for hashes

And in any case, there's no crc32 in the built-in pgcrypto module. On 17 June 2016 at 06:18, Claudio Freire wrote: > On Fri, Jun 17, 2016 at 1:09 AM, julyanto SUTANDANG > wrote: > > This way is doing faster using crc32(data) than hashtext since crc32 is > > hardware accelerated in intel (and o

Re: [PERFORM] Indexes for hashes

Hi, Just for testing... is there a fast (i.e. written in C) crc32 or a similar small hash function for PostgreSQL? On 15 June 2016 at 16:00, Ivan Voras wrote: > Hi, > > This idea is similar to the substring one, and while it does give > excellent performance and small size,

Re: [PERFORM] Indexes for hashes

integer as the result of crc32 > function? you can split the hash into 2 part and do crc32 2x ? and then > create composite index on both integer (the crc32 result) > instead of using 64 char, you only employ 2 integer as index key. > > Regards, > > Jul > > On Wed, Jun 15, 20

Re: [PERFORM] Indexes for hashes

Hi, I understand your idea, and have also been thinking about it. Basically, existing applications would need to be modified, however slightly, and that wouldn't be good. On 15 June 2016 at 15:38, hubert depesz lubaczewski wrote: > On Wed, Jun 15, 2016 at 11:34:18AM +0200, Ivan Vor

Re: [PERFORM] Indexes for hashes

On 15 June 2016 at 15:03, k...@rice.edu wrote: > On Wed, Jun 15, 2016 at 11:34:18AM +0200, Ivan Voras wrote: > > Hi, > > > > I have an application which stores a large amounts of hex-encoded hash > > strings (nearly 100 GB of them), which means: > > > >

[PERFORM] Indexes for hashes

Hi, I have an application which stores a large amounts of hex-encoded hash strings (nearly 100 GB of them), which means: - The number of distinct characters (alphabet) is limited to 16 - Each string is of the same length, 64 characters - The strings are essentially random Creating a B-T

Re: [PERFORM] tsearch2, large data and indexes

On 24 April 2014 13:34, Heikki Linnakangas wrote: > As the docs say, the GIN index does not store the weights. As such, there is > no need to strip them. A recheck would be necessary if your query needs the > weights, precisely because the weights are not included in the index. > > (In the OP's q

Re: [PERFORM] tsearch2, large data and indexes

On 22 April 2014 17:58, Jeff Janes wrote: > On Tue, Apr 22, 2014 at 12:57 AM, Ivan Voras wrote: >> >> On 22 April 2014 08:40, Heikki Linnakangas >> wrote: >> > On 04/20/2014 02:15 AM, Ivan Voras wrote: >> >> More details: after thinking about it some

Re: [PERFORM] tsearch2, large data and indexes

On 22 April 2014 08:40, Heikki Linnakangas wrote: > On 04/20/2014 02:15 AM, Ivan Voras wrote: >> More details: after thinking about it some more, it might have >> something to do with tsearch2 and indexes: the large data in this case >> is a tsvector, indexed with GIN, and th

[PERFORM] tsearch2, large data and indexes

Hello, If a table contains simple fields as well as large (hundreds of KiB) text fields, will accessing only the simple fields cause the entire record data, including the large fields, to be read and unpacked? (e.g. SELECT int_field FROM table_with_large_text) More details: after thinking about i

Re: [PERFORM] 57 minute SELECT

On 03/10/2013 03:17, Samuel Stearns wrote: > The last part, the EXPLAIN, is too big to send. Is there an alternative > way I can get it too you, other than chopping it up and sending in > multiple parts? The usual way is via http://explain.depesz.com/ . signature.asc Description: OpenPGP digi

Re: [PERFORM] ORDER BY, LIMIT and indexes

Here are two more unexpected results. Same test table (1 mil. records, "id" is SERIAL PRIMARY KEY, PostgreSQL 9.1, VACUUM ANALYZE performed before the experiments): ivoras=# explain analyze select * from lt where id > 90 limit 10; QUERY PLAN -

Re: [PERFORM] ORDER BY, LIMIT and indexes

On 6 August 2013 02:20, Michael Paquier wrote: > > On Tue, Aug 6, 2013 at 8:25 AM, Claudio Freire > wrote: >> >> On Mon, Aug 5, 2013 at 8:04 PM, Ivan Voras wrote: >> > SELECT * FROM table ORDER BY id DESC LIMIT 10 OFFSET 10 >> > >> > SELECT * F

[PERFORM] ORDER BY, LIMIT and indexes

Hello, Assuming I have a huge table (doesn't fit in RAM), of which the most important fields are "id" which is a SERIAL PRIMARY KEY and "active" which is a boolean, and I'm issuing a query like: SELECT * FROM table ORDER BY id DESC LIMIT 10 ... is pgsql smart enough to use the index to fetch onl

Re: [PERFORM] Unique values across a table of arrays - documents and tags

On 07/11/2012 16:54, Florent Guillaume wrote: > Maybe you could store the tags as fulltext words, query them using > fulltext search, and use ts_stat to gather the list of words? Needs to > be benched of course. > You'll probably need to change the config to avoid stemming and stop words. I have t

Re: [PERFORM] Unique values across a table of arrays - documents and tags

On 07/11/2012 16:34, François Beausoleil wrote: > Le 2012-11-07 à 10:21, Ivan Voras a écrit : >> Barring that, what would you suggest for efficiently handing a classic >> structure like this (meaning documents with tags)? > > Can you structure it as the "cla

[PERFORM] Unique values across a table of arrays - documents and tags

Hello, I know I need to re-engineer this so it doesn't suck by design, so I'm wondering if there is some nifty PostgreSQL feature or best practice which may automagically do the best thing. I store information about documents which are tagged by string tags. The structure is very simple: CREATE

Re: [PERFORM] wal_sync_method on FreeBSD 9.0 - ZFS

On 14/09/2012 22:19, Sébastien Lorion wrote: > I am not able to set wal_sync_method to anything but fsync on FreeBSD 9.0 > for a DB created on ZFS (I have not tested on UFS). Is that expected ? Has > it anything to do with running on EC2 ? Can you explain what prevents you for setting the wal_sync

Re: [PERFORM] Inserts in 'big' table slowing down the database

On 03/09/2012 13:03, Stefan Keller wrote: > Hi, > > I'm having performance issues with a simple table containing 'Nodes' > (points) from OpenStreetMap: > > CREATE TABLE nodes ( > id bigint PRIMARY KEY, > user_name text NOT NULL, > tstamp timestamp without time zone NOT NULL, >

Re: [PERFORM] ZFS vs. UFS

On 24/07/2012 14:51, Laszlo Nagy wrote: > > Hello, > > Under FreeBSD 9, what filesystem should I use for PostgreSQL? (Dell > PowerEdge 2900, 24G mem, 10x2T SATA2 disk, Intel RAID controller.) > > * ZFS is journaled, and it is more independent of the hardware. So if >the computer goes wron

Re: [PERFORM] Seqscan slowness and stored procedures

On 8 June 2012 11:58, Albe Laurenz wrote: > Did you take caching of table data in the buffer cache or the filesystem > cache into account?  Did you run your tests several times in a row and > were the actual execution times consistent? Yes, and yes. >> Would tweaking enable_seqscan and other pl

Re: [PERFORM] Seqscan slowness and stored procedures

On 27 May 2012 05:28, Pavel Stehule wrote: > Hello > > 2012/5/26 Ivan Voras : >> Hello, >> >> I have a SQL function (which I've pasted below) and while testing its >> code directly (outside a function), this is the "normal", default plan: >>

[PERFORM] Seqscan slowness and stored procedures

Hello, I have a SQL function (which I've pasted below) and while testing its code directly (outside a function), this is the "normal", default plan: http://explain.depesz.com/s/vfP (67 ms) and this is the plain with enable_seqscan turned off: http://explain.depesz.com/s/EFP (27 ms) Disabling s

Re: [PERFORM] Parallel (concurrent) inserts?

On 26 May 2012 01:36, Jeff Janes wrote: > On Fri, May 25, 2012 at 3:04 PM, Ivan Voras wrote: >> Hello, >> >> I'm wondering if there is ia document describing which guarantees (if >> any) PostgreSQL makes about concurrency for various operations? Speaking >

[PERFORM] Parallel (concurrent) inserts?

Hello, I'm wondering if there is ia document describing which guarantees (if any) PostgreSQL makes about concurrency for various operations? Speaking in general (i.e. IO can handle it, number of CPU cores and client threads is optimal), are fully concurrent operations (independant and non-blocking

Re: [PERFORM] rough benchmarks, sata vs. ssd

On 13 February 2012 22:49, CSS wrote: > For the top-post scanners, I updated the ssd test to include > changing the zfs recordsize to 8k. > Well now I did, added the results to > http://ns.morefoo.com/bench.html and it looks like there's > certainly an improvement.  That's with the only change fr

Re: [PERFORM] rough benchmarks, sata vs. ssd

On 31/01/2012 09:07, CSS wrote: Hello all, Just wanted to share some results from some very basic benchmarking runs comparing three disk configurations on the same hardware: http://morefoo.com/bench.html That's great! *Tyan B7016 mainboard w/onboard LSI SAS controller *2x4 core xeon E5506 (

Re: [PERFORM] PostgreSQL perform poorly on VMware ESXi

On 07/11/2011 11:36, Lucas Mocellin wrote: > Hi everybody, > > I'm having some issues with PostgreSQL 9.03 running on FreeBSD 8.2 on top > of VMware ESXi 4.1 U1. I hope your hardware is Nehalem-based or newer... > The problem is query are taking too long, and some times one query "blocks" > ever

Re: [PERFORM] Adding more memory = hugh cpu load

On 11/10/2011 00:02, Samuel Gendler wrote: > The original question doesn't actually say that performance has gone down, > only that cpu utilization has gone up. Presumably, with lots more RAM, it is > blocking on I/O a lot less, so it isn't necessarily surprising that CPU > utilization has gone up

[PERFORM] Re: PostgreSQL-related topics of theses and seminary works sought (Was: Hash index use presently(?) discouraged...)

On 17/09/2011 22:01, Stefan Keller wrote: > 2011/9/17 Tomas Vondra wrote: > (...) >> We've been asked by a local university for PostgreSQL-related topics of >> theses and seminary works > > I'm also interested in such proposals or ideas! > > Here's some list of topics: > * Adding WAL-support to

Re: [PERFORM] UPDATEDs slowing SELECTs in a fully cached database

On 12/07/2011 02:09, lars wrote: Oh, and iowait hovers around 20% when SELECTs are slow: avg-cpu: %user %nice %system %iowait %steal %idle 1.54 0.00 0.98 18.49 0.07 78.92 When SELECTs are fast it looks like this: avg-cpu: %user %nice %system %iowait %steal %idle 8.72 0.00 0.26 0.00 0.00 91.01

Re: [PERFORM] UPDATEDs slowing SELECTs in a fully cached database

On 12/07/2011 16:18, Merlin Moncure wrote: On Tue, Jul 12, 2011 at 8:22 AM, Ivan Voras wrote: On 08/07/2011 01:56, lars wrote: Setup: PostgreSQL 9.1beta2 on a high memory (~68gb, 12 cores) EC2 Linux instance (kernel 2.6.35) with the database and WAL residing on the same EBS volume with EXT4

Re: [PERFORM] UPDATEDs slowing SELECTs in a fully cached database

On 08/07/2011 01:56, lars wrote: Setup: PostgreSQL 9.1beta2 on a high memory (~68gb, 12 cores) EC2 Linux instance (kernel 2.6.35) with the database and WAL residing on the same EBS volume with EXT4 (data=ordered, barriers=1) - yes that is not an ideal setup (WAL should be on separate drive, EBS

Re: [PERFORM] Performance block size.

On 27/05/2011 00:34, Tory M Blue wrote: Working on some optimization as well as finally getting off my backside and moving us to 64bit (32gb+memory). I was reading and at some point it appears on freeBSD the Postgres block size was upped to 16kb, from 8kb. And on my fedora systems I believe the

Re: [PERFORM] FUSION-IO io cards

On 29/04/2011 16:24, Mark Steben wrote: Hi, Had a recent conversation with a tech from this company called FUSION-IO. They sell io cards designed to replace conventional disks. The cards can be up to 3 TB in size and apparently are installed in closer proximity to the CPU than the disks are.

Re: [PERFORM] Background fsck

On 08/04/2011 07:55, Ireneusz Pluta wrote: Achilleas Mantzios wrote: In anyway, having FreeBSD to fsck, (background or not) should not happen. And the problem becomes bigger when cheap SATA drives will cheat about their write cache being flushed to the disk. So in the common case with cheap har

Re: [PERFORM] Background fsck

On 07/04/2011 00:48, Scott Marlowe wrote: On Wed, Apr 6, 2011 at 4:33 PM, Ireneusz Pluta wrote: Hello, I saw some recommendations from people on the net not to use background fsck when running PostgreSQL on FreeBSD. As I recall, these opinions were just thoughts of people which they shared wit

Re: [PERFORM] high user cpu, massive SELECTs, no io waiting problem

On 15/02/2011 18:19, Thomas Pöhler wrote: Hi list, first time for me here, hope you’re not dealing too severely with me regarding guidelines. Giving my best. We are running PostgreSQL 8.4.4 on x86_64-unknown-linux-gnu, compiled by GCC gcc (Debian 4.3.2-1.1) 4.3.2, 64-bit on a Supermicro SuperSe

Re: [PERFORM] Query performance with disabled hashjoin and mergejoin

st. -- Sent from my Android phone, please excuse my brevity. Greg Smith wrote: Ivan Voras wrote: > The "vanilla" plan, with default settings is: Pause here for a second: why default settings? A default PostgreSQL configuration is suitable for systems with about 128MB of RAM. Sin

Re: [PERFORM] Query performance with disabled hashjoin and mergejoin

On 04/02/2011 15:44, Greg Smith wrote: Ivan Voras wrote: The "vanilla" plan, with default settings is: Pause here for a second: why default settings? A default PostgreSQL configuration is suitable for systems with about 128MB of RAM. Since you say you have "good enough hardware

[PERFORM] Query performance with disabled hashjoin and mergejoin

I'm running all this on a 9.0 server with good enough hardware. The query is: SELECT news.id AS news_id , news.layout_id , news.news_relation_id , news.author_id

Re: [PERFORM] High load,

On 27/01/2011 11:31, 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 see

Re: [PERFORM] Queries becoming slow under heavy load

On 25/01/2011 22:37, 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. As others said, you need to stat how many concurrent clients are working on the database and also the nu

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

On 21/01/2011 19:12, 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, loadi

Re: [PERFORM] PostgreSQL 9.0 x64 bit pgbench TPC very low question?

On 12/18/10 20:42, tuanhoanganh wrote: Here is my result without -C pgbench -h 127.0.0.1 -p -U postgres -c 100 -t 10 -s 10 pgbench You really should replace "-t 10" with something like "-T 60" or more. -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make

Re: [PERFORM] Performance under contention

2010/12/7 Віталій Тимчишин : > > > 2010/12/7 Robert Haas >> >> On Tue, Dec 7, 2010 at 1:08 PM, Ivan Voras wrote: >> >> > I'm not very familiar with PostgreSQL code but if we're >> > brainstorming... if you're only trying to protect ag

Re: [PERFORM] Performance under contention

On 7 December 2010 19:10, Robert Haas wrote: >> I'm not very familiar with PostgreSQL code but if we're >> brainstorming... if you're only trying to protect against a small >> number of expensive operations (like DROP, etc.) that don't really >> happen often, wouldn't an atomic reference counter

Re: [PERFORM] Performance under contention

On 7 December 2010 18:37, Robert Haas wrote: > On Mon, Dec 6, 2010 at 9:59 PM, Jignesh Shah wrote: >> That's exactly what I concluded when I was doing the sysbench simple >> read-only test. I had also tried with different lock partitions and it >> did not help since they all go after the same tab

Re: [PERFORM] Performance under contention

On 26 November 2010 03:00, Greg Smith wrote: > Two suggestions to improve your results here: > > 1) Don't set shared_buffers to 10GB.  There are some known issues with large > settings for that which may or may not be impacting your results.  Try 4GB > instead, just to make sure you're not even o

Re: [PERFORM] Performance under contention

On 11/22/10 18:47, Kevin Grittner wrote: Ivan Voras wrote: It looks like a hack Not to everyone. In the referenced section, Hellerstein, Stonebraker and Hamilton say: "any good multi-user system has an admission control policy" In the case of PostgreSQL I understand the counte

Re: [PERFORM] Performance under contention

On 24 November 2010 01:11, Craig Ringer wrote: > On 11/22/2010 11:38 PM, Ivan Voras wrote: >> It looks like a hack (and one which is already implemented by connection >> pool software); the underlying problem should be addressed. > > My (poor) understanding is that addr

Re: [PERFORM] Performance under contention

On 11/22/10 16:26, Kevin Grittner wrote: Ivan Voras wrote: On 11/22/10 02:47, Kevin Grittner wrote: Ivan Voras wrote: After 16 clients (which is still good since there are only 12 "real" cores in the system), the performance drops sharply Yet another data point to confirm the

Re: [PERFORM] Performance under contention

On 11/22/10 02:47, Kevin Grittner wrote: Ivan Voras wrote: After 16 clients (which is still good since there are only 12 "real" cores in the system), the performance drops sharply Yet another data point to confirm the importance of connection pooling. :-) I agree, connection po

[PERFORM] Performance under contention

This is not a request for help but a report, in case it helps developers or someone in the future. The setup is: AMD64 machine, 24 GB RAM, 2x6-core Xeon CPU + HTT (24 logical CPUs) FreeBSD 8.1-stable, AMD64 PostgreSQL 9.0.1, 10 GB shared buffers, using pgbench with a scale factor of 500 (7.5 GB

[PERFORM] Re: How to achieve sustained disk performance of 1.25 GB write for 5 mins

On 11/17/10 22:11, Eric Comeau wrote: *) what kind of data do you expect to be writing out at this speed? Large Video files ... our s/w is used to displace FTP. *) how many transactions per second will you expect to have? Ideally 1 large file, but it may have to be multiple. We find that i

Re: [PERFORM] Anyone seen this kind of lock pileup?

On 11/17/10 18:37, Josh Berkus wrote: All, Having an interesting issue on one 8.4 database. Due to poor application design, the application is requesting 8-15 exclusive (update) locks on the same row on parallel connections pretty much simultaneously (i.e. < 50ms apart). What's odd about this i

[PERFORM] Re: How to achieve sustained disk performance of 1.25 GB write for 5 mins

On 11/17/10 15:26, Eric Comeau wrote: This is not directly a PostgreSQL performance question but I'm hoping some of the chaps that build high IO PostgreSQL servers on here can help. We build file transfer acceleration s/w (and use PostgreSQL as our database) but we need to build a test server th

[PERFORM] Re: Postgres insert performance and storage requirement compared to Oracle

On 10/26/10 17:41, Merlin Moncure wrote: > On Tue, Oct 26, 2010 at 11:08 AM, Leonardo Francalanci > wrote: >>> temp tables are not wal logged or >>> synced. Periodically they can be flushed to a permanent table. >> >> >> What do you mean with "Periodically they can be flushed to >> a permanen

Re: [PERFORM] CPUs for new databases

On 10/27/10 01:45, James Cloos wrote: "JB" == Josh Berkus writes: JB> In a general workload, fewer faster cores are better. We do not scale JB> perfectly across cores. The only case where that's not true is JB> maintaining lots of idle connections, and that's really better dealt JB> with

Re: [PERFORM] oracle to psql migration - slow query in postgres

On 10/14/10 21:43, Tony Capobianco wrote: We have 4 quad-core processors and 32GB of RAM. The below query uses the members_sorted_idx_001 index in oracle, but in postgres, the optimizer chooses a sequential scan. explain analyze create table tmp_srcmem_emws1 as select emailaddress, websiteid

Re: [PERFORM] [HACKERS] MIT benchmarks pgsql multicore (up to 48)performance

On 10/07/10 02:39, Robert Haas wrote: On Wed, Oct 6, 2010 at 6:31 PM, Ivan Voras wrote: On 10/04/10 20:49, Josh Berkus wrote: The other major bottleneck they ran into was a kernel one: reading from the heap file requires a couple lseek operations, and Linux acquires a mutex on the inode to

Re: [PERFORM] [HACKERS] MIT benchmarks pgsql multicore (up to 48)performance

On 7 October 2010 03:25, Tom Lane wrote: > Ivan Voras writes: >> On 10/04/10 20:49, Josh Berkus wrote: >>>> The other major bottleneck they ran into was a kernel one: reading from >>>> the heap file requires a couple lseek operations, and Linux acquires a &

Re: [PERFORM] [HACKERS] MIT benchmarks pgsql multicore (up to 48)performance

On 10/04/10 20:49, Josh Berkus wrote: >> The other major bottleneck they ran into was a kernel one: reading from >> the heap file requires a couple lseek operations, and Linux acquires a >> mutex on the inode to do that. The proper place to fix this is >> certainly in the kernel but it may be poss

[PERFORM] Big field, limiting and ordering

Hello, I don't think this is generally solvable but maybe it is so here goes. The original situation was this: SELECT something, big_field, complex_function(big_field), rank FROM t1 UNION ALL SELECT something, big_field, complex_function(big_field), rank from t2 ORDER BY rank LIMIT small_number;

Re: [PERFORM] Understanding tsearch2 performance

On 14 July 2010 17:16, Kevin Grittner wrote: > Ivan Voras wrote: > >> which didn't help. > > Didn't help what?  You're processing each row in 22.8 microseconds. > What kind of performance were you expecting? Well, I guess you're right. What I was expe

Re: [PERFORM] Understanding tsearch2 performance

On 07/14/10 16:03, Kevin Grittner wrote: > Ivan Voras < ivo...@freebsd.org > wrote: >> On 07/14/10 15:49, Stephen Frost wrote: > >>> Regarding the statistics, it's entirely possible that the index >>> is *not* the fastest way to pull this data (it'

Re: [PERFORM] Understanding tsearch2 performance

On 07/14/10 15:49, Stephen Frost wrote: > * Ivan Voras (ivo...@freebsd.org) wrote: >> Total runtime: 0.507 ms > [...] >> Total runtime: 118.689 ms >> >> See in the first query where I have a simple LIMIT, it fetches random 10 >> rows quickly, but in the

Re: [PERFORM] Understanding tsearch2 performance

On 07/14/10 15:25, Oleg Bartunov wrote: > On Wed, 14 Jul 2010, Ivan Voras wrote: > >>> Returning 8449 rows could be quite long. >> >> You are right, I didn't test this. Issuing a query which returns a >> smaller result set is much faster. >> >&g

Re: [PERFORM] Understanding tsearch2 performance

On 07/14/10 14:31, Oleg Bartunov wrote: > Something is not good with statistics, 91 est. vs 8449 actually returned. I don't think the statistics difference is significant - it's actually using the index so it's ok. And I've run vacuum analyze just before starting the query. > Returning 8449 rows

[PERFORM] Understanding tsearch2 performance

Here's a query and its EXPLAIN ANALYZE output: cms=> select count(*) from forum; count --- 90675 (1 row) cms=> explain analyze select id,title from forum where _fts_ @@ 'fer'::tsquery; QUERY PLAN

Re: [PERFORM] raid10 write performance

On 06/23/10 14:00, Florian Weimer wrote: > * Ivan Voras: > >> On the other hand, RAID10 is simple enough that soft-RAID >> implementations should be more than adequate - any ideas why a dedicated >> card has it "slow"? > > Barrier support on RAID10 seems

Re: [PERFORM] raid10 write performance

On 06/22/10 16:40, Greg Smith wrote: > Grzegorz Jaśkiewicz wrote: >> raid: serveRAID M5014 SAS/SATA controller >> > > Do the "performant servers" have a different RAID card? This one has > terrible performance, and could alone be the source of your issue. The > ServeRAID cards are slow in gen

Re: [PERFORM] 8K recordsize bad on ZFS?

On 05/10/10 20:39, Josh Berkus wrote: On 5/9/10 1:45 AM, Dimitri wrote: Josh, it'll be great if you explain how did you change the records size to 128K? - as this size is assigned on the file creation and cannot be changed later - I suppose that you made a backup of your data and then process a

Re: [PERFORM] mysql to postgresql, performance questions

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 "relaunch" looks like you are nearing the end (the "launch") of

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

On 4 February 2010 10:02, Amitabh Kant wrote: > On Wed, Feb 3, 2010 at 10:05 PM, Ivan Voras wrote: >> >> On 02/03/10 16:10, Amitabh Kant wrote: >>> >>> Hello >>> >>> I have a server dedicated for Postgres with the following specs: >>>

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

On 02/03/10 16:10, 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 If you really do have "heavy read and write" load on the server, nothing will save you

Re: [PERFORM] Benchmark shows very slow bulk delete

James Mansion wrote: > Ivan Voras wrote: >> I wish that, when people got the idea to run a simplistic benchmark >> like this, they would at least have the common sense to put the >> database on a RAM drive to avoid problems with different cylinder >> speeds of rotatio

Re: [PERFORM] Benchmark shows very slow bulk delete

On 01/27/10 14:28, Thom Brown wrote: Had a quick look at a benchmark someone put together of MySQL vs PostgreSQL, and while PostgreSQL is generally faster, I noticed the bulk delete was very slow: http://www.randombugs.com/linux/mysql-postgresql-benchmarks.html I wish that, when people got the

Re: [PERFORM] Inserting 8MB bytea: just 25% of disk perf used?

On 01/19/10 14:36, fka...@googlemail.com wrote: Ivan Voras: [I just skimmed this thread - did you increase the number of WAL logs to something very large, like 128?] Yes, I tried even more. I will be writing data quite constantly in the real scenario later. So I wonder if increasing WAL

Re: [PERFORM] Inserting 8MB bytea: just 25% of disk perf used?

On 01/19/10 11:16, fka...@googlemail.com wrote: fka...@googlemail.com: I'll try to execute these tests on a SSD and/or Raid system. FYI: On a sata raid-0 (mid range hardware) and recent 2x 1.5 TB disks with a write performance of 100 MB/s (worst, to 200 MB/s max), I get a performance of 18.2

[PERFORM] Re: New server to improve performance on our large and busy DB - advice? (v2)

hi, You wrote a lot of information here so let's confirm in a nutshell what you have and what you are looking for: * A database that is of small to medium size (5 - 10 GB)? * Around 10 clients that perform constant write operations to the database (UPDATE/INSERT) * Around 10 clients that occ

Re: [PERFORM] Slow "Select count(*) ..." query on table with 60 Mio. rows

Kevin Grittner wrote: Matthew Wakeling wrote: This is an FAQ. I just added it to the wiki FAQ page: http://wiki.postgresql.org/wiki/FAQ#Why_is_.22SELECT_count.28.2A.29_FROM_bigtable.3B.22_slow.3F Maybe you could add a short note why an estimation like from the pg_class table is usually

Re: [PERFORM] Inserting 8MB bytea: just 25% of disk perf used?

fka...@googlemail.com wrote: Hello together, I need to increase the write performance when inserting bytea of 8MB. I am using 8.2.4 on windows with libpq. This takes about 50s, so, 800MB/50s = 16MB/s. However the harddisk (sata) could write 43 MB/s in the worst case! Why is write performanc

Re: [PERFORM] performance config help

Ivan Voras wrote: Yes, but you are issuing 133 write operations per seconds per drive(s) - this is nearly the limit of what you can get with 15k RPM drives (actually, the limit should be somewhere around 200..250 IOPS but 133 isn't that far). I saw in your other post you have fsync t

Re: [PERFORM] performance config help

Bob Dusek wrote: On Mon, Jan 11, 2010 at 8:50 AM, Scott Marlowe > wrote: On Mon, Jan 11, 2010 at 6:44 AM, Bob Dusek mailto:redu...@gmail.com>> wrote: > Hello, > > We're running Postgres 8.4.2 on Red Hat 5, on pretty hefty hardware... >

Re: [PERFORM] Air-traffic benchmark

2010/1/7 Lefteris : > On Thu, Jan 7, 2010 at 3:51 PM, Ivan Voras wrote: >> On 7.1.2010 15:23, Lefteris wrote: >> >>> I think what you all said was very helpful and clear! The only part >>> that I still disagree/don't understand is the shared_buffer optio

Re: [PERFORM] Air-traffic benchmark

On 7.1.2010 15:23, Lefteris wrote: > I think what you all said was very helpful and clear! The only part > that I still disagree/don't understand is the shared_buffer option:)) Did you ever try increasing shared_buffers to what was suggested (around 4 GB) and see what happens (I didn't see it in

Re: [PERFORM] Any have tested ZFS like PostgreSQL installation filesystem?

Ing . Marcos Luís Ortíz Valmaseda wrote: Ivan Voras escribió: Ing . Marcos Luís Ortíz Valmaseda wrote: Regards to all the list. ZFS, the new filesystem developed by the Solaris Development team and ported to FreeBSD too, have many advantages that can do that all sysadmins are questioned

Re: [PERFORM] Any have tested ZFS like PostgreSQL installation filesystem?

Ing . Marcos Luís Ortíz Valmaseda wrote: Regards to all the list. ZFS, the new filesystem developed by the Solaris Development team and ported to FreeBSD too, have many advantages that can do that all sysadmins are questioned about if it is a good filesystem to the PostgreSQL installation. Any

Re: [PERFORM] SSD + RAID

Lists wrote: Laszlo Nagy wrote: Hello, I'm about to buy SSD drive(s) for a database. For decision making, I used this tech report: http://techreport.com/articles.x/16255/9 http://techreport.com/articles.x/16255/10 Here are my concerns: * I need at least 32GB disk space. So DRAM based SS

Re: [PERFORM] limiting performance impact of wal archiving.

Laurent Laborde wrote: On Tue, Nov 10, 2009 at 3:05 PM, Ivan Voras wrote: Laurent Laborde wrote: Hi ! We recently had a problem with wal archiving badly impacting the performance of our postgresql master. Hmmm, do you want to say that copying 16 MB files over the network (and presumably you

Re: [PERFORM] limiting performance impact of wal archiving.

Laurent Laborde wrote: Hi ! We recently had a problem with wal archiving badly impacting the performance of our postgresql master. Hmmm, do you want to say that copying 16 MB files over the network (and presumably you are not doing it absolutely continually - there are pauses between log ship

Re: [PERFORM] Free memory usage Sol10, 8.2.9

Jeremy Harris wrote: On 11/03/2009 07:16 PM, Subbiah Stalin-XCGF84 wrote: All, I'm trying to understand the free memory usage and why it falls below 17G sometimes and what could be causing it. Any pointers would be appreciated. r...@prod1 # prtconf System Configuration: Sun Microsystems sun4

  1   2   >