Re: [PERFORM] Response time increases over time

2011-12-08 Thread Havasvölgyi Ottó
Yes, ext3 is the global file system, and you are right, PG xlog and data
are on this one.
Is this really what happens Aidan at fsync?
What is be the best I can do?
Mount xlog directory to a separate file system?
If so, which file system fits the best for this purpose?
Should I also mount the data separately, or is that not so important?

The strange thing is that InnoDb data and xlog are also on the same
filesystem, but on a separate one (ext4) from the global one.

Thanks,
Otto




2011/12/8 Aidan Van Dyk ai...@highrise.ca

 On Wed, Dec 7, 2011 at 5:13 PM, Havasvölgyi Ottó
 havasvolgyi.o...@gmail.com wrote:

  So there seems to be something on this Debian machine that hinders
  PostgreSQL to perform better. With 8.4 I logged slow queries (with 9.1
 not
  yet), and almost all were COMMIT, taking 10-20-30 or even more ms. But at
  the same time the fsync rate can be quite high based on pg_test_fsync, so
  probably not fsync is what makes it slow. Performance seems to degrade
  drastically as I increase the concurrency, mainly concurrent commit has
  problems as I can see.

  Do anybody have any idea based on this info about what can cause such
  behaviour, or what I could check or try?

 Let me guess, debian squeeze, with data and xlog on both on a single
 ext3 filesystem, and the fsync done by your commit (xlog) is flushing
 all the dirty data of the entire filesystem (including PG data writes)
 out before it can return...

 a.

 --
 Aidan Van Dyk Create like a
 god,
 ai...@highrise.ca   command like a
 king,
 http://www.highrise.ca/   work like a
 slave.



Re: [PERFORM] Response time increases over time

2011-12-08 Thread Marti Raudsepp
On Thu, Dec 8, 2011 at 06:37, Aidan Van Dyk ai...@highrise.ca wrote:
 Let me guess, debian squeeze, with data and xlog on both on a single
 ext3 filesystem, and the fsync done by your commit (xlog) is flushing
 all the dirty data of the entire filesystem (including PG data writes)
 out before it can return...

This is fixed with the data=writeback mount option, right?
(If it's the root file system, you need to add
rootfsflags=data=writeback to your kernel boot flags)

While this setting is safe and recommended for PostgreSQL and other
transactional databases, it can cause garbage to appear in recently
written files after a crash/power loss -- for applications that don't
correctly fsync data to disk.

Regards,
Marti

-- 
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] Response time increases over time

2011-12-08 Thread Havasvölgyi Ottó
I have moved the data directory (xlog, base, global, and everything) to an
ext4 file system. The result hasn't changed unfortuately. With the same
load test the average response time: 80ms; from 40ms to 120 ms everything
occurs.
This ext4 has default settings in fstab.
Have you got any other idea what is going on here?

Thanks,
Otto




2011/12/8 Marti Raudsepp ma...@juffo.org

 On Thu, Dec 8, 2011 at 06:37, Aidan Van Dyk ai...@highrise.ca wrote:
  Let me guess, debian squeeze, with data and xlog on both on a single
  ext3 filesystem, and the fsync done by your commit (xlog) is flushing
  all the dirty data of the entire filesystem (including PG data writes)
  out before it can return...

 This is fixed with the data=writeback mount option, right?
 (If it's the root file system, you need to add
 rootfsflags=data=writeback to your kernel boot flags)

 While this setting is safe and recommended for PostgreSQL and other
 transactional databases, it can cause garbage to appear in recently
 written files after a crash/power loss -- for applications that don't
 correctly fsync data to disk.

 Regards,
 Marti



Re: [PERFORM] Response time increases over time

2011-12-08 Thread Bob Lunney
Otto,

Separate the pg_xlog directory onto its own filesystem and retry your tests.

Bob Lunney



 From: Havasvölgyi Ottó havasvolgyi.o...@gmail.com
To: Marti Raudsepp ma...@juffo.org 
Cc: Aidan Van Dyk ai...@highrise.ca; pgsql-performance@postgresql.org 
Sent: Thursday, December 8, 2011 9:48 AM
Subject: Re: [PERFORM] Response time increases over time
 

I have moved the data directory (xlog, base, global, and everything) to an ext4 
file system. The result hasn't changed unfortuately. With the same load test 
the average response time: 80ms; from 40ms to 120 ms everything occurs.
This ext4 has default settings in fstab.
Have you got any other idea what is going on here?

