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
