Re: [PERFORM] Tuning the configuration
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 12/10/2014 06:47 PM, Patrick Krecker wrote: > On Wed, Dec 10, 2014 at 2:44 AM, Maila Fatticcioni > wrote: Hello. I need to tune a postgres > installation I've just made to get a better performance. I use two > identical servers with a hot replication configuration. The two > servers have the following hardware: > > Dual Processor Intel Xeon E5-2640V2 20Mb cache 2.00Ghz, Ram Mem. > 32Gb DDR-3 Ecc Registered, Controller MegaRaid 8-ports 1Gb cache, 4 > Enterprise Hdd NL Sas 600 4Tb Sata, 2 Samsung SSD 840 Pro Series > 512Gb, 2 Hdd 500 Gb > > I made a software raid with the last two hard disks with ext4 and > I installed Ubuntu 14.04.1 LTS (I have to use this SO) on it. I > made a hardware raid with the four SAS hard disks and I mount the > partition on it with ext4 without journaling and I put the database > on it. > > Now I have two more steps to do. > > 1- could you please help tuning the configuration? What are the > best value I should use for wal_buffers and shared_buffers? 2- I > would like to use the two SDD to store the wal file. Do you think > it is useful or how should I use them? > > Thank you for your answers. > > Best Regards, Maila Fatticcioni >> >> >> -- Sent via pgsql-performance mailing list >> (pgsql-performance@postgresql.org) To make changes to your >> subscription: >> http://www.postgresql.org/mailpref/pgsql-performance > > We used [1] to great effect when setting our server up. We have > not had to diverge much from the recommendations in that document. > > Generally, the specifics of tuning depend on the workload of your > specific instance. > > [1] https://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server > Hello. Indeed I followed this document to set up my configuration. I am glad that you recommend this as well. Eventually I use this setup: max_connections = 150 shared_buffers = 8GB work_mem = 32MB checkpoint_segments = 128 checkpoint_completion_target = 0.9 Best Regards, Maila Fatticcioni -BEGIN PGP SIGNATURE- Version: GnuPG v1 iEYEARECAAYFAlSJUaEACgkQi2q3wPb3FcPsuQCeLR5P49d60anErETNiX0iHNLe Eu4An0QN3nzr/kvlPUTm9Q1A0GkjB/gw =kdGU -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] Tuning the configuration
On 12/10/2014 11:44 AM, Maila Fatticcioni wrote: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hello. I need to tune a postgres installation I've just made to get a better performance. I use two identical servers with a hot replication configuration. The two servers have the following hardware: Dual Processor Intel Xeon E5-2640V2 20Mb cache 2.00Ghz, Ram Mem. 32Gb DDR-3 Ecc Registered, Controller MegaRaid 8-ports 1Gb cache, 4 Enterprise Hdd NL Sas 600 4Tb Sata, 2 Samsung SSD 840 Pro Series 512Gb, 2 Hdd 500 Gb I made a software raid with the last two hard disks with ext4 and I installed Ubuntu 14.04.1 LTS (I have to use this SO) on it. I made a hardware raid with the four SAS hard disks and I mount the partition on it with ext4 without journaling and I put the database on it. Leaving aside all the valid points Patrick already made, as of late I've found xfs a better choice for Postgres, performance wise. Now I have two more steps to do. 1- could you please help tuning the configuration? What are the best value I should use for wal_buffers and shared_buffers? it's probably outdated but you could try to read Greg Smith's "PostgreSQL 9.0 High Performance", because at least you could have an idea of almost all the attack-points you could use to increase you overall performance. Even in the archive of this very mailinglist you'll surely find a lot of good advice, e.g. one that I've read here recently is avoid using any kernels between ver 3.0 and 3.8 (http://www.databasesoup.com/2014/09/why-you-need-to-avoid-linux-kernel-32.html) 2- I would like to use the two SDD to store the wal file. Do you think it is useful or how should I use them? I definitely would give it a try. Andrea -- 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] Tuning the configuration
> On 11 Dec 2014, at 15:02, Andrea Suisani wrote: > > On 12/10/2014 11:44 AM, Maila Fatticcioni wrote: >> 2- I would like to use the two SDD to store the wal file. Do you think >> it is useful or how should I use them? > > I definitely would give it a try. > I don't understand the logic behind using drives, which are best for random io, for sequent io workloads. Better use 10k sas with BBU raid for wal, money wise. -- 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] Tuning the configuration
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 12/11/2014 01:11 PM, Evgeniy Shishkin wrote: > >> On 11 Dec 2014, at 15:02, Andrea Suisani >> wrote: >> >> On 12/10/2014 11:44 AM, Maila Fatticcioni wrote: >>> 2- I would like to use the two SDD to store the wal file. Do >>> you think it is useful or how should I use them? >> >> I definitely would give it a try. >> > > > I don't understand the logic behind using drives, which are best > for random io, for sequent io workloads. > > Better use 10k sas with BBU raid for wal, money wise. > > > Would you mind to explain me better why you do suggest me to use the sas raid for wal please? Thanks, M. -BEGIN PGP SIGNATURE- Version: GnuPG v1 iEYEARECAAYFAlSJkokACgkQi2q3wPb3FcOOZQCgrhy3sOP3Jds1eGlPqjSW+GhM xFIAn3YbZgEFAlwTC+SX7GG2My0pElys =Bsn7 -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] Tuning the configuration
Would you mind to explain me better why you do suggest me to use the sas raid for wal please? SSDs are known to shine when they have to deal with random access pattern rather than sequential, on the other hand 10/15K rpm SAS disk is known to be better for sequential io workloads (in general "rotating" disk use to be better at sequential rather than random access) Having said that it seems that SSDs are catching up, see: http://www.anandtech.com/show/6935/seagate-600-ssd-review/5 Andrea -- 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] Tuning the configuration
On 12/11/2014 01:11 PM, Evgeniy Shishkin wrote: On 11 Dec 2014, at 15:02, Andrea Suisani wrote: On 12/10/2014 11:44 AM, Maila Fatticcioni wrote: 2- I would like to use the two SDD to store the wal file. Do you think it is useful or how should I use them? I definitely would give it a try. I don't understand the logic behind using drives, which are best for random io, for sequent io workloads. Better use 10k sas with BBU raid for wal, money wise. Well since Malia had already used the 4 sas hd for the DB, I thought that it'd be quite quick to setup a raid1 array (even at software level, e.g. using md), placing pg_xlog in such array and measure the performance. As a following step, depending on the time constraints involved, Malia could rearrange the disk setup enterly and use the SAS disks as location for pg_xlog. Andrea -- 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] Tuning the configuration
From: pgsql-performance-ow...@postgresql.org on behalf of Evgeniy Shishkin Sent: Thursday, December 11, 2014 7:11 AM To: Andrea Suisani Cc: mfatticci...@mbigroup.it; pgsql-performance@postgresql.org Subject: Re: [PERFORM] Tuning the configuration > On 11 Dec 2014, at 15:02, Andrea Suisani wrote: > > On 12/10/2014 11:44 AM, Maila Fatticcioni wrote: >> 2- I would like to use the two SDD to store the wal file. Do you think >> it is useful or how should I use them? > > I definitely would give it a try. > > I don't understand the logic behind using drives, > which are best for random io, for sequent io workloads. > Better use 10k sas with BBU raid for wal, money wise. Very much agree with this. Because SSD is fast doesn't make it suited for certain things, and a streaming sequential 100% write workload is one of them. I've worked with everything from local disk to high-end SAN and even at the high end we've always put any DB logs on spinning disk. RAID1 is generally sufficient. SSD is king for read heavy random I/O workload. -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance -- 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] Tuning the configuration
On 12/12/14 11:36, Eric Pierce wrote: From: pgsql-performance-ow...@postgresql.org on behalf of Evgeniy Shishkin Sent: Thursday, December 11, 2014 7:11 AM To: Andrea Suisani Cc: mfatticci...@mbigroup.it; pgsql-performance@postgresql.org Subject: Re: [PERFORM] Tuning the configuration On 11 Dec 2014, at 15:02, Andrea Suisani wrote: On 12/10/2014 11:44 AM, Maila Fatticcioni wrote: 2- I would like to use the two SDD to store the wal file. Do you think it is useful or how should I use them? I definitely would give it a try. I don't understand the logic behind using drives, which are best for random io, for sequent io workloads. Better use 10k sas with BBU raid for wal, money wise. Very much agree with this. Because SSD is fast doesn't make it suited for certain things, and a streaming sequential 100% write workload is one of them. I've worked with everything from local disk to high-end SAN and even at the high end we've always put any DB logs on spinning disk. RAID1 is generally sufficient. SSD is king for read heavy random I/O workload. Mind you wal is a little different - the limiting factor is (usually) not raw sequential speed but fsync latency. These days a modern SSD has fsync response pretty much equal to that of a card with BBU + spinners - and has "more" high speed storage available (cards usually have only a 1G or so of RAM on them). regards Mark -- 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] Yet another abort-early plan disaster on 9.3
On 30 September 2014 at 10:25, Simon Riggs wrote: > On 30 September 2014 00:00, Tom Lane wrote: >> The existing cost estimation >> code effectively assumes that they're perfectly uniformly distributed; >> which is a good average-case assumption but can be horribly wrong in >> the worst case. > > Agreed. This is the main observation from which we can work. > >> If we could settle on some other model for the probable distribution >> of the matching tuples, we could adjust the cost estimates for LIMIT >> accordingly. I have not enough statistics background to know what a >> realistic alternative would be. > > I'm not sure that the correlation alone is sufficient to be able to do > that. We'd need to estimate where the values looked for are likely to > be wrt other values, then increase estimate accordingly. That sounds > like a lot of pushups grovelling through quals and comparing against > stats. So my thinking is actually to rule that out, unless you've some > ideas for how to do that? > >> Another possibility is to still assume a uniform distribution but estimate >> for, say, a 90% probability instead of 50% probability that we'll find >> enough tuples after scanning X amount of the table. Again, I'm not too >> sure what that translates to in terms of the actual math, but it sounds >> like something a statistics person could do in their sleep. The problem is one of risk. Whatever distribution we use, it will be wrong in some cases and good in others. For example, if we look at "10 Most Recent Calls" for a user, then frequent users would have one distribution, infrequent users another. So we have multiple distributions in the same data. We just can't hold enough information to make sense of this. Think about how much data needs to be scanned if the user has only done 9 calls. What I've done in the past is to rewrite the query in different ways to force different plans, then call each plan depending upon the user characteristics. This is can also be done with hints, in a more ignorant way. >> I do not think we should estimate for the worst case though. If we do, >> we'll hear cries of anguish from a lot of people, including many of the >> same ones complaining now, because the planner stopped picking fast-start >> plans even for cases where they are orders of magnitude faster than the >> alternatives. > > Fast start plans still make sense when performing an IndexScan with no > filter conditions. Those types of plan should not be changed from > current costing - they are accurate, good and very important because > of their frequency in real workloads. > > What I think we are seeing is Ordered plans being selected too often > in preference to Sorted plans when we make selectivity or stats > errors. As well as data distributions that aren't correctly described > by the statistics causing much longer execution times. > > Here are some plan selection strategies > > * Cost based - attempt to exactly calculate the cost based upon > existing stats - increase the complexity of cost calc to cover other > aspects. Even if we do that, these may not be that helpful in covering > the cases where the stats turn out to be wrong. > > * Risk based - A risk adjusted viewpoint would be that we should treat > the cost as mid-way between the best and the worst. The worst is > clearly scanning (100% - N) of the tuples, the best is just N tuples. > So we should be costing scans with excess filter conditions as a (100% > Scan)/2, no matter the conditions, based purely upon risk. > > * Simplified heuristic - deselect ordered plans when they are driven > from scans without quals or indexscans with filters, since the risk > adjusted cost is likely to be higher than the sorted cost. Inspecting > the plan tree for this could be quite costly, so would only be done > when the total cost is $high, prior to it being adjusted by LIMIT. > > > In terms of practical steps... I suggest the following: > > * Implement enable_orderedscan = on (default) | off. A switch to allow > plans to de-select ordered plans, so we can more easily see the > effects of such plans in the wild. > > * Code heuristic approach - I can see where to add my heuristic in the > grouping planner. So we just need to do a left? deep search of the > plan tree looking for scans of the appropriate type and bail out if we > find one. After looking at this for some time I now have a patch that solves this. It relies on the observation that index scans with no bounded quals don't play nicely with LIMIT. The solution relies upon the point that LIMIT does not reduce the startup cost of plans, only the total cost. So we can solve the problem by keeping the total cost estimate, just move some of that into startup cost so LIMIT does not reduce costs as much as before. It's a simple patch, but it solves the test cases I know about and does almost nothing to planning time. I tried much less subtle approaches involving direct prevention of LIMIT pushdown but the code was mu
Re: [PERFORM] Yet another abort-early plan disaster on 9.3
On 12 December 2014 at 03:22, Simon Riggs wrote: > It's a simple patch, but it solves the test cases I know about and > does almost nothing to planning time. Test cases attached. The files marked "pettus_*" are written up from Christophe Pettus' blog. The other test case is one of my own devising, based upon recent customer problems. The "10 most recent calls" is a restatement of actual problems seen in the past. Also attached is a new parameter called enable_sortedpath which can be used to turn on/off the sorted path generated by the planner. -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services sortedpath.sql Description: Binary data pettus_limit.sql Description: Binary data pettus_sel.sql Description: Binary data -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
