Possible performance drawbacks when dealing with huge MySQL tables?
The revisions table would be about the size of the present old table. It would have some more rows, but would no longer have the overhead of duplicate title information. Meanwhile, some functions that deal with page titles but don't care about their contents (whatlinkshere, allpages, orphans, etc) may well be faster by having a smaller page table instead of the text-bloated cur.
Surely most of the current queries done on the database are on cur: logged in and non-logged in users viewing and editing the most recent version of pages. Relatively few queries access old: mostly viewing old versions of pages. I don't know what the exact split is but it is probably 90/10 or something.
By merging cur and old, won't that mean that most of the queries will now access the much larger revisions table? So a relatively small number of queries will get faster, but most will get slower?
Isn't this the original reason for the split of all the page data into the cur and old tables?
I don't know the exact impact combining them back will have to response times. Presumably most of the accesses to revisions will use unique indexes, and will scale well, so maybe the cleaner code is worth it if the access times do not degrade much.
Do any of the developers have a feel for how much slower the queries on revisions would be compared to cur? Just viewing an old revision seems to take about the same time as viewing the current revision, but that is just one piece of code that queries the cur table among many.
-- Michael Richards (Nanobug)
Richards,Michael wrote:
I don't know the exact impact combining them back will have to response times.
Nobody does until it has been tested with realistic amounts of traffic. And since we don't have a staging site with a traffic simulator, it will have to be tested on the real thing. But what if it turns out to be really bad? Will there be a switch that can be easily flipped to restore the old system? The conversion back and forth doesn't seem trivial to me.
My old prejudice about databases say that the bandwidth (bytes/sec) of the database socket is a common bottleneck, which makes it a bad idea to pump blob data through there. Blob-like data can be stored on file instead, with only a filename in the database. I have no idea if this design decision has had any impact on Wikipedia's performance, and I realize it would be just as hard to test as the proposed merger between cur and old.
According to the Webalizer tables, on November 20th, 13 GB was flowing out of the English Wikipedia server, for an average of 158 KB/second (1.2 Mbit/s) (over 24 hours). But do we have any idea what the peak bandwidth is? I guess the amount of traffic between Apache/PHP engine and the MySQL database should be at the same order of magnitude, if every page view reads the text from the cur table.
Lars Aronsson wrote:
Blob-like data can be stored on file instead, with only a filename in the database.
For reasons I don't understand, many people keep coming up with this idea, but it's a really bad idea.
First of all, there is no reason to believe that reading from one file out of thousands is any faster than reading one record in the DB out of thousands. Secondly, it makes atomic transactions impossible. It makes backing up a consistent state of the database/file-system mix virtually impossible. It is too difficult to move data around without violating the consistency of the construct. And lastly, it makes creating a full-text search index quite a bit harder.
Convinced yet? ;-)
Timwi
"T" == Timwi timwi@gmx.net writes:
T> Convinced yet? ;-)
I am! Great explanation.
~ESP
Timwi wrote:
First of all, there is no reason to believe that reading from one file out of thousands is any faster than reading one record in the DB out of
I agree that this is how it should be, but I have seen some real systems where file I/O bandwidth was considerably higher than database I/O bandwidth, even after tuning all buffer sizes and kernel parameters. I don't know if that is the case with Wikipedia's system using PHP + MySQL in 2003, but it was the case with one system using ASP/VB + MS SQL Server in 1999 and with another system using Java + Oracle 9i in 2002. In these years I made my entire living from telling people they should avoid blobs, and everybody was happy with the results. The fact is that Wikipedia, using blob I/O extensively, has been slow at times. This could of course be a coincidence.
Now, much of the suspicion in these cases was drawn to the implementation of the ODBC/JDBC/equivalent drivers for the database communication, which are not open source in the case of Oracle and Microsoft, so there is reason to believe that MySQL + PHP would do better than the others.
Secondly, it makes atomic transactions impossible. It makes backing up a consistent state of the database/file-system mix virtually impossible. It is too difficult to move data around without violating the consistency of the construct.
Correct, although not entirely relevant. Atomic transactions is nothing you rely on with MySQL anyway, data seldom moves, and for some uses you can move the filename and let the file stay where it is. I agree that these are drawbacks, but they are not necessarily worse than the I/O bandwidth limitation.
"Database backup" is in general more complex than "file backup", and the smaller the database, the easier it goes.
And lastly, it makes creating a full-text search index quite a bit harder.
You can still store a copy of each text (cur) in the database, and use that for searching. The vast amount of I/O over the database client-server socket is when every page view has to read the blob from the database to the (PHP) application, through the socket where the bandwidth might be limited.
Convinced yet? ;-)
No, sorry, only a real test would convince me. Right now Wikipedia is fast, so nobody is going to test this.
On Dec 12, 2003, at 20:22, Lars Aronsson wrote:
You can still store a copy of each text (cur) in the database, and use that for searching.
We do this anyway, since InnoDB tables don't support fulltext search, and the search text has to be pre-processed to strip markup and fix up encoding. Further decoupling would not really change the search system.
As far as atomic operations; if we were to use the filesystem to store page text, the safest, simplest thing would be to name the files based on the unique revision identifiers (which we don't have yet due to the way the cur/old split works). The textual content of a given revision should never change (save perhaps being compressed), and the metadata (title, user name, comment) can still be easily worked with in the database. Rename and deletion operations would not actually have to touch the files.
The trick would be making sure that the numbers really stay unique; you need to add a row to the table to get its ID number back, and then ensure that the data actually gets written to the filesystem before anyone asks for it.
Relying on both a database and a filesystem for persistent storages means you need to maintain two ways to connect if you're going to have multiple web servers, of course. Also, this leaves us with a couple million relatively small files, which the filesystem ought to be tuned for (small block size).
The vast amount of I/O over the database client-server socket is when every page view has to read the blob from the database to the (PHP) application, through the socket where the bandwidth might be limited.
The majority of page views should be cache hits which pull the output HTML data from the local filesystem, checking the DB just enough for cache validation. (I don't have exact figures at the moment, but we should probably check.) We could make better use of filesystem or memory-based caching than we do and decrease the DB load further.
-- brion vibber (brion @ pobox.com)
Lars Aronsson lars-at-aronsson.se |wikipedia| wrote:
You can still store a copy of each text (cur) in the database, and use that for searching. The vast amount of I/O over the database client-server socket is when every page view has to read the blob from the database to the (PHP) application, through the socket where the bandwidth might be limited.
Can you elaborate on how performance problems caused by slow sockets may manifest themselves? I executed the following query, which extract 17 MB of blobs in 1.5 seconds (on a slow computer). Are you perhaps referring to the general slowness added by retrieving data over network connections instead of using local files?
$ time echo "SELECT cur_text FROM cur"|mysql wikidb >/dev/null real 0m1.449s user 0m0.990s sys 0m0.130s
//E23
E23 wrote:
Can you elaborate on how performance problems caused by slow sockets may manifest themselves? I executed the following query, which extract 17 MB of blobs in 1.5 seconds (on a slow computer). Are you perhaps referring to the general slowness added by retrieving data over network connections instead of using local files?
$ time echo "SELECT cur_text FROM cur"|mysql wikidb >/dev/null real 0m1.449s user 0m0.990s sys 0m0.130s
Here, "time" measures "echo", you should rather "echo | time mysql", but I think the resulting "real" (wall-clock) time is the same. If you pipe the output to "wc -c", you will make sure all data is retrieved and not just an error message.
These numbers look really good. But you should include PHP's client interface to the database in your tests. Write a little PHP script that makes repeated SQL statements to retrieve a total of 100 MB of blob data, call that script once with wget, and measure the response time. Note that the 100 MB data shouldn't be returned over HTTP (we're not measuring Apache's or wget's performance), only be retrieved through the PHP--MySQL connection.
You could preload your database with blob data and design the test so that you can vary the number of SQL calls and the number of records retrieved per call (1000 calls x 0.1 MB, or 10 calls x 10 MB) and see if that has any impact. Running truss (Solaris) or strace (Linux) on the Apache/PHP process during the call can also reveal what is going on under the hood, especially the timing of the read(2) calls on the database socket.
Whether the database is on the same server or accessed over a local network doesn't necessarily have any impact on the performance or bandwidth. Of course, you will never see more than 12.5 MB/s over a 100 Mbit/s Fast Ethernet. Disk I/O speeds (typically 40 MB/s) isn't necessarily a limitation if the database server already caches all the selected blob data in RAM.
Lars Aronsson wrote:
Timwi wrote:
First of all, there is no reason to believe that reading from one file out of thousands is any faster than reading one record in the DB out of
I agree that this is how it should be, but I have seen some real systems where file I/O bandwidth was considerably higher than database I/O bandwidth, even after tuning all buffer sizes and kernel parameters.
OK, clearly you have more experience with this than myself, so I won't argue about your cases. But allow me to mention the two small bits of experience that I have to offer:
* A company I worked for produced a (Windows) application with a (MS-SQL) database backend where the database could potentially get rather large. The Blobs stored in this case were images. The thing here is that before my time as an employee, they attempted to use the file system for the images, rather than Blobs. It turned out to be a nightmare for exactly the reasons I mentioned. The customers were complaining about the difficulty of creating consistent backups and ensuring consistency across multiple revisions of the DB.
* The other thing I like to mention is LiveJournal. Their database backend is pretty impressive and handles the load of almost a million active users. They have never even dreamt of placing journal entries, audio posts or user pictures into files in a file system. The way they have it now they can easily create more database clusters and move users (and their data) around between clusters using a little Perl script. With a file system, that would be quite a bit more difficult.
That said, thinking about it in a much less practical and much more abstract/general way, I have to wonder why Blobs and files are any different. They are both a linear arrangement of bits. If the mechanics that file systems such as NTFS or ext-2 use were the most efficient known way of handling this data type, certainly databases would make use of it instead of implementing something knowingly less optimal?
Correct, although not entirely relevant. Atomic transactions is nothing you rely on with MySQL anyway, data seldom moves, and for some uses you can move the filename and let the file stay where it is.
I think you're tying your thoughts too much to the way Wikipedia and MySQL work today. There is no reason to believe that all the data will always be in one physical database. Months ago I had already suggested a system similar to LiveJournal's database clustering, and it was met with enthusiasm save for the fact that not enough servers are available. There is no reason to believe that the site architecture might not change some time in the future in such a way that moving data may become vital, as it is on LiveJournal already. And there is also no reason to believe that MySQL will never support atomic transactions, or if it does, that Wikipedia will never make use of it and start relying on it.
"Database backup" is in general more complex than "file backup"
... but no way as complex as "backup of a data structure comprising a database *and* a file system".
The vast amount of I/O over the database client-server socket is when every page view has to read the blob from the database to the (PHP) application
The way to solve that is MemCacheD. ;-)
Timwi
Timwi wrote:
That said, thinking about it in a much less practical and much more abstract/general way, I have to wonder why Blobs and files are any different. They are both a linear arrangement of bits. If the mechanics that file systems such as NTFS or ext-2 use were the most efficient known way of handling this data type, certainly databases would make use of it instead of implementing something knowingly less optimal?
I absolutely agree with this thinking. The problem is that neither Microsoft nor Oracle invites me to inspect their source code, and they don't document (to me) what kind of tests their solution has been put to. It's like a dictator that says "you have to trust me", before they blindfold you. I prefer to trust my own eyes.
A similar, totally brain-dead, problem was that Sun's Java runtime environment for a println() was found to make two separate operating system calls to write(2), one for the string argument, and another one for the newline character. If two processes were appending lines to the same log file using println("line"), you would certainly expect that the result would be line1-newline-line2-newline, but sometimes it turned out to be line1-line2-newline-newline, because the way the separate write(2) calls interleaved with eachother. The undesired behaviour disappeared when the application was changed from println("line") to print("line\n"). I don't remember which version of Sun's JRE this was, or if it is still there.
This is where running truss or strace on the application can tell you a lot of the stupid solutions that are hidden in big, complex systems. If the source code is open, you have a chance to fix the problem. Otherwise, you have to code around it. In this perspective, filesystems are less complex than databases, more likely to have been thoroughly tested, and less likely to contain the most stupid bugs.
On Sat, Dec 13, 2003 at 02:01:30AM +0000, Timwi wrote:
Lars Aronsson wrote:
Blob-like data can be stored on file instead, with only a filename in the database.
For reasons I don't understand, many people keep coming up with this idea, but it's a really bad idea.
First of all, there is no reason to believe that reading from one file out of thousands is any faster than reading one record in the DB out of thousands. Secondly, it makes atomic transactions impossible. It makes backing up a consistent state of the database/file-system mix virtually impossible. It is too difficult to move data around without violating the consistency of the construct. And lastly, it makes creating a full-text search index quite a bit harder.
Convinced yet? ;-)
No at all. There are good reasons to expect much higher performance.
* filesystem resides in kernel and uses extremely very fast context switches between kernelspace and userspace, database server has to communicate using sockets, what's MUCH slower. Because it's inside a kernel it has more access to disk drivers, can implement zero-copy data transfer, can use much faster locks, more efficient SMP etc. * unless you store databases on raw partitions, you have to cope with problems like inefficient double caching of data, discontiguous storage, and lack of information about physical structure of data on disk * filesystems are very specialized - they only provide a few operations for which they're extremely fast. If the operations needed happen to be those provided by the filesystem, we're likely to achieve really great performance, otherwise, we have to implement the operations needed ourselves, and the results probably won't be as good.
I think that we're going to see databases that are at least partially kernel-space some day (some databases use raw partitions already). Just look at the difference in speed between Apache and Tux. Reiserfs4 is supposed to be something between a filesystem and a database, so what I'm saying isn't as off-base as it may seem to people used to LAMP paradigm. Maybe it'll become LTuxReiserfs4P soon.
Backing up a Wiki was much easier in Phase I, when it was possible to use rsync. Mirroring Wikipedia after the switch to MySQL started to require about two orders of magnitude more bandwidth and CPU, and it became impossible to do it in real time. Now you can only synchronize once a day.
And with regard to full-text index, it isn't any good now - it's slow, turned off most of the time, and usually returns rather bad results. Moving away from MySQL would allow us to create index that would provide better results.
Hi Tomasz,
naively judging from your vocabulary it seems that you know quite a bit more about this than I do (heh, I've already said that once in this thread), but I've spotted one thing you definitely forgot, so there may be others hidden within the thicket of your technical terms:
Tomasz Wegrzanowski wrote:
- filesystem resides in kernel and uses extremely very fast context switches between kernelspace and userspace, database server has to communicate using sockets, what's MUCH slower.
What you're forgetting here is that even if you use file systems, you will still have to use sockets to transfer the data to the webserver, except in the special case where they happen to be on the same machine. Relying on this special case is a serious sacrifice of architectural flexibility. I do not expect the webserver and the database to remain on the same machine for very much longer.
Greetings, Timwi
On Mon, Dec 15, 2003 at 05:31:28AM +0000, Timwi wrote:
Hi Tomasz,
naively judging from your vocabulary it seems that you know quite a bit more about this than I do (heh, I've already said that once in this thread), but I've spotted one thing you definitely forgot, so there may be others hidden within the thicket of your technical terms:
Tomasz Wegrzanowski wrote:
- filesystem resides in kernel and uses extremely very fast context
switches between kernelspace and userspace, database server has to communicate using sockets, what's MUCH slower.
What you're forgetting here is that even if you use file systems, you will still have to use sockets to transfer the data to the webserver, except in the special case where they happen to be on the same machine. Relying on this special case is a serious sacrifice of architectural flexibility. I do not expect the webserver and the database to remain on the same machine for very much longer.
They're not on the same machine any more.
One big DB server + many small apache servers is not the only architecture possible. Of course we have to use what hardware we have available, so moving everything to filesystem is unrealistic at the moment.
But maybe for images and the math cache, it's better to keep everything mirrored in real-time on every server. Then, filesystem-to-socket copying is being done by sendfile() system call. In the most optimal circumstances, it's going to tell network card driver to use disk cache as the source of data to be sent, without moving data around. Reasonably recent Apache should have some option to use it.
Richards,Michael wrote:
Surely most of the current queries done on the database are on cur: logged in and non-logged in users viewing and editing the most recent version of pages. Relatively few queries access old:
Every edit is a write-access to old *and* a write-access to cur.
Timwi
wikitech-l@lists.wikimedia.org