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 mfatticci...@mbigroup.it 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
-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 sick...@opinioni.net 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
[PERFORM] Tuning the configuration
-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. 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 -BEGIN PGP SIGNATURE- Version: GnuPG v1 iEYEARECAAYFAlSII/gACgkQi2q3wPb3FcPUuACgg2m2o9dQWavLrN2EmmmCpGEv YnMAoN0R/gejcKwnxf0qFPKXtaGaIG1A =oLxU -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] DRBD and Postgres: how to improve the perfomance?
Thank you very much for your ideas. I've tried to change the protocol from C to B and I got an increase in the number of TPS: 64.555763. Now I would like to follow the advice of Mr. Bernd Helmle and change the value of snd-bufsize. The servers are cross connected with a common 100 Mbit/sec Ethernet so I think they have a bandwidth around 80 Mbit/sec (even if I haven't yet done any test on it). A rate of 70Mb seems reasonable to me. The two servers are in two different racks (next to each other) and they have two power supplies connected to two different sets of UPS. Unfortunately we cannot accept a loss of recently committed transactions so we cannot put the synchronous_commit to off. Regards, Maila Fatticcioni Simon Riggs wrote: On Fri, 2007-09-07 at 11:37 +0200, Maila Fatticcioni wrote: protocol C; Try protocol B instead. -- __ Maila Fatticcioni __ Mediterranean Broadband Infrastructure s.r.l. ITALY __ signature.asc Description: OpenPGP digital signature
[PERFORM] DRBD and Postgres: how to improve the perfomance?
Hello.
We have made some performance tests with DRBD and Postgresql 8.2.3. We
have two identical servers in a cluster (Dell 2950) with a partition of
100 GB managed by DRBD: once we checked Postgres keeping his data folder
in a local partition, the second time we moved the data folder in the
shared partition. The two servers are connected point to point using a
cross cable to reduce their latency.
The partition is mounted with the option noatime in order to not update
the inode access time in case of read access.
We used pgbench for the testings, creating a dabase of about 3GB with a
scale of 200. After we perfomed 10 tests for each configuration,
simulating the usage of 100 clients with 500 transactions each.
DRBD configuration:
resource drbd0 {
protocol C;
incon-degr-cmd halt -f;
on db-node1 {
device /dev/drbd0;
disk /dev/sda2;
address10.0.0.201:7788;
meta-disk internal;
}
on db-node2 {
device/dev/drbd0;
disk /dev/sda2;
address10.0.0.202:7788;
meta-disk internal;
}
syncer {
rate 70K;
}
}
Pgbench
pgbench -i pgbench -s 200
pgbench -c 100 -t 500 pgbench
The results were that the TPS (transaction per second) with Postgres
running in the local partition is almost double than the one with the DRDB:
Postgres in shared DRBD partition: 60.863324 TPS
Postgres in local partition: 122.016138 TPS
Obviously, working with the database in DRBD, we had two writes instead
of only one but we are a bit disappointed about the low results. We
would like to know if there is any way to improve the performance in
order to have a 3/4 rate instead of the 1/2 one.
We would really appreciate it if you could give us some feedback.
Thank you in advance,
Maila Fatticcioni
--
__
Maila Fatticcioni
__
Mediterranean Broadband Infrastructure s.r.l.
ITALY
__
signature.asc
Description: OpenPGP digital signature
