WARNING: This server is unstable and will be retired in the next days.
If you want to keep this forum available, please request immediately a migration
on the Nabble Support forum.
Forums that don't receive any migration request will be deleted forever.
> I'm having trouble with one table in my database.
> When I build my database from scratch using millions of inserts, one
> table causes problems. Inserts get slower and slower. I have about
> 830,000 inserts for that table. It gets to 300,000 pretty fast, but
> then it gets slower and slower, and eventually it will only do a few
> inserts per second, and I then I have to kill sqlite3 as it will run
> for hours if not days. The -echo option reveals that it gets slower
> and slower. sqlite3 runs at 100% CPU.
> I create other similar tables with 830,000 inserts the same way, but
> inserts into them don't slow down.
> The table in question is:
> CREATE TABLE Voksenlia2 (
> temp_in REAL,
> pressure REAL,
> rh_in REAL,
> temp_in_2 REAL,
> temp_in_3 REAL,
> temp_in_4 REAL,
> temp_in_5 REAL,
> temp_ground_0cm REAL,
> temp_ground_10cm REAL,
> temp_ground_20cm REAL,
> temp_ground_50cm REAL,
> radiation INTEGER,
> radiation_2 INTEGER,
> uv REAL,
> temp_uv REAL,
> unix_time INTEGER, PRIMARY KEY (unix_time)
> The commands start this way:
> insert into Voksenlia2 values(25.010, 1011.260, null, null, null, null, null, null, null, null, null, null, null, null, null, 1072915200);
> insert into Voksenlia2 values(25.010, 1011.260, null, null, null, null, null, null, null, null, null, null, null, null, null, 1075063152);
> and eventually end in a "commit". So only one transaction.
> I've tried:
> * PRAGMA synchronous=OFF.
> * Create the database file in /dev/shm/ (RAM disk).
> * Break up the inserts into several transactions.
> * Create and fill this table before everything else.
> * Upgrade from version 3.4.2 to 3.7.10.
> Nothing has made any difference. Any ideas? Anything I could try or
> any ways to debug this?
It's the Primary Key that you're using cause for every INSERT it is
checked if unix_time is already present in a record.
So the question is if you really need unix_time as a PK