Re: Can't get Dell PE T420 (Perc H710) perform better than a MacMini with PostgreSQL

Hi Gerardo,
thank you for your response.
At the moment I can’t switch to RAID10. I know it has best performance, but both systems have RAID5 and MacMini has a consumer desktop RAID solution while T420 has a server-grade one.
Anyway, I used two configurations for each system: one for data loading operations and the other one for any other kind of operation (SELECT etc.). These configurations were made studying different combinations. I’ve changed kernel parameters as stated in the official Postgres documentation ( www.postgresql.org/docs/9.4/static/kernel-resources.html ).
I copy and paste here the various postgresql.conf involved:

T420
Normal operations
autovacuum = on
maintenance_work_mem = 512MB
work_mem = 512MB
wal_buffers = 64MB
effective_cache_size = 64GB # this helps A LOT in disk write speed when creating indexes
shared_buffers = 32GB
checkpoint_segments = 2000
checkpoint_completion_target = 1.0
effective_io_concurrency = 0 # 1 doesn’t make any substantial difference
max_connections = 10 # 20 doesn’t make any difference

Data loading (same as above with the following changes):
autovacuum = off
maintenance_work_mem = 64GB

MacMini
Normal operations
autovacuum = on
maintenance_work_mem = 128MB
work_mem = 32MB
wal_buffers = 32MB
effective_cache_size = 800MB
shared_buffers = 512MB
checkpoint_segments = 32
checkpoint_completion_target = 1.0
effective_io_concurrency = 1
max_connections = 20

Data loading (same as above with the following changes):
autovacuum = off
maintenance_work_mem = 6GB

Best regards,
Pietro

Il giorno 02/apr/2015, alle ore 04:19, Gerardo Herzig <gherzig(at)fmed(dot)uba(dot)ar> ha scritto:

> Ok, a quick view on the system, and some things that may be important to note:
>
>> Our deployment machine is a Dell PowerEdge T420 with a Perc H710 RAID
>> controller configured in this way:
>>
>> * VD0: two 15k SAS disks (ext4, OS partition, WAL partition,
>> RAID1)
>> * VD1: ten 10k SAS disks (XFS, Postgres data partition, RAID5)
>>
>
> Well...usually RAID5 have the worst performance in writing...EVER!!! Have you tested this in another raid configuration? RAID10 is usually the best bet.
>
>>
>>
>> This system has the following configuration:
>>
>> * Ubuntu 14.04.2 LTS (GNU/Linux 3.13.0-48-generic x86_64)
>> * 128GB RAM (DDR3, 8x16GB @1600Mhz)
>> * two Intel Xeon E5-2640 v2 @2Ghz
>> * Dell Perc H710 with 512MB RAM (Write cache: "WriteBack", Read
>> cache: "ReadAhead", Disk cache: "disabled"):
>> * VD0 (OS and WAL partition): two 15k SAS disks (ext4, RAID1)
>> * VD1 (Postgres data partition): ten 10k SAS disks (XFS,
>> RAID5)
>> * PostgreSQL 9.4 (updated to the latest available version)
>> * moved pg_stat_tmp to RAM disk
>>
>>
> [...]> versions.
>>
> You did not mention any "postgres" configuration at all. If you let the default checkpoint_segments=3, that would be an IO hell for your disk controler...and the RAID5 making things worst...Can you show us the values of:
>
> checkpoint_segments
> shared_buffers
> work_mem
> maintenance_work_mem
> effective_io_concurrency
>
> I would start from there, few changes, and check again. I would change the RAID first of all things, and try those tests again.
>
> Cheers.
> Gerardo