2016-04-17 10:13 GMT+02:00 Rob Willett <rob.sqlite at robertwillett.com>:
> I do not have the whole thread for what you reported but I did read
> somebody ask if you have put your inserts between a BEGIN/END transaction.
> That will make a massive difference to your speed. Also I?m unclear as to
> how Java fits in all of this. Perhaps you gave a better indication further
> up the thread I do not have.
>
?Oops, the I did not read good enough. :-(
I start with a:
?
?conn.setAutoCommit(false);
but that is not the same? It does make a big difference. Without it 1E4
records take 25 minutest, with it it takes less as a second. I did not even
try 1E5 without it, but with it, it takes 2-3 seconds.
It is at least partly to do with SQLite. When I had the load of 15 I was
using the table definition:
CREATE TABLE testUniqueUUID (
UUID text,
PRIMARY KEY(UUID)
);
I am running it again with the table definition:
CREATE TABLE testUniqueUUID (
UUID blob,
PRIMARY KEY(UUID)
CHECK(TYPEOF(UUID) = 'blob' AND
LENGTH(UUID) = 16 AND
SUBSTR(HEX(UUID), 13, 1) == '4' AND
SUBSTR(HEX(UUID), 17, 1) IN ('8', '9', 'A', 'B')
)
);
and the load is now around ten. Still high, but it does not cripple my
system.?
> There are a lot of very, very talented people on the list, I am an not one
> of them :), I would strongly urge you to provide more information rather
> than less, e.g. you provide summary information for Java by RSS. It would
> be a lot more useful to have the full raw data so people can look for
> quirks and anomalies rather than simply you interpreting it for us. I am
> NOT the best person to talk about SQLite performance, however I am more
> familiar with Unix/Linux performance and administration.
>
?Which information would be useful? Ihis is the /proc/PID/status
information:
Name: java
State: S (sleeping)
Tgid: 26455
Ngid: 0
Pid: 26455
PPid: 28670
TracerPid: 0
Uid: 1000 1000 1000 1000
Gid: 100 100 100 100
FDSize: 256
Groups: 7 33 100 486 498 1000
NStgid: 26455
NSpid: 26455
NSpgid: 26455
NSsid: 28670
VmPeak: 6935564 kB
VmSize: 6873208 kB
VmLck: 0 kB
VmPin: 0 kB
VmHWM: 111664 kB
VmRSS: 104784 kB
VmData: 6828392 kB
VmStk: 140 kB
VmExe: 4 kB
VmLib: 17148 kB
VmPTE: 492 kB
VmPMD: 32 kB
VmSwap: 0 kB
Threads: 20
SigQ: 1/63646
SigPnd: 0000000000000000
ShdPnd: 0000000000000000
SigBlk: 0000000000000000
SigIgn: 0000000000000000
SigCgt: 0000000181005ccf
CapInh: 0000000000000000
CapPrm: 0000000000000000
CapEff: 0000000000000000
CapBnd: 0000003fffffffff
Seccomp: 0
Cpus_allowed: ff
Cpus_allowed_list: 0-7
Mems_allowed:
00000000,00000000,00000000,00000000,00000000,00000000,00000000,00000000,00000000,00000000,00000000,00000000,00000000,00000000,00000000,00000001
Mems_allowed_list: 0
voluntary_ctxt_switches: 6
nonvoluntary_ctxt_switches: 1
To my untrained eye VmPeak and VmSize look high.
?
> Since most people here do not have the same issues as you and I have no
> doubt they are hitting Sqlite far harder than I or you can, I would look at
> what you are doing that is different and Java keeps jumping out at me.
?That was also my idea. I only wanted to confirm it was not a SQLite
problem. But looking at changing the UUID from text to blob makes such a
big difference, I suspect it is partly a SQLite problem.
?
> I have run (though not programmed as I?m not a Java developer) a number of
> programs running Java as the backend onto large DB2 databases and the first
> thing the developers would do would be to increase the amount of memory
> that Java is allowed to allocate and use. They would tend to throw 4GB at
> each JVM or more if the sysadmins would let them.
>
?Seeing that ?the program does not use swap, I do not think that the amount
of memory the program has is a problem.
--
Cecil Westerhof
