0

I wanna have brainstorm with you guys all about scaling option that DB2 have. Hope can helped me to resolve the problem.

I need to scale my DB2 database to anticipate flash crowd transaction to database server. My database can only serve around 200 transaction++ per sec in application term not database tps before my database totally stalled and out of cpu.

What are you guys think, if I want to increase to reach 2000++ or 10 times before, what options i have to scale my database?

Recently i read about pureScale feature. Its look promising but its not flexible solution by mean it just can be deploy on IBM System X and ours is not. Are there other solution like pureScale in shared-everything approach?

The second option maybe database partition. Is database partition or shared-nothing approach can help resolve my problem? Can add processing power to my system?

Thanks and regards,

Fritz

fritz
  • 121
  • 4
  • Desrbie your hardware. I have no problem reaching a couple of thousand databae transactions on a virtual machine with 4 cores + proper disc subsystem - so quite obviously you have extremely inefficient programming (what eats your CPU in the database server - database servers in transactional processing are IO bound, not CPU bound). – TomTom Jul 06 '12 at 04:19
  • Thats my fisrt guest to but its too bad when I monitor my server i/o its quite low but my cpu is drained out stall in 100%. My server is quite powerfull. CPU is 2 socket (total 24 core), RAM is 24gigs, storage plenty configuration RAID 10. From profiling, i capture for 1 transaction about 200s sql (180 select, 20 insert and update). – fritz Jul 06 '12 at 04:29
  • This makes no sense - this is not a database application, you use the database server as application server? You should be IO problematic, not database problematic. When you want to scale, having logic in the database server is BAD - that is the part that is hardest and MOST EXPENSIVE to scale. – TomTom Jul 06 '12 at 04:36
  • nope. the logic still in application server. i'm still confuse why the processing power drained out. any opinions? are there option to scale the db2? or scale the db would solve my problem? – fritz Jul 06 '12 at 06:55
  • No, not a db2 specialist here, but seriously - this smells like some issue somewhere. If you do pure SQL and not a lot of aggregations etc. then the CPU is normally a non issue. It is the last item that comes up as bottleneck. – TomTom Jul 06 '12 at 06:57
  • thanks tom. i would try to benchmark my db first. lets assume that my testing is ok. so the suspect is there a logic in db right? – fritz Jul 06 '12 at 07:28
  • Exactly. To a significant degree. Alterantively something may be wrong in terms of plan reuse etc. - the trick sis to find out WHAT Uses that hugh amound of CPU. It SMELLS like heavy logic. – TomTom Jul 06 '12 at 07:30
  • 1. Can you give an example of a query you're running? 2. What operating system are you using? – Leo Jul 07 '12 at 14:25
  • We are using windows but have plans to migrate to linux. We found that the bottleneck is the stored procedure make the data server stalled. After fixing the problem, it seems that the cpu is ok again. But, is there anyway to increase tps? I mean, scale out. – fritz Mar 19 '13 at 02:22

1 Answers1

1

Before you worry about how to scale up (more hardware in 1 server) or out (more servers), look at how to tune your database. Buying your way out of a performance problem is almost always more expensive than spending time to find and fix the performance problem.

Assuming that the process(es) consuming CPU on your database server are the database engine, then high CPU activity and low I/O activity is indicative that you're doing a LOT of reads, but they are just all in memory. Scanning a huge table is still in inefficient, even if that table is stored completely in memory (buffer pools).

Find the SQL statements that are using the most CPU. Look at the explain plans, and figure out how to make them more efficient. There are LOTS of resources on the web for database performance tuning.

Ian Bjorhovde
  • 10,916
  • 1
  • 28
  • 25