Sqlite is an ACID database - it ensures that data is written to disk, so a database in memory still shares a single disk resource.
Jim Wilcoxson wrote: > I'm not sure what you are considering a massive slowdown, but let's > assume that the entire database fits into memory and disk I/O isn't > the bottleneck. You said you're running 300 instances of the query on > several processors. If several means 3 CPUs, then in a perfect world, > running 300 instances will be 100 times slower than running just 1. > This assumes you are getting linear scalability, which no one ever > does, so the slowdown will be more than 100x. If you have 4 > processors, running 300 queries simultaneously should still be 75x > slower (best case) than running 1. > > You also mentioned seeing a wide variance in response times. This is > typical, because most schedulers in multi-user systems won't perfectly > distribute CPU time to 300 processes. If the scheduler decides to run > task 1 to completion, then task 2, etc., your last task will appear to > take much longer than the first task. For example, let's say that > each task by itself takes 1 second of CPU time and zero I/O time, > assuming the database is all in memory. 300 queries will take 300 > seconds to complete. If the system scheduler runs each task, in > order, to completion, then the first task will take 1 second and the > last task will take 300 seconds to complete. Wide variance. Or, the > system scheduler could decide to give each task 1/100th of a second. > It will take 3 seconds for all tasks to get a timeslice. In this > scenario, it will still take 300 seconds to complete all 300 jobs, but > they will complete within 1/100th of a second of each other, and each > job will report that it took 300 seconds to complete. No variance. > The price you will pay for no variance is that you increase the > multiprocessing overhead because now instead of doing just 300 task > switches to execute each job to completion, you are doing 300x100 = > 30,000 task switches. Task switches aren't free, so the "no variance" > schedule will take longer overall than the wide variance. This is a > classic fairness vs high throughput dilemma; you can't have both. > > If you are seeing something like 1000x slower performance, then as > others have mentioned you could have a disk I/O or locking bottleneck. > > Jim > > On 5/6/09, Igor Tandetnik <itandet...@mvps.org> wrote: > >> "Rosemary Alles" <al...@ipac.caltech.edu> wrote >> in message news:af79a266-b697-4924-b304-2b1feccba...@ipac.caltech.edu >> >>> Run on a single processor, the following query is quite fast: >>> >>> When concurrency is introduced (simply running the query on several >>> processors against the same database - say 300 instances of it) causes >>> a massive slow down >>> >> Well, you may have multiple CPUs, but you only have a single hard drive. >> That drive head can't be in multiple places simultaneously. >> >> Igor Tandetnik >> >> >> >> _______________________________________________ >> 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
