Re: [PERFORM] Dramatic change in memory usage with version 9.1
Yes, perhaps it is related to it, and the cause is the same. But they mention here a special type inet. Best regards, Otto 2011/12/22 Rafael Martinez r.m.guerr...@usit.uio.no -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 12/22/2011 12:29 AM, Havasvölgyi Ottó wrote: Hello, Can you find some relation between the memory usage and insert statements? 9.1.2 has memory problems with inserts (even the simplest ones) on Linux and Windows too, I could produce it. Using pgbench also shows it. Some memory is not reclaimed. I could produce it also with 8.4.9 on Linux, I haven't tried 8.4.10 yet. [...] Hello Are you thinking about this bug?: http://archives.postgresql.org/pgsql-bugs/2011-12/msg00068.php Our problem should not have anything to do with this bug (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 University of Oslo, Norway PGP Public Key: http://folk.uio.no/rafael/ -BEGIN PGP SIGNATURE- Version: GnuPG v2.0.14 (GNU/Linux) iEYEARECAAYFAk7y8aUACgkQBhuKQurGihTD8gCgk0Frrd/mEjQrIgG9K0dzhNxN HzcAnRiQKWBgwZaNSmY+zrGjYSJFva9o =zcv3 -END PGP SIGNATURE-
Re: [PERFORM] Dramatic change in memory usage with version 9.1
Hello, Can you find some relation between the memory usage and insert statements? 9.1.2 has memory problems with inserts (even the simplest ones) on Linux and Windows too, I could produce it. Using pgbench also shows it. Some memory is not reclaimed. I could produce it also with 8.4.9 on Linux, I haven't tried 8.4.10 yet. Best regards, Otto 2011/12/21 Rafael Martinez r.m.guerr...@usit.uio.no -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 to know how much of your workload operates with SERIALIZABLE transactions, as the behavior of those has changed significantly in 9.1 and they _are_ more expensive in RAM terms now. Hello As long as I know, all the databases are using the default, read committed. We have almost 500 databases across all our servers, but we are only dbas. We provide the infrastructure necessary to run this and help users when they need it but we have not 100% control over how they are using the 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) iEYEARECAAYFAk7yHHAACgkQBhuKQurGihQz1gCdGJY6vk89lHKMldkYlkxOeJYJ GSMAoKDRCRo1UpqlUgItzCm/XV9aCbb8 =7f6R -END PGP SIGNATURE- -- 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
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
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
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
[PERFORM] Response time increases over time
Hi all, I am running a load simulation on Debian with PostgreSQL 8.4.9 (standard Debian package). Certain number of clients do the following stepsin a transaction (read commited level) periodically (about 1.1 transaction per second / client) and concurrently: -reads a record of table Machine and State (they each have about 300 records, read size is about 1.4 KB) -reads a record of table Card (it has about 1200 records) -reads some other records from other tables, all these are straightforward, single line queries (here there are even less records in the tables) -updates Machine (1 record, updates 2-3 integer values) -updates State (1 record, updates a bytea field, about 1,3KB) -updates Card (1 record, updates an integer) -inserts 1-1 record into 2 log tables Its important, that each client updates different records, so there is no update conflict. There are no triggers or rules. Tables have simple indexes, 2 at most. As I run the simulation with more and more clients, I can observe, that at the beginning of the simulation the transaction times are quite acceptable (20-30 ms) and quite uniform/smooth, but as the simultion progresses, it becomes higher (30-40-50-60 ms) and more and more non-uniform, but the tps interestingly remains the same during the simulation. With 100 clients this kind of behaviour can be seen very well. The simulation's duration is 500 sec. I wonder why this happens on this server, and how I can keep the response time as low as at the beginning. Just for comparison, I ran the same simulation on a Windows 7 notebook machine but with PostgreSQL 9.1.2 (downloaded from EnterpriseDB's site, not PostgreSQL Plus), and it did not show this problem even with 120 clients. It's transaction times were surprisingly smooth and consistent. The client code was the same in the 2 cases. Actually I ran first the test on the Windows machine, and after that on the better Debian. I expected that it would be even better there. Network latency is quite minimal, because the clients and the database server run on VMs on a server machine in the Linux case. Here is some important config variables from the 8.4 (9.1.2 is configured similarly): ssl=false shared_buffers=24MB (OS max currently, but should not be a problem because 9.1.2 performed quite well on Windows with 24 MB) work_mem=1MB maintainance_work_mem=16MB fsync=on sync_commit=on wal_sync_method=fsync full_page_writes=on wal_buffers=1MB commit_delay=0 checkpoint segments=8 effective_cache_size=256MB vacuum: default bgwriter: default I suspected that due to the lot of update, the tables get bloated with dead rows, but vacuum analyze verbose did not show that. It seems that something cannot keep up with the load, but tps does not change, just the response time gets higher. Could you please help me with what can cause this kind of behaviour on Linux? What setting should I change perhaps? 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. Thanks in advance, Otto
[PERFORM] Query composite index range in an efficient way
Hi, Let's say I have a table (tbl) with two columns: id1, id2. I have an index on (id1,id2) And I would like to query the (12;34) - (56;78) range (so it also may contain (12;58), (13;10), (40;80) etc.). With the index this can be done quite efficiently in theory, but I cannot find a way to make this happen. I triy this in the WHERE clause: WHERE (id112 or id1=12 and id2=34) and (id156 or id1=56 and id2=78) I created a big enough table (131072 records, and it had also a 3rd field with about 120 character text data). But Postgres performs a SeqScan. I have analyzed the table before it. I also tried Row constructors with a Between expression, but in this case Postgres handled the elements of the row independently, and this led to false query result. What should I write in the Where clause to get Postgres to perform an IndexScan? I would like to apply this to other datatypes also, not just ints. Thanks in advance, Otto
Re: [PERFORM] Query composite index range in an efficient way
Thanks, it's a very good idea! Otto 2009/2/17 Kevin Grittner kevin.gritt...@wicourts.gov Havasvölgyi Ottó havasvolgyi.o...@gmail.com wrote: WHERE (id112 or id1=12 and id2=34) and (id156 or id1=56 and id2=78) As others have pointed out, if you are using 8.2 or later, you should write this as: WHERE (id1, id2) = (12, 34) and (id1, id2) = (56, 78) On earlier versions you might want to try the logically equivalent: WHERE (id1 = 12 and (id1 12 or id2 = 34)) and (id1 = 56 and (id1 56 or id2 = 78)) -Kevin
Re: [PERFORM] Best way to check for new data.
Rodrigo, You could use LISTEN + NOTIFY with triggers. In after_insert_statement trigger you could notify a listener, the client could query it immediately. Best Regards, Otto - Original Message - From: Rodrigo Madera To: pgsql-performance@postgresql.org Sent: Friday, October 28, 2005 11:39 PM Subject: [PERFORM] Best way to check for new data. I have a table that holds entries as in a ficticious table Log(id integer, msg text). Lets say then that I have the program log_tail that has as it´s sole purpose to print newly added data elements. What is the best solution in terms of performace? Thank you for your time, Rodrigo
