Обсуждение: Planning for Scalability
Dear Pg people, I would ask for your help considering this scaling issue. We are planning to move from 3Millions of events/day instance ofpostgres (8 CPU, 65 gb ram) to 5 millions of items/day. What do you suggest in order to plan this switch? Add separate server? Increase RAM? Use SSD? Any real help will be really precious and appreciated. Roberto
On Fri, Oct 03, 2014 at 10:55:04AM +0200, Roberto Grandi wrote: > Dear Pg people, > > I would ask for your help considering this scaling issue. We are planning to move from 3Millions of events/day instanceof postgres (8 CPU, 65 gb ram) to 5 millions of items/day. > What do you suggest in order to plan this switch? Add separate server? Increase RAM? Use SSD? > > Any real help will be really precious and appreciated. > Roberto > Hi Roberto, This change is within a factor of 2 of your existing load. I would start with analyzing the load on your existing system to determine where your bottlenecks are. 5M/day is 57/sec evenly distributed or 174/sec in an 8 hour period. This does not seems like a lot, but you have given us no details on your actual workload. Regards, Ken
Dear All thanks for your precious help. I'll come back to the list once analyzed our system. Roberto ----- Messaggio originale ----- Da: ktm@rice.edu A: "Roberto Grandi" <roberto.grandi@trovaprezzi.it> Cc: pgsql-performance@postgresql.org Inviato: Venerdì, 3 ottobre 2014 15:00:03 Oggetto: Re: [PERFORM] Planning for Scalability On Fri, Oct 03, 2014 at 10:55:04AM +0200, Roberto Grandi wrote: > Dear Pg people, > > I would ask for your help considering this scaling issue. We are planning to move from 3Millions of events/day instanceof postgres (8 CPU, 65 gb ram) to 5 millions of items/day. > What do you suggest in order to plan this switch? Add separate server? Increase RAM? Use SSD? > > Any real help will be really precious and appreciated. > Roberto > Hi Roberto, This change is within a factor of 2 of your existing load. I would start with analyzing the load on your existing system to determine where your bottlenecks are. 5M/day is 57/sec evenly distributed or 174/sec in an 8 hour period. This does not seems like a lot, but you have given us no details on your actual workload. Regards, Ken
Hi Roberto, Hardware etc. is a solution; but you have not yet characterised the problem. You should investigate if the events are mostly... - reads - writes - computationally intensive - memory intensive - I/O intensive - network I/O intensive - independent? (e.g. does it matter if you split the database in two?) You should also find out if the current server comfortably supports 3 million events per day or if you already have problemsthere that need addressed. Whereas if it handles 3 million with plenty of spare I/O, memory, CPU, network bandwidth, then maybe it will handle 5 millionwithout changing anything. Once you've gathered this information (using tools like pg_stat_statements, top, iotop, ... and by thinking about what thetables are doing), look at it and see if the answer is obvious. If not, think about what is confusing for a while, and then write your thoughts and data as a new question to the list. Graeme. On 03 Oct 2014, at 10:55, Roberto Grandi <roberto.grandi@trovaprezzi.it> wrote: > Dear Pg people, > > I would ask for your help considering this scaling issue. We are planning to move from 3Millions of events/day instanceof postgres (8 CPU, 65 gb ram) to 5 millions of items/day. > What do you suggest in order to plan this switch? Add separate server? Increase RAM? Use SSD? > > Any real help will be really precious and appreciated. > Roberto > > > -- > Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-performance
On Fri, Oct 3, 2014 at 5:55 AM, Roberto Grandi <roberto.grandi@trovaprezzi.it> wrote: > Dear Pg people, > > I would ask for your help considering this scaling issue. We are planning to move from 3Millions of events/day instanceof postgres (8 CPU, 65 gb ram) to 5 millions of items/day. The most important hardware part there is your I/O subsystem, which you didn't include. Lets assume you put whatever works. > What do you suggest in order to plan this switch? Add separate server? Increase RAM? Use SSD? With that kind of hardware, and a RAID10 of 4 SSDs, we're handling about 6000 peak (1300 sustained) read transactions per second. They're not trivial reads. They each process quite a lot of data. Write load is not huge, steady at 15 writes per second, but we've got lots of bulk inserts/update as well. Peak write thoughput is about 30 qps, but each query bulk-loads so it's probably equivalent to 3000 or so. In essence, unless your I/O subsystem sucks, I think you'll be fine.