Simon,
Thanks for this. You make some interesting points about cache hits and
misses. Up until now, we hadn't seen a massive performance hit, whilst
some database operations didn't work as fast as we would like them to,
nothing works as fast we would like.
I'm not familiar with these issues with virtualisation. The VPI we use
has OpenVZ at it's core (no pun intended). We can see a little on the
internet about this (well one article specifically about MySQL and
OpenVZ) but nothing else.
We are in the process of evaluating whether to move to a different VPS,
some use OpenVZ, some use KVM, internally we use VMWare ESXI. Very
little real DB work is done on the actual metal, its all virtualised.
Do you have any pointers to stuff we can read up on? We don't understand
your comment "SQLite spent that whole time accessing your 50GB database
file in an apparently random order." and would like to try and get more
information about it.
We have the option of moving off OpenVZ to KVM or ESXI so if we can
understand the issue, we can make a more informed choice. Whilst our DB
has dropped down to 500MB we still need to do a fair amount of testing
and checking to make sure there are no unusual edge cases (or bugs)
based before we promote it to live.
Many thanks
Rob
On 17 Jul 2018, at 12:05, Simon Slavin wrote:
On 17 Jul 2018, at 8:37am, Rob Willett <rob.sql...@robertwillett.com>
wrote:
I suspect that part of the issue is the VPS provider we use has a
rate limiter on IOPS which is not normally an issue for us, but that
might have slowed it down somewhat. However I don't think that it
would have slowed it down by hours.
Actually I think VPS had a lot to do with the time the operation took.
Any kind of virtual machine takes a terrible hit during the sort of
storage access involved in dropping the table.
SQLite spent that whole time accessing your 50GB database file in an
apparently random order. So you had nine hours of cache misses,
causing the virtual machine to continually write virtual pages back to
real storage and read other pages into memory. Virtual systems are
optimized for cache hits, not cache misses.
I can't prove it without a lot of pointless data manipulation on your
type of VPS, but I think you found its least optimal operation. The
good part is that now your database is less than 1GB long you're going
to see a massive increase in speed since the whole database may well
fit in the cache of your virtual machine.
Must remember in future, when people report unusually slow operations,
to ask whether they're using a virtual machine or real hardware.
Simon.
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