Thanks,
Otto





2011/12/8 Marti Raudsepp ma...@juffo.org

On Thu, Dec 8, 2011 at 06:37, Aidan Van Dyk ai...@highrise.ca wrote:
 Let me guess, debian squeeze, with data and xlog on both on a single
 ext3 filesystem, and the fsync done by your commit (xlog) is flushing
 all the dirty data of the entire filesystem (including PG data writes)
 out before it can return...

This is fixed with the data=writeback mount option, right?
(If it's the root file system, you need to add
rootfsflags=data=writeback to your kernel boot flags)

While this setting is safe and recommended for PostgreSQL and other
transactional databases, it can cause garbage to appear in recently
written files after a crash/power loss -- for applications that don't
correctly fsync data to disk.

Regards,
Marti


Re: [PERFORM] Response time increases over time

2011-12-08 Thread Havasvölgyi Ottó
I have put pg_xlog back to the ext3 partition, but nothing changed.
I have also switched off sync_commit, but nothing. This is quite
interesting...
Here is a graph about the transaction time (sync_commit off, pg_xlog on
separate file system): Graph http://uploadpic.org/v.php?img=qIjfWBkHyE
On the graph the red line up there is the tranaction/sec, it is about 110,
and does not get lower as the transaction time gets higher.
Based on this, am I right that it is not the commit, that causes these high
transaction times?
Kernel version is 2.6.32.
Any idea is appreciated.

Thanks,
Otto




2011/12/8 Bob Lunney bob_lun...@yahoo.com

 Otto,

 Separate the pg_xlog directory onto its own filesystem and retry your
 tests.

 Bob Lunney

   --
 *From:* Havasvölgyi Ottó havasvolgyi.o...@gmail.com
 *To:* Marti Raudsepp ma...@juffo.org
 *Cc:* Aidan Van Dyk ai...@highrise.ca; pgsql-performance@postgresql.org
 *Sent:* Thursday, December 8, 2011 9:48 AM

 *Subject:* Re: [PERFORM] Response time increases over time

 I have moved the data directory (xlog, base, global, and everything) to an
 ext4 file system. The result hasn't changed unfortuately. With the same
 load test the average response time: 80ms; from 40ms to 120 ms everything
 occurs.
 This ext4 has default settings in fstab.
 Have you got any other idea what is going on here?

 Thanks,
 Otto




 2011/12/8 Marti Raudsepp ma...@juffo.org

 On Thu, Dec 8, 2011 at 06:37, Aidan Van Dyk ai...@highrise.ca wrote:
  Let me guess, debian squeeze, with data and xlog on both on a single
  ext3 filesystem, and the fsync done by your commit (xlog) is flushing
  all the dirty data of the entire filesystem (including PG data writes)
  out before it can return...

 This is fixed with the data=writeback mount option, right?
 (If it's the root file system, you need to add
 rootfsflags=data=writeback to your kernel boot flags)

 While this setting is safe and recommended for PostgreSQL and other
 transactional databases, it can cause garbage to appear in recently
 written files after a crash/power loss -- for applications that don't
 correctly fsync data to disk.

 Regards,
 Marti







Re: [PERFORM] Response time increases over time

2011-12-07 Thread Havasvölgyi Ottó
Thanks, Josh.
The only reason I tried 8.4 first is that it was available for Debian as
compiled package, so it was simpler for me to do it. Anyway I am going to
test 9.1 too. I will post about the results.

Best reagrds,
Otto


2011/12/7 Josh Berkus j...@agliodbs.com

 On 12/6/11 4:30 PM, Havasvölgyi Ottó wrote:
  Is there so much difference between 8.4 and 9.1, or is this something
 else?
  Please tell me if any other info is needed.

 It is fairly likely that the difference you're seeing here is due to
 improvements made in checkpointing and other operations made between 8.4
 and 9.1.

 Is there some reason you didn't test 9.1 on Linux to compare the two?

 --
 Josh Berkus
 PostgreSQL Experts Inc.
 http://pgexperts.com

 --
 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] Response time increases over time

2011-12-07 Thread Mario Splivalo
On 12/07/2011 09:23 AM, Havasvölgyi Ottó wrote:
 Thanks, Josh.
 The only reason I tried 8.4 first is that it was available for Debian as
 compiled package, so it was simpler for me to do it. Anyway I am going
 to test 9.1 too. I will post about the results.
 

If you're using squeeze, you can get 9.1 from the debian backports.

