Re: [PERFORM] SSD + RAID
Bruce Momjian wrote: I have added documentation about the ATAPI drive flush command, and the typical SSD behavior. If one of us goes back into that section one day to edit again it might be worth mentioning that FLUSH CACHE EXT is the actual ATAPI-6 command that a drive needs to support properly. I wouldn't bother with another doc edit commit just for that specific part though, pretty obscure. I find it kind of funny how many discussions run in parallel about even really detailed technical implementation details around the world. For example, doesn't http://www.mail-archive.com/zfs-disc...@opensolaris.org/msg30585.html look exactly like the exchange between myself and Arjen the other day, referencing the same AnandTech page? Could be worse; one of us could be the poor sap at http://opensolaris.org/jive/thread.jspa;jsessionid=41B679C30D136C059E1BB7C06CA7DCE0?messageID=397730 who installed Windows XP, VirtualBox for Windows, an OpenSolaris VM inside of it, and then was shocked that cache flushes didn't make their way all the way through that chain and had his 10TB ZFS pool corrupted as a result. Hurray for virtualization! -- Greg Smith 2ndQuadrant US Baltimore, MD PostgreSQL Training, Services and Support g...@2ndquadrant.com www.2ndQuadrant.us -- 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] SSD + RAID
Greg Smith wrote: > Bruce Momjian wrote: > > I have added documentation about the ATAPI drive flush command, and the > > typical SSD behavior. > > > > If one of us goes back into that section one day to edit again it might > be worth mentioning that FLUSH CACHE EXT is the actual ATAPI-6 command > that a drive needs to support properly. I wouldn't bother with another > doc edit commit just for that specific part though, pretty obscure. That setting name was not easy to find so I added it to the documentation. -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com PG East: http://www.enterprisedb.com/community/nav-pg-east-2010.do -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
[PERFORM] How to troubleshoot high mem usage by postgres?
Hi, I'm having an issue where a postgres process is taking too much memory when performing many consecutive inserts and updates from a PHP script (running on the command line). I would like to know what sort of logging I can turn on to help me determine what is causing memory to be consumed and not released. Most PHP scripts are not long-running and properly releasing the resources using the provided functions in the pgsql PHP extension is not necessary. However since I do have a long-running script, I have taken steps to ensure everything is being properly released when it is no longer needed (I am calling the functions provided, but I don't know if the pgsql extension is doing the right thing). In spite of this, the longer the script runs and processes records, the more memory increases. It increases to the point that system memory is exhausted and it starts swapping. I killed the process at this point. I monitored the memory with top. here are the results.. the first is 10 seconds after my script started running. The second is about 26 seconds. PID USER PR NI VIRT RES SHR S %CPU %MEMTIME+ DATA COMMAND 17461 postgres 16 0 572m 405m 14m S 20.0 10.7 0:10.65 422m postmaster 17460 root 15 0 136m 14m 4632 S 10.6 0.4 0:06.16 10m php 17462 postgres 15 0 193m 46m 3936 D 3.3 1.2 0:01.77 43m postmaster PID USER PR NI VIRT RES SHR S %CPU %MEMTIME+ DATA COMMAND 17461 postgres 16 0 1196m 980m 17m S 19.0 26.0 0:25.72 1.0g postmaster 17460 root 15 0 136m 14m 4632 R 10.3 0.4 0:14.31 10m php 17462 postgres 16 0 255m 107m 3984 R 3.0 2.9 0:04.19 105m postmaster If I am indeed doing everything I can to release the resources (and I'm 95% sure I am) then it looks like the pgsql extension is at fault here. Regardless of who/what is at fault, I need to fix it. And to do that I need to find out what isn't getting released properly. How would I go about that? Thanks, Chris -- 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] How to troubleshoot high mem usage by postgres?
On Feb 27, 2010, at 2:29 PM, Chris wrote: > Hi, I'm having an issue where a postgres process is taking too much > memory when performing many consecutive inserts and updates from a PHP [snip] In your postgresql.conf file, what are the settings for work_mem and shared_buffers?
Re: [PERFORM] How to troubleshoot high mem usage by postgres?
On 28/02/2010 6:29 AM, Chris wrote: If I am indeed doing everything I can to release the resources (and I'm 95% sure I am) then it looks like the pgsql extension is at fault here. Before assuming some particular thing is at fault, you need to collect some information to determine what is actually happening. What are your queries? What are the resource-releasing functions you're using, and how? Can you boil this down to a simple PHP test-case that connects to a dummy database and repeats something that causes the backend to grow in memory usage? Trying to do this - by progressively cutting things out of your test until it stops growing - will help you track down what, exactly, is causing the growth. It'd be helpful if you could also provide some general system information, as shown here: http://wiki.postgresql.org/wiki/Guide_to_reporting_problems -- Craig Ringer -- 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] How to troubleshoot high mem usage by postgres?
Chris writes: > Hi, I'm having an issue where a postgres process is taking too much > memory when performing many consecutive inserts and updates from a PHP > script (running on the command line). I would like to know what sort > of logging I can turn on to help me determine what is causing memory > to be consumed and not released. Are you doing all these inserts/updates within a single transaction? If so, I think the odds are very good that what's eating the memory is the list of pending trigger actions, resulting from either user-created triggers or foreign-key check triggers. The best way of limiting the problem is to commit more often. If you want to try to confirm that, what I would do is run the postmaster under a more restrictive ulimit setting, so that it runs out of memory sometime before the system starts to swap. When it does run out of memory, you'll get a memory context map printed to postmaster stderr, and that will show which context is eating all the memory. If it's "AfterTriggerEvents" then my guess above is correct --- otherwise post the map for inspection. regards, tom lane -- 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] How to troubleshoot high mem usage by postgres?
On Sat, Feb 27, 2010 at 3:38 PM, Ben Chobot wrote: > In your postgresql.conf file, what are the settings for work_mem and > shared_buffers? I have not done any tuning on this db yet (it is a dev box). It is using defaults. shared_buffers = 32MB #work_mem = 1MB I do appreciate the several quick responses and I will work on responding to the them. @Craig Ringer: select version() reports: PostgreSQL 8.4.2 on x86_64-redhat-linux-gnu, compiled by GCC gcc (GCC) 4.1.2 20071124 (Red Hat 4.1.2-42), 64-bit The system has 4GB of RAM. The postgres log currently does not show any useful information. Only thing in there for today is an "Unexpected EOF on client connection" because I killed the process after it started swapping. The test input for my PHP script is a csv file with about 450,000 records in it. The php script processes the each csv record in a transaction, and on average it executes 2 insert or update statements per record. I don't think the specific statements executed are relevant (they are just basic INSERT and UPDATE statements). I will try to come up with a short script that reproduces the problem. @Tom Lane: As I mentioned above I am not doing everything in a single transaction. However I do want to try your suggestion regarding getting a "memory context map". But I'm afraid I don't know how to do what you are describing. How can I set the ulimit of postmaster? And does the postmaster stderr output go to the postgres log file? If not, where can I find it? Thanks again, Chris -- 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] How to troubleshoot high mem usage by postgres?
On Sat, Feb 27, 2010 at 3:38 PM, Craig Ringer wrote: > Can you boil this down to a simple PHP test-case that connects to a dummy > database and repeats something that causes the backend to grow in memory > usage? Trying to do this - by progressively cutting things out of your test > until it stops growing - will help you track down what, exactly, is causing > the growth. Thank you for your suggestion. I have done this, and in doing so I have also discovered why this problem is occurring. My application uses a class that abstracts away the db interaction, so I do not normally use the pg_* functions directly. Any time any statement was executed, it created a new "named" prepared statement. I wrongly assumed that calling pg_free_result() on the statement resource would free this prepared statement inside of postgres. I will simply modify the class to use an empty statement name if there is no need for it to be named (which I actually need very infrequently anyway). I have attached the script I created to test with, for those who are interested. The first line of the script has the connection string. I used a db called testdb. run from the command line with: php -f test3.php Note my comment in the php file << UNCOMMENT THIS LINE AND MEMORY ISSUE IS FIXED Thanks for the help everyone. Chris <> -- 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] SSD + RAID
Bruce Momjian wrote: > Greg Smith wrote: >> Bruce Momjian wrote: >>> I have added documentation about the ATAPI drive flush command, and the >> >> If one of us goes back into that section one day to edit again it might >> be worth mentioning that FLUSH CACHE EXT is the actual ATAPI-6 command >> that a drive needs to support properly. I wouldn't bother with another >> doc edit commit just for that specific part though, pretty obscure. > > That setting name was not easy to find so I added it to the > documentation. If we're spelling out specific IDE commands, it might be worth noting that the corresponding SCSI command is "SYNCHRONIZE CACHE"[1]. Linux apparently sends FLUSH_CACHE commands to IDE drives in the exact sample places it sends SYNCHRONIZE CACHE commands to SCSI drives[2]. It seems that the same file systems, SW raid layers, virtualization platforms, and kernels that have a problem sending FLUSH CACHE commands to SATA drives have he same exact same problems sending SYNCHRONIZE CACHE commands to SCSI drives. With the exact same effect of not getting writes all the way through disk caches. No? [1] http://linux.die.net/man/8/sg_sync [2] http://hardware.slashdot.org/comments.pl?sid=149349&cid=12519114 -- 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] SSD + RAID
Ron Mayer wrote: Linux apparently sends FLUSH_CACHE commands to IDE drives in the exact sample places it sends SYNCHRONIZE CACHE commands to SCSI drives[2]. [2] http://hardware.slashdot.org/comments.pl?sid=149349&cid=12519114 Well, that's old enough to not even be completely right anymore about SATA disks and kernels. It's FLUSH_CACHE_EXT that's been added to ATA-6 to do the right thing on modern drives and that gets used nowadays, and that doesn't necessarily do so on most of the SSDs out there; all of which Bruce's recent doc additions now talk about correctly. There's this one specific area we know about that the most popular systems tend to get really wrong all the time; that's got the appropriate warning now with the right magic keywords that people can look into it more if motivated. While it would be nice to get super thorough and document everything, I think there's already more docs in there than this project would prefer to have to maintain in this area. Are we going to get into IDE, SATA, SCSI, SAS, FC, and iSCSI? If the idea is to be complete that's where this would go. I don't know that the documentation needs to address every possible way every possible filesystem can be flushed. -- Greg Smith 2ndQuadrant US Baltimore, MD PostgreSQL Training, Services and Support g...@2ndquadrant.com www.2ndQuadrant.us -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
