2

Ours is a web-based application (built on multi-tenant architecture) running PostgreSQL v9.1.3. There are about 450 tables in our application, out of which 2-3 tables, specific to a module in the application, have huge volume of records stored compared to other remaining tables and are heavily accessed (both read and write operations) by application Users.

To give a picture/statistics on the volume of records available, currently there are 8 million records in one table and 3 million records in another table. We're expecting a immediate increase/traffic in the volume of transaction (again both read and write transactions) to these tables in the near future, since we're coming up with some exciting features within this specific module.

My question with requirements are,

  1. because we're expecting a huge traffic to this specific module, we don't want to let down the Users accessing other modules in the application to be affected because of any performance issues that it may cause, though.
  2. separating/isolating heavily accessed tables is one solution that came off the top of my head. Is it a good idea to separate/move to a different database? What are the pros and cons of this approach?
  3. any solution, comment, approach, suggestion are welcome and appreciated.
Improve this question
4
  • One table in our database: 769387000 rows. This is just one month of new data in one table. There are 200 other tables... We have about 2500 transactions per second, using 150 concurrent users. 8 million records is next to nothing in our situation. It all runs on one 24core machine and 60% idle.
    – Frank Heikens
    Commented Sep 25, 2012 at 11:26
  • @FrankHeikens: Thanks for your comment with your real-time database size. Would you mind sharing your PostgreSQL configuration (postgresql.conf)? Can you share any performance tuning tips in this regard? Thanks.
    – Gnanam
    Commented Sep 25, 2012 at 11:52
  • @FrankHeikens: What is your RAM size?
    – Gnanam
    Commented Sep 26, 2012 at 9:56
  • shared_buffers is 8GB, 48 disks in RAID 10, 256GB RAM in total. 64GB RAM would have good enough, but management wanted more.
    – Frank Heikens
    Commented Sep 26, 2012 at 11:08

2 Answers 2

Reset to default
5

You will gain a lot more with regards to performance if you move the table to a different harddisk. As long as the "busy" table and the rest are located on the same disk, moving that table into a different "file" (by moving it into a different database) won't change anything with regards to (I/O) performance.

Distributing the I/O load to a different harddisk (and a different harddisk controller) will most probably give you a better performance for the remaining data because it is not affected by the I/O done on the busy table.

To move the table to a different harddisk, you need to create a new tablespace (which of course is located on that disk) and then move that table to that tablespace.

Improve this answer
4
  • Thanks for your suggestion about moving table to a different harddisk to improve I/O performance. At the time of writing this question, when I said, move to a different database, I thought of moving to a separate server itself in my mind. In our current setup, both web server and database share the same server.
    – Gnanam
    Commented Sep 26, 2012 at 7:21
  • 1
    @Gnanam: a database is in most case limited by the I/O the system can do. Usually the WebServer does not generate that much I/O that it would influence the database server (although that highly depends on your application). Moving the database to its own machine can improve your performance. Especially as the disk system of a WebServer is usually not that "high-end" as that of a dedicated database server.
    – user1822
    Commented Sep 26, 2012 at 7:32
  • Thanks once again for your inputs. To let you know on the environment setup part, our application is hosted in the Amazon EC2 Cloud environment.
    – Gnanam
    Commented Sep 26, 2012 at 7:46
  • This comment won't apply to you, since you're on Amazon EC2. But if that different hard disk were actually a solid-state disk (SSD), you might get a staggering improvement in performace. SSDs are not without their own problems, though.
    – Mike Sherrill 'Cat Recall'
    Commented Nov 15, 2012 at 19:30
3

I think that in this case you are better off optimizing I/O generally, and trying to ensure you have a lot of RAM in your server than moving the tables onto another tablespace. If the tables are more frequently accessed and there is enough memory they will likely mostly be kept in memory and the WAL is the only file flushed to disk on commit anyway.

More memory will make the most difference. The frequently accessed pages will be more likely to be in the queue and so disk I/O will be reduced. That's the big one.

Creating additional tablespaces can also create other problems. If these are on other RAID arrays, you are dividing up your total throughpout between both arrays, and so everything will be slower. On the other hand, if you add RAM there is less disk I/O in the first place.

Improve this answer
2
  • We've 35 GB RAM available in the server. I feel, in my case, there is enough memory available.
    – Gnanam
    Commented Sep 26, 2012 at 7:29
  • If your whole db won't fit comfortably in RAM, if at least the frequently hit part will, that is the big issue there.
    – Chris Travers
    Commented Sep 26, 2012 at 9:26

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.