On Fri, Mar 24, 2017 at 3:58 AM, Pietro Pugni <pietro.pugni@xxxxxxxxx> wrote: > Hi there, > I’m running PostgreSQL 9.6.2 on Ubuntu 16.04.2 TLS (kernel > 4.4.0-66-generic). Hardware is: > - 2 x Intel Xeon E5-2690 > - 96GB RAM > - Software mdadm RAID10 (6 x SSDs) > > Postgres is used in a sort of DWH application, so all the resources are > assigned to it and the aim is to maximize the single transaction performance > instead of balancing between multiple connections. > > The configuration variables I changed are the following ones: > > checkpoint_completion_target = 0.9 > data_directory = '/mnt/raid10/pg_data_9.6.2' > default_statistics_target = 1000 > effective_cache_size = 72GB > effective_io_concurrency = 1000 > listen_addresses = '127.0.0.1,192.168.2.90' > maintenance_work_mem = 1GB > max_connections=32 > random_page_cost=1.2 > seq_page_cost=1.0 > shared_buffers = 24GB > work_mem = 512MB > > > The kernel configuration in /etc/sysctl.conf is: > > # 24GB = (24*1024*1024*1024) > kernel.shmmax = 25769803776 > > # 6MB = (24GB/4096) dove 4096 e' uguale a "getconf PAGE_SIZE" > kernel.shmall = 6291456 > > kernel.sched_migration_cost_ns = 5000000 > kernel.sched_autogroup_enabled = 0 > > vm.overcommit_memory = 2 > vm.overcommit_ratio = 90 > vm.swappiness = 4 > vm.zone_reclaim_mode = 0 > vm.dirty_ratio = 15 > vm.dirty_background_ratio = 3 > vm.nr_hugepages = 12657 > vm.min_free_kbytes=262144 > > dev.raid.speed_limit_max=1000000 > dev.raid.speed_limit_min=1000000 > > > Huge pages are being used on this machine and Postgres allocates 24GB > immediately after starting up, as set by vm.nr_hugepages = 12657. > My concern is that it never uses more than 24GB. For example, I’m running 16 > queries that use a lot of CPU (they do time series expansion and some > arithmetics). I estimate they will generate a maximum of 2.5 billions of > rows. Those queries are running since 48 hours and don’t know when they will > finish, but RAM never overpassed those 24GB (+ some system). > > Output from free -ht: > total used free shared buff/cache > available > Mem: 94G 28G 46G 17M 19G > 64G > Swap: 15G 0B 15G > Total: 109G 28G 61G Looks normal to me. Note that the OS is caching 19G of data. Postgresql is only going to allocate extra memory 512MB at a time for big sorts. Any sort bigger than that will spill to disk. GIven that top and vmstat seem to show you as being CPU bound I don't think amount of memory postgresql is using is your problem. You'd be better off to ask for help in optimizing your queries IMHO. -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
