Re: [sqlite] How to decrease IO usage

[Simon Slavin]

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

[Bebel]

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

[Bebel]

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

[Bebel]

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

[Simon Slavin]

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

[Igor Tandetnik]

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

[Michael Black]

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

[Bebel]

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