Sql-server – Seeking Suggestion for faster DB Insertion Policy

Architecturebulk-insertsql server

I have written a server which keeps on receiving concurrent requests from 30K-40K different clients concurrently. Now I need to dump all those individual requests into a database. The database right now is SQL Server.

My server has a thread pool for handling 30k-40K clients and I literally can't call some blocking calls in those threads to directly insert into SQL Server. This will literally beat the design of my server.

What efficient design should I choose for inserting into the database?

Does anyone recommend a MQ (Message Queues) design? Like from server threadpool, threads directly write into MQ and the consumer will take care of dumping what ever exists in MQ into SQL database. But again if there is some fishy happens in SQL like latency in response, then the entire Queue will be kept on piling up on and on.

This is an undergrad final year project. I am sorry but I am not a DB expert. I will be using some community edition of SQL Server for Proof of Concept, and there will be one huge table close to 14-15 columns where activity of each client will be recorded.

Even for batch upload, I need to buffer data from each client to somewhere. Right now my concern is about deciding the strategy From Server to some layer (let's it call x) that will pool the data. I am not going to directly call INSERT From the Server receive thread, for obvious reasons. There will be a separate process/service that I call consumer that will take care of doing Bulk Insert in DB. I seek experts to get me the view about data polling techniques, like RabbitMQ etc. I have coded the Server in C/C++.

There is no FIFO requirement, but indeed whoever sends the data first and that data needs to get saved into DB soon. There are 30K-40K clients and at any second at least there are close to 60% of the clients sending data to Server.

Best Answer

There is no particular policy to faster insertion (loading), which can direct you to your target. Instead, there are some configuration and prerequisites which can help you to improve write (insert) performance.

  1. The performance of any application, installed on OS lies on the hardware of the server. So, first, you should identify how much IOps disk you required, to collect that data you can use SQLIOSim.

    If you'll use SAN, then you should choose HDD with low space and high IOps (You can collect details from Vendor about complete hardware). More numbers of disks result in more IOps and better write performance.

  2. Because application writes data to disk, disk performance plays a great role when it comes to OLTP application. So, proper disk configuration is required.

    Space on disk (HDD) are divided into 512 bytes sector and 4KB (default) cluster size contains 8 sectors. But the size of SQL Server data pages is 8KB. So, formatting cluster size into 8KB for .mdf and 64KB for .ldf will boost write performance.

    You can visit this link for in-depth detail: Disk Partition Alignment Best Practices for SQL Server

  3. Place Transaction Log file (.ldf) on RAID10 or RAID1, tempdb on RAID0 (because restarting SQL Server recreates it) and data file (.mdf) according to your security procedure (on RAID5, 1 or 10). All the data files should be within a separate partition.

  4. Change Recovery Model of the database into BULK_LOGGED.

  5. For OLTP application like yours, it's recommended to create a normal table (according to requirement) with minimum indexes. So, I would suggest creating a simple table with a clustered index on varchar datatype column.

  6. Use BULK INSERT instead of BCP. Follow this link for a detailed guide about data loading: The Data Loading Performance Guide

    If it's normal INSERT through an application, then use ROWLOCK hint with the insert statement. Divide 40K clients into four parts and give them an Id. 10K = 1, 10K=2, 10K=3, 10K=4 (in a table). Now create 4 tables with the same schema (which will use to INSERT records). In Stored Procedure, on the basis of Id of User direct the transaction to respective table. This will reduce locking/blocking on the table.

  7. Make sure you have a healthy network connection between application and database server. There is no DMZ and firewall in between. Routing request through multiple devices will increase total execution time.

Related Question