Re: [sqlite] How to decrease IO usage
Then, do you know a way to optimize my usage ? I suppose you have a pattern to which database are accessed at any point in time? If so rather than opening and closing databases directly, you could pool them, so that if an operation is needed on a recently opened database, it will still be opened, which would eliminate the open/close overhead. You should be able to implement the above by redirecting your database open/close functions to two simple functions + array + critical section to protect the array if you're multi-threaded. There are many complicated strategies for pooling, but a simple "empty the pool when it's full" could be good enough to tell you if it's an option that is beneficial to I/O in your case. If it is, you can always refine the pooling strategies. Eric ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to decrease IO usage
On 10 Jan 2013, at 2:42pm, Bebel wrote: > I include all the operation made by my action (so open/close are included). > > I create one base per user, so I prefer not to merge database to make one > bigger. Okay. This means that your enquiry is not the standard one of "faster operations at all costs" that we get, you are unusual in that you want to open and close databases very quickly and answers to other people's queries probably won't consider that. I suppose you could always look at faster hardware. For example I have found that opening a SQLite file on SSD is far faster than doing the same thing on a spinning drive. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to decrease IO usage
Hi Simon, I include all the operation made by my action (so open/close are included). I create one base per user, so I prefer not to merge database to make one bigger. Thanks for your help. -- View this message in context: http://sqlite.1065341.n5.nabble.com/How-to-decrease-IO-usage-tp66474p66502.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to decrease IO usage
Hi Igor, Well it's a interesting solution, and I already explored this possibility. Now, it will be too expansive and complicated for us to migrate data from sqlite to nosql solution. We choose sqlite about 5 years ago, when nosql was not an industrial solution. Sqlite was the best choice. Now we "must" use sqlite, and we need to the best with it ! -- View this message in context: http://sqlite.1065341.n5.nabble.com/How-to-decrease-IO-usage-tp66474p66497.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to decrease IO usage
Hi Michael, Thanks for your feed back, but as far as I understand the pragma cache size, it's only increase the cache size during the session. But the cache is deleted when the database is close. In my case, I only make short transaction on the database, like open, read and immediately close. I can't maintain the session active. But, one of my possible solution is to put database in memory, with vmstat for example. It will produce something similar, because all the data will be in memory. However, I'm looking for sqlite solutions. I can get around of my latence problem with system solution, but if I can optimize my solr usage, it will be better. I feel that my usage is not as optimal as it could be. -- View this message in context: http://sqlite.1065341.n5.nabble.com/How-to-decrease-IO-usage-tp66474p66494.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to decrease IO usage
On 9 Jan 2013, at 10:59pm, Bebel wrote: > Now my problem is that my limitation for increase my latency is on the > amount of IO ops perform by the databases. In fact, sqlite make a lot of > random IO on my disks (many hundred) and this increase my disk latency. > > I made a test on a empty database, on which I insert a value and then read > it. This two sqlite operations made more than 40 IO operation. Are you including in those 40 operations the opening and closing of the database files ? Opening the database file is complicated and takes many operations. By having many tiny databases instead of fewer big databases you are going to spend a lot of time just changing which database you have open. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to decrease IO usage
On 1/9/2013 5:59 PM, Bebel wrote: I'm working with a lot of sqlite base (many million), but they are very small, 2000 entries for the bigger. In this base, I create 6 tables, but I mostly work on just one of them. My structure are very simple, I stored varchar and blob. Looks like you are using SQLite as a key-value store. Perhaps you might be happier with a real key-value store, like Berkeley DB or LevelDB, which would be optimized specifically for such use case. http://en.wikipedia.org/wiki/NoSQL#Key.E2.80.93value_stores_on_solid_state_or_rotating_disk -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to decrease IO usage
Increase your cache size? Default is 2000*page_size http://www.sqlite.org/pragma.html#pragma_cache_size -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Bebel Sent: Wednesday, January 09, 2013 5:00 PM To: sqlite-users@sqlite.org Subject: [sqlite] How to decrease IO usage Hi there, Let me explain you my problem. I'm working with a lot of sqlite base (many million), but they are very small, 2000 entries for the bigger. In this base, I create 6 tables, but I mostly work on just one of them. My structure are very simple, I stored varchar and blob. My amount of insert is not important regarding to my amount of select action. Now my problem is that my limitation for increase my latency is on the amount of IO ops perform by the databases. In fact, sqlite make a lot of random IO on my disks (many hundred) and this increase my disk latency. I made a test on a empty database, on which I insert a value and then read it. This two sqlite operations made more than 40 IO operation. Then, do you know a way to optimize my usage ? NB : Data integrity is very important on my environment. Thanks for help, and happy new year ! -- View this message in context: http://sqlite.1065341.n5.nabble.com/How-to-decrease-IO-usage-tp66474.html Sent from the SQLite mailing list archive at Nabble.com. ___ 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
[sqlite] How to decrease IO usage
Hi there, Let me explain you my problem. I'm working with a lot of sqlite base (many million), but they are very small, 2000 entries for the bigger. In this base, I create 6 tables, but I mostly work on just one of them. My structure are very simple, I stored varchar and blob. My amount of insert is not important regarding to my amount of select action. Now my problem is that my limitation for increase my latency is on the amount of IO ops perform by the databases. In fact, sqlite make a lot of random IO on my disks (many hundred) and this increase my disk latency. I made a test on a empty database, on which I insert a value and then read it. This two sqlite operations made more than 40 IO operation. Then, do you know a way to optimize my usage ? NB : Data integrity is very important on my environment. Thanks for help, and happy new year ! -- View this message in context: http://sqlite.1065341.n5.nabble.com/How-to-decrease-IO-usage-tp66474.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users