Re: [PERFORM] Postgresql in a Virtual Machine

are many when you administrate many servers/databases. regards, - -- Rafael Martinez Guerrero Center for Information Technology University of Oslo, Norway PGP Public Key: http://folk.uio.no/rafael/ -BEGIN PGP SIGNATURE- Version: GnuPG v2.0.14 (GNU/Linux

Re: [PERFORM] SQL statement over 500% slower with 9.2 compared with 9.1

this information and they can decide if they want to rewrite the statement or use the OFFSET trick. regards, - -- Rafael Martinez Guerrero Center for Information Technology University of Oslo, Norway PGP Public Key: http://folk.uio.no/rafael/ -BEGIN PGP SIGNATURE- Version: GnuPG v2.0.14

Re: [PERFORM] SQL statement over 500% slower with 9.2 compared with 9.1

it? Hello Of course, you can download a SQL dump of the tables involved, here: http://folk.uio.no/rafael/filmdatabase_testcase.sql.gz This file is 357M gunzipped and 101M gzipped. When restored in a database it will use 1473MB. # \d+ List of relations Schema

Re: [PERFORM] SQL statement over 500% slower with 9.2 compared with 9.1

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 08/28/2013 06:10 AM, Jeff Janes wrote: On Monday, August 26, 2013, Rafael Martinez wrote: Hei Could you do explain (analyze, buffers) of these? With 9.1: http://explain.depesz.com/s/FMe with 9.2: http://explain.depesz.com/s/Z1j What

Re: [PERFORM] SQL statement over 500% slower with 9.2 compared with 9.1

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 08/26/2013 02:33 PM, Rafael Martinez wrote: [] The SQL statement is: SELECT firstname || ' ' || lastname AS Name FROMPerson R WHERE R.gender like 'F' AND 19 (SELECT COUNT(DISTINCT filmId) FROM FilmParticipation F WHERE

[PERFORM] SQL statement over 500% slower with 9.2 compared with 9.1

| archive wal_sync_method | fdatasync work_mem| 16MB Any ideas on why this is happening and how to fix it? Thanks in advance for your time. regards, - -- Rafael Martinez Guerrero Center for Information Technology University of Oslo, Norway PGP

Re: [PERFORM] 9.2.2 - semop hanging

and slides. There is some way I can confirm this? 4) Rebooting the server didn't make any difference. Appreciate any help, Rafael On Tue, Jun 11, 2013 at 9:48 AM, Rafael Domiciano rafael.domici...@gmail.com wrote: Hello all you guys, Since saturday I'm get stucked in a very strange situation

[PERFORM] 9.2.2 - semop hanging

Hello all you guys, Since saturday I'm get stucked in a very strange situation: from time to time (sometimes with intervals less than 10 minutes), the server get stucked/hang (I dont know how to call it) and every connections on postgres (dont matter if it's SELECT, UPDATE, DELETE, INSERT,

[PERFORM] DBD-Pg prepared statement versus plain execution

that it runs faster the time used by parse+bind+deallocate? Thanks in advance. regards, - -- Rafael Martinez Guerrero Center for Information Technology University of Oslo, Norway PGP Public Key: http://folk.uio.no/rafael/ -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.10 (GNU/Linux) Comment: Using

[PERFORM] timing != log duration

: duration: 0.055 ms statement: DEALLOCATE foo; - --- Thanks in advance regards, - -- Rafael Martinez Guerrero Center for Information Technology University of Oslo, Norway PGP Public Key: http://folk.uio.no/rafael/ -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.10

Re: [PERFORM] Dramatic change in memory usage with version 9.1

(it was introduced in 9.1.2) We could not finish a full import of some of our databases with 9.1.2 because all ram+swap was used in a matter of minuttes. We are using 9.1.1 and we haven't seen the 9.1.2 behavior. regards, - -- Rafael Martinez Guerrero Center for Information Technology

Re: [PERFORM] Dramatic change in memory usage with version 9.1

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 12/20/2011 12:15 PM, Cédric Villemain wrote: Le 19 décembre 2011 16:04, Rafael Martinez r.m.guerr...@usit.uio.no a écrit : -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hello I am sending this email to ask if anyone has noticed a change

Re: [PERFORM] Dramatic change in memory usage with version 9.1

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 12/21/2011 12:48 AM, Craig Ringer wrote: On 19/12/2011 11:04 PM, Rafael Martinez wrote: Any ideas about why this dramatic change in memory usage when the only thing apparently changed from our side is the postgres version? It'd be interesting

[PERFORM] Dramatic change in memory usage with version 9.1

a picture about what we are talking about: * Overview of how memory use changed in one of our servers after the upgrade in the begynning og week 49: http://folk.uio.no/rafael/upgrade_to_9.1/server-1/memory-month.png http://folk.uio.no/rafael/upgrade_to_9.1/server-1/memory-year.png * We could think

