And if we're talking about consumer Windows as shipped from a given PC manufacturer, or about Windows as preconfigured by site IT, then we may be talking about orders of magnitude of slowdown.Īnd once again you may object that this should net out to zero, but it is within the scope of antimalware software to treat a collection of three files on disk (DB, WAL, lockfile) differently than a single file (old-style single SQLite DB file.)įS: If that didn't affect anything, then why do the bars flip-flop in this benchmark? That has a direct effect on this wish of yours, since it means the way you construct the benchmark affects which FS has the advantage. out-of-the-box Windows probably gives the advantage significantly to Linux, simply because Windows ships with antivirus active while Linux does not. Change those parameters and now you get a different physics-based limitation for each concurrency mode. You may then object, "But doesn't all of that net out to zero if the test is done on the same storage medium?" Answer: No! Why? Because the factor "2 rotations per transaction" assumes a particular fsync behavior and access pattern in the benchmark. but even that aside, there are single aspects of storage that can give an effect orders of magnitude in difference, such as rotating vs solid state: 7200 RPM divided by 60 seconds per minute divided by 2 rotations per transaction yields 60 transactions per second, absolute max, hard-limited by physics, yet a lone modern SSD may achieve 10000 TPS, and a RAID of them may achieve a million TPS. Storage: This is a complex field all by itself - lone HDD vs RAID vs LVM vs SAN vs SecureDigital vs m.2. In that case, we can create a table of values for you which is clear, simple, and wrong.Īll items on that list can affect the results significantly, and three of the four can affect the results by an order of magnitude or more: We're not defining deep storage, OS, FS, versions, etc., here So, as you can see, it is both faster and slower all at the same time. However, you now have the advantage that a connection can "read" from the database at the same time as another connection is writing to the database (because it is not writing to the database, it is writing to the "difference file"). It is also where all the extra inflamation comes from. This is the other place where the plastic surgeon lets the flag hang out. Occasionally the database needs to be "checkpointed" by copying the sequential pages from the WAL file to the database (read sequential write random). All reads are double indirect because they have to check for changes in the "difference file" and therefore operate somewhat more slowly than not having to do so (this is one of the places where the plastic surgeon lets the flab hang out). In a WAL database, writes are made sequentially to a "difference file" (not randomly) - and sequential writes are faster than random writes (this is where the plastic surgeon is doing the nip and tuck). One connection cannot "read" the database at the same time another is "writing" to it. Reads are always direct operations against the database file. Committing a transaction means deleting the log. In a "normal" database, writes are made randomly directly to the database file itself, and copies of the "pre-change" pages are written to a log file. And how good or bad (Windows falls in the canna-do-at-all category) is at doing I/O, particularly scatter-gather I/O. It also depends how your I/O is scattered across those 4 trillion pages. It also depends whether you are talking about a 4 page database of a 4 trillion page database. And like everything else, the "total" time is mostly the same for the same amount of work, you are just calling in a plastic surgeon to do a "little nip and tuck here in the belly" and "let it hang out more over there in the ass".
0 Comments
Leave a Reply. |