Mario

-- 
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] Response time increases over time

2011-12-07 Thread Havasvölgyi Ottó
Thanks for that Mario, I will check it out.

@All:
Anyway, I have compiled 9.1.2 from source, and unfortunately the
performance haven't got better at the same load, it is consistently quite
low (~70 ms average transaction time with 100 clients) on this Debian. I am
quite surprised about this, it is unrealistically high.
I have run pg_test_fsync, and showed about 2600 fsync/sec, which means HDD
has write caching on (it is a 7200 rpm drive, there is no HW RAID
controller). However my other machine, the simple Win7 one, on which
performance was so good and consistent, fsync/sec was a lot lower, only
about 100 as I can remember, so it probably really flushed each transaction
to disk.
I have also run load simulation on this Debian machine with InnoDb, and it
performed quite well, so the machine itself is good enough to handle this.
On the other hand it is quite poor on Win7, but that's another story...

So there seems to be something on this Debian machine that hinders
PostgreSQL to perform better. With 8.4 I logged slow queries (with 9.1 not
yet), and almost all were COMMIT, taking 10-20-30 or even more ms. But at
the same time the fsync rate can be quite high based on pg_test_fsync, so
probably not fsync is what makes it slow. Performance seems to degrade
drastically as I increase the concurrency, mainly concurrent commit has
problems as I can see.
I also checked that connection pooling works well, and clients don't
close/open connections.
I also have a graph about outstanding transaction count over time, and it
is quite strange: it shows that low performce (20-30 xacts at a time) and
high-performace (5 xact at a time) parts are alternating quite frequently
instead of being more even.
Do anybody have any idea based on this info about what can cause such
behaviour, or what I could check or try?

Thanks in advance,
Otto

2011/12/7 Mario Splivalo mario.spliv...@megafon.hr

 On 12/07/2011 09:23 AM, Havasvölgyi Ottó wrote:
  Thanks, Josh.
  The only reason I tried 8.4 first is that it was available for Debian as
  compiled package, so it was simpler for me to do it. Anyway I am going
  to test 9.1 too. I will post about the results.
 

 If you're using squeeze, you can get 9.1 from the debian backports.

Mario

 --
 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] Response time increases over time

2011-12-07 Thread Aidan Van Dyk
On Wed, Dec 7, 2011 at 5:13 PM, Havasvölgyi Ottó
havasvolgyi.o...@gmail.com wrote:

 So there seems to be something on this Debian machine that hinders
 PostgreSQL to perform better. With 8.4 I logged slow queries (with 9.1 not
 yet), and almost all were COMMIT, taking 10-20-30 or even more ms. But at
 the same time the fsync rate can be quite high based on pg_test_fsync, so
 probably not fsync is what makes it slow. Performance seems to degrade
 drastically as I increase the concurrency, mainly concurrent commit has
 problems as I can see.

 Do anybody have any idea based on this info about what can cause such
 behaviour, or what I could check or try?

Let me guess, debian squeeze, with data and xlog on both on a single
ext3 filesystem, and the fsync done by your commit (xlog) is flushing
all the dirty data of the entire filesystem (including PG data writes)
out before it can return...

a.

-- 
Aidan Van Dyk                                             Create like a god,
ai...@highrise.ca                                       command like a king,
http://www.highrise.ca/                                   work like a slave.

-- 
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] Response time increases over time

2011-12-06 Thread Josh Berkus
On 12/6/11 4:30 PM, Havasvölgyi Ottó wrote:
 Is there so much difference between 8.4 and 9.1, or is this something else?
 Please tell me if any other info is needed.

It is fairly likely that the difference you're seeing here is due to
improvements made in checkpointing and other operations made between 8.4
and 9.1.

Is there some reason you didn't test 9.1 on Linux to compare the two?

-- 
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com

-- 
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] response time when querying via JDBC and via psql differs

2008-02-26 Thread valgog
Do not use setString() method to pass the parameter to the
PreparedStatement in JDBC. Construct an SQL query string as you write
it here and query the database with this new SQL string. This will
make the planner to recreate a plan every time for every new SQL
string per session (that is not usually good) but it will make the
planner to choose a correct plan.

-- Valentine Gogichashvili

