Re: [sqlite] EXT :Re: Concurrent readonly access to a large database.

[Frank Missel]

Hi Bo,

> boun...@sqlite.org] On Behalf Of Bo Peng
> > I wonder if it would be better on just having the data organized
> > before loading it, so that the records in each of the 5000 tables
> > would be contiguously stored. Of course, that also depends on how much
> > new data will be added to the tables.
> 
> I have been thinking about this too, but I do not know how to do it
> efficiently.
>
>3. Create 5000 files, insert records to them, and import the files to the
main database. This seems to be the best option although I need to pay
>attention to OS limit on opened files.
> 
> Any suggest is welcome.

A variation of option 3 could is to load the data to an SQLite In-Memory
database (see http://www.sqlite.org/inmemorydb.html) in chunks so that every
time the in-memory database is full you open the disk files consecutively
one at a time appending the data to them from the corresponding table in the
memory database. Afterwards the in-memory database is cleared (close the
connection or drop the tables) and the cycle starts over.
This way you will not have to worry about the number of open files in the OS
which could give complications as you write.

You could of course code this this yourself holding the same amount of the
original records in a memory structure directly without using SQLite.
However, I could imagine that it would be faster to implement (and less
error prone) just using the SQLite in-memory database.

Once all 5000 files had been fully written they would then be loaded to the
final SQLite database.

> > Is the primary key an integer so that it in fact is the rowid thus
> > saving some space?
> > (see http://sqlite.org/lang_createtable.html, the section: "ROWIDs and
> > the INTEGER PRIMARY KEY".)
> 
> This certainly worth trying, as long as the performance is acceptable.
> It would be good if I can tell sqlite that there is no duplicate and
missing
> values so it does not have to check.

Well, if you don't have any particular use of a primary key its probably
best to not define any so that no processing time is used on it.
Also, if you do define a column as "INTEGER PRIMARY KEY" it becomes an alias
for the rowid for that record and thus will not be a value that you supply.
The rowid in this case will be frozen for a given record and stay the same
even when a vacuum is done on the database.

> > If there are several calculations to be done for each table it would
> > perhaps be better to work with several threads concentrating on a
> > single table at a time. This would be particularly effective if your
> > cache_size was set large enough for a table to be contained entirely
> > in memory as the threads could share the same connection and (as I
> > understand it) memory cache (see
> http://www.sqlite.org/sharedcache.html).
> 
> I am running SELECT COUNT(*), MAX(QT) FROM TABLE_XX; etc as long as the
> WHERE clause is the same. I am also running queries in multiple threads
> which works well on SSD drive, but not on mechanical drive due to the
> bottleneck on random-access speed. I am not sure if we are talking about
the
> same cache but because the tables are spread sparsely across the database,
> caching more pages did not really help.

What I mean here is that if you have a number of different COUNT, MAX or
other statistical functions to be calculated for example with several
different WHERE clauses (e.g. 10, 20 or 30), it will pay off to focus on one
table at a time performing all the queries (whether consecutively or
concurrently using several threads) as the table will be loaded to the
memory cache the first time it is accessed. This is assuming that a table
contains about a million records and thus can be contained entirely in
memory.
This will work regardless of whether you have organized your database so
that records physically are stored contiguously. Of course, it is still best
to have the data stored contiguously as that will give a huge load
improvement.
If there is only a single SELECT COUNT and MAX statement to be done for the
entire 5000 tables it will not make any difference.

> > One might even consider a different strategy:
> > Do not use the statistical function of SQLite (count, average, max,
> > etc.) but access the records individually in your code. This requires
> > that data are loaded according to the primary key and that the threads
> > accessing them do so in a turn based fashion, using the modulo
> > function to decide which thread gets to handle the record, e.g.:
> 
> If all tables have the same set of item IDs, this can be a really good
idea. This
> is unfortunately not the case because each tables have a different set of
IDs,
> despite of 70% to 90% overlapping keys. I even do not know in advance all
> available IDs.

Okay, so each table will have to be handled by itself. 


Best regards,

Frank

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

Re: [sqlite] EXT :Re: Concurrent readonly access to a large database.

[Kit]

2011/10/25 Bo Peng :
> Tables are added in batch and then kept unchanged. I mean, a database
> might have 1000 new tables one day, and 2000 later. All operations are
> on single tables.
>
> Each table is for one 'sample'. All tables have one column for 'item
> id', and optional (zero - perhaps 6) columns for item attributes,
> which can be INT or FLOAT.

Is your 'id' type INTEGER PRIMARY KEY?
Each table in SQLite have a hidden key 'rowid INTEGER PRIMARY KEY'.
You may merge it with 'id'.

> There is no index and no key because I was concerned about insertion
> performance and size of database. My understanding is that index or
> key would not help simple aggregation operations because all records
> will be iterated sequentially.

If you do not need indexes, you can use a simpler solution: CSV.
One sample (now a table) -> one CSV file. Searching will be faster.

> I am running SELECT COUNT(*), MAX(QT) FROM TABLE_XX; etc as long as
> the WHERE clause is the same.

You can create additional indexes after inserting data into a table.
To search for maximum serve well.

If the content of tables also constant, it is possible after a
creating to generate
the aggregated values? For additional search would only use this summary data.
-- 
Kit
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] EXT :Re: Concurrent readonly access to a large database.

[Bo Peng]

> Doing vacuum on a 288 Gb database is probably going to take some time.

I submitted the command yesterday night and nothing seems to be
happening after 8 hours (sqlite3 is running and there is disk
activity, but I do not see a .journal file).

> I wonder if it would be better on just having the data organized before
> loading it, so that the records in each of the 5000 tables would be
> contiguously stored. Of course, that also depends on how much new data will
> be added to the tables.

I have been thinking about this too, but I do not know how to do it
efficiently.

1. Create a temporary database and copy tables one by one to the main
database. This will take a very long time for databases with for
example 5000 tables.

2. Create single-table databases and merge them to the main database.
Because there is a limit on simultaneously attached databases, I might
not be able to create and attach 5000 databases, insert records, and
copy the tables to the main database afterwards. I might be able to
set SQLITE_LIMIT_ATTACHED dynamically, but I do not know how to do
this in Python sqlite3 module.

3. Create 5000 files, insert records to them, and import the files to
the main database. This seems to be the best option although I need to
pay attention to OS limit on opened files.

4. Insert records to some external tools (e.g. a levelDB database, or
even pipe to a sort command) to sort the records before they are
inserted to sqlite.

Any suggest is welcome.

> How many new tables/records will be added per day/month?

Tables are added in batch and then kept unchanged. I mean, a database
might have 1000 new tables one day, and 2000 later. All operations are
on single tables.

> Are records divided amongst the 5000 tables  based on time so that new
> records will go into new tables rather than be inserted evenly among the
> 5000?

No. The records have to be inserted to their associated tables.

> How many fields in the tables (I understand the 5000 tables are identical in
> structure)
> What type of data is it?

Each table is for one 'sample'. All tables have one column for 'item
id', and optional (zero - perhaps 6) columns for item attributes,
which can be INT or FLOAT.

> Are there any indexes besides the primary key?

There is no index and no key because I was concerned about insertion
performance and size of database. My understanding is that index or
key would not help simple aggregation operations because all records
will be iterated sequentially.

> Unless your calculations are always or mostly on the same fields it is
> probably best not to have any indexes.

I agree.

> Are there any redundancy in the data, e.g. character values which could be
> normalized to separate tables using an integer key reference thus reducing
> the size of the data carrying tables. Converting field contents to integer
> or bigint wherever it is possible may give improvements in both size and
> performance.

Unfortunately no, all columns are int or float. Some columns have a
large proportion of NULLs, but I do not know that before all data are
inserted, so I cannot separate them to auxiliary tables.

> Is the primary key an integer so that it in fact is the rowid thus saving
> some space?
> (see http://sqlite.org/lang_createtable.html, the section: "ROWIDs and the
> INTEGER PRIMARY KEY".)

This certainly worth trying, as long as the performance is acceptable.
It would be good if I can tell sqlite that there is no duplicate and
missing values so it does not have to check.

> If there are several calculations to be done for each table it would perhaps
> be better to work with several threads concentrating on a single table at a
> time. This would be particularly effective if your cache_size was set large
> enough for a table to be contained entirely in memory as the threads could
> share the same connection and (as I understand it) memory cache (see
> http://www.sqlite.org/sharedcache.html).

I am running SELECT COUNT(*), MAX(QT) FROM TABLE_XX; etc as long as
the WHERE clause is the same. I am also running queries in multiple
threads which works well on SSD drive, but not on mechanical drive due
to the bottleneck on random-access speed. I am not sure if we are
talking about the same cache but because the tables are spread
sparsely across the database, caching more pages did not really help.

> One might even consider a different strategy:
> Do not use the statistical function of SQLite (count, average, max, etc.)
> but access the records individually in your code. This requires that data
> are loaded according to the primary key and that the threads accessing them
> do so in a turn based fashion, using the modulo function to decide which
> thread gets to handle the record, e.g.:

If all tables have the same set of item IDs, this can be a really good
idea. This is unfortunately not the case because each tables have a
different set of IDs, despite of 70% to 90% overlapping keys. I even
do not know in advance all available IDs.

Re: [sqlite] EXT :Re: Concurrent readonly access to a large database.

[Frank Missel]

> boun...@sqlite.org] On Behalf Of Bo Peng
> 
> I will do this multiple times, with different conditions (e.g. SELECT
> MAX(c) FROM TABLE_X WHRE b > 1.0) so maintaining number of rows would
> not help. I intentionally avoided TRIGGERs because of the large amount
> (billions) of data inserted.
> 
> Other than using a SSD to speed up random access, I hope a VACUUM
> operation would copy tables one by one so content of the tables would not
> scatter around the whole database. If this is the case, disk caching
should
> work much better after VACUUM... fingers crossed.

Doing vacuum on a 288 Gb database is probably going to take some time. I
wonder if it would be better on just having the data organized before
loading it, so that the records in each of the 5000 tables would be
contiguously stored. Of course, that also depends on how much new data will
be added to the tables. 

Having worked with large databases and reading through this mail thread,
some questions and ideas come to mind:

How many new tables/records will be added per day/month?

Are records divided amongst the 5000 tables  based on time so that new
records will go into new tables rather than be inserted evenly among the
5000?

How many fields in the tables (I understand the 5000 tables are identical in
structure) 
What type of data is it?

Are there any indexes besides the primary key?
Unless your calculations are always or mostly on the same fields it is
probably best not to have any indexes.

Are there any redundancy in the data, e.g. character values which could be
normalized to separate tables using an integer key reference thus reducing
the size of the data carrying tables. Converting field contents to integer
or bigint wherever it is possible may give improvements in both size and
performance.

Is the primary key an integer so that it in fact is the rowid thus saving
some space?
(see http://sqlite.org/lang_createtable.html, the section: "ROWIDs and the
INTEGER PRIMARY KEY".)

If there are several calculations to be done for each table it would perhaps
be better to work with several threads concentrating on a single table at a
time. This would be particularly effective if your cache_size was set large
enough for a table to be contained entirely in memory as the threads could
share the same connection and (as I understand it) memory cache (see
http://www.sqlite.org/sharedcache.html) . Hereby the CPU and memory can be
put to good use rather than working with several independent connections
which do not utilize CPU and memory efficiently but just push the disk heads
around.

One might even consider a different strategy:
Do not use the statistical function of SQLite (count, average, max, etc.)
but access the records individually in your code. This requires that data
are loaded according to the primary key and that the threads accessing them
do so in a turn based fashion, using the modulo function to decide which
thread gets to handle the record, e.g.:

KeyThread
1001
1022
1033
1044
1051
1062
Etc.

(Sorry if the explanation is too explicit :-).

Thus you can have an army of threads using CPU and memory most efficiently
as the threads will be handling the same pages of the database which will be
cached in memory. Again, this requires that the records are physically
stored according to the key.
There is perhaps some overhead occurred by accessing the individual records
rather than letting SQLite do the count, max, etc. However, if there are
several calculations to be done on the same data, it may prove more
efficient this way. 

You could even consider having less tables so that the threads can work for
longer without having to switch to the next table. 


/Frank Missel

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

Re: [sqlite] EXT :Re: Concurrent readonly access to a large database.

[Simon Slavin]

On 24 Oct 2011, at 4:13pm, Bo Peng wrote:

>> Can I ask which file-system you were using on the SSD drive when you
>> obtained this result?
> 
> It is ext4 on a 512G SSD on a Ubuntu system.

Wow.  I don't know what hard disk hardware or driver you were using originally, 
but it sucks.  Even for data scattered all over a 288Gig file, that's bad.  
Linux itself is quite efficient at file handling so I'm guessing it's your 
storage device.

Alternatively, you have an incredibly good SSD drive.

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

Re: [sqlite] EXT :Re: Concurrent readonly access to a large database.

[Bo Peng]

> Can I ask which file-system you were using on the SSD drive when you
> obtained this result?

It is ext4 on a 512G SSD on a Ubuntu system.

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

Re: [sqlite] EXT :Re: Concurrent readonly access to a large database.

[Dan Kennedy]

On 10/24/2011 09:20 PM, Bo Peng wrote:

Other than using a SSD to speed up random access, I hope a VACUUM
operation would copy tables one by one so content of the tables would
not scatter around the whole database. If this is the case, disk
caching should work much better after VACUUM... fingers crossed.


VACUUM will defragment, too.  Unless your free space is fragmented.  So yes, 
probably a good move.


Dear all,

I moved the database to another (faster) machine with a SSD and a
regular HD. The performance of the query improved dramatically on the
SSD drive. More specifically, the time to sequentially execute 'select
count(*) from table_XX' on two tables took 17s instead of 7m, and
running the query concurrently on two and four tables took the same 9s
instead of 5m and 13m before. This firmly proved that random disk
access speed was the bottleneck.


Can I ask which file-system you were using on the SSD drive when you
obtained this result?

Thanks,
Dan.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] EXT :Re: Concurrent readonly access to a large database.

[Bo Peng]

>> Other than using a SSD to speed up random access, I hope a VACUUM
>> operation would copy tables one by one so content of the tables would
>> not scatter around the whole database. If this is the case, disk
>> caching should work much better after VACUUM... fingers crossed.
>
> VACUUM will defragment, too.  Unless your free space is fragmented.  So yes, 
> probably a good move.

Dear all,

I moved the database to another (faster) machine with a SSD and a
regular HD. The performance of the query improved dramatically on the
SSD drive. More specifically, the time to sequentially execute 'select
count(*) from table_XX' on two tables took 17s instead of 7m, and
running the query concurrently on two and four tables took the same 9s
instead of 5m and 13m before. This firmly proved that random disk
access speed was the bottleneck.

Because the vacuum operation does not provide a progress bar, I wrote
a script to copy tables one by one to another database on the regular
HD. The time to count the number of rows for a table decreased from
about 4m to within a minute (53s). Disk access is still a bottleneck
but this is already far better than before.

In summary, the problem with my database was that, because all tables
were created at the same time and filled evenly, records of the tables
were spread all over the 288G database. It was very slow to read
pieces of a table from a regular HD to run a query. Copying the
database to a SSD driver with much faster random access speed, or
copying all tables one by one to a new database dramatically improved
the query performance.

Many thanks again for all the help from the list,
Bo
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] EXT :Re: Concurrent readonly access to a large database.

[Simon Slavin]

On 23 Oct 2011, at 4:13pm, Bo Peng wrote:

> Other than using a SSD to speed up random access, I hope a VACUUM
> operation would copy tables one by one so content of the tables would
> not scatter around the whole database. If this is the case, disk
> caching should work much better after VACUUM... fingers crossed.

VACUUM will defragment, too.  Unless your free space is fragmented.  So yes, 
probably a good move.

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

Re: [sqlite] EXT :Re: Concurrent readonly access to a large database.

[Bo Peng]

On Sun, Oct 23, 2011 at 8:57 AM, Simon Slavin  wrote:
> It seems that this was the first problem he found with the way he arranged 
> this database.  But our solution to it would be different depending on 
> whether he wanted to do this just the once, or it was a regular requirement. 
> With this structure I might use TRIGGERs to keep track of the number of rows 
> in each table.  But Bo might want more than count(*).  But TRIGGERs could be 
> used to keep track of a total too.

I will do this multiple times, with different conditions (e.g. SELECT
MAX(c) FROM TABLE_X WHRE b > 1.0) so maintaining number of rows would
not help. I intentionally avoided TRIGGERs because of the large amount
(billions) of data inserted.

Other than using a SSD to speed up random access, I hope a VACUUM
operation would copy tables one by one so content of the tables would
not scatter around the whole database. If this is the case, disk
caching should work much better after VACUUM... fingers crossed.

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

Re: [sqlite] EXT :Re: Concurrent readonly access to a large database.

[Simon Slavin]

On 23 Oct 2011, at 2:47pm, Bo Peng wrote:

> On Sun, Oct 23, 2011 at 8:12 AM, Black, Michael (IS)
>  wrote:
>> #1 What's the size of your database?
> 
> 288G, 5000 table, each with ~1.4 million records

Worth adding here Bo's original post:

On 22 Oct 2011, at 8:52pm, Bo Peng wrote:

> I needed to get some summary statistics of each table but
> find that it will take days to run 'SELECT count(*) FROM table_XX'
> (XX=1,...,5000) sequentially.

It seems that this was the first problem he found with the way he arranged this 
database.  But our solution to it would be different depending on whether he 
wanted to do this just the once, or it was a regular requirement.

With this structure I might use TRIGGERs to keep track of the number of rows in 
each table.  But Bo might want more than count(*).  But TRIGGERs could be used 
to keep track of a total too.

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

Re: [sqlite] EXT :Re: Concurrent readonly access to a large database.

[Bo Peng]

On Sun, Oct 23, 2011 at 8:12 AM, Black, Michael (IS)
 wrote:
> #1 What's the size of your database?

288G, 5000 table, each with ~1.4 million records

> #2 What's your cache_size setting?

default

> #3 How are you loading the data?  Are your table inserts interleaved or by 
> table?  Your best bet would be by interleaving during insert so cache hits 
> would be better.

The tables were created all at once, with records inserted evenly, so
the content of each table is spread all over the 288G place. I believe
this is the reason why cache_size did not help.

> Looks to me like you're getting disk thrashing in test3 and test4 which 
> cache_size could affect also.

I am now thinking that if I vacuum the database so that all tables are
copied one by one. The performance could be increased dramatically
because the content of each table could be read to memory easier.

> And are you running your test twice to bypass the initial cache filling of 
> sqlite?

I ran all the tests on tables that have not been processed (cached).

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

Re: [sqlite] EXT :Re: Concurrent readonly access to a large database.

[Black, Michael (IS)]

#1 What's the size of your database?

#2 What's your cache_size setting?

#3 How are you loading the data?  Are your table inserts interleaved or by 
table?  Your best bet would be by interleaving during insert so cache hits 
would be better.



Looks to me like you're getting disk thrashing in test3 and test4 which 
cache_size could affect also.



And are you running your test twice to bypass the initial cache filling of 
sqlite?





Michael D. Black

Senior Scientist

Advanced Analytics Directorate

Advanced GEOINT Solutions Operating Unit

Northrop Grumman Information Systems


From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of Bo Peng [ben@gmail.com]
Sent: Saturday, October 22, 2011 10:05 PM
To: General Discussion of SQLite Database
Subject: EXT :Re: [sqlite] Concurrent readonly access to a large database.

> It's not only speed in KB/sec that matters. It's also disk system
> usage as reported by iostat. If it's close to 100% then SQLite can't
> do any better.

A sad day.

I copied the database to a faster driver with RAID 0, made another
copy of the database (referred to as DB1), and ran another set of
tests:

test1: two sequential processes of sqlite count(*) table1 and table 2
in DB1 --> 7m15s

test2: two concurrent processes of sqlite count(*) table1 and table2
in DB1 --> 5m22s

test3: four concurrent processes of sqlite count(*) table 1, 2, 3, and
4 in DB1 --> 12m58s

test4: two concurrent processes of sqlite count(*) table1 in DB1, and
table1 in DB2 --> 9m51s.

Although running two or more processes can save some time, the
performance gain is not that big (tests 2 and 3), splitting the
database into several smaller ones would not help either (test 4).

Anyway, the iostat output of my system is

2011 Oct 22 21:16:36,  load: 0.03,  disk_r:   2676 KB,  disk_w:  0 KB

  UIDPID   PPID CMD  DEVICE  MAJ MIN DBYTES
0  0  0  ??   14   8  65536
  503732730 sqlite3  ??   14  14 R  1323008
  503731730 sqlite3  ??   14  14 R  1355776

If I understand correctly, the IO load is only 3% when two sqlite3
processes are running, so perhaps I can still tweak sqlite3 to run
faster. I will also copy the database around and see if other disks
(SSD?), operating system (linux?), and file systems can provide better
performance.

Thanks again for all the help,
Bo
___
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