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