Re: [sqlite] Sqlite and docker performance question

[Sebastien HEITZMANN]

to close this subject I have some more information.

After a try on my ubuntu I notice that the différence doesn't exist. So I
ugraded the production HOST to à 4.4 linux kernel.

The timing is now the same on the host and in the container ( but both are
slower. snif ... )

fs tuning is not so easy.

Thanks all for your help.


2017-11-30 19:29 GMT+01:00 Scott Robison :

> Perhaps the file sync performed by SQLite is more expensive in the docker
> environment than in the host. That would make sense to me.
>
> On Nov 30, 2017 7:07 AM, "Sebastien HEITZMANN" <2...@2le.net> wrote:
>
> > In my last mail i have multiple table creation and index. It seam that
> the
> > overtime is for all the create statement.
> >
> > It really looks like a disk pb. But can't identify so much difference in
> my
> > direct disk access ( with DD )
> >
> > I will try it on an other host.
> >
> > 2017-11-30 14:59 GMT+01:00 Simon Slavin :
> >
> > > OP wrote:
> > >
> > > > CREATE TABLE f (fid VARCHAR, path VARCHAR, meta VARCHAR, mtime
> INTEGER,
> > > > virtual INTEGER, pfid VARCHAR, type VARCHAR, ts INTEGER);
> > >
> > > Is this the first content of a new file ?  If so, SQLite has to create
> > the
> > > file and write some structure information as well as writing the table.
> > I
> > > suspect that the time taken for the overhead is far more than the time
> > > taken for the CREATE command.
> > >
> > > Could you try changing f.sql to create ten tables ?  For example create
> > > the table "f1 as above then create tables "f2" to "f1" with the same
> > > columns ?  It would be interesting to see what this does to both
> timings.
> > >
> > > Simon.
> > >
> > > ___
> > > sqlite-users mailing list
> > > sqlite-users@mailinglists.sqlite.org
> > > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> > >
> >
> >
> >
> > --
> > *Sébastien HEITZMANN*
> > Gérant & Directeur technique
> > +33 (0)3 89 333 889
> > Plus d'info sur : www.2le.net
> >
> >
> >  > lentreprise/194148499368?ref=ts>
> >   
> > 
> > ___
> > sqlite-users mailing list
> > sqlite-users@mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
*Sébastien HEITZMANN*
Gérant & Directeur technique
+33 (0)3 89 333 889
Plus d'info sur : www.2le.net



  

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Sqlite and docker performance question

[Scott Robison]

Perhaps the file sync performed by SQLite is more expensive in the docker
environment than in the host. That would make sense to me.

On Nov 30, 2017 7:07 AM, "Sebastien HEITZMANN" <2...@2le.net> wrote:

> In my last mail i have multiple table creation and index. It seam that the
> overtime is for all the create statement.
>
> It really looks like a disk pb. But can't identify so much difference in my
> direct disk access ( with DD )
>
> I will try it on an other host.
>
> 2017-11-30 14:59 GMT+01:00 Simon Slavin :
>
> > OP wrote:
> >
> > > CREATE TABLE f (fid VARCHAR, path VARCHAR, meta VARCHAR, mtime INTEGER,
> > > virtual INTEGER, pfid VARCHAR, type VARCHAR, ts INTEGER);
> >
> > Is this the first content of a new file ?  If so, SQLite has to create
> the
> > file and write some structure information as well as writing the table.
> I
> > suspect that the time taken for the overhead is far more than the time
> > taken for the CREATE command.
> >
> > Could you try changing f.sql to create ten tables ?  For example create
> > the table "f1 as above then create tables "f2" to "f1" with the same
> > columns ?  It would be interesting to see what this does to both timings.
> >
> > Simon.
> >
> > ___
> > sqlite-users mailing list
> > sqlite-users@mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >
>
>
>
> --
> *Sébastien HEITZMANN*
> Gérant & Directeur technique
> +33 (0)3 89 333 889
> Plus d'info sur : www.2le.net
>
>
>  lentreprise/194148499368?ref=ts>
>   
> 
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Sqlite and docker performance question

[Sebastien HEITZMANN]

In my last mail i have multiple table creation and index. It seam that the
overtime is for all the create statement.

It really looks like a disk pb. But can't identify so much difference in my
direct disk access ( with DD )

I will try it on an other host.

2017-11-30 14:59 GMT+01:00 Simon Slavin :

