STEEMSQL - A public SQL database with all blockchain data – Update 5

in steemsql •  3 years ago  (edited)

SteemSQL is a public MS-SQL database with all the blockchain data in it.

Previous posts:

What’s new?

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.

Performance matter

When you have to deal with such an amount of data, the main bottleneck are:

  1. 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.

  1. 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.

Support me and my work as a witness by voting for me here!

You like this post, do not forget to upvote or follow me or resteem

Authors get paid when people like you upvote their post.
If you enjoyed what you read here, create your account today and start earning FREE STEEM!
Sort Order:  

Thank you very much for providing this service, upvoted!

Thanks =)