Re: [PERFORM] Distributed/Parallel Computing
Hi Jeff, These are bulk updates of GIS data and OLTP. For example, we are running some sqls to remove specific POIs those are intersecting with others, for such exercise we need to compare and remove the data form diffrent tables including the 20M data tables. Apart form these there are bulk selects (read only) which are coming form the client systems also. Thanks Viji On Tue, Oct 6, 2009 at 8:10 AM, Jeff Janes jeff.ja...@gmail.com wrote: On Mon, Oct 5, 2009 at 12:11 PM, Viji V Nair v...@fedoraproject.org wrote: Hi Team, This question may have asked many times previously also, but I could not find a solution for this in any post. any help on the following will be greatly appreciated. We have a PG DB with PostGIS functions. There are around 100 tables in the DB and almost all the tables contains 1 million records, around 5 table contains more than 20 million records. The total DB size is 40GB running on a 16GB, 2 x XEON 5420, RAID6, RHEL5 64bit machines, the questions is 1. The geometry calculations which we does are very complex and it is taking a very long time to complete. We have optimised PG config to the best, now we need a mechanism to distribute these queries to multiple boxes. What is best recommended way for this distributed/parallel deployment. We have tried PGPOOL II, but the performance is not satisfactory. Going for a try with GridSQL What is the nature of the transactions being run? Are they primarily read-only other than bulk updates to the GIS data, are they OLTP in regards to the GIS data, or are they transactional with regards to other tables but read-only with respect to the GIS? Jeff
[PERFORM] What is the role of #fsync and #synchronous_commit in configuration file .
Hi , I want to imporve the performance for inserting of huge data in my table . I have only one idex in table . First question - i want to know the role played by #fsync = onand #synchronous_commit = on They are commented by default in 8.4 . When made like this :- fsync = off synchronous_commit = off It improve the performance :) and query took less time . I want to understand more in details what exactly had happened one is made them off , is it dangerous to do this ? as it will not sync the data in each commit . Pls help me out . -- Thanks, Keshav Upadhyaya
Re: [PERFORM] Best suiting OS
Am 05.10.2009 um 23:44 schrieb Karl Denninger: Axel Rau wrote: Am 05.10.2009 um 20:06 schrieb Karl Denninger: gjournal, no. ZFS has potential stability issues - I am VERY interested in it when those are resolved. It looks good on a test platform but I'm unwilling to run it in production; there are both reports of crashes and I have been able to crash it under some (admittedly rather extreme) synthetic loads. How do you prevent from long running fsck with TB size ufs partitions? I had some hope for zfs13 and fbsd 8.0. Axel Turn on softupdates. Fsck is deferred and the system comes up almost instantly even with TB-sized partitions; the fsck then cleans up the cruft. Last time, I checked, there was a issue with background-fsck. I will give it a chance with my new 8.0 box. Do you have any experience with SSDs w/o BBUed Raidcontroller? Are they fast enough to ensure flash write out of drive cache at power failure after fsync ack? Axel --- axel@chaos1.de PGP-Key:29E99DD6 +49 151 2300 9283 computing @ chaos claudius -- 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] What is the role of #fsync and #synchronous_commit in configuration file .
From: keshav upadhyaya Subject: [PERFORM] What is the role of #fsync and #synchronous_commit in configuration file . Hi , I want to imporve the performance for inserting of huge data in my table . I have only one idex in table . First question - i want to know the role played by #fsync = onand #synchronous_commit = on I want to understand more in details what exactly had happened one is made them off , is it dangerous to do this ? as it will not sync the data in each commit . The settings are described in the docs: http://www.postgresql.org/docs/8.4/interactive/runtime-config-wal.html If you turn fsync off, you risk data loss in case of power or hardware failure. Dave -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
[PERFORM] Dumping + restoring a subset of a table?
Hi everyone, I am looking for a way to dump+restore a subset of a database (on another server), using both selection and projection of the source tables (for simplicity assume a single table). I understand that pg_dump will not let me do this. One way I considered is creating a view with the subset definition and dumping it instead of the original table. In that case how do I restore the target table from the dumped view (what does pg_dump generate for a view?)? Can I still use pg_dump to create SQL commands (vs the binary file option), and will these still use COPY instead of INSERT statements? Is there another way to do this? Maybe replication? I care mostly about the time needed to replicate the DB (subset), less so about temp space needed. Thanks. -- Shaul
Re: [PERFORM] Speed / Server
If my un-word wrapping is correct your running ~90% user cpu. Yikes. Could you get away with fewer disks for this kind of thing? On Mon, Oct 5, 2009 at 5:32 PM, Scott Marlowe scott.marl...@gmail.comwrote: On Mon, Oct 5, 2009 at 7:30 AM, Nikolas Everett nik9...@gmail.com wrote: But you should plan on partitioning to multiple db servers up front and save pain of conversion later on. A dual socket motherboard with 16 to 32 SAS drives and a fast RAID controller is WAY cheaper than a similar machine with 4 to 8 sockets is gonna be. And if you gotta go there anyway, might as well spend your money on other stuff. I agree. If you can partition that sensor data across multiple DBs and have your application do the knitting you might be better off. If I may be so bold, you might want to look at splaying the systems out across your backends. I'm just trying to think of a dimension that you won't want to aggregate across frequently. Agreed back. If there's a logical dimension to split data on, it becomes much easier to throw x machines at it than to try and build one ubermachine to handle it all. On the other hand, one of these 16 to 32 SAS drive systems with a raid card will likely get you a long way. Yes they can. We're about to have to add a third db server, cause this is the load on our main slave db: procs ---memory-- ---swap-- -io --system-- -cpu-- r b swpd free buff cache si sobibo in cs us sy id wa st 22 0220 633228 229556 2843297600 638 30400 21 3 73 3 0 19 1220 571980 229584 284351800096 7091 9796 90 6 4 0 0 20 0220 532208 229644 2844024400 140 3357 7110 9175 90 6 3 0 0 19 1220 568440 229664 2844368800 146 1527 7765 10481 90 7 3 0 0 9 1220 806668 229688 284452400099 326 6661 10326 89 6 5 0 0 9 0220 814016 229712 284461440054 1544 7456 10283 90 6 4 0 0 11 0220 782876 229744 284476280096 406 6619 9354 90 5 5 0 0 29 1220 632624 229784 2844996400 113 994 7109 9958 90 7 3 0 0 It's working fine. This has a 16 15k5 SAS disks. A 12 Disk RAID-10, a 2 disk mirror for pg_xlog / OS, and two spares. It has 8 opteron cores and 32Gig ram. We're completely CPU bound because of the type of app we're running. So time for slave number 2...
Re: [PERFORM] Best suiting OS
Axel Rau wrote: Am 05.10.2009 um 23:44 schrieb Karl Denninger: Turn on softupdates. Fsck is deferred and the system comes up almost instantly even with TB-sized partitions; the fsck then cleans up the cruft. Last time, I checked, there was a issue with background-fsck. I will give it a chance with my new 8.0 box. Do you have any experience with SSDs w/o BBUed Raidcontroller? Are they fast enough to ensure flash write out of drive cache at power failure after fsync ack? Axel --- axel@chaos1.de PGP-Key:29E99DD6 +49 151 2300 9283 computing @ chaos claudius IMHO use the right tools for the job. In a DBMS environment where data integrity is the deal this means a BBU'd RAID adapter. SSDs have their own set of issues, at least at present. For data that is read-only (or nearly-so) and of size where it can fit on a SSD they can provide VERY significant performance benefits, in that there is no seek or latency delay. However, any write-significant application is IMHO still better-suited to rotating media at present. This will change I'm sure, but it is what it is as of this point in time. I have yet to run into a problem with background-fsck on a softupdate-set filesystem. In theory there is a potential issue with drives that make their own decision on write-reordering; in practice on a DBMS system you run with a BBU'd RAID controller and as such the controller and system UPS should prevent this from being an issue. One of the potential issues that needs to be kept in mind with any critical application is that disks that have intelligence may choose to re-order writes. This can bring trouble (data corruption) in any application where a drive claims to have committed a block to stable storage where in fact it only has it in its buffer RAM and has not written it to a platter yet. The only reasonable solution to this problem is to run backed-up power so as to mitigate the risk of power disappearing at an inopportune time. Backed-up power brings other advantages as well (as a quality UPS usually comes with significant filtering and power conditioning) which refuses the up front risk of failures and is thus IMHO mandatory for any system that carries data you care about. -- Karl attachment: karl.vcf -- 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] Speed / Server
On Tue, Oct 6, 2009 at 7:21 AM, Nikolas Everett nik9...@gmail.com wrote: If my un-word wrapping is correct your running ~90% user cpu. Yikes. Could you get away with fewer disks for this kind of thing? Probably, but the same workload on a 6 disk RAID-10 is 20% or so IOWAIT. So somewhere between 6 and 12 disks we go from significant IOWAIT to nearly none. Given that CPU bound workloads deteriorate more gracefully than IO Bound, I'm pretty happy having enough extra IO bandwidth on this machine. -- 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] updating a row in a table with only one row
Merlin Moncure wrote: On Mon, Oct 5, 2009 at 5:17 AM, Michal Vitecek f...@mageo.cz wrote: Could the problem be the HW RAID card? There's ServerRAID 8k with 256MB with write-back enabled. Could it be that its internal cache becomes full and all disk I/O operations are delayed until it writes all changes to hard drives? that's possible...the red flag is going to be iowait. if your server can't keep up with the sync demands for example, you will eventually outrun the write cache and you can start to see slow queries. With your server though it would take in the hundreds of (write) transactions per second to do that minimum. The problem is that the server is not loaded in any way. The iowait is 0.62%, there's only 72 sectors written/s, but the maximum await that I saw was 28ms (!). Any attempts to reduce the time (I/O schedulers, disabling bgwriter, increasing number of checkpoints, decreasing shared buffers, disabling read cache on the card etc.) didn't help. After some 3-5m there occurs a COMMIT which takes 100-1x longer time than usual. Setting fsynch to off Temporarily improved the COMMIT times considerably but I fear to have this option off all the time. Is anybody else using the same RAID card? I suspect the problem lies somewhere between the aacraid module and the card. The aacraid module ignores setting of the 'cache' parameter to 3 -- this should completely disable the SYNCHRONIZE_CACHE command. Any hints? Thanks, -- Michal Vitecek (f...@mageo.cz) -- 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] updating a row in a table with only one row
On Tue, Oct 6, 2009 at 10:59 AM, Michal Vitecek f...@mageo.cz wrote: Merlin Moncure wrote: On Mon, Oct 5, 2009 at 5:17 AM, Michal Vitecek f...@mageo.cz wrote: Could the problem be the HW RAID card? There's ServerRAID 8k with 256MB with write-back enabled. Could it be that its internal cache becomes full and all disk I/O operations are delayed until it writes all changes to hard drives? that's possible...the red flag is going to be iowait. if your server can't keep up with the sync demands for example, you will eventually outrun the write cache and you can start to see slow queries. With your server though it would take in the hundreds of (write) transactions per second to do that minimum. The problem is that the server is not loaded in any way. The iowait is 0.62%, there's only 72 sectors written/s, but the maximum await that I saw was 28ms (!). Any attempts to reduce the time (I/O schedulers, disabling bgwriter, increasing number of checkpoints, decreasing shared buffers, disabling read cache on the card etc.) didn't help. After some 3-5m there occurs a COMMIT which takes 100-1x longer time than usual. Setting fsynch to off Temporarily improved the COMMIT times considerably but I fear to have this option off all the time. Is anybody else using the same RAID card? I suspect the problem lies somewhere between the aacraid module and the card. The aacraid module ignores setting of the 'cache' parameter to 3 -- this should completely disable the SYNCHRONIZE_CACHE command. I think you're right. One thing you can do is leave fsync on but disable synchronous_commit. This is compromise between fsync on/off (data consistent following crash, but you may lose some transactions). We need to know what iowait is at the precise moment you get the long commit time. Throw a top, give it short update interval (like .25 seconds), and watch. merlin -- 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] updating a row in a table with only one row
Merlin Moncure wrote: On Tue, Oct 6, 2009 at 10:59 AM, Michal Vitecek f...@mageo.cz wrote: Merlin Moncure wrote: On Mon, Oct 5, 2009 at 5:17 AM, Michal Vitecek f...@mageo.cz wrote: Could the problem be the HW RAID card? There's ServerRAID 8k with 256MB with write-back enabled. Could it be that its internal cache becomes full and all disk I/O operations are delayed until it writes all changes to hard drives? that's possible...the red flag is going to be iowait. if your server can't keep up with the sync demands for example, you will eventually outrun the write cache and you can start to see slow queries. With your server though it would take in the hundreds of (write) transactions per second to do that minimum. The problem is that the server is not loaded in any way. The iowait is 0.62%, there's only 72 sectors written/s, but the maximum await that I saw was 28ms (!). Any attempts to reduce the time (I/O schedulers, disabling bgwriter, increasing number of checkpoints, decreasing shared buffers, disabling read cache on the card etc.) didn't help. After some 3-5m there occurs a COMMIT which takes 100-1x longer time than usual. Setting fsynch to off Temporarily improved the COMMIT times considerably but I fear to have this option off all the time. Is anybody else using the same RAID card? I suspect the problem lies somewhere between the aacraid module and the card. The aacraid module ignores setting of the 'cache' parameter to 3 -- this should completely disable the SYNCHRONIZE_CACHE command. I think you're right. One thing you can do is leave fsync on but disable synchronous_commit. This is compromise between fsync on/off (data consistent following crash, but you may lose some transactions). We need to know what iowait is at the precise moment you get the long commit time. Throw a top, give it short update interval (like .25 seconds), and watch. top(1) has a batch mode (-b) that's useful for sending results to a file. Craig -- 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] Speed / Server
Scott Marlowe wrote: On Tue, Oct 6, 2009 at 8:26 AM, Scott Marlowe scott.marl...@gmail.com wrote: On Tue, Oct 6, 2009 at 7:21 AM, Nikolas Everett nik9...@gmail.com wrote: If my un-word wrapping is correct your running ~90% user cpu. Yikes. Could you get away with fewer disks for this kind of thing? Probably, but the same workload on a 6 disk RAID-10 is 20% or so IOWAIT. So somewhere between 6 and 12 disks we go from significant IOWAIT to nearly none. Given that CPU bound workloads deteriorate more gracefully than IO Bound, I'm pretty happy having enough extra IO bandwidth on this machine. note that spare IO also means we can subscribe a slony slave midday or run a query on a large data set midday and not overload our servers. Spare CPU capacity is nice, spare IO is a necessity. More importantly when you run out of I/O bandwidth bad things tend to happen very quickly; the degradation of performance when you hit the IO wall is extreme to the point of being essentially a zeropoint event. -- Karl attachment: karl.vcf -- 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] Speed / Server
On Tue, Oct 6, 2009 at 1:59 PM, Karl Denninger k...@denninger.net wrote: More importantly when you run out of I/O bandwidth bad things tend to happen very quickly; the degradation of performance when you hit the IO wall is extreme to the point of being essentially a zeropoint event. Or as I like to put it IO bandwidth has sharp knees. -- 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] Speed / Server
On Sun, Oct 4, 2009 at 6:45 PM, anth...@resolution.com wrote: All: We have a web-application which is growing ... fast. We're currently running on (1) quad-core Xeon 2.0Ghz with a RAID-1 setup, and 8GB of RAM. Our application collects a lot of sensor data, which means that we have 1 table which has about 8 million rows, and we're adding about 2.5 million rows per month. The problem is, this next year we're anticipating significant growth, where we may be adding more like 20 million rows per month (roughly 15GB of data). A row of data might have: The system identifier (int) Date/Time read (timestamp) Sensor identifier (int) Data Type (int) Data Value (double) One approach that can sometimes help is to use arrays to pack data. Arrays may or may not work for the data you are collecting: they work best when you always pull the entire array for analysis and not a particular element of the array. Arrays work well because they pack more data into index fetches and you get to skip the 20 byte tuple header. That said, they are an 'optimization trade off'...you are making one type of query fast at the expense of others. In terms of hardware, bulking up memory will only get you so far...sooner or later you have to come to terms with the fact that you are dealing with 'big' data and need to make sure your storage can cut the mustard. Your focus on hardware upgrades should probably be size and quantity of disk drives in a big raid 10. Single user or 'small number of user' big data queries tend to benefit more from fewer core, fast cpus. Also, with big data, you want to make sure your table design and indexing strategy is as tight as possible. merlin -- 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] Speed / Server
On Tue, 2009-10-06 at 17:16 -0400, Merlin Moncure wrote: On Sun, Oct 4, 2009 at 6:45 PM, anth...@resolution.com wrote: All: We have a web-application which is growing ... fast. We're currently running on (1) quad-core Xeon 2.0Ghz with a RAID-1 setup, and 8GB of RAM. Our application collects a lot of sensor data, which means that we have 1 table which has about 8 million rows, and we're adding about 2.5 million rows per month. The problem is, this next year we're anticipating significant growth, where we may be adding more like 20 million rows per month (roughly 15GB of data). A row of data might have: The system identifier (int) Date/Time read (timestamp) Sensor identifier (int) Data Type (int) Data Value (double) One approach that can sometimes help is to use arrays to pack data. Arrays may or may not work for the data you are collecting: they work best when you always pull the entire array for analysis and not a particular element of the array. Arrays work well because they pack more data into index fetches and you get to skip the 20 byte tuple header. That said, they are an 'optimization trade off'...you are making one type of query fast at the expense of others. In terms of hardware, bulking up memory will only get you so far...sooner or later you have to come to terms with the fact that you are dealing with 'big' data and need to make sure your storage can cut the mustard. Your focus on hardware upgrades should probably be size and quantity of disk drives in a big raid 10. Single user or 'small number of user' big data queries tend to benefit more from fewer core, fast cpus. Also, with big data, you want to make sure your table design and indexing strategy is as tight as possible. Thanks for all of the input. One thing we're going to try is to slice up the data based on the data type ... so that we can spread the data rows into about 15 different tables. This should produce 15 tables, the largest which will have about 50% of the data, with the rest having an uneven distribution of the remaining data. Most of the graphs / reports that we're doing need to only use one type of data at a time, but several will need to stitch / combine data from multiple data tables. These combined with some new processors, and a fast RAID-10 system should give us what we need going forward. Thanks again! -- Anthony -- 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] Speed / Server
On Sun, 4 Oct 2009, anth...@resolution.com wrote: The nasty part of this problem is that the data needs to be readily available for reports, and we cannot consolidate the data for reporting purposes. Just because you have to store the detailed data doesn't mean you can't store a conslidated view on it too. Have you considered driving the primary reporting off of materialized views, so you only compute those once? I know we need a LOT of RAM (as much as we can afford), and we're looking at a couple of Nehalem systems w/ a large, and fast, RAID-10 disk set up. There is a lot of variation in RAID-10 setups that depends on the controller used. Make sure you're careful to consider the controller card and performance of its battery-backed cache a critical component here; performance does not scale well with additional drives if your controller isn't good. What card are you using now for your RAID-1 implementation? 1. Other than partitioning (by system, and/or date), and splitting up the data into multiple tables (by data type), what could be done within Postgresql to help with this type of set up (1 large table)? This seems like a perfect fit for partitioning by date. 2. Before going out and buying a beast of a system, we'd like to get some idea of performance on a high-end system. We may need to split this up, or move into some other type of architecture. Do you know of anyone who would let us play with a couple of systems to see what would be an applicable purchase? Find vendors who sell things you like and ask if they have an eval system available. As prices move up, those become more common. -- * Greg Smith gsm...@gregsmith.com http://www.gregsmith.com Baltimore, MD -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
