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 sin
have any suggestion for fine tuning this controller? I’m referring to
parameters like nr_requests, queue_depth, etc.
Also, any way to optimize the various mdadm parameters available at
/sys/block/mdX/ ? I disabled the internal bitmap and write performance improved.
Thank you
Pietro Pugni
It didn’t let the server boot properly with SSDs mounted.
Thank you for the suggestion
Pietro Pugni
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
ual embedded RAID controller (LSI 2008).
I don’t know if I can connect a 12Gb/s HBA directly to my existing 6Gb/s
expander/backplane.. sure I will have the right cables but don’t know if it
will work without changing the expander/backplane.
Thank you a lot for your time
Pietro Pugni
/12 or so.
> For my part I scaled out over asynchronous dblink which is a much more
> maintenance heavy strategy (but works fabulous although I which you could
> asynchronously connect).
Thank you for your hints
Pietro Pugni
theoretical IOPS. If we imagine adding only 2 more disks, I will achieve 720k
theoretical IOPS in RAID0.
What HBA controller would you suggest me able to handle more than 700k IOPS?
Have you got some advices about using mdadm RAID software on SATAIII SSDs and
plain HBA?
Thank you everyone
Pietro
;
> dear Pietro,
> are you sure about
>
> effective_io_concurrency = 30
>
> could you please explain the type of disk storage?
>
>
> Il 14/Nov/2016 12:46, "Pietro Pugni" <mailto:pietro.pu...@gmail.com>> ha scritto:
> Dear list,
> I’m looking
Dear list,
I’m looking for some guidelines on how to optimize the configuration of a
production database dedicated to a DWH application.
I run the application on different machines and have solved several issues
since now but am struggling on a production environment running Red Hat 6.7 and
Post
Log rotation was active and set to 5MB or 1 day.
I don’t know if it is a bug, but Postgres was logging even if logging_collector
was set to “off”.
Also, that big log file wasn’t visible for me, in fact “ls” and “du” didn’t
detect it.
Thanks again
Best regards,
Pietro Pugni
> Il giorno 14
space was filling up. The log file reached 110GB in size.
After disabling *ALL* the log options in postgresql.conf, the log file does
just the essential and default information.
I’m sorry to have launched a false alarm, but we can consider the issue solved.
Thank you again
Best regards,
Pietro
= 0.02
cpu_index_tuple_cost = 0.01
cpu_operator_cost = 0.005
effective_cache_size = 24GB
default_statistics_target = 1000
May be that some of these parameters causes this strange behavior?
checkpoint_completion_target?
Thanks to everyone for the support.
Best regards,
Pietro Pugni
configuration about work_mem, shared_buffers, max_connections
etc. Kernel version? If possible avoid 3.2 and 3.8-3.13. Also think to
upgrade your OS version.
>From today I'm on vacancy, so others could help :)
Pietro Pugni
Il 12/ago/2015 03:49, "Rural Hunter" ha scritto:
> arti
Hi Rural Hunter,
Try to create an index on cid attribute.
How many rows has article_729?
Pietro Pugni
Il 11/ago/2015 16:51, "Rural Hunter" ha scritto:
> yes i'm very sure. from what i observed, it has something to do with the
> concurrent query planing. if i disconnect o
> Ciao Pietro,
> stavo seguendo thread sulla mailing list Postgresql.
>
> Puoi farmi un piccolo riassunto delle conclusioni perchè non sono sicuro di
> aver capito tutto?
Ciao Domenico,
sì effettivamente la mailing list è un po’ dispersiva.
Utilizzando il collation di tipo “C” il Dell T420 impi
Hi Jeff
> The default collation for the database cluster is set when you create the
> cluster with initdb (the package you used to install postgresql might provide
> scripts that wrap initdb and call it something else, sorry I can't be much
> use with those).
> You can set it with --lc-colla
I meant “collation”, not “collection”.
Pietro
Il giorno 07/apr/2015, alle ore 18:49, Pietro Pugni ha
scritto:
>
>> On Tue, Apr 7, 2015 at 6:27 AM, Pietro Pugni wrote:
>> Hi Jeff,
>> sorry for the latency but server was down due to a error I made in the
>>
> On Tue, Apr 7, 2015 at 6:27 AM, Pietro Pugni wrote:
> Hi Jeff,
> sorry for the latency but server was down due to a error I made in the
> sysctl.conf file.
>> Yes, but are the defaults for those two systems? on psql, use \l to see.
>>
>
> \l re
Hi Josh,
at the moment the server is unreachable so I can’t calculate sizes. I run all
of my test both with all data loaded into Postgres and with no data loaded
(except from the single 20mln rows table with relative indexes).
To give you an idea, with all data loaded into Postgres with indexes t
Hi Josh,
> Did you already post the results of:
> cat /proc/sys/vm/zone_reclaim_mode
zone_reclaim_mode was set on 0 for all my tests. I’ve also set it to the other
values (1, 2, 4) but there was no improvement. Tests results are the following
(1 run for each test):
echo 0 > /proc/sys/vm/zone_re
Hi Aidan,
thank you again for your support.
I found an interesting article showing better performance from a Intel i5 vs a
Intel Xeon on different Postgres versions:
http://blog.pgaddict.com/posts/performance-since-postgresql-7-4-to-9-4-pgbench
I have to say that MacMini has a 2011 CPU (
http://
Hi didier,
thank you for your time.
I forgot to display before the output of free. I’ve looked into it before and I
found difficult to fully understand if there was something wrong.
Before starting Postgres:
total used free sharedbuffers cached
Mem: 1
Hi Tigran,
> The modern CPUs trying to be too smart.
>
> try to run this code to disable CPUs c-states:
>
> > setcpulatency.c
>
> #include
> #include
> #include
>
> int main(int argc, char **argv) {
> int32_t l;
> int fd;
>
> if (argc != 2) {
> fprintf(stderr, "Usage: %s \n
Il giorno 02/apr/2015, alle ore 14:29, didier ha scritto:
> Hi,
>
> On Thu, Apr 2, 2015 at 12:47 PM, Pietro Pugni wrote:
>> Hi Jeff,
>> thank you for your response.
>> I’m using Postgres 9.0 on MacMini because I’ve noticed that it’s quite fast
>> compared to d
Hi Aidan,
>
> T420
> work_mem = 512MB
>
> MacMini
> work_mem = 32MB
>
> So that is why the T420 does memory sorts and the mini does disk sorts.
>
> I'd start looking at why memory sorts on the T420 is so slow. Check your
> numa settings, etc (as already mentioned).
>
> For a drastic test
changes):
autovacuum = off
maintenance_work_mem = 6GB
Best regards,
Pietro
Il giorno 01/apr/2015, alle ore 16:27, Ilya Kosmodemiansky
ha scritto:
> Hi Pietro,
>
> On Wed, Apr 1, 2015 at 3:56 PM, Pietro Pugni wrote:
>> T420: went from 311seconds (default postgresql.conf) to 1
ust looking at the 2 B_2 queries, I'm curious as to why is the execution
> plan different between the 2 machines. Is the optimiser stats updated on both
> databases?
>
> Regards,
> Wei Shan
>
> On 1 April 2015 at 22:32, Aidan Van Dyk wrote:
> On Wed, Apr 1, 2015
giorno 01/apr/2015, alle ore 18:38, Jeff Janes ha
scritto:
> On Wed, Apr 1, 2015 at 6:56 AM, Pietro Pugni wrote:
> This question was posted originally on
> http://dba.stackexchange.com/questions/96444/cant-get-dell-pe-t420-perc-h710-perform-better-than-a-macmini-with-postgresql
&g
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
This question was posted originally on
http://dba.stackexchange.com/questions/96444/cant-get-dell-pe-t420-perc-h710-perform-better-than-a-macmini-with-postgresql
and they suggested to post it on this mailing list.
It's months that I'm trying to solve a performance issue with PostgreSQL. I’m
abl
29 matches
Mail list logo
