On 24 Aug 2015, at 2:48am, Simon Slavin wrote:
> My suspicion here is that there's a design fault in ZFS.
To correct myself here, what I meant to write was that there was a fault in the
implementation of ZFS that Paolo is using, not in the basic design of ZFS
itself.
Simon.
On 24 Aug 2015, at 2:32am, Roger Binns wrote:
> On 08/19/2015 05:56 PM, Paolo Bolzoni wrote:
>> I left running the pragma quick check during the night and finished
>> in 2 hours and 46 minutes, so it is about 8 times slower than in
>> ext4. Zfs is an advanced filesystem plenty of features, but
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
On 08/23/2015 06:48 PM, Simon Slavin wrote:
> Paolo is using Linux which does not do read-ahead optimization like
> some versions of Windows. Therefore if he really is using an SSD
> then fragmentation is not an issue.
You are confusing things. The
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
On 08/19/2015 05:56 PM, Paolo Bolzoni wrote:
> I left running the pragma quick check during the night and finished
> in 2 hours and 46 minutes, so it is about 8 times slower than in
> ext4. Zfs is an advanced filesystem plenty of features, but this
>
On Wed, Aug 19, 2015 at 6:53 PM, Simon Slavin wrote:
> On 19 Aug 2015, at 10:46am, Paolo Bolzoni
> wrote:
> Just by itself, the above information is significant to the SQLite team.
> Perhaps when you have had a chance to confirm it a new thread can be started
> called 'SQLite database on
On Wed, Aug 19, 2015 at 11:44 AM, Simon Slavin wrote:
> Hmm. Would it be possible to format an external drive in ZFS and try the
> operations on files stored on that ?
As you might have guessed from the timezone I am not at home atm, so I
do not have spare external disks.
However, I do have
I see. Thanks nameless person known as sqlite-mail (npkasm for short),
what you say makes sense.
However it does not explain why the pragma checks are so slow.
Anyhow, npkasm, I will keep in mind for the future. Good point indeed.
On Wed, Aug 19, 2015 at 3:59 PM, sqlite-mail
wrote:
> Hello !
>
>
> is there any target date when the preRelease branch gets over to a actual
> release?
>
> Is a really hard show stopper for our development at the moment. We checked
> everything for compatibility before merge your current trunk to Visual
> Studio 2015 and we forget about the SQLite design
Hi,
is there any target date when the preRelease branch gets over to a actual
release?
Is a really hard show stopper for our development at the moment. We checked
everything for compatibility before merge your current trunk to Visual Studio
2015 and we forget about the SQLite design tool. :(
@Simon
I tried zpool scrub on both my disks and it returned nothing, I also
tried executing stress[1] on the disk and no error appeared in the log
or in stress itself.
However, coping the sqlite db on an external disk connected via usb3
and formatted with Ntfs actually does the pragma quick_check
On 19 Aug 2015, at 10:46am, Paolo Bolzoni
wrote:
> As you might have guessed from the timezone I am not at home atm, so I
> do not have spare external disks.
> However, I do have an expendable 16BG usb stick so I tried on that.
>
> First I formatted it using zfs and I did the Pragma
Hello !
The problem with foreign keys most of the time is not the the referenced
table/field (normally primary key that do no need extra index) but the
dependent table/field when they do not have a proper index, any time you
update/delete a record on the referenced table a linear scan is
On 19 Aug 2015, at 3:27am, Paolo Bolzoni
wrote:
> coping the sqlite db on an external disk connected via usb3
> and formatted with Ntfs actually does the pragma quick_check in little
> more than 20 seconds and pragma integrity_check in 5 minutes.
Those times are completely typical for SQLite.
It really seems something strange happens at filesystem level.
This is a simple copy of slightly less than 1gb. It needs 9 seconds
including sync.
% date && sudo rsync -Pr italy-latest.osm.pbf / && sync && date
Tue Aug 18 19:22:23 JST 2015
sending incremental file list
italy-latest.osm.pbf
It seems my system CPUs overheats regularly and so the system throttle
the CPU speed.
It is definitely not good, I need to disassemble my laptop, clean
everything, and probably replace the conductive cpu paste.
However, this does not explain anything. sqlite3 appears IO bound, not
CPU bound: in
I think the ATTACH slowness was a misunderstanding caused by the
optimization as it does not happen anymore now I compiled with -O0.
The long time was actually deleting the table from the input db, this
explains also why sqlite was making the journal file for the
operation.
I am aware it sounds
Just another guess:
Have you tried to increase the page chache drastically ?
I can remeber that "PRAGMA quick_check" is pretty slow
for bigger DBs without an increased page cache.
Maybe something like:
PRAGMA cache_size=50;
PRAGMA quick_check;
Marcus
Am 18.08.2015 um 12:38 schrieb Paolo
[sqlite] ATTACH DATABASE
>statement speed
>
> It really seems something strange happens at filesystem level.
>
> This is a simple copy of slightly less than 1gb. It needs 9 seconds
> including sync.
> % date && sudo rsync -Pr italy-latest.osm.pbf / && sync &&a
I think I got it. As often when something is really weird, it is was
not what I expected.
In the input and output database I had a table of the same name and using:
DROP TABLE IF EXISTS WaysNodes;
sqlite3 was actually deleting the table of the input db, this was
unexpected as I thought that
On 18 Aug 2015, at 11:38am, Paolo Bolzoni
wrote:
> but after some seconds it drops terribly to less than 10MB/s
This, along with some information from your previous posts, all goes to suggest
you have a hardware problem of some kind. My guess is that your hard disk is
becoming faulty,
On 18 Aug 2015, at 7:30am, Paolo Bolzoni
wrote:
> Any other idea of what can I try? Perhaps my filesystem is misconfigured?
The long time you quote is not standard for SQLite and I don't think anyone can
help you solve it by knowing picky details of SQLite. I'm even surprised that
it
On 18 Aug 2015, at 4:14am, Paolo Bolzoni
wrote:
> In the input and output database I had a table of the same name and using:
>
> DROP TABLE IF EXISTS WaysNodes;
>
> sqlite3 was actually deleting the table of the input db, this was
> unexpected as I thought that
> without any prefix it
The pragma integrity_check is still running... Maybe my disk sucks for
some reason?
The output of iostat looks normal to me though..
$ iostat -dmN encplate 1 5
Linux 4.1.4-1-ARCH (slyrogue) 08/17/2015 _x86_64_(8 CPU)
Device:tpsMB_read/sMB_wrtn/sMB_read
Dear list,
I have a program that materialize the subset of a database in a second
database for future use.
To do so I create the database object on the OUTPUT database, create
tables, create the indexes, and vacuum it just in the case I reused an
old file.
After I attach the INPUT db and I copy
On 17 August 2015 at 14:52, Paolo Bolzoni
wrote:
> After I attach the INPUT db and I copy the useful lines.
>
> The input db is about 13GB, so not really large, however the step on
> this sql statement (where ? is of course binded to the db name)
>
> ATTACH DATABASE ? AS indb;
>
> requires
On 17 Aug 2015, at 9:22am, Paolo Bolzoni
wrote:
> The pragma integrity_check is still running... Maybe my disk sucks for
> some reason?
I wonder whether the hard disk is faulty or the file is on a bad sector.
If the other tests show nothing, can you duplicate the input database file ?
Do
On 17 Aug 2015, at 8:17am, Simon Slavin wrote:
> Also, out of interest, can you run "PRAGMA integrity_check" on all the
> database files involved ?
Also out of interest, instead of just ATTACHing the input database,
1) Close the output database
2) Reopen the output database
3) ATTACH the
On 17 Aug 2015, at 7:52am, Paolo Bolzoni
wrote:
> I have a program that materialize the subset of a database in a second
> database for future use.
>
> To do so I create the database object on the OUTPUT database, create
> tables, create the indexes, and vacuum it just in the case I reused an
28 matches
Mail list logo