> OP wrote:
>
> > CREATE TABLE f (fid VARCHAR, path VARCHAR, meta VARCHAR, mtime INTEGER,
> > virtual INTEGER, pfid VARCHAR, type VARCHAR, ts INTEGER);
>
> Is this the first content of a new file ?  If so, SQLite has to create the
> file and write some structure information as well as writing the table.  I
> suspect that the time taken for the overhead is far more than the time
> taken for the CREATE command.
>
> Could you try changing f.sql to create ten tables ?  For example create
> the table "f1 as above then create tables "f2" to "f1" with the same
> columns ?  It would be interesting to see what this does to both timings.
>
> Simon.
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
*Sébastien HEITZMANN*
Gérant & Directeur technique
+33 (0)3 89 333 889
Plus d'info sur : www.2le.net



  

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Sqlite and docker performance question

[Simon Slavin]

OP wrote:

> CREATE TABLE f (fid VARCHAR, path VARCHAR, meta VARCHAR, mtime INTEGER,
> virtual INTEGER, pfid VARCHAR, type VARCHAR, ts INTEGER);

Is this the first content of a new file ?  If so, SQLite has to create the file 
and write some structure information as well as writing the table.  I suspect 
that the time taken for the overhead is far more than the time taken for the 
CREATE command.

Could you try changing f.sql to create ten tables ?  For example create the 
table "f1 as above then create tables "f2" to "f1" with the same columns ?  It 
would be interesting to see what this does to both timings.

Simon.

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Sqlite and docker performance question

[Sebastien HEITZMANN]

my test script is a little bit more complicated but with .timer it give
this result. Each statement is longer. So no load time or something like
that.

ON HOST

root@dipsy:/usr/share/kserver4# time sqlite3 dbm.db < dbm.sql
Run Time: real 0.027 user 0.00 sys 0.00
Run Time: real 0.020 user 0.00 sys 0.00
Run Time: real 0.007 user 0.00 sys 0.00
Run Time: real 0.009 user 0.00 sys 0.00
Run Time: real 0.010 user 0.00 sys 0.00
Run Time: real 0.010 user 0.00 sys 0.00
Run Time: real 0.007 user 0.004000 sys 0.00
Run Time: real 0.008 user 0.00 sys 0.00
Run Time: real 0.005 user 0.00 sys 0.00
Run Time: real 0.013 user 0.00 sys 0.00
Run Time: real 0.007 user 0.00 sys 0.00
Run Time: real 0.007 user 0.00 sys 0.00
Run Time: real 0.007 user 0.00 sys 0.00
Run Time: real 0.018 user 0.00 sys 0.00

real 0m0.157s
user 0m0.004s
sys 0m0.000s

IN CONTAINER

root@1e90b83b1b3f:/tmp# time sqlite3 dbm.db < dbm.sql
Run Time: real 0.062 user 0.00 sys 0.00
Run Time: real 0.069 user 0.00 sys 0.00
Run Time: real 0.068 user 0.00 sys 0.00
Run Time: real 0.065 user 0.00 sys 0.00
Run Time: real 0.068 user 0.00 sys 0.004000
Run Time: real 0.059 user 0.00 sys 0.00
Run Time: real 0.053 user 0.00 sys 0.00
Run Time: real 0.058 user 0.00 sys 0.00
Run Time: real 0.055 user 0.00 sys 0.00
Run Time: real 0.061 user 0.00 sys 0.00
Run Time: real 0.056 user 0.00 sys 0.00
Run Time: real 0.048 user 0.00 sys 0.00
Run Time: real 0.048 user 0.00 sys 0.004000
Run Time: real 0.049 user 0.00 sys 0.00

real 0m0.822s
user 0m0.004s
sys 0m0.008s


2017-11-30 14:41 GMT+01:00 Keith Medcalf <kmedc...@dessus.com>:

