Re: [sqlite] SQLite and Large Databases
Teg <[EMAIL PROTECTED]> wrote: > > dhc> Recompile version 3.5.2 with -DSQLITE_MEMORY_SIZE=1000 > dhc> or however much memory you want SQLite to use. This will > dhc> create a static array of char[] of size 1000 (or whatever > dhc> other size you give it) and use that instead of malloc() > dhc> to obtain all the memory it needs. With this approach it > dhc> is impossible for SQLite to use more than the specified > dhc> amount of memory since it never calls malloc(). > > > Win32 here. I set this option and get insta-crashes in both debug and > release builds. Using the latest 3.5.2 from the website. > > -DSQLITE_MEMORY_SIZE=1000 > > It crashes right here when it was trying to push pager pages out. > Works OK when I do it. > int sqlite3OsWrite(sqlite3_file *id, const void *pBuf, int amt, i64 offset){ > DO_OS_MALLOC_TEST; > return id->pMethods->xWrite(id, pBuf, amt, offset); > } > > > If you need more information let me know. It's not causing my any real > problems, I just disable the setting and it's back to normal. > > > -- > Best regards, > Tegmailto:[EMAIL PROTECTED] > > > . - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] SQLite and Large Databases
On Nov 8, 2007 5:04 PM, PokerAce <[EMAIL PROTECTED]> wrote: > I am using Windows Task Manager to determine memory usage. I minimize my > application (which causes the task manager to show a more accurate > measurement of memory used) and still the memory usage is enormous. Windows Task Manager doesn't count DLL's memory usage twice, it just add that memory to the first linked program, while a second one will appear to consume a lot less memory (but inverting the situation when the order of the programs execution changes). For example, the first .NET application will seem to use a LOT of memory (because all the DLLs it will link to), while the second one may use just a few mega (which is probably the actual memory it's allocating more the program executable size, adding any DLL loaded specific to the program). As other said, use ProcessExplorer (www.sysinternals.com) if you really want to have a meaningful clue on the memory usage of your program. Regards, ~Nuno Lucas - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] SQLite and Large Databases
PokerAce <[EMAIL PROTECTED]> wrote: > I am using Windows Task Manager to determine memory usage. I minimize my > application (which causes the task manager to show a more accurate > measurement of memory used) and still the memory usage is enormous. > > Recompile version 3.5.2 with -DSQLITE_MEMORY_SIZE=1000 or however much memory you want SQLite to use. This will create a static array of char[] of size 1000 (or whatever other size you give it) and use that instead of malloc() to obtain all the memory it needs. With this approach it is impossible for SQLite to use more than the specified amount of memory since it never calls malloc(). -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] SQLite and Large Databases
Actually, I am. I abstract the database communication in my application, but I access each database using their native API. On Nov 8, 2007 12:44 PM, Joe Wilson <[EMAIL PROTECTED]> wrote: > --- PokerAce <[EMAIL PROTECTED]> wrote: > > "Are you certain it's sqlite RAM, and not your application?" > > > > Yes, we are also testing PostgreSQL and MySQL and the application memory > > with those stays < 20 mb. > > You're not using the sqlite3 API directly, are you? > If that's the case, I think your database driver is leaking memory. > > > __ > Do You Yahoo!? > Tired of spam? Yahoo! Mail has the best spam protection around > http://mail.yahoo.com > > > - > To unsubscribe, send email to [EMAIL PROTECTED] > > - > >
Re: [sqlite] SQLite and Large Databases
--- PokerAce <[EMAIL PROTECTED]> wrote: > "Are you certain it's sqlite RAM, and not your application?" > > Yes, we are also testing PostgreSQL and MySQL and the application memory > with those stays < 20 mb. You're not using the sqlite3 API directly, are you? If that's the case, I think your database driver is leaking memory. __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] SQLite and Large Databases
"Are you certain it's sqlite RAM, and not your application?" Yes, we are also testing PostgreSQL and MySQL and the application memory with those stays < 20 mb. "Is your temp_store set to memory or file?" I have not changed this, so it's whatever the default is. "How are you performing your inserts (prepared statements)?" Not using prepared statements, just sending a batch of "INSERT INTO" statements. "How many rows are you inserting per batch?" Do you mean per transaction? If so, approximately 30 or so. The 500 mb memory usage was after 150k transactions. "What's your database page_size?" Whatever the default is. "Can you build your table indexes after you populate the data?" Preferably not. "Is this for a poker showdown database by any chance?" Nope. :-) It's for: http://preview.pokertracker3.com On Nov 8, 2007 11:59 AM, Joe Wilson <[EMAIL PROTECTED]> wrote: > --- PokerAce <[EMAIL PROTECTED]> wrote: > > I'm trying to see if SQLite is suitable for large databases ( > 1 gb, > > millions of rows in each of several tables). Initially, the memory > usage > > was outrageous (~ 500 mb for a 1.3 gb db), but I got that down to < 30 > mb by > > setting the cache size to 0 and setting a low soft heap limit. That > works > > when I'm reading from the database, but when I am inserting these rows, > the > > memory usage grows back into the ~500 mb range. My goal is to never > have > > the application use more than 100 mb of memory, preferably much less > than > > that. Does anyone have any suggestions? > > If your cache_size is 0 I'm not sure what's eating up 500M of RAM for > inserts. > > Some questions that might give you some ideas: > > Are you certain it's sqlite RAM, and not your application? > Is your temp_store set to memory or file? > How are you performing your inserts (prepared statements)? > How many rows are you inserting per batch? > What's your database page_size? > Can you build your table indexes after you populate the data? > > Is this for a poker showdown database by any chance? > http://games.cs.ualberta.ca/poker/IRC/ > > > > __ > Do You Yahoo!? > Tired of spam? Yahoo! Mail has the best spam protection around > http://mail.yahoo.com > > > - > To unsubscribe, send email to [EMAIL PROTECTED] > > - > >
Re: [sqlite] SQLite and Large Databases
I am using Windows Task Manager to determine memory usage. I minimize my application (which causes the task manager to show a more accurate measurement of memory used) and still the memory usage is enormous. On Nov 8, 2007 11:55 AM, Roger Binns <[EMAIL PROTECTED]> wrote: > -BEGIN PGP SIGNED MESSAGE- > Hash: SHA1 > > PokerAce wrote: > > Initially, the memory usage > > was outrageous (~ 500 mb for a 1.3 gb db), > > How are you measuring memory usage? You have to be very careful as most > operating system tools will report a number greater than the size of all > current mallocs() and depending on the pattern of malloc/free can report > something substantially larger. It doesn't mean the memory usage is > that large. > > Roger > -BEGIN PGP SIGNATURE- > Version: GnuPG v1.4.6 (GNU/Linux) > > iD4DBQFHMz9wmOOfHg372QQRAtPXAJiW52di3U2JP6ywpE6MvB3e0Jr4AJ9B1bvj > kpBlioHvm8lesFP3S1LEfQ== > =zwuw > -END PGP SIGNATURE- > > > - > To unsubscribe, send email to [EMAIL PROTECTED] > > - > >
Re: [sqlite] SQLite and Large Databases
If you have to set that manually, it was not set. I am using version 3.5.2. I am doing tens of thousands of transactions, each without ~30 separate INSERTs. On Nov 8, 2007 11:40 AM, <[EMAIL PROTECTED]> wrote: > PokerAce <[EMAIL PROTECTED]> wrote: > > I'm trying to see if SQLite is suitable for large databases ( > 1 gb, > > millions of rows in each of several tables). Initially, the memory > usage > > was outrageous (~ 500 mb for a 1.3 gb db), but I got that down to < 30 > mb by > > setting the cache size to 0 and setting a low soft heap limit. That > works > > when I'm reading from the database, but when I am inserting these rows, > the > > memory usage grows back into the ~500 mb range. My goal is to never > have > > the application use more than 100 mb of memory, preferably much less > than > > that. Does anyone have any suggestions? > > > > Did you compile with SQLITE_ENABLE_MEMORY_MANAGEMENT=1? > The soft_heap_limit is a no-op if you did not. > > What version are you running. What SQL are you executing that > causes the memory usage to shoot up? > > > -- > D. Richard Hipp <[EMAIL PROTECTED]> > > > > - > To unsubscribe, send email to [EMAIL PROTECTED] > > - > >
Re: [sqlite] SQLite and Large Databases
--- PokerAce <[EMAIL PROTECTED]> wrote: > I'm trying to see if SQLite is suitable for large databases ( > 1 gb, > millions of rows in each of several tables). Initially, the memory usage > was outrageous (~ 500 mb for a 1.3 gb db), but I got that down to < 30 mb by > setting the cache size to 0 and setting a low soft heap limit. That works > when I'm reading from the database, but when I am inserting these rows, the > memory usage grows back into the ~500 mb range. My goal is to never have > the application use more than 100 mb of memory, preferably much less than > that. Does anyone have any suggestions? If your cache_size is 0 I'm not sure what's eating up 500M of RAM for inserts. Some questions that might give you some ideas: Are you certain it's sqlite RAM, and not your application? Is your temp_store set to memory or file? How are you performing your inserts (prepared statements)? How many rows are you inserting per batch? What's your database page_size? Can you build your table indexes after you populate the data? Is this for a poker showdown database by any chance? http://games.cs.ualberta.ca/poker/IRC/ __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] SQLite and Large Databases
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 PokerAce wrote: > Initially, the memory usage > was outrageous (~ 500 mb for a 1.3 gb db), How are you measuring memory usage? You have to be very careful as most operating system tools will report a number greater than the size of all current mallocs() and depending on the pattern of malloc/free can report something substantially larger. It doesn't mean the memory usage is that large. Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) iD4DBQFHMz9wmOOfHg372QQRAtPXAJiW52di3U2JP6ywpE6MvB3e0Jr4AJ9B1bvj kpBlioHvm8lesFP3S1LEfQ== =zwuw -END PGP SIGNATURE- - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] SQLite and Large Databases
PokerAce <[EMAIL PROTECTED]> wrote: > I'm trying to see if SQLite is suitable for large databases ( > 1 gb, > millions of rows in each of several tables). Initially, the memory usage > was outrageous (~ 500 mb for a 1.3 gb db), but I got that down to < 30 mb by > setting the cache size to 0 and setting a low soft heap limit. That works > when I'm reading from the database, but when I am inserting these rows, the > memory usage grows back into the ~500 mb range. My goal is to never have > the application use more than 100 mb of memory, preferably much less than > that. Does anyone have any suggestions? > Did you compile with SQLITE_ENABLE_MEMORY_MANAGEMENT=1? The soft_heap_limit is a no-op if you did not. What version are you running. What SQL are you executing that causes the memory usage to shoot up? -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] -