Databases
StackOverflow2013
Postsdbo
POArchitecture question for performance and scalability
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POArchitecture question for performance and scalability
    primarykey
    data
    text
    <p>I have a high performance system (well I think, but not yet there 100% ) written entirely in C# and I think I have made some big architectural mistakes while designing. The reason is that it is not easily scalable.</p> <p>Though it currently works pretty well, I want to make sure it is horizontally scalable for the increase of volumes which I expect might happen after a few months.</p> <p>This system has a high number of concurrent connections of data coming into the system which will get into the database eventually after processing. We currently get about 300 records/connections per minute.</p> <p>The system is architected like this.</p> <ol> <li>The whole system is hosted on a win 2003 server 8 GB RAM/4 vCPU infra in amazon</li> <li>C# Socket server which gets the data and puts into a MSMQ</li> <li>A processor for data and insert to sql server 2008 database table. One of the main table has about 3 GB data even after clearing of periodical data. This has the right indexes and currently reports are reasonably faster enough even to a remote location.</li> <li>This processed data is then posted to MQ which is then processed for rules which will generate certain alerts</li> <li>There are some other associated programs apart from the above</li> </ol> <p>Now the main worry is about scalability of the processor in Step (3) and scalability of Sql server 2008. As the size of concurrent connections increase along with the sql server data, it will make my life tougher.</p> <p>I have come up with 2 alternatives. One of them is a major replacement of backend processors considering the fact that the current system in entirely built on Microsoft technologies.</p> <p>For all the options, for the main largest table use a postgresql/pgpool III load balanced(stream replicated) solution for storage. The other tables &amp; schema will still remain in sql 2008. This gives me a cost effective solution of database storage.</p> <p>Option 1: - Replace MSMQ with JBOSS &amp; HornetQ - Put the data processor in step 3 to a container managed "message driven bean" in JBOSS ejb container which will give me options for load balancing and clustering.<br> - This option will need me to move major part of my solution to unix/linux (am considering fedora)</p> <p>Option 2: - Replace MSMQ with Queues of ActiveMQ (Clustered and load balanced) - Write a Java app which will handle the queue messages and take care of database persistence.<br> This option will allow me to increase the number of linux servers with the activemq cluster instance and a new instance of the java app.</p> <p>Option 3: - Replace MSMQ with Queues of ActiveMQ (Clustered and load balanced) - Use just the current data processor (with some small changes to push data to postgresql) This option will force me to remain with Windows </p> <p>Please note that the system is a real-time system. It is enough if the system is 99% fault proof. This is not a trading system, so I can afford for a little amount of data loss. </p> <p>Don't know if I have explained what I want clearly. But I welcome any questions, as they will definitely help me explain it much better.</p> <p>Please give your valuable suggestions in making the right choice for a long term solution. I am actually against option 3 myself, but don't want to make a mistake again by leaving it out of the list. </p> <p>Muthu</p> <p>Added for clarification:</p> <p>Apologies for not being clear. 1. The question is actually about scalability of the architecture. Especially horizontal scalability. 2. The current average load is about 300 per minute and this may not exactly be spread inside a single minute. 3. The load might scale to 10 times more easily in the next 8-12 months. </p> <p>The problem is we were selling about 50 devices in a month and now the sales team is ramping up too fast. I believe this might double soon.</p> <p>Sql server had about 8 GB of data and we restricted the amount of storage per device and this has helped in reducing the size. Currently the biggest table is partitioned as 1 partition per 200 device and the queries are reasonable. But I can see a bottleneck on the Sql side with the scalability.</p> <p>So even if the Sql server is put on another server, there is going to be a limit to the amount of simultaneous updates I can do on sql server. I can't see a horizontal scalability option with load balancing for Sql server (though it supports a High availability option with clustering). did I mis-understand MS Sql in load balancing?</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USMuthu
    1. USMuthu
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POArchitecture question for performance and scalability
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POArchitecture question for performance and scalability
      1. This table or related slice is empty.
      1. VTUpMod
    1. CO"Now the main worry is about scalability of the processor in Step (3) and scalability of Sql server 2008." - 3GB is not much data. SQL Server 2008 will no doubt scale better than anything you or I could write!
      singulars
      1. POArchitecture question for performance and scalability
      1. USMitch Wheat
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload