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

> 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 > >  UID    PID   PPID CMD              DEVICE  MAJ MIN D            BYTES >    0      0      0                  ??       14   8              65536 >  503    732    730 sqlite3

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

On 23 Oct 2011, at 4:05am, Bo Peng wrote: > 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

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

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

On Sat, Oct 22, 2011 at 6:02 PM, Bo Peng wrote: >> You may create multiple threads, but your hard drive only has one set of >> heads. > > I now realize this problem and is moving the data to a faster drive. > However, when copying the data out, the activity monitor reports >

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

On 22 Oct 2011, at 10:57pm, Igor Tandetnik wrote: > You may create multiple threads, but your hard drive only has one set of > heads. Right. I use lots of Macs. I also think you're I/O bound. I think you're just better resign yourself to an overnight run. If you're going to do this a lot,

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

> You may create multiple threads, but your hard drive only has one set of > heads. I now realize this problem and is moving the data to a faster drive. However, when copying the data out, the activity monitor reports 40MB/sec read speed. I admit this is not a fast drive, but comparing to the

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

Bo Peng wrote: > I have a database with about 5000 tables each with more than 1 million > records. 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. I therefore created

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

On Sat, Oct 22, 2011 at 4:18 PM, Pavel Ivanov wrote: >> Using three tables (e.g. test.sh 111 112 113), the first command takes >> 13m3s, the second command takes 12m45s. I am wondering if there is any >> magic to make the second script finish in 5 minutes by executing the >>

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

> Using three tables (e.g. test.sh 111 112 113), the first command takes > 13m3s, the second command takes 12m45s. I am wondering if there is any > magic to make the second script finish in 5 minutes by executing the > query in parallel ... Try to execute "pragma cache_size = 100" before

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

> > Multithreaded mode allows SQLite to be accessed via multiple threads as long > as threads don't shared connection handles.  This is the what's sometimes > called the apartment model of multithreading. Thank you very much for your quick reply. Is there a way to enable multi-thread mode from

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

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 22/10/11 12:52, Bo Peng wrote: > I mean, is it possible, in theory, to read a sqlite database from > multiple threads/processes each with performance comparable to a > single thread/process? Yes. The details are explained here:

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

; Subject: [sqlite] Concurrent readonly access to a large database. > > Dear all, > > I have a database with about 5000 tables each with more than 1 million > records. I needed to get some summary statistics of each table but > find that it will take days to run 'SELECT count(*) FROM tabl

[sqlite] Concurrent readonly access to a large database.

Dear all, I have a database with about 5000 tables each with more than 1 million records. 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. I therefore created 10 threads, each having its own