>
> In addition to the execution time of the SQL you are also measuring time
> to load and link the sqlite3 command.
>
> A perhaps more realistic test would be to change the f.sql to contain:
>
> .timer on
> CREATE TABLE f (fid VARCHAR, path VARCHAR, meta VARCHAR, mtime INTEGER,
> virtual INTEGER, pfid VARCHAR, type VARCHAR, ts INTEGER);
>
> and then run the same test and see what the actual execution time of the
> SQL statement is.  It will probably come to 1 tick (so between 1 and 2
> ticks), whatever the unit of a tick is in the container or on the native
> host, or perhaps even zero if the execution time was less than a tick.
>
> ---
> The fact that there's a Highway to Hell but only a Stairway to Heaven says
> a lot about anticipated traffic volume.
>
> >-Original Message-
> >From: sqlite-users [mailto:sqlite-users-
> >boun...@mailinglists.sqlite.org] On Behalf Of Sebastien HEITZMANN
> >Sent: Thursday, 30 November, 2017 04:01
> >To: sqlite-users@mailinglists.sqlite.org
> >Subject: [sqlite] Sqlite and docker performance question
> >
> >Hi,
> >
> >In our application we use sqlite with great satisfaction.
> >
> >We currently benchmark our application and came up to a strange
> >difference
> >in creating a very simple sqlite db.
> >
> >We juste create a sigle table in a new db. In my docker container it
> >take 4
> >time more time than in the host system.
> >
> >time sqlite3 /tmp/foo.db < f.sql
> >
> >on the host machine
> >real 0m0.216s
> >
> >and in the docker container
> >real 0m0.826s
> >
> >
> >the f.sql contain a single sql
> >
> >CREATE TABLE f (fid VARCHAR, path VARCHAR, meta VARCHAR, mtime
> >INTEGER,
> >virtual INTEGER, pfid VARCHAR, type VARCHAR, ts INTEGER);
> >
> >I first think about a drive performance difference so i check the
> >write
> >thruput with dd and obtain nearly the same value.
> >
> >root@dipsy:/usr/share/kserver4# time dd if=/dev/zero of=/tmp/test
> >bs=512
> >count=1024000
> >1024000+0 enregistrements lus
> >1024000+0 enregistrements écrits
> >524288000 octets (524 MB) copiés, 7,04168 s, 74,5 MB/s
> >
> >real 0m7.056s
> >user 0m0.228s
> >sys 0m2.688s
> >
> >in the container
> >
> >root@1e90b83b1b3f:/project# time dd if=/dev/zero of=/tmp/test bs=512
> >count=1024000
> >1024000+0 records in
> >1024000+0 records out
> >524288000 bytes (524 MB) copied, 6.79671 s, 77.1 MB/s
> >
> >real 0m6.977s
> >user 0m0.272s
> >sys 0m2.600s
> >
> >the filesystem is ext4 with data=ordered
> >and /tmp is a volume mounted from the host in the [ docker -v
> >/tmp:/tmp ]
> >
> >
> >So my question is where can i investigate to see what happend ?
> >
> >Any idea or suggestion ?
> >
> >
> &

Re: [sqlite] Sqlite and docker performance question

[Sebastien HEITZMANN]

All my other tests give me some equivalent time in host and in container.

I have reduced the problem to the creation of the database. The rest of my
workload run approximatively in the same time.

You're right, there is some variance between each test but the 4 factor
stay the same. Sometime it's 180ms , sometime 240ms on the host. But on the
container it is always around 800ms.

I also use pysqlite with the same result.

I'm currently test the disk access with bonnie++ to see if any filesystem
or disk setting could be implied.

SEB

2017-11-30 12:48 GMT+01:00 Olivier Mascia :

> > Le 30 nov. 2017 à 12:00, Sebastien HEITZMANN <2...@2le.net> a écrit :
> >
> > We juste create a sigle table in a new db. In my docker container it
> take 4
> > time more time than in the host system.
> >
> > time sqlite3 /tmp/foo.db < f.sql
> >
> > on the host machine
> > real 0m0.216s
> >
> > and in the docker container
> > real 0m0.826s
>
> Couldn't it be a discrepancy in the way 'time' measures on the host or
> through the container?
> Or simply a difference in speed of code execution through the container?
> Instead of an actual difference of behaviour/performance of SQLite itself?
>
> The test (creation of this small db) is so small that timing it has a
> large potential for variance due to the system itself.
>
> --
> Best Regards, Meilleures salutations, Met vriendelijke groeten,
> Olivier Mascia
>
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
*Sébastien HEITZMANN*
Gérant & Directeur technique
+33 (0)3 89 333 889
Plus d'info sur : www.2le.net



  

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Sqlite and docker performance question

[Keith Medcalf]

In addition to the execution time of the SQL you are also measuring time to 
load and link the sqlite3 command.  

A perhaps more realistic test would be to change the f.sql to contain:

.timer on
CREATE TABLE f (fid VARCHAR, path VARCHAR, meta VARCHAR, mtime INTEGER,
virtual INTEGER, pfid VARCHAR, type VARCHAR, ts INTEGER);

and then run the same test and see what the actual execution time of the SQL 
statement is.  It will probably come to 1 tick (so between 1 and 2 ticks), 
whatever the unit of a tick is in the container or on the native host, or 
perhaps even zero if the execution time was less than a tick.

---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.

