Re: [PERFORM] Tuning guidelines for server with 256GB of RAM and SSDs?
On 06/07/16 07:17, Mkrtchyan, Tigran wrote: Hi, We had a similar situation and the best performance was with 64MB background_bytes and 512 MB dirty_bytes. Tigran. On Jul 5, 2016 16:51, Kaixi Luo wrote: Here are my server specs: RAID1 - 2x480GB Samsung SSD with power loss protection (will be used to store the PostgreSQL database) RAID1 - 2x240GB Crucial SSD with power loss protection. (will be used to store PostgreSQL transactions logs) Can you tell the exact model numbers for the Samsung and Crucial SSD's? It typically matters! E.g I have some Crucial M550 that have capacitors and (originally) claimed to be power off safe, but with testing have been shown to be not really power off safe at all. I'd be dubious about Samsungs too. The Intel Datacenter range (S3700 and similar) are known to have power off safety that does work. 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] Tuning guidelines for server with 256GB of RAM and SSDs?
Regarding the Nordeus blog Merlin linked. They say: "This doesn't mean the data was really written to disk, it can still remain in the disk cache, but enterprise drives usually make sure the data was really written to disk on fsync calls." This isn't actually true for enterprise drives (when I say enterprise in the context of an SSD, I'm assuming full power loss protection via capacitors on the drive like the Intel DC S3x00 series). Most enterprise SSDs will ignore calls to disable disk cache or to flush the disk cache as doing so is entirely unnecessary. Regarding write back cache: Disabling the write back cache won't have a real large impact on the endurance of the drive unless it reduces the total number of bytes written (which it won't). I've seen drives that perform better with it disabled and drives that perform better with it enabled. I would test in your environment and make the decision based on performance. Regarding the Crucial drive for logs: As far as I'm aware, none of the Crucial drives have power loss protection. To use these drives you would want to disable disk cache which would drop your performance a fair bit. Write amplification: I wouldn't expect write amplification to be a serious issue unless you hit every LBA on the device early in its life and never execute TRIM. This is one of the reasons software RAID can be a better solution for something like this. MDADM supports TRIM in RAID devices. So unless you run the drives above 90% full, the write amplification would be minimal so long as you have a daily fstrim cron job. Wes Vaske | Senior Storage Solutions Engineer Micron Technology From: pgsql-performance-ow...@postgresql.org on behalf of Merlin Moncure Sent: Wednesday, July 6, 2016 1:13 PM To: Kaixi Luo Cc: postgres performance list Subject: Re: [PERFORM] Tuning guidelines for server with 256GB of RAM and SSDs? On Tue, Jul 5, 2016 at 9:50 AM, Kaixi Luo wrote: > Hello, > > I've been reading Mr. Greg Smith's "Postgres 9.0 - High Performance" book > and I have some questions regarding the guidelines I found in the book, > because I suspect some of them can't be followed blindly to the letter on a > server with lots of RAM and SSDs. > > Here are my server specs: > > Intel Xeon E5-1650 v3 Hexa-Core Haswell > 256GB DDR4 ECC RAM > Battery backed hardware RAID with 512MB of WriteBack cache (LSI MegaRAID SAS > 9260-4i) > RAID1 - 2x480GB Samsung SSD with power loss protection (will be used to > store the PostgreSQL database) > RAID1 - 2x240GB Crucial SSD with power loss protection. (will be used to > store PostgreSQL transactions logs) > > First of all, the book suggests that I should enable the WriteBack cache of > the HWRAID and disable the disk cache to increase performance and ensure > data safety. Is it still advisable to do this on SSDs, specifically the step > of disabling the disk cache? Wouldn't that increase the wear rate of the > SSD? At the time that book was written, the majority of SSDs were known not to be completely honest and/or reliable about data integrity in the face of a power event. Now it's a hit or miss situation (for example, see here: http://blog.nordeus.com/dev-ops/power-failure-testing-with-ssds.htm). The intel drives S3500/S3700 and their descendants are the standard against which other drives should be judged IMO. The S3500 family in particular offers tremendous value for database usage. Do your research; the warning is still relevant but the blanket statement no longer applies. Spinning drives are completely obsolete for database applications in my experience. Disabling write back cache for write heavy database loads will will destroy it in short order due to write amplication and will generally cause it to underperform hard drives in my experience. With good SSDs and a good motherboard, I do not recommend a caching raid controller; software raid is a better choice for many reasons. One parameter that needs to be analyzed with SSD is effective_io_concurrency. see https://www.postgresql.org/message-id/CAHyXU0yiVvfQAnR9cyH%3DHWh1WbLRsioe%3DmzRJTHwtr%3D2azsTdQ%40mail.gmail.com merlin -- 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 guidelines for server with 256GB of RAM and SSDs?
On Wed, Jul 6, 2016 at 12:13 PM, Merlin Moncure wrote: > Disabling write back cache for write heavy database loads will will > destroy it in short order due to write amplication and will generally > cause it to underperform hard drives in my experience. Interesting. We found our best performance with a RAID-5 of 10 800GB SSDs (Intel 3500/3700 series) that we got MUCH faster performance with all write caching turned off on our LSI MEgaRAID controllers. We went from 3 to 4ktps to 15 to 18ktps. And after a year of hard use we still show ~90% life left (these machines handle thousands of writes per second in real use) It could be that the caching was getting in the way of RAID calcs or some other issue. With RAID-1 I have no clue what the performance will be with write cache on or off. -- To understand recursion, one must first understand recursion. -- 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 guidelines for server with 256GB of RAM and SSDs?
On Tue, Jul 5, 2016 at 9:50 AM, Kaixi Luo wrote: > Hello, > > I've been reading Mr. Greg Smith's "Postgres 9.0 - High Performance" book > and I have some questions regarding the guidelines I found in the book, > because I suspect some of them can't be followed blindly to the letter on a > server with lots of RAM and SSDs. > > Here are my server specs: > > Intel Xeon E5-1650 v3 Hexa-Core Haswell > 256GB DDR4 ECC RAM > Battery backed hardware RAID with 512MB of WriteBack cache (LSI MegaRAID SAS > 9260-4i) > RAID1 - 2x480GB Samsung SSD with power loss protection (will be used to > store the PostgreSQL database) > RAID1 - 2x240GB Crucial SSD with power loss protection. (will be used to > store PostgreSQL transactions logs) > > First of all, the book suggests that I should enable the WriteBack cache of > the HWRAID and disable the disk cache to increase performance and ensure > data safety. Is it still advisable to do this on SSDs, specifically the step > of disabling the disk cache? Wouldn't that increase the wear rate of the > SSD? At the time that book was written, the majority of SSDs were known not to be completely honest and/or reliable about data integrity in the face of a power event. Now it's a hit or miss situation (for example, see here: http://blog.nordeus.com/dev-ops/power-failure-testing-with-ssds.htm). The intel drives S3500/S3700 and their descendants are the standard against which other drives should be judged IMO. The S3500 family in particular offers tremendous value for database usage. Do your research; the warning is still relevant but the blanket statement no longer applies. Spinning drives are completely obsolete for database applications in my experience. Disabling write back cache for write heavy database loads will will destroy it in short order due to write amplication and will generally cause it to underperform hard drives in my experience. With good SSDs and a good motherboard, I do not recommend a caching raid controller; software raid is a better choice for many reasons. One parameter that needs to be analyzed with SSD is effective_io_concurrency. see https://www.postgresql.org/message-id/CAHyXU0yiVvfQAnR9cyH%3DHWh1WbLRsioe%3DmzRJTHwtr%3D2azsTdQ%40mail.gmail.com 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] less than 2 sec for response - possible?
Well, our CPU\RAM configs are almost same... The difference is - you're fetching\grouping 8 times less rows than I: You scan 16.5 mln rows and fetch ~200k rows in 2 seconds and than spend 1.4 sec for aggregation I'm scanning 3.5 mln rows and fetching 1.5 mln rows (8 times more than you) in 1.8 seconds and then spending rest (2.3 seconds) for aggregation... So please try to extend dates range 8 times and repeat your test. On 07/06/16 08:27, Torsten Zuehlsdorff wrote: On 06.07.2016 17:06, trafdev wrote: Wondering what are your CPU\RAM characteristics? Intel Core i7-2600 Quad Core 32 GB DDR3 RAM 2x 3 TB SATA III HDD HDD is: Model Family: Seagate Barracuda XT Device Model: ST33000651AS Firmware Version: CC45 User Capacity:3,000,592,982,016 bytes [3.00 TB] Sector Size: 512 bytes logical/physical Rotation Rate:7200 rpm Form Factor: 3.5 inches Device is:In smartctl database [for details use: -P show] ATA Version is: ATA8-ACS T13/1699-D revision 4 SATA Version is: SATA 3.0, 6.0 Gb/s (current: 6.0 Gb/s) RAM is for example: Handle 0x002D, DMI type 17, 28 bytes Memory Device Array Handle: 0x002A Error Information Handle: No Error Total Width: 64 bits Data Width: 64 bits Size: 8192 MB Form Factor: DIMM Set: None Locator: DIMM0 Bank Locator: BANK0 Type: DDR3 Type Detail: Synchronous Speed: 1333 MHz Manufacturer: Undefined Serial Number: 4430793 Asset Tag: AssetTagNum0 Part Number: CT102464BA160B.C16 Rank: 2 OS is FreeBSD 10.3. Do you need more information? Greetings, Torsten -- 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] DELETE takes too much memory
On Mon, Jul 4, 2016 at 11:35 AM, Kouber Saparev wrote: > I tried to DELETE about 7 million rows at once, and the query went up to 15% > of the RAM (120 GB in total), which pushed some indexes out and the server > load went up to 250, so I had to kill the query. > > The involved table does not have neither foreign keys referring to other > tables, nor other tables refer to it. The size of the table itself is 19 GB > (15% of 120 GB). So why the DELETE tried to put the entire table in memory, > or what did it do to take so much memory? > > I am using 9.4.5. How did you measure memory usage exactly? In particular, memory consumption from the pid attached to the query or generalized to the server? Is this linux and if so what memory metric did you use? What kinds of indexes are on this table (in particular, gin/gist?)? 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] less than 2 sec for response - possible?
On 06.07.2016 17:06, trafdev wrote: Wondering what are your CPU\RAM characteristics? Intel Core i7-2600 Quad Core 32 GB DDR3 RAM 2x 3 TB SATA III HDD HDD is: Model Family: Seagate Barracuda XT Device Model: ST33000651AS Firmware Version: CC45 User Capacity:3,000,592,982,016 bytes [3.00 TB] Sector Size: 512 bytes logical/physical Rotation Rate:7200 rpm Form Factor: 3.5 inches Device is:In smartctl database [for details use: -P show] ATA Version is: ATA8-ACS T13/1699-D revision 4 SATA Version is: SATA 3.0, 6.0 Gb/s (current: 6.0 Gb/s) RAM is for example: Handle 0x002D, DMI type 17, 28 bytes Memory Device Array Handle: 0x002A Error Information Handle: No Error Total Width: 64 bits Data Width: 64 bits Size: 8192 MB Form Factor: DIMM Set: None Locator: DIMM0 Bank Locator: BANK0 Type: DDR3 Type Detail: Synchronous Speed: 1333 MHz Manufacturer: Undefined Serial Number: 4430793 Asset Tag: AssetTagNum0 Part Number: CT102464BA160B.C16 Rank: 2 OS is FreeBSD 10.3. Do you need more information? Greetings, Torsten -- 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] less than 2 sec for response - possible?
Wondering what are your CPU\RAM characteristics? On 07/06/16 01:35, Torsten Zuehlsdorff wrote: On 05.07.2016 17:35, trafdev wrote: [..] Without TIMESTAMP cast: QUERY PLAN HashAggregate (cost=1405666.90..1416585.93 rows=335970 width=86) (actual time=4797.272..4924.015 rows=126533 loops=1) " Group Key: subid, sid" Buffers: shared hit=1486949 -> Index Scan using ix_feed_sub_aid_date on feed_sub (cost=0.44..1313275.32 rows=3359694 width=86) (actual time=0.019..1783.104 rows=3588376 loops=1) Index Cond: ((aid = 3) AND (date >= '2016-06-01'::date) AND (date <= '2016-06-30'::date)) Buffers: shared hit=1486949 Planning time: 0.164 ms Execution time: 4941.259 ms I need to be sure it's a physical limitation of a Postgresql (when all data is in a memory and fetching\joining 1.5 mln of rows can't be done in less than 2-3 seconds) and there is no way to improve it. It could be a physical limitation of your hardware. I just did a short test on one of my databases: Aggregate (cost=532018.95..532018.96 rows=1 width=0) (actual time=3396.689..3396.689 rows=1 loops=1) Buffers: shared hit=155711 -> Index Only Scan using requests_request_time_idx on requests (cost=0.43..493109.90 rows=15563620 width=0) (actual time=0.021..2174.614 rows=16443288 loops=1) Index Cond: ((request_time >= '2016-07-01 00:00:00+00'::timestamp with time zone) AND (request_time <= '2017-07-06 00:00:00+00'::timestamp with time zone)) Heap Fetches: 31254 Buffers: shared hit=155711 Planning time: 0.143 ms Execution time: 3396.715 ms (8 rows) As you can see i can get 16.4 Mio rows within 3.4 seconds from cache. Your index-scan fetches 3.5 mio in 1.7 second, that's hardly half of the performance of my database. Greetings, Torsten -- 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] less than 2 sec for response - possible?
On 05.07.2016 17:35, trafdev wrote: > [..] Without TIMESTAMP cast: QUERY PLAN HashAggregate (cost=1405666.90..1416585.93 rows=335970 width=86) (actual time=4797.272..4924.015 rows=126533 loops=1) " Group Key: subid, sid" Buffers: shared hit=1486949 -> Index Scan using ix_feed_sub_aid_date on feed_sub (cost=0.44..1313275.32 rows=3359694 width=86) (actual time=0.019..1783.104 rows=3588376 loops=1) Index Cond: ((aid = 3) AND (date >= '2016-06-01'::date) AND (date <= '2016-06-30'::date)) Buffers: shared hit=1486949 Planning time: 0.164 ms Execution time: 4941.259 ms I need to be sure it's a physical limitation of a Postgresql (when all data is in a memory and fetching\joining 1.5 mln of rows can't be done in less than 2-3 seconds) and there is no way to improve it. It could be a physical limitation of your hardware. I just did a short test on one of my databases: Aggregate (cost=532018.95..532018.96 rows=1 width=0) (actual time=3396.689..3396.689 rows=1 loops=1) Buffers: shared hit=155711 -> Index Only Scan using requests_request_time_idx on requests (cost=0.43..493109.90 rows=15563620 width=0) (actual time=0.021..2174.614 rows=16443288 loops=1) Index Cond: ((request_time >= '2016-07-01 00:00:00+00'::timestamp with time zone) AND (request_time <= '2017-07-06 00:00:00+00'::timestamp with time zone)) Heap Fetches: 31254 Buffers: shared hit=155711 Planning time: 0.143 ms Execution time: 3396.715 ms (8 rows) As you can see i can get 16.4 Mio rows within 3.4 seconds from cache. Your index-scan fetches 3.5 mio in 1.7 second, that's hardly half of the performance of my database. Greetings, Torsten -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
