On 2/8/07, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:
"Stan Bielski" <[EMAIL PROTECTED]> wrote: > > Anyone care to take a guess what's going on here? The problem is that you are thrashing. The working set on the database file is exceeding the amount of memory that your OS has set aside for disk cache.
The best solution, if possible, is to insert records in sorted order by index. If you are inserting in batches, try inserting them all into a TEMP table first. Then do INSERT INTO maintab SELECT * FROM temptab ORDER BY indexcolumn; DELETE FROM temptab;
Thanks for your reply. I understand that I'm experiencing thrashing, but I'm curious as to why I'm experiencing it. My understanding is that the row_id PRIMARY KEY column has an index on it by default. Inserting into this index doesn't cause thrashing, but inserting into the secondary index does. I hoped that enabling the shared cache mode and periodically doing the select operation on it would keep it in SQLITE's cache. Based on what I saw from I/O stat, top, and my timing measurements, that doesn't seem to be the case. In fact, my cache "priming" experiment showed that it didn't help at all, despite a cache size of 500 MB and the index being on a column with an INTEGER pragma. The transaction immediately following the cache priming would be just as slow as the transactions far away from it. Why would a transaction inserting 1024 rows of 0.5 KB each result in the eviction of that index, despite the abundance of cache? The solution you propose doesn't work when your table has multiple indices of this type. One can imagine altering the schema as to store each indexed column in a separate table, with a column containing the rowid to the main table, but that would result in a significant amount of redundant overhead. Again, thanks for the reply... I'm just trying to figure out what's happening internally without adding instrumentation to the source code ;-) -Stan ' If SQLite were smarter, it might be able to deal with
this situation automatically. It is not clear at this time if making SQLite smart enough to deal with this is compatible with the goal of keeping SQLite small and nimble. We are working the problem. Do not expect a resolution in the near term. -- D. Richard Hipp <[EMAIL PROTECTED]> ----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] -----------------------------------------------------------------------------
