Kevin, thanks for your attention!
I've read SlowQueryQuestions, but anyway can't find bottleneck...
Here requested information:
OS: Ubuntu 9.10 64bit, Postgresql 8.4.2 with Postgis
Hardware: AMD Phenom(tm) II X4 945, 8GB RAM, 2 SATA 750GB (pg db
installed in software RAID 0)
Please also note that this hardware isn't dedicated DB server, but
also serve as web server and file server.
I have about 3 million rows in core_object, 1.5 million in
plugin_plugin_addr and 1.5 million in plugins_guide_address.
When there were 300 000+ objects queries works perfectly, but as db
enlarge things go worse...
# select version();
PostgreSQL 8.4.2 on x86_64-pc-linux-gnu, compiled by GCC gcc-4.4.real
(Ubuntu 4.4.1-4ubuntu8) 4.4.1, 64-bit
---postgresql.conf---
data_directory = '/mnt/fast/postgresql/8.4/main'
hba_file = '/etc/postgresql/8.4/main/pg_hba.conf'
ident_file = '/etc/postgresql/8.4/main/pg_ident.conf'
external_pid_file = '/var/run/postgresql/8.4-main.pid'
listen_addresses = 'localhost'
port = 5432
max_connections = 250
unix_socket_directory = '/var/run/postgresql'
ssl = true
shared_buffers = 1024MB
temp_buffers = 16MB
work_mem = 128MB
maintenance_work_mem = 512MB
fsync = off
wal_buffers = 4MB
checkpoint_segments = 16
effective_cache_size = 1536MB
log_min_duration_statement = 8000
log_line_prefix = '%t '
datestyle = 'iso, mdy'
lc_messages = 'en_US.UTF-8'
lc_monetary = 'en_US.UTF-8'
lc_numeric = 'en_US.UTF-8'
lc_time = 'en_US.UTF-8'
default_text_search_config = 'pg_catalog.english'
standard_conforming_strings = on
escape_string_warning = off
constraint_exclusion = on
checkpoint_completion_target = 0.9
---end postgresql.conf---
I hope this help!
Any ideas are appreciated!
On Apr 9, 12:44 am, Kevin.Gritt...@wicourts.gov ("Kevin Grittner")
wrote:
>
> Could you show us the output from "select version();", describe your
> hardware and OS, and show us the contents of your postgresql.conf
> file (with all comments removed)? We can then give more concrete
> advice than is possible with the information provided so far.
>
> http://wiki.postgresql.org/wiki/SlowQueryQuestions
>
> -Kevin
>
> --
> Sent via pgsql-performance mailing list (pgsql-performa...@postgresql.org)
> To make changes to your subscription:http://www.postgresql.org/mailpref/pgsql-performance