Re: slow database

alemon(at)tiago(dot)hazor(dot)com(dot)br writes:
> my data base is very slow. The machine is a processor Xeon 2GB with
> 256 MB of RAM DDR. My archive of configuration is this:

> sort_mem = 131072 # min 64, size in KB
> #vacuum_mem = 8192 # min 1024, size in KB

Change it back to 8192, or perhaps even less. This large value is
probably causing swapping, because it leads to every sort trying to
use 1073741824 bytes of memory, which is considerably more than you
have.

> fsync = false
> wal_sync_method = fdatasync # the default varies across platforms:

I presume that you are aware that you have chosen the value that
leaves your data vulnerable to corruption? I wouldn't set this to false...

> enable_seqscan = false
> enable_indexscan = false
> enable_tidscan = false
> enable_sort = false
> enable_nestloop = false
> enable_mergejoin = false
> enable_hashjoin = false

Was there some reason why you wanted to disable every query
optimization strategy that can be disabled? If you're looking to get
slow queries, this would accomplish that nicely.

> effective_cache_size = 170000 # typically 8KB each
> random_page_cost = 1000000000 # units are one sequential page fetch cost
> cpu_tuple_cost = 0.3 # (same)
> cpu_index_tuple_cost = 0.6 # (same)
> cpu_operator_cost = 0.7 # (same)

Where did you get those numbers? The random_page_cost alone will
probably force every query to do seq scans, ignoring indexes, and is
_really_ nonsensical. The other values seem way off.

> default_statistics_target = 1 # range 1-1000

... Apparently it didn't suffice to try to disable query optimization,
and modify the cost parameters into nonsense; it was also "needful" to
tell the statistics analyzer to virtually eliminate statistics
collection.

If you want a value other than 10, then pick a value slightly LARGER than 10.

> somebody please knows to give tips to me to increase the performance

Delete the postgresql.conf file, create a new database using initdb,
and take the file produced by _that_, and replace with that one. The
default values, while not necessarily perfect, are likely to be 100x
better than what you have got.

Was this the result of someone trying to tune the database for some
sort of anti-benchmark?
--
let name="cbbrowne" and tld="cbbrowne.com" in name ^ "@" ^ tld;;
http://www.ntlug.org/~cbbrowne/rdbms.html
Rules of the Evil Overlord #179. "I will not outsource core
functions." <http://www.eviloverlord.com/>