Обсуждение: Hardware performance tuning
I am running the PostgreSQL 7.1.3 server on a Debian GNU/Linux (testing) system. The computer is more-or-less a dedicated PostgreSQL se and Zope server. It has 1 GB of memory (DIMMs are really cheap these days) and a single 7200 rpm ATA-100 IDE drive. The processor is a 1.2 GHz Athlon, in case that matters. I read Bruce Momjian's supplement "PostgreSQL Performance Tuning" to his Addison-Wesley book. He mentions two parameters, shared_buffers and sort_mem, that you should consider increasing when you have a reasonable amount of memory available. My postgresql.conf file currently contains debug_level = 0 hostname_lookup = on log_connections = on log_pid = on log_timestamp = on syslog = 2 tcpip_socket = on fsync = off # if syslog is 0, turn silent_mode off! silent_mode = off syslog_facility = LOCAL0 trace_notify = off max_connections = 64 # shared_buffers must be at least twice max_connections, and not less than 16 shared_buffers = 512 sort_mem = 2048 Yesterday we were loading a database that has about a dozen tables. The largest table (about 250,000 rows) has referential integrity constraints relative to two other tables. The load went reasonably quickly but I did notice that the postmaster process was using about 85% of the cpu time and seemed to be doing a lot of system calls. I was surprised that it used very little memory, about 8 MB I believe. I suspect that if I increase the sort_mem parameter or the shared_buffers parameter it will run much faster at the expense of using more memory. I really wouldn't object if the postmaster process used a couple hundred megabytes of memory since the machine has enough memory and is more-or-less dedicated to PostgreSQL. Would it do any good for me to bump up the sort_mem parameter? If so, how big should I make it? Are there other tuning parameters that I could profitably tweak? -- Douglas Bates bates@stat.wisc.edu Statistics Department 608/262-2598 University of Wisconsin - Madison http://www.stat.wisc.edu/~bates/
Take a look at what Bruce Momjian has to say. One thing is certain however, your shared buffers are pitifully low for the amount of memory you have available. http://www2.linuxjournal.com/lj-issues/issue88/4791.html Jason Douglas Bates <bates@stat.wisc.edu> writes: > I am running the PostgreSQL 7.1.3 server on a Debian GNU/Linux > (testing) system. The computer is more-or-less a dedicated PostgreSQL > se and Zope server. It has 1 GB of memory (DIMMs are really cheap > these days) and a single 7200 rpm ATA-100 IDE drive. The processor is > a 1.2 GHz Athlon, in case that matters. > > I read Bruce Momjian's supplement "PostgreSQL Performance Tuning" to > his Addison-Wesley book. He mentions two parameters, shared_buffers > and sort_mem, that you should consider increasing > when you have a reasonable amount of memory available. > > My postgresql.conf file currently contains > > debug_level = 0 > hostname_lookup = on > log_connections = on > log_pid = on > log_timestamp = on > syslog = 2 > tcpip_socket = on > fsync = off > # if syslog is 0, turn silent_mode off! > silent_mode = off > syslog_facility = LOCAL0 > trace_notify = off > max_connections = 64 > # shared_buffers must be at least twice max_connections, and not less than 16 > shared_buffers = 512 > sort_mem = 2048 > > Yesterday we were loading a database that has about a dozen tables. > The largest table (about 250,000 rows) has referential integrity > constraints relative to two other tables. The load went reasonably > quickly but I did notice that the postmaster process was using about > 85% of the cpu time and seemed to be doing a lot of system calls. I > was surprised that it used very little memory, about 8 MB I believe. > > I suspect that if I increase the sort_mem parameter or the > shared_buffers parameter it will run much faster at the expense of > using more memory. I really wouldn't object if the postmaster process > used a couple hundred megabytes of memory since the machine has enough > memory and is more-or-less dedicated to PostgreSQL. Would it do any > good for me to bump up the sort_mem parameter? If so, how big should > I make it? Are there other tuning parameters that I could profitably > tweak? > > -- > Douglas Bates bates@stat.wisc.edu > Statistics Department 608/262-2598 > University of Wisconsin - Madison http://www.stat.wisc.edu/~bates/ > > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org