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?
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
