> Is access to *one* of the 20 million different SQLite files getting > progressively slower? How big is that specific SQLite file? Is that > the one that is "huge"? I use SQLite over an NAS (at times), and never > experience any noticeable slowdown. Is access to his NAS itself slow, > perhaps not just via SQLite but just over the regular filesystem?
Each NAS_000 ~ N storages have approximately 300,000 files, the average size of file is few MB (not over GB). The broker servers (with SQLite library) are on the NAS and The front-end web servers (more than 200 servers) communicate with living broker servers after request location from location addressing system. There are high frequency read/write/delete operations. The number of files/storages/clients keep increasing little by little. /Edward On Wed, Jan 7, 2009 at 9:57 PM, P Kishor <punk.k...@gmail.com> wrote: > On 1/7/09, Thomas Briggs <t...@briggs.cx> wrote: >> I actually thought the original question was perfectly clear. I >> thought the proposed solution (included in the original post) was >> perfectly logical too. So what's all the fuss? > > The confusion, at least for me, arose from the following sentence in the OP -- > > "I'm using SQLite, all data (very huge and 20 million files) " > > and the response to request for clarification of the above. > > - we know he is using SQLite > > - we know "it" is all data (although, I am not sure what else could > SQLite be used for other than "data") > > - we know "it" is very huge > > - we know there are 20 million *files* involved > > No matter how I put together the above four pieces of information, I > can't grok it. > > Is access to *one* of the 20 million different SQLite files getting > progressively slower? How big is that specific SQLite file? Is that > the one that is "huge"? I use SQLite over an NAS (at times), and never > experience any noticeable slowdown. Is access to his NAS itself slow, > perhaps not just via SQLite but just over the regular filesystem? > > So there... no fuss, just a desire to understand better what exactly > is the problem. > >> >> >> On Wed, Jan 7, 2009 at 7:28 AM, P Kishor <punk.k...@gmail.com> wrote: >> > On 1/6/09, Edward J. Yoon <edwardy...@apache.org> wrote: >> >> Thanks, >> >> >> >> In more detail, SQLite used for user-based applications (20 million is >> >> the size of app-users). and MySQL used for user location (file path on >> >> NAS) addressing. >> > >> > Edward, >> > >> > At least I still don't understand why you have 20 million databases. >> > My suspicion is that something is getting lost in the translation >> > above, and neither you nor anyone on the list is benefitting from it. >> > Could you please make a little more effort at explaining what exactly >> > is your problem -- it well might be an "xy problem." >> > >> > If you really do have 20 million SQLite databases on a NAS, and you >> > don't care about changing anything about the situation except for >> > improving the speed of access from that NAS, well, since you will >> > likely be accessing only one db at a time, perhaps you could copy that >> > specific db to a local drive before opening it. >> > >> > In any case, something tells me that you will get better mileage if >> > you construct a good question for the list with enough background >> > detail. >> > >> > >> >> >> >> >> >> On Wed, Jan 7, 2009 at 1:31 PM, P Kishor <punk.k...@gmail.com> wrote: >> >> > On 1/6/09, Edward J. Yoon <edwardy...@apache.org> wrote: >> >> >> > Do you have 20 million sqlite databases? >> >> >> >> >> >> >> >> >> Yes. >> >> > >> >> > Since all these databases are just files, you should stuff them into a >> >> > Postgres database, then write an application that extracts the >> >> > specific row from the pg database with 20 mil rows giving you your >> >> > specific SQLite database on which you can do your final db work. >> >> > >> >> > Seriously, you need to rethink 20 mil databases as they defeat the >> >> > very purpose of having a database. >> >> > >> >> > >> >> >> >> >> >> >> >> >> On Wed, Jan 7, 2009 at 12:36 PM, Jim Dodgen <j...@dodgen.us> wrote: >> >> >> > I think the question was about the structure of your data >> >> >> > >> >> >> > a sqlite database is a file and can contain many tables. tables >> can contain >> >> >> > many rows. >> >> >> > >> >> >> > Do you have 20 million sqlite databases? >> >> >> > >> >> >> > This information can help people formulate an answer. >> >> >> > >> >> >> > On Tue, Jan 6, 2009 at 6:14 PM, Edward J. Yoon >> <edwardy...@apache.org>wrote: >> >> >> > >> >> >> >> Thanks for your reply. >> >> >> >> >> >> >> >> > That's a lot of files. Or did you mean rows? >> >> >> >> > Are you sure? There can be many other reasons. >> >> >> >> >> >> >> >> There is a lot of files. So, I don't know exactly why at this >> time, >> >> >> >> But thought network latency can´t be denied. >> >> >> >> >> >> >> >> /Edward >> >> >> >> >> >> >> >> On Wed, Jan 7, 2009 at 4:07 AM, Kees Nuyt <k.n...@zonnet.nl> >> wrote: >> >> >> >> > On Tue, 6 Jan 2009 11:23:29 +0900, "Edward J. Yoon" >> >> >> >> > <edwardy...@apache.org> wrote in General Discussion of >> >> >> >> > SQLite Database <sqlite-users@sqlite.org>: >> >> >> >> > >> >> >> >> >> Hi, I'm newbie in here. >> >> >> >> >> >> >> >> >> >> I'm using SQLite, all data (very huge and 20 million files) >> >> >> >> > >> >> >> >> > That's a lot of files. Or did you mean rows? >> >> >> >> > >> >> >> >> >> stored on NAS storage. Lately my system has been getting >> >> >> >> >> progressively slower. Network cost seems too large. >> >> >> >> > >> >> >> >> > Are you sure? There can be many other reasons. >> >> >> >> > >> >> >> >> >> To improve its performance, I'm think about local lock file >> >> >> >> >> instead of NAS as describe below. >> >> >> >> >> >> >> >> >> >> char str[1024] = "/tmp"; >> >> >> >> >> strcat(str, lockfile); >> >> >> >> >> sprintf(str, "%s-lock", zFilename); >> >> >> >> >> >> >> >> >> >> But, I'm not sure this is good idea. >> >> >> >> >> I would love to hear your advice!! >> >> >> >> > >> >> >> >> > I think that's not the right way to start. >> >> >> >> > This is what I would do, more or less in >> >> >> >> > this order: >> >> >> >> > >> >> >> >> > 1- Optimize the physical database properties >> >> >> >> > PRAGMA page_size (read the docss first!) >> >> >> >> > PRAGMA [default_]cache_size >> >> >> >> > >> >> >> >> > 2- Optimize SQL: use transactions >> >> >> >> > where appropriate. >> >> >> >> > >> >> >> >> > 3- Optimize your code. Don't close database >> >> >> >> > connections if they can be reused. >> >> >> >> > >> >> >> >> > 4- Optimize the schema: create indexes that >> >> >> >> > help, leave out indexes that don't help. >> >> >> >> > >> >> >> >> > 5- Investigate the communication to/from NAS. >> >> >> >> > Do all NIC's train at the highest possible speed? >> >> >> >> > Some limiting switch or router in between? >> >> >> >> > Do you allow jumbo frames? >> >> >> >> > >> >> >> >> > 6- Consider SAN/fSCSI, direct attached storage. >> >> >> >> > >> >> >> >> > 7- Consider changing SQLite code. >> >> >> >> > >> >> >> >> > >> >> >> >> > Without more details on your use case, people will only get >> >> >> >> > general advice like the above. >> >> >> >> > >> >> >> >> >>Thanks. >> >> >> >> > >> >> >> >> > Hope this helps. >> >> >> >> > -- >> >> >> >> > ( Kees Nuyt >> >> >> >> > ) >> >> >> >> > c[_] >> >> >> >> > _______________________________________________ >> >> >> >> > sqlite-users mailing list >> >> >> >> > sqlite-users@sqlite.org >> >> >> >> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> >> >> >> > >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> -- >> >> >> >> Best Regards, Edward J. Yoon @ NHN, corp. >> >> >> >> edwardy...@apache.org >> >> >> >> http://blog.udanax.org >> >> >> >> _______________________________________________ >> >> >> >> sqlite-users mailing list >> >> >> >> sqlite-users@sqlite.org >> >> >> >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> >> >> >> >> >> >> > >> >> >> > >> >> >> > >> >> >> > -- >> >> >> > Jim Dodgen >> >> >> > j...@dodgen.us >> >> >> > _______________________________________________ >> >> >> > sqlite-users mailing list >> >> >> > sqlite-users@sqlite.org >> >> >> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> >> >> > >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> -- >> >> >> >> >> >> Best Regards, Edward J. Yoon @ NHN, corp. >> >> >> edwardy...@apache.org >> >> >> http://blog.udanax.org >> > _______________________________________________ >> > sqlite-users mailing list >> > sqlite-users@sqlite.org >> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> > >> _______________________________________________ >> sqlite-users mailing list >> sqlite-users@sqlite.org >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> > > > -- > Puneet Kishor http://www.punkish.org/ > Nelson Institute for Environmental Studies http://www.nelson.wisc.edu/ > Open Source Geospatial Foundation (OSGeo) http://www.osgeo.org/ > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- Best Regards, Edward J. Yoon @ NHN, corp. edwardy...@apache.org http://blog.udanax.org _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
