I need to build deploy plan for a medium application which contain many postgres database (720). The model of almost of that are similar, but i have to separate its for manager and performance. Each database have about 400.000 to 1.000.000 record include both read and write. I have two questions: 1. How could i calculate amount of database on each machine (in centos 2.08 GHz CPU and 4 GB RAM)? Or how many database can i delop on each machine? The concurrence i guest about 10. 2. Is there any tutorial to calculate database size? 3. Is postgres database can "Active - Standby"?
-
1What is typical row width? That is, how many bytes you plan to store per every row (not including indexes)?mvp– mvp2013-10-22 07:40:01 +00:00Commented Oct 22, 2013 at 7:40
-
@mvp : how to calculate row width? i have test on my database with query: EXPLAIN SELECT * FROM thuadat it show result: "Seq Scan on thuadat (cost=0.00..741.29 rows=13029 width=7288)" ? is row width of my table is 77288 (thuadat table is biggest table).Truong Pham– Truong Pham2013-10-22 08:26:52 +00:00Commented Oct 22, 2013 at 8:26
-
postgresql.org/docs/9.1/static/…regilero– regilero2013-10-22 10:01:33 +00:00Commented Oct 22, 2013 at 10:01
Add a comment
|
1 Answer
I don't think that your server can possibly handle such load (if these are your true numbers).
Simple calculation: lets round up your 720 databases to 1000. Also lets round up your average row width of 7288 to 10000 (10KB). Assume that every database will be using 1 million rows.
Considering all these facts, total size of database in bytes can be estimated as:
1,000 * 10,000 * 1,000,000 = 10,000,000,000,000 = 10 TB
In other words, you will need at least few biggest hard drives money can buy (probably 4TB), and then you will need to use hardware or software RAID to get some adequate reliability out of them.
Note that I did not account for indexes. Depending on nature of your data and your queries, indexes can take anything from 10% to 100% of your data size. Full text search indexes can take 5x more than raw data.
At any rate, you server with just 4GB of ram will be barely moving trying to serve such a huge installation.
However, it should be able to serve not 1,000, but probably 10 or slightly more databases for your setup.
2 Comments
Truong Pham
Thank mvp for you anwser. It provides me the way to calculate database size. By the way, can you show me how to calculate row width?
mvp
You can use pg_total_relation_size() to get actual usage by table, or pg_database_size(name) to get usage by whole database. Alternatively, you can estimate sizes by simply adding them up - integer takes 4-8 bytes, string takes as much as you put into it plus some small overhead (few bytes) and so on. And of course, most accurate estimation is by looking at actual disk using
df or du over database directory.