Re: [PERFORM] WAL partition filling up after high WAL activity

disappeared. PS.- In our case, the disk space used by all the extra WAL files was almost the equivalent to the 17GB of our GIN index. regards, - -- Rafael Martinez Guerrero Center for Information Technology University of Oslo, Norway PGP Public Key: http://folk.uio.no/rafael/ -BEGIN PGP

Re: [PERFORM] raid setup for db

Thomas Finneid wrote: Hi I am wondering what stripe size, on a raid 0, is the most suitable for postgres 8.2? Hello Raid 0 for a database? This is a disaster waiting to happen. Are you sure you want to use raid0? regards -- Rafael Martinez, r.m.guerr...@usit.uio.no Center

Re: [PERFORM] Query planner making bad decisions

: ((customfields_1.name)::text = 'QA Origin'::text) Total runtime: 2142.347 ms (26 rows) -- Rafael Martinez, r.m.guerr...@usit.uio.no Center for Information Technology Services University of Oslo, Norway PGP Public Key: http://folk.uio.no/rafael/ -- Sent via pgsql-performance mailing list

[PERFORM] SQL With Dates

the SQL with fix date (date = '2009-04-01') o cost in explain always still about 200 or less. But with a period the cost is high, about 6000 or more. Select is using Index and the date is using index too. There is some way to use date period with less cost? Rafael Domiciano

Re: [PERFORM] SQL With Dates

Hello Grzegorz, Thnks for response, but lot of the selects is using BETWEEN and the cost is the same. 2009/4/20 Grzegorz Jaśkiewicz gryz...@gmail.com BETWEEN X AND Y On Mon, Apr 20, 2009 at 2:55 PM, Rafael Domiciano rafael.domici...@gmail.com wrote: Hello People, I have initiated

[PERFORM] Suspicious top output

Are those connections that were not closed or something like that? should i worry? Thanks in advance, as always yours trully Rafael -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql

Re: [PERFORM] Performance problems deleting data

Tom Lane wrote: Rafael Martinez [EMAIL PROTECTED] writes: Any ideas why it is taking 2462558.813 ms to finish when the total time for the deletion is 2.546 ms + 3.422 ms + 0.603ms? Hei Tom, I got this information from my colleague: Is the problem repeatable? Repeatable as in about 30

[PERFORM] Performance problems deleting data

to wait for other tables, that would not show anywhere? (only in pg_locks) Thanks in advance regards -- Rafael Martinez, [EMAIL PROTECTED] Center for Information Technology Services University of Oslo, Norway PGP Public Key: http://folk.uio.no/rafael/ -- Sent via pgsql-performance mailing

Re: [PERFORM] Need to run CLUSTER to keep performance

Rafael Martinez wrote: Heikki Linnakangas wrote: In 8.1, CLUSTER will remove those tuples anyway, but it's actually not correct. With other words, we have to be very carefull to not run CLUSTER on a table been modified inside a transaction if we do not want to lose data

Re: [PERFORM] Need to run CLUSTER to keep performance

Tom Lane wrote: Rafael Martinez [EMAIL PROTECTED] writes: Heikki Linnakangas wrote: On a small table like that you could run VACUUM every few minutes without much impact on performance. That should keep the table size in check. Ok, we run VACUUM ANALYZE only one time a day, every night

Re: [PERFORM] Need to run CLUSTER to keep performance

Rafael Martinez wrote: We have more information about this 'problem'. Sending this just in case it can help Checking all the log files from these vacuum jobs we have been running, we found one that looks difference from the rest, specially on the amount of removed pages. We

[PERFORM] Need to run CLUSTER to keep performance

. -- The tables with this 'problem' are not big, so CLUSTER finnish very fast and it does not have an impact in the access because of locking. But we wonder why this happens. Do you need more information? Thanks in advance. regards -- Rafael Martinez

Re: [PERFORM] Need to run CLUSTER to keep performance

Heikki Linnakangas wrote: Rafael Martinez wrote: The tables with this 'problem' are not big, so CLUSTER finnish very fast and it does not have an impact in the access because of locking. But we wonder why this happens. 2 seconds for seq scanning 12 MB worth of data sounds like a lot. Have

Re: [PERFORM] Need to run CLUSTER to keep performance

on pages. You need to run CLUSTER or VACUUM FULL once to shrink the relation, but after that frequent-enough VACUUMs should keep the table size down. Ok, thanks for the advice. We will try this and will come back with more information. -- Rafael Martinez, [EMAIL PROTECTED] Center for Information

Re: [PERFORM] Need to run CLUSTER to keep performance

Alvaro Herrera wrote: Rafael Martinez wrote: The 'problem' is that performance decrease during the day and the only thing that helps is to run CLUSTER on the table with problems. VACUUM ANALYZE does not help. Probably because all the live tuples are clustered at the end of the table

Re: [PERFORM] random_page_costs - are defaults of 4.0 realistic for SCSI RAID 1

