SteemSQL is a public MS-SQL database with all the blockchain data in it.
- Introduction post
- SteemSQL - Update 1
- SteemSQL - Update 2 - Language detection
- SteemSQL - Update 3
- SteemSQL - Update 4
Two days ago, I announced a maintenance of my servers infrastructure.
Indeed, more and more people are using SteemSQL and rely on its availability and performance to provide services and information to all Steemit users
A never-ending growing database
One of the problem we face is that the amount of data managed by the server grows every day.
To give you an idea, here is a graph that shows the daily amount of data added to the blockchain:
You can see that each day, approximately 75000 KB of data are added to the blockchain.
As more active users are joining Steem, we can expect this amount to continue to increase even faster.
When you have to deal with such an amount of data, the main bottleneck are:
- The storage performances
The speed of the storage to deliver the data for processing is the key factor. The faster the server can read data, the faster it can use it to make computation.
As user on Voice are more and more active, they server has also to constantly write the new data generated. This can also affect the performances because when you write data, the server put a lock on its database, preventing others to read uncommitted data to avoid computation errors.
- The computation power
When you have to aggregate several millions of values to compute a sum or an average trend, you need a lot of computation power and preferably, you do not want to be interrupted by others while you are doing the math.
A new high-performance dedicated infrastructure
SteemSQL was previously hosted on an existing shared infrastructure. Even if it is a high-end server, it still uses some legacy technology like SAS hard drives and shared its environment with some others process (like my witness and seed nodes).
Given the success of SteemSQL and the growing and growing load on the server, I have decided to move it to a brand new high-performance fully dedicated infrastructure.
The new server hosting Steem SQL now has the following spec:
4 CPUs running at 3.5Ghz with latest Intel Xeon v5 technology
This is far enough to handle the most demanding computation requests. The actual average load on the CPU is now down to 6.7%
2x500GB RAID SSD drives with the latest NVMe technology
This is the very latest storage technology and it is BLAZING FAST !!!
To give you an idea, here a performance comparison chart:
The previous server was using SAS HDD with high-end cache (similar to the first red column)
The new server use NVMe RAM (the green column)
I used to wait a lot when doing some stats. I now get the results in a few seconds. I have to admit i was really impressed by its performance the first time I ran some heavy queries.
On top of this, the server has a guaranteed dedicated 500Mb/s internet bandwidth.
No limits ... no tea
This major infrastructure migration will allow me and my fellow Steem statisticians to provide you with more crazy statistics and fancy graphs.
The only drawback is that we all have no more time to take a break and drink a cup of tea.
That’s life ...
Thanks for reading!
All payout from this post will be dedicated keep SteemSQL running. Thanks for your support.
You like this post, do not forget to upvote or follow me or resteem