#steemsql

STEEMSQL - Update 9 - Performances improvement

SteemSQL had to be redesigned to manage the awesome growth of Steemit.

What’s new?

In my last witness report, I expressed my concern about SteemSQL's ability to ingest the growing mass of data that is generated every day.

Indeed, if you look at the following chart taken from my last daily stats, you will find that the number of transactions to be processed has tripled (+199%) on the last 30 days.

For posts and comments, this represents respectively an increase of 315% and 297% And the number of upvotes has more than doubled with an increase of 112%

Gradually, the performances of data injection in the database have deteriorated. SteemSQL ended up struggling with the numerous updates all transactions generated. At some times, I found it with being late for more than 24 hours compared to the data available in the block chain (that's why I was unable to publish my daily stats for 2 days).

So, I decided to take the bull by the horns and performed a thorough analysis that led to several changes to the database and a complete redesign of the Database Injector.

1. Changes to the database.

These mainly concern the indexes of the database. I found that many indexes had been created to improve performances of some queries, but that finally these indexes were rarely used.

The indexes of the database have therefore been analyzed one by one, some have been deleted, others have been modified.

2. Changes to the Database Injector

The Database Injector is the process responsible for reading each block of the blockchain, and distributing the transactions in the different tables of the database.

I worked on the optimization of these processes by separating the injection of the transactions from the update of the impacted non-transactional tables (Accounts and Comments tables)

These processes now run in parallel thanks to an optimized multithreaded implementation of the different tasks.

Result

Wow, thanks to this work, I have been able to reduce the overall processing time and got it divided by 3 !!

And I am persuaded that it is still possible to further improve it because I worked a bit in emergency mode in order to restore the database to a functional state as quickly as possible. I had to take into account that more and more people rely on SteemSQL for their process Or analysis.

Things are done and SteemSQL is now ready to face without any problem a doubling of the number of transactions. This is something I really wish for Steemit!

Oh ... and for those who missed this announcement ... in the middle of my optimization work, I found a bit of time to launch the "Happy Birthday" action on SteemitBoard ... that was just to think to something different while I was doing small pauses ... :P

Thanks for reading !

All payout from this post will be dedicated keep SteemSQL running. Thanks for your support.

######

footer created with steemitboard - click any award to see my board of honor

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

Comments · 17

@financialcritic(58)· 3337d
Love this! Upvoted, followed, and you have my witness vote.
@lukmarcus(60)· 3342d
Great news!
@tohamy7(61)· 3342d
Read the topic aleni I like you I hope to improve the situation of the site to the urethra+follow
@teamsteem(74)· 3342d
Nice work my friend!
@pnc(65)· 3343d
Thks for your work @arcange. I've been your chart for my Steem presentation during meetup. Upvoted! Let's Steem On.
@socialrandoms(46)· 3343d
You have very interesting blog posts and upvoted a few i have read. Followed you, hoping you follow back check out my posts 👍☺
@digimad(50)· 3343d
Cheers Arcange, very useful!

A suggestion: you could change the login password to 'withnolock' to remind people :P
@justoneopinion(41)· 3343d
Wonderful, but I am a little concerned for my test Investment of 100 bucks SP that eventually dropped to 85 bucks total account value. So, where is the advantage to existing member account values from the folks you are showing were added? That's a 15% haircut for me. In two months :((
@brianjuice(50)· 3343d
Nice 👍🏼! I was wondering what the load time was on transactional data. Glad to hear SteemSQL is continuously improving.

Question: Are edits to posts triggering an update to the existing records?

Also, do you know the approximate load time for transaction made? i.e how frequent are loads?
@mrsullivan(41)· 3343d
So is this SQL database decentralized? Or is in centralized and controlled?
@ebohio2008(55)· 3343d
Good work. Keep it up
@shaunmza(64)· 3343d
Awesome job, thanks again for making this service freely available for all of us to use!
@vitkolesnik(63)· 3343d
The transaction chart looks like an exponential growth is happening.
@arckrai(63)· 3343d
Great to see how much posts and comments have increased I've voted for you as witness. Thank you for your great work!
@swtcamito(52)· 3343d
good post
@drakos(70)· 3343d
SteemSQL is a new proprietary database? Is it better than the well proven MySQL or MariaDB?
@artedellavita(70)· 3343d
Good morning 🌞

And once again, great info!