> OK, this seems to be the cause. What were the original values of the > config variables? If you've lowered the work_mem and you need to sort a > lot of data, this may be a problem. What amounts of data are you working > with? If the data were not analyzed recently, the execution plans will be > inefficient and this may be the result. The reason is that i'm using config that i used before and it worked perfect. work_mem is 192mb. I tried ANALYZE, but it didn't change anything. Amounts of data... at least, backup is over ~2.2Gb. I tried to use EXPLAIN ANALYZE for slow queries that i get from serverlog, but it also didn't change anything. 2009/7/30 <tv@xxxxxxxx>: >> Unfortunately had to downgrade back to 8.3. Now having troubles with >> that and still solving them. >> >> For future upgrade, what is the basic steps? > > 1. create database > 2. dump the data from the old database > 3. load the data into the new database > 4. analyze etc. (I prefer to do this manually at the beginning) > 5. check that everything is working (that the correct execution plans are > used, etc.) > > You may even run the (2) and (3) at once - use pipe instead of a file. > >> >>>Was the database analyzed recently? >> Hm... there was smth like auto analyzer in serverlog when i started it >> first time, but i didn't mention that. >> Should I analyze whole db? How to do it? > > Just execute 'ANALYZE' and the whole database will be analyzed, but when > the autovacuum daemon is running this should be performed automatically (I > guess - check the pg_stat_user_tables, there's information about last > manual/automatic vacuuming and/or analysis). > >> And how should I change _cost variables? > > I haven't noticed you've not modified those variables, so don't change them. > >> I/O was very high. at first memory usage grew up and then began to full >> swap. > > OK, this seems to be the cause. What were the original values of the > config variables? If you've lowered the work_mem and you need to sort a > lot of data, this may be a problem. What amounts of data are you working > with? If the data were not analyzed recently, the execution plans will be > inefficient and this may be the result. > > regards > Tomas > > -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
