Re: Tuning
От | Dave Cramer |
---|---|
Тема | Re: Tuning |
Дата | |
Msg-id | 55B1AD7B-DCC1-4129-AE14-1EDF0EF786FD@fastcrypt.com обсуждение исходный текст |
Ответ на | Tuning ("John Parnefjord" <John.Parnefjord@kib.ki.se>) |
Список | pgsql-performance |
On 26-Jan-07, at 6:28 AM, John Parnefjord wrote: > > Hi! > > I'm planning to move from mysql to postgresql as I believe the latter > performs better when it comes to complex queries. The mysql database > that I'm running is about 150 GB in size, with 300 million rows in the > largest table. We do quite a lot of statistical analysis on the data > which means heavy queries that run for days. Now that I've got two new > servers with 32GB of ram I'm eager to switch to postgresql to improve > perfomance. One database is to be an analysis server and the other an > OLTP server feeding a web site with pages. > > I'm setting for Postgresql 8.1 as it is available as a package in > Debian > Etch AMD64. > > As I'm new to postgresql I've googled to find some tips and found some > interesting links how configure and tune the database manager. Among > others I've found the PowerPostgresql pages with a performance > checklist > and annotated guide to postgresql.conf > [http://www.powerpostgresql.com/]. And of course the postgresql site > itself is a good way to start. RevSys have a short guide as well > [http://www.revsys.com/writings/postgresql-performance.html] > > I just wonder if someone on this list have some tips from the real > world > how to tune postgresql and what is to be avoided. AFAIK the following > parameters seems important to adjust to start with are: > > -work_mem > -maintenance_work_mem - 50% of the largest table? Isn't it possible for this to be larger than memory ? > -shared_buffers - max value 50000 Where does this shared buffers maximum come from ? It's wrong it should be 1/4 of available memory (8G) to start and tuned from there > -effective_cache_size - max 2/3 of available ram, ie 24GB on the > hardware described above > -shmmax - how large dare I set this value on dedicated postgres > servers? as big as required by shared buffer setting above > -checkpoint_segments - this is crucial as one of the server is > transaction heavy > -vacuum_cost_delay > > Of course some values can only be estimated after database has been > feed > data and queries have been run in a production like manner. > > Cheers > // John > > Ps. I sent to list before but the messages where withheld as I'm > not "a > member of any of the restrict_post groups". This is perhaps due to the > fact that we have changed email address a few weeks ago and there > was a > mismatch between addresses. So I apologize if any similar messages > show > up from me, just ignore them. > > ---------------------------(end of > broadcast)--------------------------- > TIP 2: Don't 'kill -9' the postmaster >
В списке pgsql-performance по дате отправления: