Storage node performance improvement ideas

Do you have ideas how to improve the storage node performance but you don’t know how to address them? No problem. Post them here. We can disscuss them and I will create Jira tickets for it.

Credits:

The main performance problem is the chosen dbchema. At the last time i looked in the db i saw that near 30% of the data are used only by the id of one satellite which is written over and over in one table.
The db structure need a bit of normalization, not to much or everything, because normalization could decrease performance, but in some points it will result in a smaller and faster db design.
@BlackDuck Why did you choose to use a sqlite db?
https://storjlabs.atlassian.net/browse/V3-2092

https://storjlabs.atlassian.net/browse/V3-2109

I created a ticket for used_serials as well: https://storjlabs.atlassian.net/browse/V3-2108

1 Like

My opinion would be to switch to MySQL, here are the reasons:

  1. Better performance, since it is multithread-compatible
  2. Less likely to get corrupt in a crash
    2a. You can make a cluster that is even less likely to get corrupt.
    2b. Depending on how the node operates (what it keeps in RAM while running), the node could possibly be made to run in a cluster for better availability and performance.
  3. Probably not very different from sqlite, may not require a lot of changes in the code.

The downsides:

  1. It may use more RAM
  2. It is more difficult to set up.

The setup difficulty can be mitigated by having scripts that would automatically set up the database as part of the install package.

Or at least have a setting to use MySQL for those who can set it up or already have a db server so that they could take advantage of the improved performance and reliability.

I’ve used MySQL since 2003. Could it be an option, but still use sqlite by default? For more advanced users like myself.

Use a satellite lookup table, saves data.

1 Like

For something like a RPi3 (or if internet connection is 5mbps) MySQL probably would not improve anything, but it would help improve performance and reliability for those who want it.

Some other software allows choice between sqlite, MySQL and sometimes Postgres, so I think it would not be that difficult to offer the option.

My opinion would be to switch to MySQL, here are the reasons:

  1. Better performance, since it is multithread-compatible
  2. Less likely to get corrupt in a crash
    2a. You can make a cluster that is even less likely to get corrupt.
    2b. Depending on how the node operates (what it keeps in RAM while running), the node could possibly be made to run in a cluster for better availability and performance.
  3. Probably not very different from sqlite, may not require a lot of changes in the code.

1.) That is not correct. For our usecase SQLite has the better performance.
2.) An SQLite database is highly resistant to corruption. You can force a curruption by using it wrong. We should fix that ofc.

I am not against MySQL. Long term we could make a comparision especially for a big storage node. At the moment our database layout is wrong and moving that to a different database will not help. It would only force us to write 2 database migrations instead of 1. So lets make sure our database layout is looking good and doesn’t need to be changed in the future. If we reached that point we can think about supporting other databases.

2 Likes

I would very much like to see a storage node that could operate in a cluster (load balancing among multiple instances with common database and storage).

I do not have a lot of experience with sqlite - for my stuff I use MySQL, even if the db is a single table with 100 rows. I somehow came to regard sqlite as the “I’m too lazy to install a proper DBMS” db that is prone to corruption. The number of SNOs with corrupted dbs only strengthened that belief :slight_smile: as I only had corrupt InnoDB tables as a result of bad sectors.

But yea, optimizing the db usage (say, not doing something similar to SELECT sum(x) FROM table on each upload would probably make the node faster :slight_smile: )

I’m sure you realize that operating this db in a cluster is a super fringe desire

5 hours downtime means the node has to survive any single failure unattended, since I may not be able to get to it (and fix whatever is broken) in time.

Also, if it was possible to run the node in a cluster, I could scale up the performance in case the CPU becomes a bottleneck again.

I did some analysis of my info.db with sqlite3_analyzer here is the result. (need to split this in multiple messages)

What can’t we take out of these informations.

  1. 70% of the Data is not needed for the operation.

The order_archive could be helpfull if there are problems with the payment to check if something wents wrong, but after a successfull payment there is no reason to keep them. I am also see no benefit to keep this in database, the archive should be written in a text log, the situation where it is needed are rare.

The certificates seams only to be uplink certificates. Afaik the libuplink generates a new cert for every upload, this explains why are some many entries in there. As long the uplink generate everytime a new one there is no reason to keep them, if they are only taken onetime or short period they should hold in ram

Same for bandwidth_usage historic data could be written in a text file each per month, with a result head, then it don’t be needed recalculate each time.

  1. The database could be more normalized

The satellite_id is written in multiple columns in its full 32byte length, many many many times, same with other ids. We have 4 known satellites, if the get its own table and only get refered with a one to four byte pointer much space could be saved.

  1. Why are there such many blob types?
    All id types stored as blob, but blobs are slower than varchar entries, of course it took time in storagenode to convert the bytes to varchar, but it you be faster to convert it and have a faster database.

  2. Delete old data

My Database is around 1GB in size, i started in April, there were 3 wipes after my start, but this database containing data over all the time.

This are many low hanging fruits, if this all get fixed i belive the database could shrink down to 10% or less in size, which would speed up many things. I see no sense to switch to MySQL or other database, this will speed up nothing.

3 Likes
/** Disk-Space Utilization Report For C:\Temp\Diag\info\info.db

Page size in bytes................................ 4096      
Pages in the whole file (measured)................ 259813    
Pages in the whole file (calculated).............. 259813    
Pages that store data............................. 243656      93.8% 
Pages on the freelist (per header)................ 16157        6.2% 
Pages on the freelist (calculated)................ 16157        6.2% 
Pages of auto-vacuum overhead..................... 0            0.0% 
Number of tables in the database.................. 10        
Number of indices................................. 12        
Number of defined indices......................... 10        
Number of implied indices......................... 2         
Size of the file in bytes......................... 1064194048
Bytes of user payload stored...................... 592892747   55.7% 

*** Page counts for all tables with their indices *****************************

ORDER_ARCHIVE..................................... 90269       34.7% 
CERTIFICATE....................................... 86628       33.3% 
BANDWIDTH_USAGE................................... 31726       12.2% 
PIECEINFO......................................... 19122        7.4% 
USED_SERIAL....................................... 15904        6.1% 
UNSENT_ORDER...................................... 2            0.0% 
VOUCHERS.......................................... 2            0.0% 
SQLITE_MASTER..................................... 1            0.0% 
SQLITE_SEQUENCE................................... 1            0.0% 
VERSIONS.......................................... 1            0.0% 

*** Page counts for all tables and indices separately *************************

ORDER_ARCHIVE..................................... 80779       31.1% 
SQLITE_AUTOINDEX_CERTIFICATE_1.................... 47326       18.2% 
CERTIFICATE....................................... 39302       15.1% 
BANDWIDTH_USAGE................................... 14563        5.6% 
PIECEINFO......................................... 13458        5.2% 
IDX_BANDWIDTH_USAGE_CREATED....................... 8830         3.4% 
IDX_BANDWIDTH_USAGE_SATELLITE..................... 8333         3.2% 
IDX_ORDER_ARCHIVE_SATELLITE....................... 7674         3.0% 
PK_USED_SERIAL.................................... 7146         2.8% 
USED_SERIAL....................................... 5724         2.2% 
PK_PIECEINFO...................................... 4664         1.8% 
IDX_USED_SERIAL................................... 3034         1.2% 
IDX_ORDER_ARCHIVE_STATUS.......................... 1816         0.70% 
IDX_PIECEINFO_DELETION_FAILED..................... 500          0.19% 
IDX_PIECEINFO_EXPIRATION.......................... 500          0.19% 
IDX_ORDERS........................................ 1            0.0% 
SQLITE_AUTOINDEX_VOUCHERS_1....................... 1            0.0% 
SQLITE_MASTER..................................... 1            0.0% 
SQLITE_SEQUENCE................................... 1            0.0% 
UNSENT_ORDER...................................... 1            0.0% 
VERSIONS.......................................... 1            0.0% 
VOUCHERS.......................................... 1            0.0% 

*** All tables and indices ****************************************************

Percentage of total database......................  93.8%    
Number of entries................................. 6408690   
Bytes of storage consumed......................... 998014976 
Bytes of payload.................................. 867004832   86.9% 
Bytes of metadata................................. 30566576     3.1% 
Average payload per entry......................... 135.29    
Average unused bytes per entry.................... 15.67     
Average metadata per entry........................ 4.77      
Average fanout.................................... 23.00     
Maximum payload per entry......................... 1098      
Entries that use overflow......................... 9            0.0% 
Index pages used.................................. 10231     
Primary pages used................................ 233416    
Overflow pages used............................... 9         
Total pages used.................................. 243656    
Unused bytes on index pages....................... 10329395    24.6% 
Unused bytes on primary pages..................... 90082526     9.4% 
Unused bytes on overflow pages.................... 31647       85.8% 
Unused bytes on all pages......................... 100443568   10.1% 

*** All tables ****************************************************************

Percentage of total database......................  59.2%    
Number of entries................................. 2125037   
Bytes of storage consumed......................... 630091776 
Bytes of payload.................................. 592896569   94.1% 
Bytes of metadata................................. 16441795     2.6% 
Average payload per entry......................... 279.01    
Average unused bytes per entry.................... 9.77      
Average metadata per entry........................ 7.74      
Average fanout.................................... 394.00    
Maximum payload per entry......................... 1098      
Entries that use overflow......................... 0            0.0% 
Index pages used.................................. 390       
Primary pages used................................ 153441    
Overflow pages used............................... 0         
Total pages used.................................. 153831    
Unused bytes on index pages....................... 218254      13.7% 
Unused bytes on primary pages..................... 20535158     3.3% 
Unused bytes on overflow pages.................... 0         
Unused bytes on all pages......................... 20753412     3.3% 

*** All indices ***************************************************************

Percentage of total database......................  34.6%    
Number of entries................................. 4283653   
Bytes of storage consumed......................... 367923200 
Bytes of payload.................................. 274108263   74.5% 
Bytes of metadata................................. 14124781     3.8% 
Average payload per entry......................... 63.99     
Average unused bytes per entry.................... 18.60     
Average metadata per entry........................ 3.30      
Average fanout.................................... 9.00      
Maximum payload per entry......................... 1066      
Entries that use overflow......................... 9            0.0% 
Index pages used.................................. 9841      
Primary pages used................................ 79975     
Overflow pages used............................... 9         
Total pages used.................................. 89825     
Unused bytes on index pages....................... 10111141    25.1% 
Unused bytes on primary pages..................... 69547368    21.2% 
Unused bytes on overflow pages.................... 31647       85.8% 
Unused bytes on all pages......................... 79690156    21.7% 

*** Table BANDWIDTH_USAGE and all its indices *********************************

Percentage of total database......................  12.2%    
Number of entries................................. 2174829   
Bytes of storage consumed......................... 129949696 
Bytes of payload.................................. 111722269   86.0% 
Bytes of metadata................................. 9135808      7.0% 
Average payload per entry......................... 51.37     
Average unused bytes per entry.................... 4.18      
Average metadata per entry........................ 4.20      
Average fanout.................................... 122.00    
Maximum payload per entry......................... 79        
Entries that use overflow......................... 0            0.0% 
Index pages used.................................. 260       
Primary pages used................................ 31466     
Overflow pages used............................... 0         
Total pages used.................................. 31726     
Unused bytes on index pages....................... 144772      13.6% 
Unused bytes on primary pages..................... 8946847      6.9% 
Unused bytes on overflow pages.................... 0         
Unused bytes on all pages......................... 9091619      7.0% 

*** Table BANDWIDTH_USAGE w/o any indices *************************************

Percentage of total database......................   5.6%    
Number of entries................................. 724943    
Bytes of storage consumed......................... 59650048  
Bytes of payload.................................. 54598496    91.5% 
Bytes of metadata................................. 4580202      7.7% 
B-tree depth...................................... 3         
Average payload per entry......................... 75.31     
Average unused bytes per entry.................... 0.65      
Average metadata per entry........................ 6.32      
Average fanout.................................... 393.00    
Non-sequential pages.............................. 14313       98.3% 
Maximum payload per entry......................... 79        
Entries that use overflow......................... 0            0.0% 
Index pages used.................................. 37        
Primary pages used................................ 14526     
Overflow pages used............................... 0         
Total pages used.................................. 14563     
Unused bytes on index pages....................... 20706       13.7% 
Unused bytes on primary pages..................... 450644       0.76% 
Unused bytes on overflow pages.................... 0         
Unused bytes on all pages......................... 471350       0.79% 

*** Indices of table BANDWIDTH_USAGE ******************************************

Percentage of total database......................   6.6%    
Number of entries................................. 1449886   
Bytes of storage consumed......................... 70299648  
Bytes of payload.................................. 57123773    81.3% 
Bytes of metadata................................. 4555606      6.5% 
Average payload per entry......................... 39.40     
Average unused bytes per entry.................... 5.95      
Average metadata per entry........................ 3.14      
Average fanout.................................... 76.00     
Maximum payload per entry......................... 41        
Entries that use overflow......................... 0            0.0% 
Index pages used.................................. 223       
Primary pages used................................ 16940     
Overflow pages used............................... 0         
Total pages used.................................. 17163     
Unused bytes on index pages....................... 124066      13.6% 
Unused bytes on primary pages..................... 8496203     12.2% 
Unused bytes on overflow pages.................... 0         
Unused bytes on all pages......................... 8620269     12.3% 

*** Index IDX_BANDWIDTH_USAGE_CREATED of table BANDWIDTH_USAGE ****************

Percentage of total database......................   3.4%    
Number of entries................................. 724943    
Bytes of storage consumed......................... 36167680  
Bytes of payload.................................. 29608834    81.9% 
Bytes of metadata................................. 2280785      6.3% 
B-tree depth...................................... 4         
Average payload per entry......................... 40.84     
Average unused bytes per entry.................... 5.90      
Average metadata per entry........................ 3.15      
Average fanout.................................... 75.00     
Non-sequential pages.............................. 8621        97.6% 
Maximum payload per entry......................... 41        
Entries that use overflow......................... 0            0.0% 
Index pages used.................................. 117       
Primary pages used................................ 8713      
Overflow pages used............................... 0         
Total pages used.................................. 8830      
Unused bytes on index pages....................... 60971       12.7% 
Unused bytes on primary pages..................... 4217090     11.8% 
Unused bytes on overflow pages.................... 0         
Unused bytes on all pages......................... 4278061     11.8% 

*** Index IDX_BANDWIDTH_USAGE_SATELLITE of table BANDWIDTH_USAGE **************

Percentage of total database......................   3.2%    
Number of entries................................. 724943    
Bytes of storage consumed......................... 34131968  
Bytes of payload.................................. 27514939    80.6% 
Bytes of metadata................................. 2274821      6.7% 
B-tree depth...................................... 4         
Average payload per entry......................... 37.95     
Average unused bytes per entry.................... 5.99      
Average metadata per entry........................ 3.14      
Average fanout.................................... 78.00     
Non-sequential pages.............................. 8112        97.4% 
Maximum payload per entry......................... 38        
Entries that use overflow......................... 0            0.0% 
Index pages used.................................. 106       
Primary pages used................................ 8227      
Overflow pages used............................... 0         
Total pages used.................................. 8333      
Unused bytes on index pages....................... 63095       14.5% 
Unused bytes on primary pages..................... 4279113     12.7% 
Unused bytes on overflow pages.................... 0         
Unused bytes on all pages......................... 4342208     12.7% 

*** Table CERTIFICATE and all its indices *************************************

Percentage of total database......................  33.3%    
Number of entries................................. 392012    
Bytes of storage consumed......................... 354828288 
Bytes of payload.................................. 290449423   81.9% 
Bytes of metadata................................. 3371755      0.95% 
Average payload per entry......................... 740.92    
Average unused bytes per entry.................... 155.63    
Average metadata per entry........................ 8.60      
Average fanout.................................... 9.00      
Maximum payload per entry......................... 1098      
Entries that use overflow......................... 9            0.002% 
Index pages used.................................. 9276      
Primary pages used................................ 77343     
Overflow pages used............................... 9         
Total pages used.................................. 86628     
Unused bytes on index pages....................... 9547445     25.1% 
Unused bytes on primary pages..................... 51428018    16.2% 
Unused bytes on overflow pages.................... 31647       85.8% 
Unused bytes on all pages......................... 61007110    17.2% 

*** Table CERTIFICATE w/o any indices *****************************************

Percentage of total database......................  15.1%    
Number of entries................................. 196006    
Bytes of storage consumed......................... 160980992 
Bytes of payload.................................. 148181249   92.0% 
Bytes of metadata................................. 2019859      1.3% 
B-tree depth...................................... 3         
Average payload per entry......................... 756.00    
Average unused bytes per entry.................... 55.00     
Average metadata per entry........................ 10.31     
Average fanout.................................... 396.00    
Non-sequential pages.............................. 37963       96.6% 
Maximum payload per entry......................... 1098      
Entries that use overflow......................... 0            0.0% 
Index pages used.................................. 99        
Primary pages used................................ 39203     
Overflow pages used............................... 0         
Total pages used.................................. 39302     
Unused bytes on index pages....................... 54801       13.5% 
Unused bytes on primary pages..................... 10725083     6.7% 
Unused bytes on overflow pages.................... 0         
Unused bytes on all pages......................... 10779884     6.7% 

*** Index SQLITE_AUTOINDEX_CERTIFICATE_1 of table CERTIFICATE *****************

Percentage of total database......................  18.2%    
Number of entries................................. 196006    
Bytes of storage consumed......................... 193847296 
Bytes of payload.................................. 142268174   73.4% 
Bytes of metadata................................. 1351896      0.70% 
B-tree depth...................................... 8         
Average payload per entry......................... 725.84    
Average unused bytes per entry.................... 256.25    
Average metadata per entry........................ 6.90      
Average fanout.................................... 5.00      
Non-sequential pages.............................. 33062       69.9% 
Maximum payload per entry......................... 1066      
Entries that use overflow......................... 9            0.005% 
Index pages used.................................. 9177      
Primary pages used................................ 38140     
Overflow pages used............................... 9         
Total pages used.................................. 47326     
Unused bytes on index pages....................... 9492644     25.3% 
Unused bytes on primary pages..................... 40702935    26.1% 
Unused bytes on overflow pages.................... 31647       85.8% 
Unused bytes on all pages......................... 50227226    25.9% 

*** Table ORDER_ARCHIVE and all its indices ***********************************

Percentage of total database......................  34.7%    
Number of entries................................. 2174829   
Bytes of storage consumed......................... 369741824 
Bytes of payload.................................. 349780630   94.6% 
Bytes of metadata................................. 10892006     2.9% 
Average payload per entry......................... 160.83    
Average unused bytes per entry.................... 4.17      
Average metadata per entry........................ 5.01      
Average fanout.................................... 292.00    
Maximum payload per entry......................... 477       
Entries that use overflow......................... 0            0.0% 
Index pages used.................................. 309       
Primary pages used................................ 89960     
Overflow pages used............................... 0         
Total pages used.................................. 90269     
Unused bytes on index pages....................... 174644      13.8% 
Unused bytes on primary pages..................... 8894544      2.4% 
Unused bytes on overflow pages.................... 0         
Unused bytes on all pages......................... 9069188      2.5% 

*** Table ORDER_ARCHIVE w/o any indices ***************************************

Percentage of total database......................  31.1%    
Number of entries................................. 724943    
Bytes of storage consumed......................... 330870784 
Bytes of payload.................................. 317948592   96.1% 
Bytes of metadata................................. 6428476      1.9% 
B-tree depth...................................... 3         
Average payload per entry......................... 438.58    
Average unused bytes per entry.................... 8.96      
Average metadata per entry........................ 8.87      
Average fanout.................................... 397.00    
Non-sequential pages.............................. 60564       75.0% 
Maximum payload per entry......................... 477       
Entries that use overflow......................... 0            0.0% 
Index pages used.................................. 203       
Primary pages used................................ 80576     
Overflow pages used............................... 0         
Total pages used.................................. 80779     
Unused bytes on index pages....................... 105711      12.7% 
Unused bytes on primary pages..................... 6388005      1.9% 
Unused bytes on overflow pages.................... 0         
Unused bytes on all pages......................... 6493716      2.0% 

*** Indices of table ORDER_ARCHIVE ********************************************

Percentage of total database......................   3.7%    
Number of entries................................. 1449886   
Bytes of storage consumed......................... 38871040  
Bytes of payload.................................. 31832038    81.9% 
Bytes of metadata................................. 4463530     11.5% 
Average payload per entry......................... 21.95     
Average unused bytes per entry.................... 1.78      
Average metadata per entry........................ 3.08      
Average fanout.................................... 89.00     
Maximum payload per entry......................... 38        
Entries that use overflow......................... 0            0.0% 
Index pages used.................................. 106       
Primary pages used................................ 9384      
Overflow pages used............................... 0         
Total pages used.................................. 9490      
Unused bytes on index pages....................... 68933       15.9% 
Unused bytes on primary pages..................... 2506539      6.5% 
Unused bytes on overflow pages.................... 0         
Unused bytes on all pages......................... 2575472      6.6% 

*** Index IDX_ORDER_ARCHIVE_SATELLITE of table ORDER_ARCHIVE ******************

Percentage of total database......................   3.0%    
Number of entries................................. 724943    
Bytes of storage consumed......................... 31432704  
Bytes of payload.................................. 27514939    87.5% 
Bytes of metadata................................. 2266913      7.2% 
B-tree depth...................................... 4         
Average payload per entry......................... 37.95     
Average unused bytes per entry.................... 2.28      
Average metadata per entry........................ 3.13      
Average fanout.................................... 78.00     
Non-sequential pages.............................. 7215        94.0% 
Maximum payload per entry......................... 38        
Entries that use overflow......................... 0            0.0% 
Index pages used.................................. 98        
Primary pages used................................ 7576      
Overflow pages used............................... 0         
Total pages used.................................. 7674      
Unused bytes on index pages....................... 59681       14.9% 
Unused bytes on primary pages..................... 1591171      5.1% 
Unused bytes on overflow pages.................... 0         
Unused bytes on all pages......................... 1650852      5.3% 

*** Index IDX_ORDER_ARCHIVE_STATUS of table ORDER_ARCHIVE *********************

Percentage of total database......................   0.70%   
Number of entries................................. 724943    
Bytes of storage consumed......................... 7438336   
Bytes of payload.................................. 4317099     58.0% 
Bytes of metadata................................. 2196617     29.5% 
B-tree depth...................................... 3         
Average payload per entry......................... 5.96      
Average unused bytes per entry.................... 1.28      
Average metadata per entry........................ 3.03      
Average fanout.................................... 227.00    
Non-sequential pages.............................. 1783        98.2% 
Maximum payload per entry......................... 7         
Entries that use overflow......................... 0            0.0% 
Index pages used.................................. 8         
Primary pages used................................ 1808      
Overflow pages used............................... 0         
Total pages used.................................. 1816      
Unused bytes on index pages....................... 9252        28.2% 
Unused bytes on primary pages..................... 915368      12.4% 
Unused bytes on overflow pages.................... 0         
Unused bytes on all pages......................... 924620      12.4% 

*** Table PIECEINFO and all its indices ***************************************

Percentage of total database......................   7.4%    
Number of entries................................. 918576    
Bytes of storage consumed......................... 78323712  
Bytes of payload.................................. 69450367    88.7% 
Bytes of metadata................................. 3953694      5.0% 
Average payload per entry......................... 75.61     
Average unused bytes per entry.................... 5.36      
Average metadata per entry........................ 4.30      
Average fanout.................................... 134.00    
Maximum payload per entry......................... 221       
Entries that use overflow......................... 0            0.0% 
Index pages used.................................. 142       
Primary pages used................................ 18980     
Overflow pages used............................... 0         
Total pages used.................................. 19122     
Unused bytes on index pages....................... 92104       15.8% 
Unused bytes on primary pages..................... 4827547      6.2% 
Unused bytes on overflow pages.................... 0         
Unused bytes on all pages......................... 4919651      6.3% 

*** Table PIECEINFO w/o any indices *******************************************

Percentage of total database......................   5.2%    
Number of entries................................. 229644    
Bytes of storage consumed......................... 55123968  
Bytes of payload.................................. 50487397    91.6% 
Bytes of metadata................................. 1818942      3.3% 
B-tree depth...................................... 3         
Average payload per entry......................... 219.85    
Average unused bytes per entry.................... 12.27     
Average metadata per entry........................ 7.92      
Average fanout.................................... 384.00    
Non-sequential pages.............................. 13360       99.28% 
Maximum payload per entry......................... 221       
Entries that use overflow......................... 0            0.0% 
Index pages used.................................. 35        
Primary pages used................................ 13423     
Overflow pages used............................... 0         
Total pages used.................................. 13458     
Unused bytes on index pages....................... 23055       16.1% 
Unused bytes on primary pages..................... 2794574      5.1% 
Unused bytes on overflow pages.................... 0         
Unused bytes on all pages......................... 2817629      5.1% 

*** Indices of table PIECEINFO ************************************************

Percentage of total database......................   2.2%    
Number of entries................................. 688932    
Bytes of storage consumed......................... 23199744  
Bytes of payload.................................. 18962970    81.7% 
Bytes of metadata................................. 2134752      9.2% 
Average payload per entry......................... 27.53     
Average unused bytes per entry.................... 3.05      
Average metadata per entry........................ 3.10      
Average fanout.................................... 52.00     
Maximum payload per entry......................... 71        
Entries that use overflow......................... 0            0.0% 
Index pages used.................................. 107       
Primary pages used................................ 5557      
Overflow pages used............................... 0         
Total pages used.................................. 5664      
Unused bytes on index pages....................... 69049       15.8% 
Unused bytes on primary pages..................... 2032973      8.9% 
Unused bytes on overflow pages.................... 0         
Unused bytes on all pages......................... 2102022      9.1%
2 Likes
*** Index IDX_PIECEINFO_DELETION_FAILED of table PIECEINFO ********************

Percentage of total database......................   0.19%   
Number of entries................................. 229644    
Bytes of storage consumed......................... 2048000   
Bytes of payload.................................. 1345370     65.7% 
Bytes of metadata................................. 694928      33.9% 
B-tree depth...................................... 3         
Average payload per entry......................... 5.86      
Average unused bytes per entry.................... 0.03      
Average metadata per entry........................ 3.03      
Average fanout.................................... 166.00    
Non-sequential pages.............................. 466         93.4% 
Maximum payload per entry......................... 6         
Entries that use overflow......................... 0            0.0% 
Index pages used.................................. 3         
Primary pages used................................ 497       
Overflow pages used............................... 0         
Total pages used.................................. 500       
Unused bytes on index pages....................... 5867        47.7% 
Unused bytes on primary pages..................... 1835         0.090% 
Unused bytes on overflow pages.................... 0         
Unused bytes on all pages......................... 7702         0.38% 

*** Index IDX_PIECEINFO_EXPIRATION of table PIECEINFO *************************

Percentage of total database......................   0.19%   
Number of entries................................. 229644    
Bytes of storage consumed......................... 2048000   
Bytes of payload.................................. 1345370     65.7% 
Bytes of metadata................................. 694928      33.9% 
B-tree depth...................................... 3         
Average payload per entry......................... 5.86      
Average unused bytes per entry.................... 0.03      
Average metadata per entry........................ 3.03      
Average fanout.................................... 166.00    
Non-sequential pages.............................. 474         95.0% 
Maximum payload per entry......................... 6         
Entries that use overflow......................... 0            0.0% 
Index pages used.................................. 3         
Primary pages used................................ 497       
Overflow pages used............................... 0         
Total pages used.................................. 500       
Unused bytes on index pages....................... 5867        47.7% 
Unused bytes on primary pages..................... 1835         0.090% 
Unused bytes on overflow pages.................... 0         
Unused bytes on all pages......................... 7702         0.38% 

*** Index PK_PIECEINFO of table PIECEINFO *************************************

Percentage of total database......................   1.8%    
Number of entries................................. 229644    
Bytes of storage consumed......................... 19103744  
Bytes of payload.................................. 16272230    85.2% 
Bytes of metadata................................. 744896       3.9% 
B-tree depth...................................... 4         
Average payload per entry......................... 70.86     
Average unused bytes per entry.................... 9.09      
Average metadata per entry........................ 3.24      
Average fanout.................................... 46.00     
Non-sequential pages.............................. 4447        95.4% 
Maximum payload per entry......................... 71        
Entries that use overflow......................... 0            0.0% 
Index pages used.................................. 101       
Primary pages used................................ 4563      
Overflow pages used............................... 0         
Total pages used.................................. 4664      
Unused bytes on index pages....................... 57315       13.9% 
Unused bytes on primary pages..................... 2029303     10.9% 
Unused bytes on overflow pages.................... 0         
Unused bytes on all pages......................... 2086618     10.9% 

*** Table SQLITE_MASTER *******************************************************

Percentage of total database......................   0.0%    
Number of entries................................. 21        
Bytes of storage consumed......................... 4096      
Bytes of payload.................................. 3822        93.3% 
Bytes of metadata................................. 202          4.9% 
B-tree depth...................................... 1         
Average payload per entry......................... 182.00    
Average unused bytes per entry.................... 3.43      
Average metadata per entry........................ 9.62      
Maximum payload per entry......................... 549       
Entries that use overflow......................... 0            0.0% 
Primary pages used................................ 1         
Overflow pages used............................... 0         
Total pages used.................................. 1         
Unused bytes on primary pages..................... 72           1.8% 
Unused bytes on overflow pages.................... 0         
Unused bytes on all pages......................... 72           1.8% 

*** Table SQLITE_SEQUENCE *****************************************************

Percentage of total database......................   0.0%    
Number of entries................................. 1         
Bytes of storage consumed......................... 4096      
Bytes of payload.................................. 17           0.42% 
Bytes of metadata................................. 12           0.29% 
B-tree depth...................................... 1         
Average payload per entry......................... 17.00     
Average unused bytes per entry.................... 4067.00   
Average metadata per entry........................ 12.00     
Maximum payload per entry......................... 17        
Entries that use overflow......................... 0            0.0% 
Primary pages used................................ 1         
Overflow pages used............................... 0         
Total pages used.................................. 1         
Unused bytes on primary pages..................... 4067        99.29% 
Unused bytes on overflow pages.................... 0         
Unused bytes on all pages......................... 4067        99.29% 

*** Table UNSENT_ORDER and all its indices ************************************

Percentage of total database......................   0.0%    
Number of entries................................. 0         
Bytes of storage consumed......................... 8192      
Bytes of payload.................................. 0            0.0% 
Bytes of metadata................................. 16           0.20% 
Average payload per entry......................... 0.0       
Average unused bytes per entry.................... 0.0       
Average metadata per entry........................ 0.0       
Maximum payload per entry......................... 0         
Entries that use overflow......................... 0         
Primary pages used................................ 2         
Overflow pages used............................... 0         
Total pages used.................................. 2         
Unused bytes on primary pages..................... 8176        99.80% 
Unused bytes on overflow pages.................... 0         
Unused bytes on all pages......................... 8176        99.80% 

*** Table UNSENT_ORDER w/o any indices ****************************************

Percentage of total database......................   0.0%    
Number of entries................................. 0         
Bytes of storage consumed......................... 4096      
Bytes of payload.................................. 0            0.0% 
Bytes of metadata................................. 8            0.20% 
B-tree depth...................................... 1         
Average payload per entry......................... 0.0       
Average unused bytes per entry.................... 0.0       
Average metadata per entry........................ 0.0       
Maximum payload per entry......................... 0         
Entries that use overflow......................... 0         
Primary pages used................................ 1         
Overflow pages used............................... 0         
Total pages used.................................. 1         
Unused bytes on primary pages..................... 4088        99.80% 
Unused bytes on overflow pages.................... 0         
Unused bytes on all pages......................... 4088        99.80% 

*** Index IDX_ORDERS of table UNSENT_ORDER ************************************

Percentage of total database......................   0.0%    
Number of entries................................. 0         
Bytes of storage consumed......................... 4096      
Bytes of payload.................................. 0            0.0% 
Bytes of metadata................................. 8            0.20% 
B-tree depth...................................... 1         
Average payload per entry......................... 0.0       
Average unused bytes per entry.................... 0.0       
Average metadata per entry........................ 0.0       
Maximum payload per entry......................... 0         
Entries that use overflow......................... 0         
Primary pages used................................ 1         
Overflow pages used............................... 0         
Total pages used.................................. 1         
Unused bytes on primary pages..................... 4088        99.80% 
Unused bytes on overflow pages.................... 0         
Unused bytes on all pages......................... 4088        99.80% 

*** Table USED_SERIAL and all its indices *************************************

Percentage of total database......................   6.1%    
Number of entries................................. 748410    
Bytes of storage consumed......................... 65142784  
Bytes of payload.................................. 45597172    70.0% 
Bytes of metadata................................. 3213011      4.9% 
Average payload per entry......................... 60.93     
Average unused bytes per entry.................... 21.82     
Average metadata per entry........................ 4.29      
Average fanout.................................... 65.00     
Maximum payload per entry......................... 87        
Entries that use overflow......................... 0            0.0% 
Index pages used.................................. 244       
Primary pages used................................ 15660     
Overflow pages used............................... 0         
Total pages used.................................. 15904     
Unused bytes on index pages....................... 370430      37.1% 
Unused bytes on primary pages..................... 15962171    24.9% 
Unused bytes on overflow pages.................... 0         
Unused bytes on all pages......................... 16332601    25.1% 

*** Table USED_SERIAL w/o any indices *****************************************

Percentage of total database......................   2.2%    
Number of entries................................. 249470    
Bytes of storage consumed......................... 23445504  
Bytes of payload.................................. 21675971    92.5% 
Bytes of metadata................................. 1594039      6.8% 
B-tree depth...................................... 3         
Average payload per entry......................... 86.89     
Average unused bytes per entry.................... 0.70      
Average metadata per entry........................ 6.39      
Average fanout.................................... 357.00    
Non-sequential pages.............................. 5684        99.32% 
Maximum payload per entry......................... 87        
Entries that use overflow......................... 0            0.0% 
Index pages used.................................. 16        
Primary pages used................................ 5708      
Overflow pages used............................... 0         
Total pages used.................................. 5724      
Unused bytes on index pages....................... 13981       21.3% 
Unused bytes on primary pages..................... 161513       0.69% 
Unused bytes on overflow pages.................... 0         
Unused bytes on all pages......................... 175494       0.75% 

*** Indices of table USED_SERIAL **********************************************

Percentage of total database......................   3.9%    
Number of entries................................. 498940    
Bytes of storage consumed......................... 41697280  
Bytes of payload.................................. 23921201    57.4% 
Bytes of metadata................................. 1618972      3.9% 
Average payload per entry......................... 47.94     
Average unused bytes per entry.................... 32.38     
Average metadata per entry........................ 3.24      
Average fanout.................................... 44.00     
Maximum payload per entry......................... 55        
Entries that use overflow......................... 0            0.0% 
Index pages used.................................. 228       
Primary pages used................................ 9952      
Overflow pages used............................... 0         
Total pages used.................................. 10180     
Unused bytes on index pages....................... 356449      38.2% 
Unused bytes on primary pages..................... 15800658    38.8% 
Unused bytes on overflow pages.................... 0         
Unused bytes on all pages......................... 16157107    38.7% 

*** Index IDX_USED_SERIAL of table USED_SERIAL ********************************

Percentage of total database......................   1.2%    
Number of entries................................. 249470    
Bytes of storage consumed......................... 12427264  
Bytes of payload.................................. 10200351    82.1% 
Bytes of metadata................................. 784814       6.3% 
B-tree depth...................................... 3         
Average payload per entry......................... 40.89     
Average unused bytes per entry.................... 5.78      
Average metadata per entry........................ 3.15      
Average fanout.................................... 74.00     
Non-sequential pages.............................. 2982        98.3% 
Maximum payload per entry......................... 41        
Entries that use overflow......................... 0            0.0% 
Index pages used.................................. 41        
Primary pages used................................ 2993      
Overflow pages used............................... 0         
Total pages used.................................. 3034      
Unused bytes on index pages....................... 24137       14.4% 
Unused bytes on primary pages..................... 1417962     11.6% 
Unused bytes on overflow pages.................... 0         
Unused bytes on all pages......................... 1442099     11.6% 

*** Index PK_USED_SERIAL of table USED_SERIAL *********************************

Percentage of total database......................   2.8%    
Number of entries................................. 249470    
Bytes of storage consumed......................... 29270016  
Bytes of payload.................................. 13720850    46.9% 
Bytes of metadata................................. 834158       2.8% 
B-tree depth...................................... 4         
Average payload per entry......................... 55.00     
Average unused bytes per entry.................... 58.99     
Average metadata per entry........................ 3.34      
Average fanout.................................... 38.00     
Non-sequential pages.............................. 6758        94.6% 
Maximum payload per entry......................... 55        
Entries that use overflow......................... 0            0.0% 
Index pages used.................................. 187       
Primary pages used................................ 6959      
Overflow pages used............................... 0         
Total pages used.................................. 7146      
Unused bytes on index pages....................... 332312      43.4% 
Unused bytes on primary pages..................... 14382696    50.5% 
Unused bytes on overflow pages.................... 0         
Unused bytes on all pages......................... 14715008    50.3% 

*** Table VERSIONS ************************************************************

Percentage of total database......................   0.0%    
Number of entries................................. 6         
Bytes of storage consumed......................... 4096      
Bytes of payload.................................. 345          8.4% 
Bytes of metadata................................. 32           0.78% 
B-tree depth...................................... 1         
Average payload per entry......................... 57.50     
Average unused bytes per entry.................... 619.83    
Average metadata per entry........................ 5.33      
Maximum payload per entry......................... 58        
Entries that use overflow......................... 0            0.0% 
Primary pages used................................ 1         
Overflow pages used............................... 0         
Total pages used.................................. 1         
Unused bytes on primary pages..................... 3719        90.8% 
Unused bytes on overflow pages.................... 0         
Unused bytes on all pages......................... 3719        90.8% 

*** Table VOUCHERS and all its indices ****************************************

Percentage of total database......................   0.0%    
Number of entries................................. 6         
Bytes of storage consumed......................... 8192      
Bytes of payload.................................. 787          9.6% 
Bytes of metadata................................. 40           0.49% 
Average payload per entry......................... 131.17    
Average unused bytes per entry.................... 1227.50   
Average metadata per entry........................ 6.67      
Maximum payload per entry......................... 228       
Entries that use overflow......................... 0            0.0% 
Primary pages used................................ 2         
Overflow pages used............................... 0         
Total pages used.................................. 2         
Unused bytes on primary pages..................... 7365        89.9% 
Unused bytes on overflow pages.................... 0         
Unused bytes on all pages......................... 7365        89.9% 

*** Table VOUCHERS w/o any indices ********************************************

Percentage of total database......................   0.0%    
Number of entries................................. 3         
Bytes of storage consumed......................... 4096      
Bytes of payload.................................. 680         16.6% 
Bytes of metadata................................. 23           0.56% 
B-tree depth...................................... 1         
Average payload per entry......................... 226.67    
Average unused bytes per entry.................... 1131.00   
Average metadata per entry........................ 7.67      
Maximum payload per entry......................... 228       
Entries that use overflow......................... 0            0.0% 
Primary pages used................................ 1         
Overflow pages used............................... 0         
Total pages used.................................. 1         
Unused bytes on primary pages..................... 3393        82.8% 
Unused bytes on overflow pages.................... 0         
Unused bytes on all pages......................... 3393        82.8% 

*** Index SQLITE_AUTOINDEX_VOUCHERS_1 of table VOUCHERS ***********************

Percentage of total database......................   0.0%    
Number of entries................................. 3         
Bytes of storage consumed......................... 4096      
Bytes of payload.................................. 107          2.6% 
Bytes of metadata................................. 17           0.42% 
B-tree depth...................................... 1         
Average payload per entry......................... 35.67     
Average unused bytes per entry.................... 1324.00   
Average metadata per entry........................ 5.67      
Maximum payload per entry......................... 36        
Entries that use overflow......................... 0            0.0% 
Primary pages used................................ 1         
Overflow pages used............................... 0         
Total pages used.................................. 1         
Unused bytes on primary pages..................... 3972        97.0% 
Unused bytes on overflow pages.................... 0         
Unused bytes on all pages......................... 3972        97.0%
3 Likes

Nice work @BlackDuck!
This is very good insight and has been shared with the corresponding team internally!

2 Likes

Also it could be beneficial to offer a separate mount point for the database so people with that capability could have the DB on another drive like an SSD, since it doesn’t take up much space.

3 Likes

You would be doubling the chance of disk failure damaging your node though

Not if RAID is used for both the db and files.

You would still be doubling the risk, but the risk you’re doubling would be much smaller.

Either way, if you add that feature you can’t assume SNO’s will have redundant RAID on both. In fact it’s fair to assume most won’t. Hence it would reduce the reliability of the network.

So, no feature to allow some SNOs to increase the performance just because others may use it incorrectly?

Wouldn’t those non-RAID SNOs just get disqualified over time leaving the ones with RAID?

I still want MySQL though :slight_smile:

you’re thinking about individual nodes without thinking about the impact on network stability and data reliability. Introducing an option that makes nodes less reliable has impact on those factors, which increases repair costs or in the worst case risk of losing data.

1 Like

Actually it wouldn’t hurt the network at all as SNOs are already not advised to use redundancy since the network is designed to deal with that.

It would double the risk to the SNO, yes.