I'm using PostgreSQL 8 for a mmorpg.
The part of each operation is : select: 50%, update: 40%, insert: 10%.
I have no more than 4-5 concurrent connections to the database, but each of them does A LOT of queries (several per second).
The database size is about 1GB, but it'll probably be around 2GB in a fews months.
The OS will be FreeBSD (version production 5.3 probably, or 4.10)
At this time, i'm looking for a new server. Before to buy it, I grab some informations..
So, my question is : what would be the best hardware for this type of needs ?
Of course, I'm not asking for a trademark and/or for prices, but for hints.
- What is the most important part of the system : CPU ? RAM ? Disks ?
Usually Disks/RAM. Since you've got a lot of updates/inserts, battery-backed write-cache on your raid controller would be good.
- Is a server with 2 or more CPUs much better than a server with a single one, for a pgsql database ?
With 2+ connections, each can be serviced by one CPU. Of course, if your disk I/O is saturated then it won't help.
- How much RAM do I need ? The size of the data ? Twice the size ?
Ideally, enough to hold your "working set". That is, enough cache to store all pages/indexes you regularly access.
- I heard Raid1+0 is better than Raid 5. Is it right ? What would be the best configuration, regarding performances and security ?
It can depend - check the list archives for a lot of discussion on this. More disks is always better.
- Does the CPU type (i386, PowerPC, ....) matters ?
Dual-Xeons have given problems. A lot of people seem to think Opteron-based systems provide good value.
- A lot of queries probably generate a lot of network output. Does the network controller matters ?
Well, obviously the more time spent handling network I/O, the less time you spend running queries. I'd think it would have to be a *lot* of activity to make a serious difference.
- And finally, last question : is it possible to run a single postgresql database on several servers ? (hardware clustering)
Not easily, and it probably wouldn't provide any performance benefit. Plenty of replication options though.
Thanks in advance for your answers, and sorry for my crap english (i'm french).
Your English is perfect. -- Richard Huxton Archonet Ltd
---------------------------(end of broadcast)--------------------------- TIP 8: explain analyze is your friend