Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Why sqlite3 temp files were renamed 'etilqs_*' (2006)

github.com

Post reply on HN
Print view View original

31–40 of 146 posts

Re: Why sqlite3 temp files were renamed 'etilqs_*' (2006)

#31

Post by belthesar »

endisneigh wrote: post #4

Somewhat related - I’m very very curious to hear a detailed account of someone who uses SQLite for a production app with high traffic. For the embedded use case I think it’s a slam dunk, but there are many interesting use cases for server side but they all seem to be toyish. The locking behavior of SQLite is somewhat problematic unless you use WAL and even then not perfect

A super smart guy on my team at a previous job replaced around $100,000 of server and SAN hardware used for a(n) (admittedly incredibly absurdly designed, well before our time) analytics system built using MySQL filtered replication triggering stored procedures in this magical Rube Goldberg-ian dystopia with a 3 node Flask app running with 2 cores and 4 GB of RAM each performing the analytics work JIT. The app would use in-memory SQLite3 tables to perform the same work as the stored procedure operations, and cost about 50ms of extra time per request for a feature of the app that was rarely used.

Admittedly, not high traffic like you asked, but one of my favorite uses of SQLite hands down.

Re: Why sqlite3 temp files were renamed 'etilqs_*' (2006)

#32

Post by djbusby »

codedokode wrote: post #29
lopkeny12ko wrote: post #27

Earlier quoted context omitted.

Did you even open the link? Human readability was not a goal, in fact quite the opposite.

I meant use the name of the program that embeds SQLite, for example, McAfee, Google Chrome etc. This way the user could easily understand which program has created the files.

How does it get that in a cross platform way? Or want if the program name has exotic characters?

Re: Why sqlite3 temp files were renamed 'etilqs_*' (2006)

#33

Post by cwillu »

The relevant snippet:

   /*
   ** Temporary files are named starting with this prefix followed by 16 random
   ** alphanumeric characters, and no file extension. They are stored in the
   ** OS's standard temporary file directory, and are deleted prior to exit.
   ** If sqlite is being embedded in another program, you may wish to change the
   ** prefix to reflect your program's name, so that if your program exits
   ** prematurely, old temporary files can be easily identified. This can be done
   ** using -DSQLITE_TEMP_FILE_PREFIX=myprefix_ on the compiler command line.
   **
   ** 2006-10-31:  The default prefix used to be "sqlite_".  But then
   ** Mcafee started using SQLite in their anti-virus product and it
   ** started putting files with the "sqlite" name in the c:/temp folder.
   ** This annoyed many windows users.  Those users would then do a 
   ** Google search for "sqlite", find the telephone numbers of the
   ** developers and call to wake them up at night and complain.
   ** For this reason, the default name prefix is changed to be "sqlite" 
   ** spelled backwards.  So the temp files are still identified, but
   ** anybody smart enough to figure out the code is also likely smart
   ** enough to know that calling the developer will not help get rid
   ** of the file.
   */

Re: Why sqlite3 temp files were renamed 'etilqs_*' (2006)

#34

Post by storyinmemo »

hakfoo wrote: post #24
endisneigh wrote: post #4

Somewhat related - I’m very very curious to hear a detailed account of someone who uses SQLite for a production app with high traffic. For the embedded use case I think it’s a slam dunk, but there are many interesting use cases for server side but they all seem to be toyish. The locking behavior of SQLite is somewhat problematic unless you use WAL and even then not perfect

I've been pushing to try SQLite as a "sacrificial memozation." Basically we have two tasks separated by 5-10 days. When we do the first task, we calculate a bunch of information as a "side effect". At the second task, we don't have that information and trying to reconstruct it without the original context is very slow, because a lot of it is dependent on temporal state-- what was happening 5-10 days ago. The other us…

You should look into temporal.io. Your workflow problem is highly suited to it.

Re: Why sqlite3 temp files were renamed 'etilqs_*' (2006)

#35

Post by remram »

deaddodo wrote: post #25
sltkr wrote: post #13

Earlier quoted context omitted.

This isn't remotely comparable. Those .DS_Store files are created in arbitrary directories by the Apple file manager or something. The SQLite temp files are created in the OS-specific temporary directory (e.g. C:/Users/username/AppData/Local/Temp or whatever on Windows) which is specifically intended for that purpose. SQLite isn't doing anything wrong; that's where it's supposed to store temporary data that doesn't f…

