Обсуждение: Re: Postgres is using 100% CPU
On Sun, May 31, 2015 at 7:53 PM, Yves Dorfsman <yves@zioup.com> wrote: >> That's the thing, even on an old laptop with a slow IDE disk, 273 > individual >> inserts should not take more than a second. > I think that would depend on settings such as synchronous_commit, commit_delay, or whether 2-phase commit is being used. If synchronous commit is enabled and commit_delay was not used (e.g. 0), and you have a client synchronously making individualinserts to the DB (1 transaction each), then surely you have delays due to waiting for each transaction to commitsynchronously to WAL on disk? I believe yes / 0 are the default settings for synchronous commit and commit_delay. (Interestingly the manual pages do notspecify.) Assuming a 5400RPM laptop drive (which is a typical drive - some laptop drives run < 5000RPM), and assuming you are writinga sequential log to disk (with very short gaps between entries being added, e.g. no seek time, only rotational latency)will mean 5400 transactions per minute, 1 write per rotation. That's a maximum 90 transactions per second synchronised to WAL. It would take just over 3 seconds. Ashik, try altering your postgresql.conf to say 'commit_delay=100' or 'synchronous_commit=off'. Let us know if that fixesthe problem. Read up on the options before you change them. Graeme Bell
> I believe yes / 0 are the default settings for synchronous commit and commit_delay. ** (Interestingly the manual pagesdo not specify.) ** Sorry, I've just spotted the settings in the text. The statement (marked **) is incorrect. Defaults are yes/0. (http://www.postgresql.org/docs/9.4/static/runtime-config-wal.html) Graeme.
Thanks everyone for your response. I will try these settings.
On Tue, Jun 2, 2015 at 1:28 PM, Graeme B. Bell <grb@skogoglandskap.no> wrote:
> I believe yes / 0 are the default settings for synchronous commit and commit_delay. ** (Interestingly the manual pages do not specify.) **
Sorry, I've just spotted the settings in the text. The statement (marked **) is incorrect.
Defaults are yes/0. (http://www.postgresql.org/docs/9.4/static/runtime-config-wal.html)
Graeme.
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance