# Storage node performance improvement ideas **URL:** \ **Category:** STORJLINGS\ **Tags:** storagenode\ **Created:** [July 3, 2019, 10:45am UTC](https://forum.storj.io/t/storage-node-performance-improvement-ideas/314 "2019-07-03T10:45:00Z")\ **Posts on this page:** 20\ **Page:** 1
**Author:** ![littleskunk](https://storj.bcdn.literatehosting.com/user_avatar/forum.storj.io/littleskunk/32/63_2.png) [@littleskunk](https://forum.storj.io/u/littleskunk)\ **Post date:** [July 3, 2019, 10:45am UTC](https://forum.storj.io/t/storage-node-performance-improvement-ideas/314/1 "2019-07-03T10:45:00Z")
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? - #5 by BlackDuck](https://forum.storj.io/t/why-did-you-choose-to-use-a-sqlite-db/278/5) > [Log in with Atlassian account](https://storjlabs.atlassian.net/browse/V3-2092) > [@BlackDuck](#): > > 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. > **[Log in with Atlassian account](https://storjlabs.atlassian.net/login.jsp?permissionViolation=true&os_destination=%2Fbrowse%2FV3-2109&page_caps=&user_role=)** > > Log in to Jira, Confluence, and all other Atlassian Cloud products here. Not an Atlassian user? Sign up for free. I created a ticket for used\_serials as well: [Log in with Atlassian account](https://storjlabs.atlassian.net/browse/V3-2108) ---
**Author:** ![Pentium100](https://storj.bcdn.literatehosting.com/user_avatar/forum.storj.io/pentium100/32/6082_2.png) [@Pentium100](https://forum.storj.io/u/Pentium100)\ **Post date:** [July 4, 2019, 6:52am UTC](https://forum.storj.io/t/storage-node-performance-improvement-ideas/314/2 "2019-07-04T06:52:06Z")
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. ---
**Author:** ![subwolf](https://storj.bcdn.literatehosting.com/user_avatar/forum.storj.io/subwolf/32/67_2.png) [@subwolf](https://forum.storj.io/u/subwolf)\ **Post date:** [July 4, 2019, 8:30am UTC](https://forum.storj.io/t/storage-node-performance-improvement-ideas/314/3 "2019-07-04T08:30:50Z")
I’ve used MySQL since 2003. Could it be an option, but still use sqlite by default? For more advanced users like myself. ---
**Author:** ![subwolf](https://storj.bcdn.literatehosting.com/user_avatar/forum.storj.io/subwolf/32/67_2.png) [@subwolf](https://forum.storj.io/u/subwolf)\ **Post date:** [July 4, 2019, 9:32am UTC](https://forum.storj.io/t/storage-node-performance-improvement-ideas/314/4 "2019-07-04T09:32:00Z")
Use a satellite lookup table, saves data. ---
**Author:** ![Pentium100](https://storj.bcdn.literatehosting.com/user_avatar/forum.storj.io/pentium100/32/6082_2.png) [@Pentium100](https://forum.storj.io/u/Pentium100)\ **Post date:** [July 4, 2019, 10:21am UTC](https://forum.storj.io/t/storage-node-performance-improvement-ideas/314/5 "2019-07-04T10:21:12Z")
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. ---
**Author:** ![littleskunk](https://storj.bcdn.literatehosting.com/user_avatar/forum.storj.io/littleskunk/32/63_2.png) [@littleskunk](https://forum.storj.io/u/littleskunk)\ **Post date:** [July 4, 2019, 2:25pm UTC](https://forum.storj.io/t/storage-node-performance-improvement-ideas/314/6 "2019-07-04T14:25:03Z")
> 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. ---
**Author:** ![Pentium100](https://storj.bcdn.literatehosting.com/user_avatar/forum.storj.io/pentium100/32/6082_2.png) [@Pentium100](https://forum.storj.io/u/Pentium100)\ **Post date:** [July 4, 2019, 3:09pm UTC](https://forum.storj.io/t/storage-node-performance-improvement-ideas/314/7 "2019-07-04T15:09:17Z")
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 🙂 ) ---
**Author:** ![BrightSilence](https://storj.bcdn.literatehosting.com/user_avatar/forum.storj.io/brightsilence/32/6048_2.png) [@BrightSilence](https://forum.storj.io/u/BrightSilence)\ **Post date:** [July 4, 2019, 3:29pm UTC](https://forum.storj.io/t/storage-node-performance-improvement-ideas/314/8 "2019-07-04T15:29:54Z")
I’m sure you realize that operating this db in a cluster is a super fringe desire ---
**Author:** ![Pentium100](https://storj.bcdn.literatehosting.com/user_avatar/forum.storj.io/pentium100/32/6082_2.png) [@Pentium100](https://forum.storj.io/u/Pentium100)\ **Post date:** [July 4, 2019, 3:39pm UTC](https://forum.storj.io/t/storage-node-performance-improvement-ideas/314/9 "2019-07-04T15:39:32Z")
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. ---
**Author:** ![BlackDuck](https://storj.bcdn.literatehosting.com/user_avatar/forum.storj.io/blackduck/32/38_2.png) [@BlackDuck](https://forum.storj.io/u/BlackDuck)\ **Post date:** [July 4, 2019, 5:59pm UTC](https://forum.storj.io/t/storage-node-performance-improvement-ideas/314/10 "2019-07-04T17:59:36Z")
I did some analysis of my info.db with [sqlite3\_analyzer](https://www.sqlite.org/sqlanalyze.html) 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. ---
**Author:** ![BlackDuck](https://storj.bcdn.literatehosting.com/user_avatar/forum.storj.io/blackduck/32/38_2.png) [@BlackDuck](https://forum.storj.io/u/BlackDuck)\ **Post date:** [July 4, 2019, 6:01pm UTC](https://forum.storj.io/t/storage-node-performance-improvement-ideas/314/11 "2019-07-04T18:01:03Z")
``` /** 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% ``` ---
**Author:** ![BlackDuck](https://storj.bcdn.literatehosting.com/user_avatar/forum.storj.io/blackduck/32/38_2.png) [@BlackDuck](https://forum.storj.io/u/BlackDuck)\ **Post date:** [July 4, 2019, 6:01pm UTC](https://forum.storj.io/t/storage-node-performance-improvement-ideas/314/12 "2019-07-04T18:01:32Z")
``` ***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% ``` ---
**Author:** ![stefanbenten](https://storj.bcdn.literatehosting.com/user_avatar/forum.storj.io/stefanbenten/32/62_2.png) [@stefanbenten](https://forum.storj.io/u/stefanbenten)\ **Post date:** [July 5, 2019, 7:22pm UTC](https://forum.storj.io/t/storage-node-performance-improvement-ideas/314/13 "2019-07-05T19:22:29Z")
Nice work @BlackDuck! This is very good insight and has been shared with the corresponding team internally! ---
**Author:** ![Storgeez](https://storj.bcdn.literatehosting.com/user_avatar/forum.storj.io/storgeez/32/68_2.png) [@Storgeez](https://forum.storj.io/u/Storgeez)\ **Post date:** [July 8, 2019, 3:26pm UTC](https://forum.storj.io/t/storage-node-performance-improvement-ideas/314/14 "2019-07-08T15:26:35Z")
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. ---
**Author:** ![BrightSilence](https://storj.bcdn.literatehosting.com/user_avatar/forum.storj.io/brightsilence/32/6048_2.png) [@BrightSilence](https://forum.storj.io/u/BrightSilence)\ **Post date:** [July 8, 2019, 6:11pm UTC](https://forum.storj.io/t/storage-node-performance-improvement-ideas/314/15 "2019-07-08T18:11:18Z")
You would be doubling the chance of disk failure damaging your node though ---
**Author:** ![Pentium100](https://storj.bcdn.literatehosting.com/user_avatar/forum.storj.io/pentium100/32/6082_2.png) [@Pentium100](https://forum.storj.io/u/Pentium100)\ **Post date:** [July 8, 2019, 6:44pm UTC](https://forum.storj.io/t/storage-node-performance-improvement-ideas/314/16 "2019-07-08T18:44:09Z")
Not if RAID is used for both the db and files. ---
**Author:** ![BrightSilence](https://storj.bcdn.literatehosting.com/user_avatar/forum.storj.io/brightsilence/32/6048_2.png) [@BrightSilence](https://forum.storj.io/u/BrightSilence)\ **Post date:** [July 8, 2019, 6:48pm UTC](https://forum.storj.io/t/storage-node-performance-improvement-ideas/314/17 "2019-07-08T18:48:45Z")
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. ---
**Author:** ![Pentium100](https://storj.bcdn.literatehosting.com/user_avatar/forum.storj.io/pentium100/32/6082_2.png) [@Pentium100](https://forum.storj.io/u/Pentium100)\ **Post date:** [July 8, 2019, 7:09pm UTC](https://forum.storj.io/t/storage-node-performance-improvement-ideas/314/18 "2019-07-08T19:09:24Z")
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 🙂 ---
**Author:** ![BrightSilence](https://storj.bcdn.literatehosting.com/user_avatar/forum.storj.io/brightsilence/32/6048_2.png) [@BrightSilence](https://forum.storj.io/u/BrightSilence)\ **Post date:** [July 8, 2019, 7:14pm UTC](https://forum.storj.io/t/storage-node-performance-improvement-ideas/314/19 "2019-07-08T19:14:54Z")
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. ---
**Author:** ![Storgeez](https://storj.bcdn.literatehosting.com/user_avatar/forum.storj.io/storgeez/32/68_2.png) [@Storgeez](https://forum.storj.io/u/Storgeez)\ **Post date:** [July 8, 2019, 9:03pm UTC](https://forum.storj.io/t/storage-node-performance-improvement-ideas/314/20 "2019-07-08T21:03:20Z")
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. [Next page](https://forum.storj.io/t/storage-node-performance-improvement-ideas/314.md?page=2)