> Those .DS_Store files are created in arbitrary directories by the Apple file manager or something. .DS_Store files come from Apple's file systems containing a separate data and resource fork. APFS can natively store the contents, but for foreign file systems/network shares, a .DS_Store file is created to store those attributes.

I never knew that. How come they end up in Git repositories then? They shouldn't be visible to Git running on a native Mac filesystem?

Re: Why sqlite3 temp files were renamed 'etilqs_*' (2006)

#36

Post by endisneigh »

bob1029 wrote: post #20
endisneigh wrote: post #4

Somewhat related - I’m very very curious to hear a detailed account of someone who uses SQLite for a production app with high traffic. For the embedded use case I think it’s a slam dunk, but there are many interesting use cases for server side but they all seem to be toyish. The locking behavior of SQLite is somewhat problematic unless you use WAL and even then not perfect

> there are many interesting use cases for server side but they all seem to be toyish. > The locking behavior of SQLite is somewhat problematic unless you use WAL and even then not perfect SQLite with WAL and synchronous configured appropriately will insert a row in ~50uS on NVMe hardware. This is completely serialized throughput (i.e. the highest isolation level available in SQL Server, et. al.). At scale, this can…

Is your application a networked one?

Re: Why sqlite3 temp files were renamed 'etilqs_*' (2006)

#37

Post by bob1029 »

endisneigh wrote: post #36
bob1029 wrote: post #20

Earlier quoted context omitted.

> there are many interesting use cases for server side but they all seem to be toyish. > The locking behavior of SQLite is somewhat problematic unless you use WAL and even then not perfect SQLite with WAL and synchronous configured appropriately will insert a row in ~50uS on NVMe hardware. This is completely serialized throughput (i.e. the highest isolation level available in SQL Server, et. al.). At scale, this can…

Is your application a networked one?

It is a web app that has exclusive ownership over its SQLite databases. Exactly one SQLiteConnection instance per for the lifetime of the application.

Re: Why sqlite3 temp files were renamed 'etilqs_*' (2006)

#38

Post by chasil »

sedatk wrote: post #14
pronoiac wrote: post #5

Looks like the line numbers were lost: https://github.com/mackyle/sqlite/blob/18cf47156abe94255ae14... It's because McAfee started using SQLite, angry users would stumble upon the files, do a minimum of searching or thinking, and be furious at SQLite developers.

I wonder why users were angry about some files in their temp folder. Did McAfee fail to cleanup those files, or were they too big? Edit: more information here: https://www2.sqlite.org/cvstrac/wiki?p=McafeeProblem Apparently, McAfee kept those files locked when it was using them, so the files couldn't be deleted and people got angry that they couldn't clean them up. Sounds like a loud minority to me.

In Linux, these files are unlinked, so they are invisible in the filesystem. It is legal to unlink an active file descriptor, but continue reads/writes to it.

I think that lsof can still see these temporary files; I'm not sure how I first noticed it.

Windows implements a POSIX kernel layer, so perhaps this functionality could be coaxed out of it.

Re: Why sqlite3 temp files were renamed 'etilqs_*' (2006)

#40

Post by arp242 »

eyegor wrote: post #16
endisneigh wrote: post #4

Somewhat related - I’m very very curious to hear a detailed account of someone who uses SQLite for a production app with high traffic. For the embedded use case I think it’s a slam dunk, but there are many interesting use cases for server side but they all seem to be toyish. The locking behavior of SQLite is somewhat problematic unless you use WAL and even then not perfect

Yes, the sqlite defaults are quite terrible out of the box. I'm not sure why they never changed them, it will start choking at 5k inserts where other dbs can do 100x that (and so will sqlite in wal and a few other settings). Getting it to perform well in high traffic scenarios would be a lot of effort. I struggle to get it to be vaguely performant in embedded use cases and often roll my own poor man's version unless…

> I'm not sure why they never changed them

Because SQLite is run in many different environments and scenarios, and what's terrible in one scenario is perfect for another. There are no defaults that will work for everyone. This also applies to MySQL, PostgreSQL, etc. but the range of scenarios for those is more limited (no embedded for example) so the defaults are a bit more tuned to what's suitable for your scenario.

Post reply on HN