Re: More cores or higer frequency ?

On Tue, May 23, 2017 at 1:49 PM, Sven R. Kunze <srkunze(at)mail(dot)de> wrote:

> On 23.05.2017 22:14, Jarek wrote:
>
>> I have pool of clients (~30) inserting to database about 50 records per
>> second (in total from all clients) and small numer (<10) clients
>> querying database for those records once per 10s.
>> Other queries are rare and irregular.
>> The biggest table has ~ 100mln records (older records are purged
>> nightly). Database size is ~13GB.
>> I near future I'm expecting ~150 clients and 250 inserts per second and
>> more clients querying database.
>> Server is handling also apache with simple web application written in
>> python.
>> For the same price, I can get 8C/3.2GHz or 14C/2.6GHz. Which one will be
>> better ?
>>
>>
I would start by trying a few things on your existing equipment.

If your inserts are coming from individual connections, say, via the
web-app in a connect-insert-disconnect fashion then pooling can be a huge
win. Connection overhead is a bigger factor than you might imagine and I've
seen as much as a 10x improvement in small queries when pooling was added.

If the every-10-second queries are running on the recently inserted data
then partitioning by time range could substantially improve the speed of
inserts, queries and purging. It's pretty easy to do, now, with pg_partman
or similar but built-in auto-partitioning is coming in version 10.

Fast commit to disk is a win - think SSD or RAID with BBU cache and with a
relatively modest 13GB database you should be able to spec enough RAM to
keep everything in memory.

Cheers,
Steve