PDA

View Full Version : Postgres tablespaces on separate server



Folding
11-26-2009, 09:35 PM
Hi,

I currently have postgres installed on a Linux server, HM connects and works fine. Performace is much better than it was when the database was local.

I would like to improve performance by relocating the data and indexes to separate tablespaces, on separate fast disks. Is there any way to tell HM to use different tablespaces when creating the database?

Short dumping the database (with pg_dump -C), manually editing the schema and restoring it, I do not see how to do this.

Thanks,

netsrak
11-27-2009, 04:47 AM
You need to create a new database via pgadmin with the settings you like and then restore a backup of an holdemmanager database into this new database.
At least using a different tablespace for the data works this way.

Folding
11-27-2009, 10:35 AM
Thanks for the reply. Unfortunately that will not work for what I want to do. The tablespace assignment must occur at the level of the "Create Index" statement. Otherwise the indexes and the tables all end up in the same tablespace.

It looks like I should dump the database with "pg_dump -s -C" to get only the schema and necessary create commands, then with "pg_dump -a" to get only the data. Then edit the schema, drop the database, restore the schema, and restore the data.

A project for a day that I am not playing poker.

netsrak
11-27-2009, 12:12 PM
there is also a SQL command "ALTER index...."
and you can change the tablespace for an index via pgadmin -> server->database->tables index properties

but i have never tried this and don't know what happens.

If you want to test it: backup your database, create a new tablespace via pgadmin and try one of the above.

Please let me know the results.

BeaucoupFish
11-27-2009, 06:37 PM
Thx Fozzy for linking me to this thread.

Folding, I have had the same thought for a while. This technique is a common optimisation implemented in business / industrial applications (experience from a previous job using Oracle).

My own database is not particularly large so I don't think I can get any useful performance improvement data from trying it, but if this turns out to be a useful optimisation, I think it would be very useful for users with larger db's who either can't afford or have too much data for a SSD drive, and the developers could modify the db creation script so that it uses a separate tablespace for general table data and index data.

I'm not very familiar with postgres so I don't know how to identify all the indexes using SQL, but it looks like there is a view (pg_indexes) that lists all indexes in a database, so you could use that to try and create your own script. Again, I am not familiar enough to know commands to drop and then rebuild the indexes, but it should be easy enough to look up.

Alternatively, if the developers could publish the schema or db creation script, you could work from that (this would be the cleanest and easiest method). Either way, it would be interesting to see performance data from a large enough db, and if it is a good improvement, publish the info as another tuning suggestion.

netsrak
11-28-2009, 07:15 AM
I think its possible to generate the db creation script via pgadmin.

BeaucoupFish
11-28-2009, 05:44 PM
One way to do this might be to create a new HEM db (using HEM), then using the backup tool, create a plain text file of the schema, without data. This includes all the create index commands at the end, so you can use it to create a new script that drops the indexes and then re-creates them (adding in the correct format to specify a different tablespace). Then rebuild the indexes.

Having 2 identical databases would be preferable so you can performance test the configurations accurately.

Folding
12-18-2009, 09:28 PM
I ran "pg_dump -s -C" to get only the schema and necessary create commands, then "pg_dump -a" to get only the data, edited the schema to use two new tablespaces (data & index), created the new database and restored the data.

Works fine, no problems. I have not done formal benchmarks, and going by "feel" do not notice a difference. I will leave it configured this way, as it should be faster than the way it was before. The speed is fine, as it was before.

The database server is a 3.8GHZ Hyperthread P4 with 4gb mem running openSUSE 11.2. The OS, /tmp and swap are on dedicated 10K scsi disks. The Postgres database is now on 2 WD VelociRaptors (10K rpm) data on one, indexes on the other. Before the database was on a WD Caviar Black (7200rpm). The machine is also a mail/web/file/streaming video/music server for four people.

BeaucoupFish
12-18-2009, 11:15 PM
I mentioned via PM to Folding that I also tried this (just using a rather unscientific / non-quantitative "try") and while it was pretty easy to do, I didn't see any performance changes. I have a very small db though (it might be interesting to try this on someone like LeatherAss's db!).

When you see this done in industrial applications, I think the performance improvements seen are due to larger and more complicated db's (table size, table element size / complexity, total number of data rows etc), and this concept might simply be irrelevant to a HEM db, even a large one.

Other db performance improvements that might have better results are looking at whether additional table indexes would be useful, and of course how the client interfaces with the db, SQL optimisation, stored procedures etc, plus just changes in perceived performance by retrieving data more on-demand rather than all-at-once...stuff like that.