On Feb 25, 11:06 am, [EMAIL PROTECTED] (Pavel Rotek) wrote:
 Hi all,

   i have strange problem with performance in PostgreSQL (8.1.9). My problem
 shortly:

   I'm using postgreSQL via JDBC driver (postgresql-8.1-404.jdbc3.jar) and
 asking the database for search on table with approximately 3 000 000
 records.
   I have created functional index table(lower(href) varchar_pattern_ops)
 because of lower case like searching. When i ask the database directly
 from psql, it returns result in 0,5 ms, but when i put the same command via
 jdbc driver, it returns in 10 000 ms. Where can be the problem?? Any problem
 with PostgreSQL tuning??

 The command is
 select df.id as id, df.c as c, df.href as href, df.existing as existing,
 df.filesize as filesize from documentfile df where (lower(href) like
 'aba001!_223581.djvu' escape '!' ) order by  id limit 1   Thank you very
 much for any help,

   Kind regards,

   Pavel Rotek


---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq


Re: [PERFORM] response time when querying via JDBC and via psql differs

2008-02-25 Thread Nikolas Everett
The thing to remember here is that prepared statements are only planned once
and strait queries are planned for each query.

When you give the query planner some concrete input like in your example
then it will happily use the index because it can check if the input starts
with % or _.  If you use JDBC to set up a prepared statement like:

 select df.id as id, df.c as c, df.href as href, df.existing as existing,
 df.filesize as filesize from documentfile df where (lower(href) like ?
 escape '!' ) order by  id limit 1

then the query planner takes the safe route like Markus said and doesn't use
the index.

I think your best bet is to use connection.createStatement instead of
connection.prepareStatement.  The gain in query performance will offset the
loss in planning overhead.  I'm reasonably sure the plans are cached anyway.

--Nik
On Mon, Feb 25, 2008 at 6:10 AM, Markus Bertheau 
[EMAIL PROTECTED] wrote:

 2008/2/25, Pavel Rotek [EMAIL PROTECTED]:
I have created functional index table(lower(href) varchar_pattern_ops)
  because of lower case like searching. When i ask the database directly
  from psql, it returns result in 0,5 ms, but when i put the same command
 via
  jdbc driver, it returns in 10 000 ms. Where can be the problem?? Any
 problem
  with PostgreSQL tuning??

 Most likely the problem is that the JDBC driver uses prepared statements,
 in
 which the query is planned withouth the concrete argument value. For like
 only
 patterns that don't start with % or _ can use the index. Without the
 argument
 value PostgreSQL can't tell whether that is the case, so it takes the safe
 route and chooses a sequential scan.

 to solve this particular problem, you have to convince jdbc to not use a
 prepared statement for this particular query.

 Markus

 ---(end of broadcast)---
 TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match



Re: [PERFORM] Response time

2003-11-05 Thread radha.manohar
The \timing psql command gives different time for the same query executed
repeatedly.

So, how can we know the exact response time for any query?

Thanks and Regards,

Radha

 On Tue, 2003-11-04 at 09:49, [EMAIL PROTECTED] wrote:
 How do we measure the response time in postgresql?

 In addition to EXPLAIN ANALYZE, the log_min_duration_statement
 configuration variable and the \timing psql command might also be
 useful.

 -Neil



 ---(end of broadcast)---
 TIP 8: explain analyze is your friend




---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly


Re: [PERFORM] Response time

2003-11-05 Thread Neil Conway
[EMAIL PROTECTED] writes:
 The \timing psql command gives different time for the same query executed
 repeatedly.

That's probably because executing the query repeatedly results in
different execution times, as one would expect. \timing returns the
exact query response time, nevertheless.

-Neil


---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly


Re: [PERFORM] Response time

2003-11-05 Thread Andrew Sullivan
On Wed, Nov 05, 2003 at 11:35:22AM -0600, [EMAIL PROTECTED] wrote:
 The \timing psql command gives different time for the same query executed
 repeatedly.

Why do you believe that the same query will always take the same time
to execute?

A

-- 

Andrew Sullivan 204-4141 Yonge Street
Afilias CanadaToronto, Ontario Canada
[EMAIL PROTECTED]  M2P 2A8
 +1 416 646 3304 x110


---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly


Re: [PERFORM] Response time

2003-11-04 Thread Neil Conway
On Tue, 2003-11-04 at 09:49, [EMAIL PROTECTED] wrote:
 How do we measure the response time in postgresql?

In addition to EXPLAIN ANALYZE, the log_min_duration_statement
configuration variable and the \timing psql command might also be
useful.

-Neil



---(end of broadcast)---
TIP 8: explain analyze is your friend