needs to
decide it can't run a checkpoint (which is believe is what happening).
If would also like to know if killing (possibly with -9) a process
with an open handle might accidentally leave the DB locked.
Thank you,
Gerlando
On Sun, Mar 24, 2019 at 12:56 PM Larry Brasfield
wrote:
>
> Ge
Hi,
I'm using SQLite in WAL mode in the following scenario:
- One writer which continuously writes new rows and deletes older ones
- Multiple readers performing queries
Writer and readers are running each in a separate process.
The (Inactive) Journal Size limit is set to 100MB by pragmas and in
Hi Ryan,
first of all thank you for your patience and contribution.
[]
>
> Add to that the fact that an SQLite TABLE is, in and of itself, nothing
> less than a covering Index with row_id as a key (or a custom key for
> WITHOUT ROWID tables), and as such it is a rather good Index and a
>
Hi Rowan,
thank you for your kind support. You grasped the essence of my questions.
:-)
I'm using SQLite 3.25.00.
Thank you,
Gerlando
On Tue, Feb 5, 2019 at 9:59 AM Rowan Worth wrote:
> On Tue, 5 Feb 2019 at 16:06, Simon Slavin wrote:
>
> > On 5 Feb 2019, at 8:00am, Gerl
Thank you for your explanations guys. All this makes perfect sense.
I still can't find a solution to my problem though -- write a query that is
guaranteed to return sorted results, in some optimal way.
Any suggestion welcome.
Thank you,
Gerlando
Il lun 4 feb 2019, 22:24 Simon Slavin ha
On Mon, Feb 4, 2019 at 4:52 PM Simon Slavin wrote:
> On 4 Feb 2019, at 1:55pm, Gerlando Falauto
> wrote:
>
> > Or (most likely) my understanding of how data is retrieved is plain
> wrong...
>
> Or your understanding how the current version of SQLite is correct, but a
&
Hi Luuk,
It says:
>
> SQLite *attempts* to use an index to satisfy the ORDER BY clause of a
> query when possible
>
>
> To be (abolutely!) SURE results are in the correct order, you need an
> ORDER BY.
>
No questioning about that. ORDER BY *must* be there in order to get the
results correctly
understanding of how data is retrieved is plain wrong...
Thank you!
Gerlando
On Mon, Feb 4, 2019 at 1:26 PM Luuk wrote:
>
> On 3-2-2019 23:29, Gerlando Falauto wrote:
> > IMHO, adding the ORDER BY clause to query 1) above (i.e. query 2) should
> > ideally yield the exa
gt; WHERE ts > 1
>AND ts < 10
> ORDER BY 1,2,3;
>
>
> ---
> The fact that there's a Highway to Hell but only a Stairway to Heaven says
> a lot about anticipated traffic volume.
>
> >-Original Message-
> >From: sqlite-users [mailto:sqlite-users-
> >boun.
; ORDER BY 1,2,3;
>
>
> ---
> The fact that there's a Highway to Hell but only a Stairway to Heaven says
> a lot about anticipated traffic volume.
>
> >-Original Message-
> >From: sqlite-users [mailto:sqlite-users-
> >boun...@mailinglists.sqlite.org] On Behalf Of
Simon, Tim,
Il sab 2 feb 2019, 23:40 Simon Slavin ha scritto:
> On 2 Feb 2019, at 10:19pm, Gerlando Falauto
> wrote:
>
> > Results should be naturally ordered by source1, source2,ts.
>
> [Sorry, I missed this the first time. Thanks, Tim.]
>
> Sorry, no. You're m
Hi,
it's me again, struggling with indices once again.
What I'm now trying to do is filter on source1 and by range of timestamp.
Results should be naturally ordered by source1, source2,ts.
1)
SELECT source1, source2, ts, value
FROM rolling
WHERE source1 = 'aaa'
AND ts > 1 AND ts < 10;
QUERY
YES! Thank you!
Many thanks for the ".eqp full" tip also, that really explains a lot
(though I don't really understand any of it yet).
Have a great day!
Gerlando
On Mon, Jan 28, 2019 at 6:50 AM Keith Medcalf wrote:
>
> Do you perhaps want this:
>
> select source1,
>source2,
>(
Hi,
I have a database table looking like this:
CREATE TABLE `rolling` (
`source1`TEXT NOT NULL,
`source2`TEXT NOT NULL,
`ts`INTEGER NOT NULL,
`value`TEXT
);
CREATE INDEX `sources` ON `rolling` (
`source1`,
`source2`,
`ts`
);
INSERT INTO rolling
nd it's finally telling me with the
ANY() clauses.
However, I still can't seem to get the min(ts) query to be optimized.
Perhaps I need some sub-query?
Any ideas?
Thank you!
Gerlando
On Thu, Jan 24, 2019 at 4:47 PM Richard Hipp wrote:
> On 1/24/19, Gerlando Falauto wrote:
> > What
Thank you!
There's one thing I don't understand though:
What is the purpose of ANALYZE sqlite_master; ?
Thank you!
Gerlando
On Thu, Jan 24, 2019 at 4:07 PM Richard Hipp wrote:
> On 1/24/19, Gerlando Falauto wrote:
> > Hi,
> >
> > I read about the skip-scan optimi
Hi,
I read about the skip-scan optimization:
https://www.sqlite.org/optoverview.html#skipscan
is there a way to check whether it is being used for a given query, or not?
Explain query plan does not seem to give any insight...
I tried both before and after running ANALYZE; / DROP TABLE
abase to reach
> steady-state ... to see if as it gets bigger the pagechange set per
> checkpoint increases much.
>
> ---
> The fact that there's a Highway to Hell but only a Stairway to Heaven says
> a lot about anticipated traffic volume.
>
> >-Original Message-
> >From:
this flight recorder mode
of mine).
I guess there might be obvious reasons for NOT doing so, which I however
fail to see at present -- any hints would be highly appreciated.
Thanks!
Gerlando
On Mon, Oct 29, 2018 at 9:50 PM Gerlando Falauto
wrote:
> On Mon, Oct 29, 2018 at 6:38 PM Keith Medcalf wr
On Tue, Oct 30, 2018 at 4:58 AM Keith Medcalf wrote:
>
> If you don't mind me asking, what sort of data are you collecting?
> Are you the master (ie, scanning) or a slave (getting async data pushed to
> you).
> Are you "compressing" the returned data (storing only changes exceeding
> the
On Mon, Oct 29, 2018 at 6:38 PM Keith Medcalf wrote:
>
> See the ext/misc/unionvtab.c extension for "reading" a bunch of databases
> as if they were a single database.
>
> https://www.sqlite.org/src/artifact/0b3173f69b8899da
Cool, indeed.
I also had a look at the CSV file extension:
On Mon, Oct 29, 2018 at 2:28 PM Richard Hipp wrote:
> On 10/28/18, Gerlando Falauto wrote:
> >
> > This is the current approach:
> > - There is just one single-threaded writer process, which also
> periodically
> > issues DELETE statements to remove older data.
Hi Keith,
thanks for your feedback and your generous insights.
Bottom line is, it's a cheap device (MLC with 3000 P/E cycles) and there's
nothing I can do about it, I just have to make the best of it.
If there's any way to figure out how "crappy" it is, I'd be interested in
hearing that.
>If
Hi Simon,
On Mon, Oct 29, 2018 at 1:30 AM Simon Slavin wrote:
> On 28 Oct 2018, at 11:47pm, Gerlando Falauto
> wrote:
>
> > On an SSD, I have no clue what's going on -- will writing a 2 MB block
> one page at a time be much worse than writing an entire block at once?
>
Hi Keith,
thanks for the heads up on thermal stress.
The next factor is the internal write multiplication factor. Lets say you
> have a device which is divided into 2 MB blocks. And you update 1 sector
> (512 bytes) somewhere in this block. The device must (a) read out the
> entire 2MB block
On Sun, Oct 28, 2018 at 6:18 PM Simon Slavin wrote:
> On 28 Oct 2018, at 2:06pm, Gerlando Falauto
> wrote:
>
> > There are two (apparently) opposing requirements:
> > - in case of a power outage, all data collected up to at most N seconds
> > prior to the power
Hi everyone,
as I mentioned a few months ago, I'm using SQLite to continuously log data
collected from several sources on a linux system:
This is the current approach:
- There is just one single-threaded writer process, which also periodically
issues DELETE statements to remove older data.
- In
On Tue, Aug 7, 2018 at 3:28 PM, Simon Slavin wrote:
> On 7 Aug 2018, at 1:54pm, Gerlando Falauto
> wrote:
>
> > I just realized I'm using default settings... perhaps I should use WAL
> mode
> > instead?
>
> How important is it to you that SELECT gets up-to-d
I just realized I'm using default settings... perhaps I should use WAL mode
instead?
Thanks,
Gerlando
On Tue, Aug 7, 2018 at 2:22 PM, Gerlando Falauto wrote:
> Hi Simon,
> than you for your answer.
>
> On Tue, Aug 7, 2018 at 2:09 PM, Simon Slavin wrote:
>
>> On 7
Hi Simon,
than you for your answer.
On Tue, Aug 7, 2018 at 2:09 PM, Simon Slavin wrote:
> On 7 Aug 2018, at 12:55pm, Gerlando Falauto
> wrote:
>
> > I'm trying to implement a logging system based on SQLite, using python3
> > package apsw.
> > There's one process con
Hi,
I'm trying to implement a logging system based on SQLite, using python3
package apsw.
There's one process constantly writing and another one reading.
From time to time I get an exception from the writer, complaining the
database is locked.
I'm pretty sure there's no other process writing, and
On Mon, Jul 30, 2018 at 9:42 PM, David Raymond
wrote:
> Doesn't sound quite right to me.
>
> No matter the index you have to search through it to find the spot to do
> the insert. Both are going to do that search only once. An insert on a
> unique index isn't going to search through it for
On Mon, Jul 30, 2018 at 9:19 PM, Keith Medcalf wrote:
>
> >> A query doing a single insert of a few bytes with no Indexes, no
> >> triggers, no functions will be stupendously fast, whereas any
> >> increase in one or more of the above will slow things down.
> >> How much exactly is something you
On Mon, Jul 30, 2018 at 1:58 AM, R Smith wrote:
> On 2018/07/30 12:39 AM, Gerlando Falauto wrote:
>
>>
>> The question that needs to be answered specifically is: How many data
>>> input sources are there? as in how many Processes will attempt to write
>>> t
Hi Ryan,
thank you for your reply.
>I think you are perhaps missing a core idea here - the only use-case
>that requires sharding is where you have very high write-concurrency
>from multiple sources, and even then, the sharding, in order to have any
>helpful effect, needs to distinguish "write
Hi,
I'm totally new to sqlite and I'd like to use it for some logging
application on an embedded
linux-based device. Data comes from multiple (~10), similar sources at a
steady rate.
The rolling data set would be in the size of 20 GB. Such an amount of
storage would suffice to retain data from
36 matches
Mail list logo
