OPTIMIZE TABLE
is a SQL statement - run it in a MySQL client.
On the command line of the server, you should have the command line client installed; you can connect to your database in the client with mysql -u username -p
.
See the documentation on OPTIMIZE TABLE
for more information.
Your PostgreSQL configuration is way off. This was suspicious from your initial post,
Cpu(s): 18.9%us, 14.4%sy, 0.0%ni, 53.4%id, 11.8%wa, 0.0%hi, 1.5%si, 0.0%st
Mem: 32865916k total, 7263720k used, 25602196k free, 575608k buffers
Swap: 16777208k total, 0k used, 16777208k free, 4464212k cached
Out of 32GB on your server, ~25GB is free excluding ~575MB of buffer.
From your postgresql.conf file,
shared_buffers = 32MB # min 128kB
#temp_buffers = 8MB # min 800kB
#max_prepared_transactions = 0 # zero disables the feature
...
#work_mem = 1MB # min 64kB
#maintenance_work_mem = 16MB # min 1MB
#max_stack_depth = 2MB
I'm assuming this is a dedicated database. If so, change it to the following parameters and reload/restart,
shared_buffers = 16GB # min 128kB
temp_buffers = 128MB # min 800kB
#max_prepared_transactions = 0 # zero disables the feature
...
work_mem = 8MB # min 64kB
maintenance_work_mem = 64MB # min 1MB
max_stack_depth = 4MB
Do let me know how this changes your performance and can further tune it as needed.
Regards to unlogged tables, if your temporary tables contain temporary data that is ephemeral and, as you mentioned, are created on session, it's better to use unlogged tables.
You can truncate your tables post session if that is acceptable.
More info here -- http://michael.otacoo.com/postgresql-2/unlogged-table-performance-in-postgresql-9-1/
I'm unsure of why you need temp tables for replication. Can't you use PostgreSQL streaming replication?
Best Answer
There is a camp in DBA-land (or was at one point, I haven't been through there recently) that held that optimal performance was to be found by micromanaging the storage infrastructure to such an extent that the systems and storage administrators had best get out of the way and just give them a pile of hard-drives, a server or two, and an occasional helping hand when asked.
These systems usually end up with a large number of RAID1 pairs with datafiles purposely and systematically spread across each and every one of them. Log drives would be used and similarly segregated. The ideas behind this method are pretty simple:
There is a significant problem with this approach, and Chopper3 nailed it: maintainability.
A system like that is going to need constant and continual attention and fine tuning to keep performing at 'optimum' as databases grow/shrink, usecases change, applications and their usage patterns evolve, recovery from run-away conditions happen, and maintenance cycles change. The sort of architecture I describe above works best for write-almost-never/read-lots sorts of workloads.
It also is used when it really is important for the database to eeke every last fractional percentage point of performance out of the hardware. This is a budget decision in many cases, where it is deemed that DBA time is cheaper than more hardware. However, there are some HPC cases where it just isn't possible to make a bigger box so you have to optimize what you have. For the rest of us who aren't using monster database servers there are always upgrades.
The other camp, and the one I ascribe to, handles it differently. It puts more faith in storage abstraction, and tends to scale a lot better. Instead of umpty R10 pairs, you put all of those pairs into a RAID10 set; or alternately a small number of RAID10 sets. This allows IOP aggregation same as above, but each datafile/tablespace/database gets access to quite a bit of surge capacity. By using multiple R10 sets you can provide I/O separation for those critical databases and log-files that need it. The need to micromanage per-file peformance is greatly reduced.