>-Original Message-
>From: sqlite-users [mailto:sqlite-users-
>boun...@mailinglists.sqlite.org] On Behalf Of Sebastien HEITZMANN
>Sent: Thursday, 30 November, 2017 04:01
>To: sqlite-users@mailinglists.sqlite.org
>Subject: [sqlite] Sqlite and docker performance question
>
>Hi,
>
>In our application we use sqlite with great satisfaction.
>
>We currently benchmark our application and came up to a strange
>difference
>in creating a very simple sqlite db.
>
>We juste create a sigle table in a new db. In my docker container it
>take 4
>time more time than in the host system.
>
>time sqlite3 /tmp/foo.db < f.sql
>
>on the host machine
>real 0m0.216s
>
>and in the docker container
>real 0m0.826s
>
>
>the f.sql contain a single sql
>
>CREATE TABLE f (fid VARCHAR, path VARCHAR, meta VARCHAR, mtime
>INTEGER,
>virtual INTEGER, pfid VARCHAR, type VARCHAR, ts INTEGER);
>
>I first think about a drive performance difference so i check the
>write
>thruput with dd and obtain nearly the same value.
>
>root@dipsy:/usr/share/kserver4# time dd if=/dev/zero of=/tmp/test
>bs=512
>count=1024000
>1024000+0 enregistrements lus
>1024000+0 enregistrements écrits
>524288000 octets (524 MB) copiés, 7,04168 s, 74,5 MB/s
>
>real 0m7.056s
>user 0m0.228s
>sys 0m2.688s
>
>in the container
>
>root@1e90b83b1b3f:/project# time dd if=/dev/zero of=/tmp/test bs=512
>count=1024000
>1024000+0 records in
>1024000+0 records out
>524288000 bytes (524 MB) copied, 6.79671 s, 77.1 MB/s
>
>real 0m6.977s
>user 0m0.272s
>sys 0m2.600s
>
>the filesystem is ext4 with data=ordered
>and /tmp is a volume mounted from the host in the [ docker -v
>/tmp:/tmp ]
>
>
>So my question is where can i investigate to see what happend ?
>
>Any idea or suggestion ?
>
>
>
>--
>*Sébastien HEITZMANN*
>Gérant & Directeur technique
>+33 (0)3 89 333 889
>Plus d'info sur : www.2le.net
>
>
><http://www.facebook.com/pages/2le-Logiciel-libre-pour-
>lentreprise/194148499368?ref=ts>
>  <http://twitter.com/2le_net#>
><http://www.viadeo.com/profile/00225bbq5gc1ukcu>
>___
>sqlite-users mailing list
>sqlite-users@mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Sqlite and docker performance question

[Olivier Mascia]

> Le 30 nov. 2017 à 12:00, Sebastien HEITZMANN <2...@2le.net> a écrit :
> 
> We juste create a sigle table in a new db. In my docker container it take 4
> time more time than in the host system.
> 
> time sqlite3 /tmp/foo.db < f.sql
> 
> on the host machine
> real 0m0.216s
> 
> and in the docker container
> real 0m0.826s

Couldn't it be a discrepancy in the way 'time' measures on the host or through 
the container?
Or simply a difference in speed of code execution through the container?
Instead of an actual difference of behaviour/performance of SQLite itself?

The test (creation of this small db) is so small that timing it has a large 
potential for variance due to the system itself.

-- 
Best Regards, Meilleures salutations, Met vriendelijke groeten,
Olivier Mascia


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Sqlite and docker performance question

[Sebastien HEITZMANN]

Hi,

In our application we use sqlite with great satisfaction.

We currently benchmark our application and came up to a strange difference
in creating a very simple sqlite db.

We juste create a sigle table in a new db. In my docker container it take 4
time more time than in the host system.

time sqlite3 /tmp/foo.db < f.sql

on the host machine
real 0m0.216s

and in the docker container
real 0m0.826s


the f.sql contain a single sql

CREATE TABLE f (fid VARCHAR, path VARCHAR, meta VARCHAR, mtime INTEGER,
virtual INTEGER, pfid VARCHAR, type VARCHAR, ts INTEGER);

I first think about a drive performance difference so i check the write
thruput with dd and obtain nearly the same value.

root@dipsy:/usr/share/kserver4# time dd if=/dev/zero of=/tmp/test bs=512
count=1024000
1024000+0 enregistrements lus
1024000+0 enregistrements écrits
524288000 octets (524 MB) copiés, 7,04168 s, 74,5 MB/s

real 0m7.056s
user 0m0.228s
sys 0m2.688s

in the container

root@1e90b83b1b3f:/project# time dd if=/dev/zero of=/tmp/test bs=512
count=1024000
1024000+0 records in
1024000+0 records out
524288000 bytes (524 MB) copied, 6.79671 s, 77.1 MB/s

real 0m6.977s
user 0m0.272s
sys 0m2.600s

the filesystem is ext4 with data=ordered
and /tmp is a volume mounted from the host in the [ docker -v /tmp:/tmp ]


So my question is where can i investigate to see what happend ?

Any idea or suggestion ?



-- 
*Sébastien HEITZMANN*
Gérant & Directeur technique
+33 (0)3 89 333 889
Plus d'info sur : www.2le.net



  

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users