sqlite - Best way to store 100,000+ CSV text files on server? -



sqlite - Best way to store 100,000+ CSV text files on server? -

we have application need store thousands of little csv files. 100,000+ , growing annually same amount. each file contains around 20-80kb of vehicle tracking data. each info set (or file) represents single vehicle journey.

we storing info in sql server, size of database getting little unwieldy , ever need access journey info 1 file @ time (so need query in mass or otherwise store in relational database not needed). performance of database degrading add together more tracks, due time taken rebuild or update indexes when inserting or deleting data.

there 3 options considering:

we utilize filestream feature of sql externalise info files, i've not used feature before. filestream still result in 1 physical file per database object (blob)?

alternatively, store files individually on disk. there end beingness half 1000000 of them after 3+ years. ntfs file scheme cope ok amount?

if lots of files problem, should consider grouping datasets/files little database (one peruser) each user? there lightweight database sqlite can store files?

one farther point: info highly compressible. zipping files reduces them 10% of original size. utilise compression if possible minimise disk space used , backup size.

i have few thoughts, , subjective, mileage ond other readers' mileage may vary, still ball rolling if other folks want set differing points of view...

firstly, have seen performance issues folders containing many files. 1 project got around creating 256 directories, called 00, 01, 02... fd, fe, ff , within each 1 of farther 256 directories same naming convention. potentially divides 500,000 files across 65,536 directories giving few in each - if utilize hash/random generator spread them out. also, filenames pretty short store in database - e.g. 32/af/file-xyz.csv. doubtless bite head off, sense 10,000 files in 1 directory plenty going on with.

secondly, 100,000 files of 80kb amounts 8gb of info not big these days - little usb flash drive in fact - think arguments compression not valid - storage cheap. of import though, backup. if have 500,000 files have lots of 'inodes' traverse , think statistic used many backup products can traverse 50-100 'inodes' per sec - going waiting long time. depending on downtime can tolerate, may improve take scheme offline , raw, block device - @ 100mb/s can 8gb in 80 seconds , can't imagine traditional, file-based backup can close that. alternatives may filesysten permits snapshots , can backup snapshot. or mirrored filesystem permits split mirror, backup 1 re-create , rejoin mirror.

as said, pretty subjective , sure others have other ideas.

sqlite file storage scalability

-

Comments

Post a Comment

Popular posts from this blog

formatting - SAS SQL Datepart function returning odd values -

formatting - SAS SQL Datepart function returning odd values - i'm having massive problem project can't seem right. i'm trying modify info variable in datetime format. first info 30mar12:00:00:00. i've been advised utilize code below returns value 30mar60:5:30:00. want date component , have no thought how info got exported in format wasn't in microsoft access. i've tried several mods code 1 time again comes blank or sends error message. proc sql; update dataset set date = datepart(date); quit; any advice appreciated. i'm guessing variable date numeric , formatted datetime. 1 time you've carried out conversion, alter format date9. sas datetime value number of seconds since 01 jan 1960, whereas dates number of days since 01 jan 1960. both stored numbers, using right format display value key. illustration below. data _null_; a='30mar12:00:00:00'dt; b=datepart(a); c=b; format b datetime. c date9.; set b c; run;
Read more

c++ - Apple Mach-O Linker Error(Duplicate Symbols For Architecture armv7) -

c++ - Apple Mach-O Linker Error(Duplicate Symbols For Architecture armv7) - here error: duplicate symbol _objc_metaclass_$_bfapplinkreturntorefererview in: /users/user/documents/facebooksdk/facebooksdk.framework/facebooksdk(bfapplinkreturntorefererview.o) /users/user/desktop/project/project/project/assets/facebook/editor/ios/facebooksdk/facebooksdk.a(bfapplinkreturntorefererview.o) ld: 889 duplicate symbols architecture armv7 clang: error: linker command failed exit code 1 (use -v see invocation) i have searched find solution of error couldn't. there tons of similar error this, 1 hasn't been asked yet. problem shows when add together "-objc" "other linker flags". need add together utilize admob service. problem? how can solve this? i faced same issue while integrating facebook-ios-sdk. because using 3rd party library had bolts.framework linked against it. solution found incorporate facebook-ios-sdk source code hosted on github.
Read more

php - Yii 2: Unable to find a class into the extension 'yii2-admin' -

php - Yii 2: Unable to find a class into the extension 'yii2-admin' - extension's github project: https://github.com/mdmsoft/yii2-admin i'm using advanced template of yii2, i've backend , frontend , common folders advanced folder. the advanced folder on same level of vendor folder. i've others extensions , working then installed yii2-admin composer php composer.phar require mdmsoft/yii2-admin "*" installation worked well. into vendor folder i've mdmsoft folder yii2-admin subfolder it. this actual content of v endor/yiisoft/extensions.php (modified composer installation, haven't manually touched it) <?php $vendordir = dirname(__dir__); homecoming array ( 'yiisoft/yii2-jui' => array ( 'name' => 'yiisoft/yii2-jui', 'version' => '2.0.0.0', 'alias' => array ( '@yii/jui' => $vendordir . '/yiisoft/yii2-jui',
Read more