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. @BlackDuckWhy did you choose to use a sqlite db? - #5 by BlackDuck Log in with Atlassian account
My opinion would be to switch to MySQL, here are the reasons:
Better performance, since it is multithread-compatible
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.
Probably not very different from sqlite, may not require a lot of changes in the code.
The downsides:
It may use more RAM
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.
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:
Better performance, since it is multithread-compatible
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.
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.
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 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 )
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.
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.
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.
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.
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.
/** 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%
*** 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%
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.
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.
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.
as I only had corrupt InnoDB tables as a result of bad sectors.