Re: [sqlite] Strange WAL mode on 5GB+ database
On 11/11/2010 6:47 AM, Alexey Pechnikov wrote: > $ ls -lh merch.db* > -rw-r--r-- 1 - - 5,8G Ноя 10 23:01 merch.db > -rw-r--r-- 1 - - 32K Ноя 10 23:04 merch.db-shm > -rw-r--r-- 1 - - 449M Ноя 10 23:01 merch.db-wal > > sqlite> pragma journal_mode; > wal > sqlite> pragma journal_mode=delete; > Error: database is locked Can't convert to delete mode while a WAL file exists. > sqlite> pragma integrity_check; > ok Causes WAL file to be written back and deleted. > sqlite> pragma journal_mode=delete; > delete No probs as no WAL file now. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Many many tables... performance wise?
On 16/10/2010 12:01 PM, Dustin Sallings wrote: > > ...but there will also be a unique index on rowid, which will get large > and need to be maintained. I'm concerned that this alone could be limiting > me somewhat. > > I have a similar application with a single table that I'd like to split > into more based on an identifier that appears in the table. All of my > operations are limited to one of these identifiers (though it's not indexed, > the lookup is always by rowid). Occasionally, I want to delete all records > based on an ID. > > Bobby Tables is not relevant to my application as I know how to do my > bindings properly and have no confusion with data types (this is an integer) > or user data vs. executable code. > > As a single table, I can easily have many tens of millions of rows. > Splitting it into 1,024 tables by a specific ID, I'd expect the each index to > be smaller and (at the very least), I'll have a far easier time deleting a > large chunk all at once. > > I do intend to do some experimentation here, though it'd be helpful to > have some more detailed pointers as to why the intuition is wrong here. > Very similar to me. So far it is proving a good solution. But proof would be scaling from a dozen dynamic tables so far to hundreds+. If you do some testing on that please post. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Many many tables... performance wise?
This is basically where I am coming from, but deletion (of possibly millions of entries) is slow and ties up the system (see previous thread by someone some days back). In experimentation having dynamic tables and doing a drop table is proving a big winner, though so far only using about a dozen tables. On 16/10/2010 10:32 AM, Scott Hess wrote: > Having a table with an owner_id, key, and value, with a unique index > on (owner_id, key) will probably be more efficient than having a > separate table per owner. Also, it will be easier to code safely, > because bind parameters don't work on table names (I'm assuming you're > using dynamic table names in this case - if so, watch out for johny > drop tables). > > -scott > > > On Fri, Oct 15, 2010 at 4:26 PM, Andrew Davison > wrote: >> There are no schemas per se. Just key+blob. They are backup datasets. >> Nothing fancy databasey. Just wondering about the actual impact of >> having many tables. >> >> On 15/10/2010 6:54 PM, Simon Slavin wrote: >>> >>> On 15 Oct 2010, at 7:36am, Andrew Davison wrote: >>> >>>> What's the take on having hundreds of tables in a database? >>> >>> Generally not. A database should be designed. By a human. I don't know >>> about you, but I can't hold hundreds of schema in my head at the same time. >>> Rather than have two or more tables with the same schema, it's usually >>> better to have one table with an extra column to mark what kind of data >>> each record is. There are exceptions to this but it's a good design >>> principle. >>> >>>>Any likely >>>> performance problems apart from first time a table is accessed? Does it >>>> affect the cache? >>> >>> SQLite keeps data from each table (and each index) in different pages of >>> filespace. So each time you switch from one table to another you're >>> switching to another page of the file. And if you have 100 tables in a >>> file you have 200 pages of space, reserved for only one kind of data that >>> can't be used for anything else. That's an argument for fewer but bigger >>> tables. >>> >>> I understand why you asked the question but I think that an SQLite newbie >>> can only figure it out from experience. My advice is to design stuff >>> whatever way makes it simplest for you to do your programming. Worry about >>> performance only if it turns out to be too slow or too unwieldy or annoying >>> in some other way. >>> >>> Simon. >>> ___ >>> sqlite-users mailing list >>> sqlite-users@sqlite.org >>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >>> >> >> >> ___ >> sqlite-users mailing list >> sqlite-users@sqlite.org >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Many many tables... performance wise?
There are no schemas per se. Just key+blob. They are backup datasets. Nothing fancy databasey. Just wondering about the actual impact of having many tables. On 15/10/2010 6:54 PM, Simon Slavin wrote: > > On 15 Oct 2010, at 7:36am, Andrew Davison wrote: > >> What's the take on having hundreds of tables in a database? > > Generally not. A database should be designed. By a human. I don't know > about you, but I can't hold hundreds of schema in my head at the same time. > Rather than have two or more tables with the same schema, it's usually better > to have one table with an extra column to mark what kind of data each record > is. There are exceptions to this but it's a good design principle. > >> Any likely >> performance problems apart from first time a table is accessed? Does it >> affect the cache? > > SQLite keeps data from each table (and each index) in different pages of > filespace. So each time you switch from one table to another you're > switching to another page of the file. And if you have 100 tables in a file > you have 200 pages of space, reserved for only one kind of data that can't be > used for anything else. That's an argument for fewer but bigger tables. > > I understand why you asked the question but I think that an SQLite newbie can > only figure it out from experience. My advice is to design stuff whatever > way makes it simplest for you to do your programming. Worry about > performance only if it turns out to be too slow or too unwieldy or annoying > in some other way. > > Simon. > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Multiple prepared statements
Yup, my bad. Fixed. On 16/10/2010 12:03 AM, Andrew Davison wrote: > On 15/10/2010 11:49 PM, Pavel Ivanov wrote: >>> Now I decide that I want a second type of insert, so I try to use a >>> prepared statement for that as well. However it always fails. As long as >>> the other prepared statement is hanging round I can't prepare a new one. >>> Does this seem right or am I really soing something wrong? >> >> You are doing something wrong. I always keep like 10 prepared >> statements for each connection and it works perfectly. I work in C++ >> so it's a direct SQLite feature. That's what prepared statements are >> for. >> > > That's what I thought, so something is wrong. > Thanks. > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Multiple prepared statements
On 15/10/2010 11:49 PM, Pavel Ivanov wrote: >> Now I decide that I want a second type of insert, so I try to use a >> prepared statement for that as well. However it always fails. As long as >> the other prepared statement is hanging round I can't prepare a new one. >> Does this seem right or am I really soing something wrong? > > You are doing something wrong. I always keep like 10 prepared > statements for each connection and it works perfectly. I work in C++ > so it's a direct SQLite feature. That's what prepared statements are > for. > That's what I thought, so something is wrong. Thanks. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Multiple prepared statements
In my database I do lots of inserts, of exactly the same nature so I use a prepared statement, which I cache, always reseting after use. Works fine. Now I decide that I want a second type of insert, so I try to use a prepared statement for that as well. However it always fails. As long as the other prepared statement is hanging round I can't prepare a new one. Does this seem right or am I really soing something wrong? Can I not have multiple prepared statements created? Regards. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Many many tables... performance wise?
What's the take on having hundreds of tables in a database? Any likely performance problems apart from first time a table is accessed? Does it affect the cache? Regards. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] RTREE still uses float
Is it intentional that RTREE code still uses 'float' types? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite is perfect for FILE based MESSAGE QUEUE?
I've used SQLite for exactly this. I have no gripes with it at all. I ended up using just one table in one DB as indexing makes things simple and fast. WAL i've been trying these last few days and seems trouble-free. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite is perfect for FILE based MESSAGE QUEUE?
I've used SQLite for exactly this. I have no gripes with it at all. I ended up using just one table in one DB as indexing makes things simple and fast and I support arbitrary queues with multiple readers. WAL i've been trying these last few days and seems trouble-free. On 30/09/2010 6:11 PM, Lynton Grice wrote: > Hi there, > > > > I am a HUGE SQLite fan and have an interesting question I have been > scratching my head about for a couple days now. > > > > First off my daily job is very much around "messaging" and I am very > familiar with message queue products like Websphere MQ and Fiorano MQ. > > > > When you install Websphere MQ or Fiorano MQ they have a FILE based queue > underneath by default (typically one file per queue with the messages etc > stored at different offsets). > > > > There messaging systems will have "file writer locks" that in essence will > only allow ONE WRITER per queue at any one time. So any clients sending > messages will have "to wait in line" until the message can be physically > written to file. > > > > Bottomline: We have one writer and multiple readers per queuefine. > > > > Then I scratch my head and wonder why SQLite is not the PERFECT persistence > layer for building an awesome "file based queue" on? It is lightening fast > by default, and also has the same "locking issues" described above. We are > talking milliseconds anywaybetween writes > > > > Bottomline: Is there any reason why anyone would think someone like me > coding a solid message queue using SQLite is a bad idea? Am I missing > something? I personally think it is a wonderful idea and would be hugely > useful to my daily job > > > > Any help or advise in this regard would be hugely appreciated ;-) > > > > Thanks > > > > Lynton > > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Changing file descriptor of database file
For people wanting to re-do their networking code I would reccommend Asio (or Boost::Asio), see: http://think-async.com/Asio/ Ad. On 8/05/2010 9:14 AM, Roger Binns wrote: > -BEGIN PGP SIGNED MESSAGE- > Hash: SHA1 > > On 05/07/2010 01:43 PM, Pavel Ivanov wrote: >> select() has limitation that it can be used >> only with file descriptors less than 1024. > > This is rarely true and I only know of one crappy operating system that had > that limitation 13 years ago. You can simply recompile with > - -DFD_SETSIZE=1 in all files that use fd_set to have a different "limit". > > However select() is a horrendous interface, especially beyond a trivial > number of file descriptors and there are lots of better ones out there with > a variety of attributes and support. I'd recommend using libevent which > abstracts all this stuff away. Heck, it even works on Windows! > >http://en.wikipedia.org/wiki/Libevent > > Roger > -BEGIN PGP SIGNATURE- > Version: GnuPG v1.4.10 (GNU/Linux) > Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/ > > iEYEARECAAYFAkvknu8ACgkQmOOfHg372QRbTQCeKx8ZA++bGsvNCybLlg3P3uwl > eXkAoML1M+wGVvaoCWZaRT7OnwvpgPdD > =WLrY > -END PGP SIGNATURE- > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