Jean-David Beyer escribió: Gregory Stark wrote (in part): The extra spindles speed up sequential i/o too so the ratio between sequential and random with prefetch would still be about 4.0. But the ratio between sequential and random without prefetch would be even higher. I never

[PERFORM] Vacum Analyze problem

, however, sudennly, it starts to take forever (the execution of the query) until i make another VACUUM ANALYZE, and so on ... I'd like to point that i am a novice when it comes to non basic postgresql performance related stuff. Thank you all in advance Rafael ---(end

Re: [PERFORM] Vacum Analyze problem

). Thanks for your reply. Rafael ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings

Re: [PERFORM] Vacum Analyze problem

stuff. Thank you all in advance Rafael ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through

Re: [PERFORM] upgraded to pgsql 8.2.4, getting worse performance then 7.4.x

#maintenance_work_mem = 16MB# min 1MB fsync = off # turns forced synchronization on or off #effective_cache_size = 128MB [] -- Rafael Martinez, [EMAIL PROTECTED] Center for Information Technology Services University of Oslo, Norway PGP

[PERFORM] Problems with an update-from statement and pg-8.1.4

btree (received) received_id_index btree (mail_id) received_queue_id_index btree (queue_id) Foreign-key constraints: $1 FOREIGN KEY (mail_id) REFERENCES mail(mail_id) - Thanks in advance. regards, -- Rafael Martinez

Re: [PERFORM] Problems with an update-from statement and pg-8.1.4

On Wed, 2006-12-06 at 14:55 -0500, Ted Allen wrote: Stephan Szabo wrote: On Wed, 6 Dec 2006, Rafael Martinez wrote: mailstats=# EXPLAIN update mail SET spamscore = '-5.026' FROM mail m, mail_received mr where mr.mail_id = m.mail_id AND mr.queue_id = '1GrxLs-0004N9-I1' and mr.mailhost

Re: [PERFORM] Problems with an update-from statement and pg-8.1.4

On Wed, 2006-12-06 at 14:19 -0600, Erik Jones wrote: Rafael Martinez wrote: On Wed, 2006-12-06 at 14:55 -0500, Ted Allen wrote: Stephan Szabo wrote: On Wed, 6 Dec 2006, Rafael Martinez wrote: mailstats=# EXPLAIN update mail SET spamscore = '-5.026' FROM mail m

[PERFORM] Same SQL, 104296ms of difference between 7.4.12 and 8.0.7

rows=48 width=13) (actual time=0.013..0.850 rows=54 loops=1) Filter: (((rightname)::text = 'OwnTicket'::text) AND (((objecttype)::text = 'RT::System'::text) OR ((objecttype)::text = 'RT::Queue'::text))) Total runtime: 108486.306 ms (21 rows) -- Rafael Martinez

Re: [PERFORM] Same SQL, 104296ms of difference between 7.4.12 and

On Fri, 2006-04-07 at 15:31, Richard Huxton wrote: Rafael Martinez Guerrero wrote: Hello I have a sql statement that takes 108489.780 ms with 8.0.7 in a RHEL4/amd64linux server with 2xAMD Opteron(tm) Processor 275 2.00GHz / 8GB RAM and only 4193.588 ms with 7.4.12 in a RHEL3/386linux

Re: [PERFORM] Same SQL, 104296ms of difference between 7.4.12 and

. The problem is not the amount of memory. It works much faster with only 16M and 7.4.12 than 8.0.7. -- Rafael Martinez, [EMAIL PROTECTED] Center for Information Technology Services University of Oslo, Norway PGP Public Key: http://folk.uio.no/rafael/ ---(end

Re: [PERFORM] Same SQL, 104296ms of difference between 7.4.12 and

On Fri, 2006-04-07 at 13:36 -0400, Tom Lane wrote: I wrote: Rafael Martinez Guerrero [EMAIL PROTECTED] writes: I have a sql statement that takes 108489.780 ms with 8.0.7 in a RHEL4/amd64linux server with 2xAMD Opteron(tm) Processor 275 2.00GHz / 8GB RAM and only 4193.588 ms with 7.4.12

[PERFORM] Help with optimizing a sql statement

) cachedgroupmembers3 btree (groupid) disgroumem btree (groupid, memberid, disabled) -- Rafael Martinez, [EMAIL PROTECTED] Center for Information Technology Services University of Oslo, Norway PGP Public Key: http

Re: [PERFORM] Help with optimizing a sql statement

On Thu, 2006-02-09 at 18:22 -0500, Tom Lane wrote: Rafael Martinez Guerrero [EMAIL PROTECTED] writes: WHERE ((ACL_2.RightName = 'OwnTicket')) AND ((CachedGroupMembers_4.MemberId = Principals_1.id)) AND ((Groups_3.id = CachedGroupMembers_4.GroupId)) AND ((Principals_1.Disabled = '0