I'm a little worried about how long it takes to open one of 20,000,000 files in a directory on the NAS?
On Wed, Jan 7, 2009 at 6:36 AM, Edward J. Yoon <edwardy...@apache.org>wrote: > > 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 > -- Jim Dodgen j...@dodgen.us _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
