Re: [PERFORM] Response time increases over time
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
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
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
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
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
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
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
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
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
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
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
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
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
[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
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
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