On 24 July 2012 20:21, Richard Huxton wrote:
> On 24/07/12 12:14, Aleksei Arefjev wrote:
>
>> Hi,
>>
>> In statistical reports gathered by PgBadger on our PostgreSQL databases
>> almost always we have in "Queries that took up the most time" report
>> table information about transactions start tim
On Tue, Jul 24, 2012 at 6:23 PM, John Lister wrote:
> Cheers, I'll give it a go, I wonder if this is likely to be integrated into
> the main code? As has been mentioned here before, postgresql isn't as badly
> affected as mysql for example, but I'm wondering if the trend to larger
> memory and mor
My experience is that disabling swap and turning off zone_reclaim_mode
gets rid of any real problem for a large memory postgresql database
server. While it would be great to have a NUMA aware pgsql, I
question the solidity and reliability of the current linux kernel
implementation in a NUMA eviron
On Tue, Jul 24, 2012 at 6:23 PM, John Lister wrote:
> Cheers, I'll give it a go, I wonder if this is likely to be integrated into
> the main code? As has been mentioned here before, postgresql isn't as badly
> affected as mysql for example, but I'm wondering if the trend to larger
> memory and mor
On 24/07/2012 21:12, Claudio Freire wrote:
On Tue, Jul 24, 2012 at 3:41 PM, Claudio Freire wrote:
On Tue, Jul 24, 2012 at 3:36 PM, John Lister wrote:
Do you have a suggestion about how to do that? I'm running Ubuntu 12.04 and
PG 9.1, I've modified pg_ctlcluster to cause pg_ctl to use a wrappe
On Tue, Jul 24, 2012 at 5:12 PM, Claudio Freire wrote:
> Something like the attached patch (untested)
Sorry, on that patch, MPOL_INTERLEAVE should be MPOL_DEFAULT
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postg
On Tue, Jul 24, 2012 at 3:41 PM, Claudio Freire wrote:
> On Tue, Jul 24, 2012 at 3:36 PM, John Lister
> wrote:
>> Do you have a suggestion about how to do that? I'm running Ubuntu 12.04 and
>> PG 9.1, I've modified pg_ctlcluster to cause pg_ctl to use a wrapper script
>> which starts the postmas
On Tue, Jul 24, 2012 at 11:27 AM, Laszlo Nagy wrote:
>
> > I wonder if UFS has better performance or not. Or can you suggest
>> > another fs? Just of the PGDATA directory.
>>
>
> Relying on physically moving a disk isn't a good backup/recovery
> strategy. Disks are the least reliable single co
On Tue, Jul 24, 2012 at 3:36 PM, John Lister wrote:
> Do you have a suggestion about how to do that? I'm running Ubuntu 12.04 and
> PG 9.1, I've modified pg_ctlcluster to cause pg_ctl to use a wrapper script
> which starts the postmaster using a numactl wrapper, but all subsequent
> client process
On Tue, Jul 18, 2012 at 2:38 AM, Claudio Freire wrote:
>It must have been said already, but I'll repeat it just in case:
>I think postgres has an easy solution. Spawn the postmaster with
>"interleave", to allocate shared memory, and then switch to "local" on
>the backends.
Do you have a suggesti
On 24.07.2012 14:51, Laszlo Nagy wrote:
* UFS is not journaled.
There is journal support for UFS as far as i know. Please have a look
at the gjournal manpage.
Yes, but gjournal works for disk devices. I would have rely on the hw
card for RAID. When the card goes wrong I won't be able to
> I wonder if UFS has better performance or not. Or can you suggest
> another fs? Just of the PGDATA directory.
Relying on physically moving a disk isn't a good backup/recovery
strategy. Disks are the least reliable single component in a modern
computer. You should figure out the b
On 24/07/12 12:14, Aleksei Arefjev wrote:
Hi,
In statistical reports gathered by PgBadger on our PostgreSQL databases
almost always we have in "Queries that took up the most time" report
table information about transactions start time ('BEGIN;' command).
Something like that in example below:
2
Thomas Kellerer writes:
> Tom Lane wrote on 24.07.2012 17:55:
>> FWIW, it might be helpful to cast this as a NOT EXISTS rather than
>> NOT IN subquery.
> Hmm. How would you change that into an NOT EXISTS clause (so that one of the
> duplicates remains)
> Everything I come up with is in fact slow
Tom Lane wrote on 24.07.2012 17:55:
Joins on tid columns just aren't supported very well at the moment.
Partly that's from lack of round tuits, and partly it's because it
doesn't seem all that wise to encourage people to use them. There
are gotchas if any of the rows receive concurrent updates.
Thomas Kellerer writes:
> Tom Lane, 24.07.2012 16:23:
>> IIRC, type tid doesn't have any hash support.
> So the "bad" plan is expected?
Joins on tid columns just aren't supported very well at the moment.
Partly that's from lack of round tuits, and partly it's because it
doesn't seem all that wis
This may be another issue of the problem discussed here:
http://postgresql.1045698.n5.nabble.com/avoid-prepared-statements-on-complex-queries-td4996363.html
(Kris Jurka explains the crux of it in that thread).
Note that it seems the preparing/planning interaction was not the
poster's actual proble
Tom Lane, 24.07.2012 16:23:
Thomas Kellerer writes:
DELETE FROM dupes
WHERE id NOT IN (SELECT min(b.id)
FROM dupes b
GROUP BY first_name, last_Name
HAVING count(*) > 1);
Doesn't that kill the non-duplicates too?
Ah right - another g
On 24/07/2012 15:30, Craig James wrote:
On Tue, Jul 24, 2012 at 6:22 AM, Ioannis Anagnostopoulos
mailto:ioan...@anatec.com>> wrote:
Hello,
The Postres 9.0 database we use gets about 20K inserts per minute.
As long as you don't query at the same time the database is
copying fi
>
> 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 c
On Tue, Jul 24, 2012 at 6:22 AM, Ioannis Anagnostopoulos wrote:
> Hello,
> The Postres 9.0 database we use gets about 20K inserts per minute. As long
> as you don't query at the same time the database is copying fine. However
> long running queries seems to delay so much the db that the applicati
On 24.07.2012 14:51, Laszlo Nagy wrote:
* UFS is not journaled.
There is journal support for UFS as far as i know. Please have a look at
the gjournal manpage.
Greetings,
Torsten
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscrip
Thomas Kellerer writes:
> DELETE FROM dupes
> WHERE id NOT IN (SELECT min(b.id)
> FROM dupes b
> GROUP BY first_name, last_Name
> HAVING count(*) > 1);
Doesn't that kill the non-duplicates too?
> Why does the usage of the CTID column change
Hello,
The Postres 9.0 database we use gets about 20K inserts per minute. As
long as you don't query at the same time the database is copying fine.
However long running queries seems to delay so much the db that the
application server buffers the incoming data as it cannot insert them
fast eno
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
Hi.
As far as I know UFS is faster than ZFS on FreeBSD 9.0.
Some users reported stability problem with ZFS on AMD64 and maybe UFS is
better choice.
Best regards
Georgi
On 07/24/2012 03:51 PM, Laszlo Nagy wrote:
Hello,
Under FreeBSD 9, what filesystem should I use for PostgreSQL? (Dell
Pow
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 wrong, I can move the zfs array to a different server.
*
hi al,
> With Parition :-
>
>
> explain analyze
> select *
> from table1 as c
> inner join table2 as a on c.crmid = a.table2id and deleted = 0
> where module ='Leads';
>
> QUERY PLAN
In addition to the previous mail, I am adding here that -
My Postgresql version is 9.1.2.
And one more thing, executing the following query I got two query plan
where the second one looked strange to me.
If showed to take 20950.579 ms, but investigating both the plan I found
that it took less time
Hi,
In statistical reports gathered by PgBadger on our PostgreSQL databases
almost always we have in "Queries that took up the most time" report table
information about transactions start time ('BEGIN;' command). Something
like that in example below:
23h34m52.26s48,556,1670.00sBEG
I partitioned a table, but didn't find any improvement in query timing.
The basic table was like as follows :-
\d table1
>Table "public.table1_old"
> Column|Type | Modifiers
> --+-+
>
Hi,
I was testing a query to delete duplicates to see how well using ctid works if
the table doesn't have a unique identifier available.
The table definition is:
create table dupes
(
id integer primary key,
first_name text,
last_name text
);
My test table has 100.000 rows with ~13000
> > That latter test - won't that pretty much just read from the page
> cache?
> > 'sync' may well have forced dirty pages to disk but does it actually
> > evict them to?
>
> Basically, the cache is avoided because of the size of the file.
> 600 blocks at 8k exceeds the size of RAM in the mach
Hi Jim,> > I've already tried something very similar using dd. No performance> penalties during a normal running of the system - or when this blocking> happens either actually. But I agree, it does indeed sound like some> sort of I/O problem. I just don't know what! I do have a few more tricks> up
On Mon, Jul 23, 2012 at 4:07 PM, Tom Lane wrote:
> Daniele Varrazzo writes:
>> Since "big" was sharded, the query plan results in something like:
>> [ugly]
>
> [ squint... ] 9.1 certainly ought to be able to find a smarter plan for
> such a case. For instance, if I try this on 9.1 branch tip:
>
> This looks like this is a pure IO issue. You mentioned that this was a
> software RAID system. I wonder if there's some complication there.
>
> Have you tried setting the disk queues to deadline?
>
> echo "deadline" > /sys/block/{DEVICE-NAME}/queue/scheduler
> That might help. But to be honest,
On Tue, 2012-07-24 at 08:30 +0800, Craig Ringer wrote:
> On 07/23/2012 10:46 PM, Jim Vanns wrote:
> > Nothing obvious - and we log a fair amount. No tmp table creations,
> > no locks held.
> >
> > To add to this EXPLAIN reports it took only 0.23ms to run (for example)
> > whereas the wall clock tim
Thank you for this response.
It turns out our performance problems were solved when I switched off 'Prepared
statements' in Geoserver for the PostGIS data store. It makes quite a huge
difference.
Riaan
>>> Brett Walker 7/24/2012 09:54 AM >>>
Hi Riaan,
I am familiar if the Geoserver/Geoto
Hi Riaan,
I am familiar if the Geoserver/Geotools package and I believe that the problem
is not Postgres/PostGIS but rather Geoserver.
The DB Connection Closed message is not sent at the end of the query, but
rather at the end of the rendering. There is more than just querying happening
between
Hi
We have noticed something strange in the interaction between our Geoserver
instance and Postgres/PostGIS.
After setting Geoserver's log level to include developer debugging, I managed
to capture a single request from Geoserver WMS to PostGIS.
The (shortened) sequence of events and t
40 matches
Mail list logo
