[sqlite] Any ideas on how long it takes to drop a large table...rather an important question now...
Hi, We're doing a massive tidy on our database which is approx 50GB. One table is approx 49GB of that 50GB which we need to delete as we have recorded the information in a far better format thats approx 99% more efficient. If only we had been this clever when we started We've just 'dropped' the table and were assuming that dropping the table would be quite quick. It's not. So far we've been waiting for 30 mins and nothing has come back yet. We can see that the -wal file is upto 2.5GB. We have this terrible feeling that it'll need to get to 49GB or so before the table gets dropped. We can just about handle that in the current filesystem. We're now getting nervous about dropping this table. We had assumed that it would be a really quick and easy operation based on absolutely no checking whatsoever. When we looked on line all we could see was a reference to a very, very old and outdated page (https://sqlite.org/speed.html) which talks about speed and at the bottom of that page the comments "SQLite is slower than the other databases when it comes to dropping tables. This probably is because when SQLite drops a table, it has to go through and erase the records in the database file that deal with that table. MySQL and PostgreSQL, on the other hand, use separate files to represent each table so they can drop a table simply by deleting a file, which is much faster. On the other hand, dropping tables is not a very common operation so if SQLite takes a little longer, that is not seen as a big problem." Is this still the case, is it going to take a long time? If we assume that the table is 49GB then will we need to wait until the -wal file is at 49GB. By our estimates thats approximately 10 hours away. Any help or idea or suggestions welcomed, but please be quick. Rob ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] I'm getting a compiler error on a SQLIte build on Ubuntu 16.04 (which surprises me)
Hi I'm trying to build the SQLite packages from the source. I **only** want it to get a copy of sqlite_analyse which for some reason doesn't appear to be easily available as a compiled option. The download package doesn't work on Ubuntu 16.04 and as far as we can see, there are no other downloadable binaries that don't come from China. For some reason, I don't really want to download something from Beijing The full steps I followed are at the very bottom of this e-mail. They are a direct copy from my terminal output. **TLDR Summary** 1. OS is a reasonably fresh (< 4 weeks old) Ubuntu 16.04 LTS Server running under OpenVZ on a commercial ISP. It's patched to the current levels. I don't think this is a problem as we don't have any other issues but... 2. SQLite3 is already installed as the normal (and older) SQLIte that is distributed with Ubuntu 16.04 LTS 3. gcc is installed. I have created a small C program to test that it can compile (Hello World) and it's fine. (gcc (Ubuntu 5.4.0-6ubuntu1~16.04.10) 5.4.0 20160609) 4. Downloaded https://www.sqlite.org/2018/sqlite-autoconf-324.tar.gz through wget. 5. Checked the sha1sum and it matches the sqlite page. 6. Untarred the file into it's own directory 7. cd'ed to directory and ran ./configure. 8. Checked output of configure and nothing seemed wrong. Details are below. 9. Ran make and get a compiler failure! (see below). I'm gobsmacked that the compiler has failed to be honest. So my first assumption is that we have cocked up something. However we've googled and checked and can't see anybody with a similar issue. I suspect that one of the Define statements is not playing nicely but not wholly sure which one OR we are missing a package that needs to be installed, e.g. TCL x,y or z. root@preprod1:/jambuster/src/sqlite/sqlite-autoconf-324# make /bin/bash ./libtool --tag=CC --mode=compile gcc -DPACKAGE_NAME=\"sqlite\" -DPACKAGE_TARNAME=\"sqlite\" -DPACKAGE_VERSION=\"3.24.0\" -DPACKAGE_STRING=\"sqlite\ 3.24.0\" -DPACKAGE_BUGREPORT=\"http://www.sqlite.org\"; -DPACKAGE_URL=\"\" -DPACKAGE=\"sqlite\" -DVERSION=\"3.24.0\" -DSTDC_HEADERS=1 -DHAVE_SYS_TYPES_H=1 -DHAVE_SYS_STAT_H=1 -DHAVE_STDLIB_H=1 -DHAVE_STRING_H=1 -DHAVE_MEMORY_H=1 -DHAVE_STRINGS_H=1 -DHAVE_INTTYPES_H=1 -DHAVE_STDINT_H=1 -DHAVE_UNISTD_H=1 -DHAVE_DLFCN_H=1 -DLT_OBJDIR=\".libs/\" -DHAVE_FDATASYNC=1 -DHAVE_USLEEP=1 -DHAVE_LOCALTIME_R=1 -DHAVE_GMTIME_R=1 -DHAVE_DECL_STRERROR_R=1 -DHAVE_STRERROR_R=1 -DHAVE_POSIX_FALLOCATE=1 -DHAVE_ZLIB_H=1 -I. -D_REENTRANT=1 -DSQLITE_THREADSAFE=1 -DSQLITE_ENABLE_FTS5 -DSQLITE_ENABLE_JSON1 -DSQLITE_HAVE_ZLIB -DSQLITE_ENABLE_FTS3 -DSQLITE_ENABLE_RTREE -g -O2 -MT sqlite3.lo -MD -MP -MF .deps/sqlite3.Tpo -c -o sqlite3.lo sqlite3.c libtool: compile: gcc -DPACKAGE_NAME=\"sqlite\" -DPACKAGE_TARNAME=\"sqlite\" -DPACKAGE_VERSION=\"3.24.0\" "-DPACKAGE_STRING=\"sqlite 3.24.0\"" -DPACKAGE_BUGREPORT=\"http://www.sqlite.org\"; -DPACKAGE_URL=\"\" -DPACKAGE=\"sqlite\" -DVERSION=\"3.24.0\" -DSTDC_HEADERS=1 -DHAVE_SYS_TYPES_H=1 -DHAVE_SYS_STAT_H=1 -DHAVE_STDLIB_H=1 -DHAVE_STRING_H=1 -DHAVE_MEMORY_H=1 -DHAVE_STRINGS_H=1 -DHAVE_INTTYPES_H=1 -DHAVE_STDINT_H=1 -DHAVE_UNISTD_H=1 -DHAVE_DLFCN_H=1 -DLT_OBJDIR=\".libs/\" -DHAVE_FDATASYNC=1 -DHAVE_USLEEP=1 -DHAVE_LOCALTIME_R=1 -DHAVE_GMTIME_R=1 -DHAVE_DECL_STRERROR_R=1 -DHAVE_STRERROR_R=1 -DHAVE_POSIX_FALLOCATE=1 -DHAVE_ZLIB_H=1 -I. -D_REENTRANT=1 -DSQLITE_THREADSAFE=1 -DSQLITE_ENABLE_FTS5 -DSQLITE_ENABLE_JSON1 -DSQLITE_HAVE_ZLIB -DSQLITE_ENABLE_FTS3 -DSQLITE_ENABLE_RTREE -g -O2 -MT sqlite3.lo -MD -MP -MF .deps/sqlite3.Tpo -c sqlite3.c -fPIC -DPIC -o .libs/sqlite3.o gcc: internal compiler error: Killed (program cc1) Please submit a full bug report, with preprocessed source if appropriate. See for instructions. Makefile:539: recipe for target 'sqlite3.lo' failed make: *** [sqlite3.lo] Error 1 10. I checked the Makefile but there's no obvious issue 11. We've done this three times now with the same result. 12. The fact the compiler barfs is worrying, we know how many people use SQLite so for this sort of error to occur is a little surprising and we still thinksqlite-us...@mailinglists.sqlite.org its our fault, but we cannot see what we have done wrong. Any help or suggestions welcomed. I have to say I am sick to the back teeth of reading websites that purport to tell you how to compile SQLite to find that it simply consists of apt-get install sqlite3 (or whatever). The signal to noise ration here is very, very low. We need a clickbait filter. Thanks Rob - **Detailed summary** wget https://www.sqlite.org/2018/sqlite-autoconf-324.tar.gz --2018-07-09 08:28:39-- https://www.sqlite.org/2018/sqlite-autoconf-324.tar.gz Resolving www.sqlite.org (www.sqlite.org)... 45.33.6.223, 2600:3c00::f03c:91ff:fe96:b959 Connecting to www.sqlite.org (www.sqlite.org)|45.33.6.223|:443... connected. HTTP request sent, a
Re: [sqlite] Mailing list shutting down...
Richard, We use Discourse (as a user) to get support for the Mail in a Box system. MIAB use Discourse for their support systems. I also think Discourse is used for the Ionic support pages as well as they have very similar looking interfaces. Until now I paid little attention to them. I was going to say that I wasn't too impressed with it as a system, then I thought again and realised that it actually works pretty well and doesn't get in your way too much. That's a pretty good compliment as the software isn't in your face all the time telling you how nice it is, anybody used Slack recently :) We use it quite a lot and in hindsight it works well. We've never spun a Discourse system up, but I have some spare time this evening and might just put one on a VMWare ESXI server and see how it looks. I may be older than Dr Hipp as I can recall running Unix on a 64KB (yep KB) box in the 80's, so am very familiar with maximising resources, but I go the other way now and and run dedicated (but small and self contained) boxes that are very focused and don't try to cram as much into a single box/instance/VM as possible. I don't care about the fact I'm running 30 small Linux boxes on my single ESXI server as I can spin them up and most of the time they don't do anything. I accept the issues over maintenance though, but I have a standard set of instructions I follow to harden the boxes and restrict logins with things like fail2ban. From start to finish I can have a hardened Ubuntu box up in around 20-30 minutes. Very happy to share these instructions as somebody may say they are rubbish and can provide better hardening instructions. I'm UK based, but happy to help, setting this sort of stuff up is something I can do and have regularly done (but NOT for Discourse), anyway I'm better at this than SQL :) It's currently 20:30 UK time, can help, other people have helped me enough on this forum, so I feel I can contribute something back. Thoughts on what needs to be done: 1. Setup the VMware instance correctly based on the Discourse info. 2. Provide some sort of access via ssh, passwords or whatever. 3. Details of IP addresses. 4. Firewall configure, Its not clear if these VM's are behind other firewalls and what the access rights are, e.g. you have https. 5. What's the SSL situation. We've just moved from RapidSSL to LetsEncrypt as a) They are free b) They self renew c) They weren't going to be blacklisted by Google as they were really Symantec certificates. 6. Does the installation need root access? 7. Postfix information, e.g. is it a satellite, a relay etc etc. One wrong move here and we get the IP address and domain name banned. Did that for our domain whilst setting up MIAB. 8. Installation of Discourse. 9. How do multiple people work together on the same box? Slack? Skype? Shouting loudly 10. Documenting the build? 11. How to test the build? Testers needed and a test plan needs to be put together. SQLite has an excellent reputation, this shouldn't sully it. 12. Profit? Just my 2p worth, Rob On 13 Jun 2018, at 19:59, Richard Hipp wrote: Cross-posted to the fossil-users mailing list since www.fossil-scm.org and www.sqlite.org are the same machine and both mailing lists are impacted by the current problem. On 6/13/18, Luiz Américo wrote: How about using https://www.discourse.org/ ? Open source projects can use for free Thanks for the pointer, Luiz. Discourse is moving the right direction, I think. To install it, one downloads a docker container and runs it on some Linux VM someplace. (They recommend Digital Ocean, which is where I www3.sqlite.org is hosted already.) It's a self-contained package with minimal dependencies that just works. And it uses SQLite! My kind of software! Here are my remaining points of heartburn with Discourse: (1) The installation guide recommends using an external email service, and they even recommend four appropriate services. I clicked through to each one, having never heard of any of them before. All four are pushing email marketing for companies sending 10 million or more emails per month. It seems to me that aggressive email marketing is the root cause of my problem in the first place, so I am somewhat reluctant to engage a marketing firm to help with the solution. Fortunately, Discourse also allows one to use a self-hosting Postfix installation, which is what we are currently running on sqlite.org. (2) Discourse seems to want to run on a machine all by itself. (It is written in Rails and has its own webserver.) I suppose I could spin up yet another VM to do that. But I learned this craft in an age where machines were big and expensive and the goal was to cram as many services as you could fit onto a single machine and IP address, and so spinning up a separate machine with its own domain name just to manage the mailing list seems wasteful, somehow. And, that means there is one more machine that I have t
