On Tue, Oct 27, 2015 at 12:14 PM, Bill Moran <wmoran@xxxxxxxxxxxxxxxxx> wrote: > On Tue, 27 Oct 2015 11:30:45 +0800 > "657985552@xxxxxx" <657985552@xxxxxx> wrote: > >> Dear sir: >> Recently a wired question about postgresql database really bothered me a lot, so i really need your help. Here is the problem, in the most situations the postgre database work very well, Average 3500tps/s per day, the cpu usage of its process is 3%~10% and every query can be responsed in less than 20ms, but sometimes the cpu usages of its process can suddenly grow up to 90%+ , at that time a simple query can cost 2000+ms. ps: My postgresql version is 9.3.5 and the database is oltp server. > > 9.3.5 is pretty old, you should probably schedule an upgrade. > >> shared_buffers | 25GB > > Try setting this to 16GB. It's been a while since I tested on > large-memory/high-load systems, but I seem to remember that > shared_buffers above 16G could cause these sorts of intermittant > stalls. > > If that doesn't improve the situation, you'll probably need to > provide more details, specifically the layout of the table in > question, as well as the queries that are active when the > problem occurs, and the contents of the pg_locks table when > the problem is occurring. possible culprits: *) io based problems (check iowait, rule this out first) *) THP compaction (rule this out second) *) runaway query plan *) concurrency problems within postgres itself (perf top capture during load is essential) maybe some other things I'm not thinking of. merlin -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
