2

I have just installed Postgres 9.1 on a server with 32GB of RAM and 320GB solid-state disk.

I will be using this server to serve both my site and my database (i.e. it's not just a database server). The database is 500m rows, approximately 120GB of data.

Once the data is loaded, the database needs to be optimised for SELECT queries - the data is fairly static in nature, and only changes once every three months. 99% of the queries made to the database will be reads.

I'm wondering what would be good Postgres settings to start out with. I've set:

effective_cache_size 8GB
shared_buffers 4GB

in the Postgres config file, but I haven't touched any of the other defaults.

With the settings above, I'm finding it very slow (more than an hour and still running) to create an index on one field in my table, and I want to create many of them.

Are there other settings I could look at to make index creation faster? Or can I safely increase these settings?

Improve this question
1
  • Since you have 32GB RAM you can increase shared_buffers to approximately 12.8GB. This will improve the select performance according to my believe.
    – Viraj
    Mar 18, 2015 at 12:33

1 Answer 1

Reset to default
6

If I were setting up a new server, I wouldn't use anything below 9.3.6. There have been many improvements since 9.1.

And if you use 9.4.1, there is some code to let it use larger amounts of RAM for index creation than it previously could.

Neither of the parameters you listed are particularly important for creating indexes. The most critical for that purpose would be maintenance_work_mem. On a 32GB server, if I'm only building one index at a time and keeping a close eye on things, I'd set it to 16GB for the local session that is building the large index.

Also, the C collation is much faster than the other collations, if you have a choice.

Is the bottleneck CPU or IO?

Improve this answer
6
  • Thanks, this is a really useful answer. I'm not sure whether the bottleneck is CPU or IO: how can I tell?
    – Richard
    Mar 17, 2015 at 19:40
  • Is this a linux server? If so, I use top. If there is only one active process on the system, with top it is easy to tell if that process is bottlenecked on CPU (the %CPU for that process is near 100) or IO (The %wa for the entire system is near 1/number of CPU, and the %CPU for the process is low). If there are many active processes, it can be harder to tell.
    – jjanes
    Mar 17, 2015 at 21:23
  • Thanks @jjanes. Yes, it's a Debian server. I've upgraded Postgres to 9.4 and I've set maintenance_work_mem to 16GB. Now when creating the index, top shows Postgres using 100% of CPU, so I guess CPU is the bottleneck. Any other suggestions? It's still slow (been running for more than 2 hours now). Thanks again.
    – Richard
    Mar 19, 2015 at 11:29
  • I'm interested in what you said about C collation. The database is currently using UTF-8 collation, but I'm only ever going to need simple LIKE comparisons on uppercase strings. This is my current index statement: CREATE INDEX mytable_presentation_code_varchar_pattern_ops_idx ON mytable(presentation_code varchar_pattern_ops);. Maybe I should re-create the whole database using the C collation and try again?
    – Richard
    Mar 19, 2015 at 11:43
  • 2
    utf-8 is not a collation, it's an encoding (i.e. the rules telling how characters are converted to bytes and reciprocally), and it's not mutually exclusive with the C locale. C.UTF-8 as the lc_collate and lc_ctype of a database is acceptable and even quite possibly the best choice in your case.
    – Daniel Vérité
    Mar 19, 2015 at 14:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.