On Tue, Oct 27, 2015 at 12:14 PM, Bill Moran <wmo...@potentialtech.com> wrote: > On Tue, 27 Oct 2015 11:30:45 +0800 > "657985...@qq.com" <657985...@qq.com> 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@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
