On Sat, Nov 21, 2009 at 12:38 AM, Jona Christopher Sahnwaldt jcsahnwaldt@gmail.com wrote:

With the indexes defined in tables.sql, query performance is ok. For example, selecting the titles of all articles that are not redirects takes five or ten minutes (didn't profile it exactly).

Any reason I would like to ask is why not use PostgreSQL?

Seems MySQL is not suitable for handling large table (e.g. over few GB), I just wonder why wikipedia don't use PostgreSQL?

It should provide better performance.

On Sat, Nov 21, 2009 at 12:05 PM, Q overlordq@gmail.com wrote:

MediaWiki has used MySQL since the beginning and has let the code get away with things that shouldn't have been done which makes switching to Postgres hard.

Isn't that mediawiki also support pgsql?

http://www.mediawiki.org/wiki/Installation

On Fri, Nov 20, 2009 at 10:47 PM, Ryan Chan ryanchan404@gmail.com wrote:

Any reason I would like to ask is why not use PostgreSQL?

Seems MySQL is not suitable for handling large table (e.g. over few GB), I just wonder why wikipedia don't use PostgreSQL?

It should provide better performance.

MySQL is easily capable of handling very large tables, if used properly. Certainly tables the size of Wikipedia's (which aren't very big by DB standards). Selecting a list of all titles that are not redirects will take a long time on any database, unless you have everything in memory, because it requires a table scan -- there's no index that covers the relevant columns (IIRC). Of course, if you don't configure MySQL properly, or don't give it a reasonable amount of hardware, it will perform poorly, but the database is not much overtaxed on Wikipedia right now.

It's also worth pointing out that Wikipedia uses a version of MySQL with substantial modifications, and Wikimedia sysadmins are very familiar with its behavior. Switching to a new technology might theoretically be better in the long term (although I wouldn't take that for granted in this case), but the transition cost would be substantial. Heck, Wikipedia hasn't even upgraded to MySQL 4.1, let alone a whole different DBMS.

On Sat, Nov 21, 2009 at 7:02 PM, Anthony wikimail@inbox.org wrote:

You could build an index on page_is_redirect in the "page" table (see enwiki-*-page.sql.gz).  But I'm pretty sure Postgresql wouldn't use it, and would do a sequential scan, since pretty much all the pages are going to have to be accessed anyway.

Five or ten minutes sounds about right.  I can't imagine this is a query you want to run over and over again.  If it is, you'd probably want to use partitioning (http://www.postgresql.org/docs/8.4/interactive/ddl-partitioning.html), but you're still not going to cut down the query time very much, as it's going to be returning millions of rows.

Yeah, pretty much. If you had an index on (page_is_redirect, page_namespace, page_title) or such, that would speed it up significantly (at least in MySQL). It would have to scan through the whole index, but that only contains three columns plus a row id of some kind, so it should be quite a lot faster than scanning the whole table. But this isn't a likely query for optimization.

Yes, it can be very hard to switch your DBMS, and that's a very good thing for MySQL.  :)

Let's not have a DBMS flame war here, please.

On Sat, Nov 21, 2009 at 9:48 PM, Jona Christopher Sahnwaldt jcsahnwaldt@gmail.com wrote:

The one thing that is slow is builiding the indexes after the data has been imported (eight hours or so). Maybe we could omit some indexes that are not used in our application, but I haven't really looked into that.

MyISAM should be able to build keys quite quickly; it can do so by sorting if all goes well. That's commonly ten times as fast as rebuilding by keycache, or more. I don't know offhand how mwdumper works, but you might want to try increasing the value of myisam_max_sort_file_size to something larger than all your indexes, if you have enough disk space. Or you could do something like import into a table with no keys at all, create a table with the same definition but with keys and no data, shut down MySQL, copy the new table's .frm and .MYI over the old table's, run myisamchk --sort-recover on the old table (with the data and copied .frm and .MYI), and restart MySQL, but this is at your own risk, of course. :)

On Sat, Nov 21, 2009 at 10:36 PM, Anthony wikimail@inbox.org wrote:

Quite a lot?  The theoretical max would be about twice as fast, as (page_is_redirect, page_namespace, page_title) is going to take up at least half as much space as the whole page table.

Yes, that's true. Twice as fast is still a pretty good improvement, though. :)

I know it wouldn't be reached by PostgreSQL, which would still do a sequential scan through the table.

MySQL skips the table and just looks at the index, in both InnoDB and MyISAM.

mysql> EXPLAIN SELECT page_namespace, page_title FROM page WHERE page_is_redirect=0; +----+-------------+-------+------+---------------+------+---------+------+-------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+------+-------+-------------+ | 1 | SIMPLE | page | ALL | NULL | NULL | NULL | NULL | 43603 | Using where | +----+-------------+-------+------+---------------+------+---------+------+-------+-------------+ 1 row in set (0.00 sec)

mysql> EXPLAIN SELECT page_namespace, page_title FROM page2 WHERE page_is_redirect=0; +----+-------------+-------+------+------------------+------------------+---------+-------+-------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+------------------+------------------+---------+-------+-------+-------------+ | 1 | SIMPLE | page2 | ref | page_is_redirect | page_is_redirect | 1 | const | 22048 | Using index | +----+-------------+-------+------+------------------+------------------+---------+-------+-------+-------------+ 1 row in set (0.00 sec)

Note "Using index" in the second query; it never looks at the table data, only the index. (Those queries are on a local copy of Simple, not enwiki, thus the small row counts.)

I'm actually in the process of trying to import enwiki into a postgresql database right now.  Attempt 1 was to import everything into a MySQL database (with no indexes), export it as a TSV file, then import from the TSV file into Postgresql.  Hit a snag with some data that Postgres is saying isn't valid UTF8, which is probably due to something I did wrong with the import, but I can't figure out what it is.

The most likely problem is that it's not actually valid UTF-8. There are some places where we end up truncating things bytewise rather than characterwise, like edit summaries, leaving only the first byte or two of a multibyte character. Last I checked, edit summaries were just passed to the database with no hard length check, so MySQL in non-strict mode with a binary schema (like Wikipedia) will just truncate them to fit. (In strict mode it will raise an error, and if we used utf8 rather than binary it would presumably truncate by characters.)

On Sat, Nov 21, 2009 at 10:40 PM, Ryan Chan ryanchan404@gmail.com wrote:

Is the source available in the svn? Can you point me to the right direction?

http://svn.wikimedia.org/viewvc/mysql/

On Sun, Nov 22, 2009 at 11:45 AM, Ryan Chan ryanchan404@gmail.com wrote:

May I ask why still using the 4.0 version? Seems 5.1 above did provide much performance enhancements?

I'm not the one to ask. My understanding is that it's mostly a question of how much administrative resources it would take to switch.

On Sun, Nov 22, 2009 at 11:26 AM, Aryeh Gregor Simetrical+wikilist@gmail.com wrote:

Last I checked, edit summaries were just passed to the database with no hard length check, so MySQL in non-strict mode with a binary schema (like Wikipedia) will just truncate them to fit.  (In strict mode it will raise an error, and if we used utf8 rather than binary it would presumably truncate by characters.)

Hmm, okay. Should I be using character set and collation "binary"? The dumps build the table using character set utf8, and don't say anything about the collation. Is this specific to edit summaries, or does it apply to all the text fields?

I guess this is getting off topic, but I'm not quite sure what the topic of this thread is anyway.

On Sun, Nov 22, 2009 at 11:54 AM, Aryeh Gregor Simetrical+wikilist@gmail.com wrote:

On Sun, Nov 22, 2009 at 11:45 AM, Ryan Chan ryanchan404@gmail.com wrote:

...

May I ask why still using the 4.0 version? Seems 5.1 above did provide much performance enhancements?

I'm not the one to ask. My understanding is that it's mostly a question of how much administrative resources it would take to switch.

I don't know if it was supposed to be taken as sarcasm or not, but Tim Starling recently commented that "it seems that I'm the only staff member who knows MySQL." That was a joke, right?

On Sun, Nov 22, 2009 at 12:02 PM, Aryeh Gregor Simetrical+wikilist@gmail.com wrote:

Wikipedia uses binary fields for everything.  It has to, since MySQL 4.0 doesn't support anything else.

Reading through https://bugzilla.wikimedia.org/show_bug.cgi?id=164 was enlightening.

On Sat, Nov 21, 2009 at 8:45 PM, Aryeh Gregor Simetrical+wikilist@gmail.com wrote:

Yeah, pretty much. If you had an index on (page_is_redirect, page_namespace, page_title) or such, that would speed it up significantly (at least in MySQL). It would have to scan through the whole index, but that only contains three columns plus a row id of some kind, so it should be quite a lot faster than scanning the whole table.

Quite a lot? The theoretical max would be about twice as fast, as (page_is_redirect, page_namespace, page_title) is going to take up at least half as much space as the whole page table. But I'm not sure even that theoretical max could be reached by MySQL.

I know it wouldn't be reached by PostgreSQL, which would still do a sequential scan through the table. If you clustered on page_is_redirect you'd save yourself from having to go through the parts of the table which were redirects, but you're still stuck with a sequential scan.

In either database, if you really wanted the absolute fastest solution, you'd create a materialized view for exactly that query. But as you said, "this isn't a likely query for optimization."

...

Yes, it can be very hard to switch your DBMS, and that's a very good thing for MySQL. :)

Let's not have a DBMS flame war here, please.

Aww, c'mon, just a little light ribbing... I couldn't resist, you set me right up for it.

I'm actually in the process of trying to import enwiki into a postgresql database right now. Attempt 1 was to import everything into a MySQL database (with no indexes), export it as a TSV file, then import from the TSV file into Postgresql. Hit a snag with some data that Postgres is saying isn't valid UTF8, which is probably due to something I did wrong with the import, but I can't figure out what it is.

Hi!

I was traveling around a bit, missed some of threads entirely!

Ryan writes:

Any reason I would like to ask is why not use PostgreSQL?

Any reason we should?

Seems MySQL is not suitable for handling large table (e.g. over few GB), I just wonder why wikipedia don't use PostgreSQL?

Is PG more suitable? Last time I looked at it, both engines were using B+-Trees.

It should provide better performance.

Do you have any benchmarks on that?

Simetrical writes:

Heck, Wikipedia hasn't even upgraded to MySQL 4.1, let alone a whole different DBMS.

We do have 5.1 servers running in production for quite a while (e.g. dewiki's lomaria :-) We were running enwiki slaves on 5.0 too few times :) It is not like there're any showstoppers for migration at the moment.

Antony writes:

If it is, you'd probably want to use partitioning

Partitioning makes selects faster only when there's parallel execution on multiple partitions at once. PG doesn't have that, MySQL doesn't have that, some commercial PG offsprings (Greenplum?) have it.

Simetrical writes again:

Let's not have a DBMS flame war here, please.

Oh come on, it has been a while. Nowadays we also need people from NoSQL camp, telling we should migrate to ultimately scalable erlang- based key/value/document storages, with lots of javascript map/reduce.

Jona writes:

The one thing that is slow is builiding the indexes after the data has been imported (eight hours or so).

People with not enough of RAM to have efficient b-tree builds can use either InnoDB Plugin's fast-index-creation, or Tokutek's fractal tree storage (which is commercial software, but has free license up to 50G, or for developers, iirc)

Ryan asks:

May I ask why still using the 4.0 version?

Because it does what we need it to do, is rock-solid and fast enough. Also because someone was lazy with 5.1 build engineering, but now there's one nearly ready for production at lp:~wikimedia

Seems 5.1 above did provide much performance enhancements?

Yes, some of them are same ones we had in our 4.0 builds for years, others are ones we don't really need. We're read-i/o-constrained and we're doing quite well at that with our current builds.

I don't know if it was supposed to be taken as sarcasm or not, but Tim Starling recently commented that "it seems that I'm the only staff member who knows MySQL." That was a joke, right?

Tim is indeed the only one at the staff who knows really well how to handle replicated MySQL setups, as well as other advanced MySQL topics. Apparently it wasn't only WMF staff running Wikipedia's databases.

Jona comments on utf8:

Yes. The main problem with using UTF-8 for the tables is that MySQL only supports Unicode characters U+0000 .. U+FFFF. Other characters are silently removed, which leads to problems with duplicate page titles etc.

Actually the main problem with using utf8 is that most of language- specific collations are case-insensitive, which would mean lots of pain with case senstive->case insensitive transition (due to how indexes work, it is relatively difficult to have efficient sorting order different from equality rules). And yes, characters outside BMP can be an issue, but we would be hitting that as a problem only in few page titles.

Dmitry suggests:

There was a message at mysql.com site that google performance ehancements were incorporated into version 5.4.

Google performance enhancements were also incorporated into version 4.0.40. Not all, but most of ones we'd need (I/O related, we're not really in shape with our datasets where we would care about SMP performance ;-)

BR, Domas

On Tue, Nov 24, 2009 at 10:17 PM, Anthony wikimail@inbox.org wrote:

On Tue, Nov 24, 2009 at 7:57 PM, Domas Mituzas midom.lists@gmail.com wrote:

...

Antony writes:

...

If it is, you'd probably want to use partitioning

Partitioning makes selects faster only when there's parallel execution on multiple partitions at once.

[snip] I have no idea how this works on MySQL, or if it works on MySQL at all.  In MySQL, you could achieve the same thing through clustering, however.

http://dev.mysql.com/doc/refman/5.1/en/partitioning-pruning.html

"When the optimizer can make use of partition pruning in performing a query, execution of the query can be an order of magnitude faster than the same query against a nonpartitioned table containing the same column definitions and data. "

I don't see any mention of "parallel execution", and I don't see why that would be necessary to benefit from partitioning.

On Wed, Nov 25, 2009 at 12:39 AM, Domas Mituzas midom.lists@gmail.com wrote:

But.... who will partition based on is_redirect? If it is for one-off task, you can just create two separate tables and do 'manual partitioning' even in sqlite :) Even though your is_redirect queries may become faster, you just added *2 cost for every other index operation (as partitions require you to loop over all indexes for all the lookups not satisfied by partitioning key).

Please read my comment over again: "I can't imagine this is a query you want to run over and over again. If it is, you'd probably want to use partitioning."

...

http://dev.mysql.com/doc/refman/5.1/en/partitioning-pruning.html

"When the optimizer can make use of partition pruning in performing a query, execution of the query can be an order of magnitude faster than the same query against a nonpartitioned table containing the same column definitions and data. "

This is obvious, dropping a partition is faster than DELETE that has to go and maintain the index. I'm not talking about DELETE operations, but SELECTs, you seem to fail at reading there :-)

The word "DELETE" does not appear anywhere on that page I referred to. The examples on the page are all SELECTs. Try again.

And how do native language collations work? (and since which version are they supported per-database? are they supported per-schema? ;-)

I suspect you either know the answers to these questions or can easily look them up. Is there a particular problem you're having with them which is unsuitable for Wikipedia? Does Wikipedia not use a separate database for each language?

I got somewhat incorrect results once I used lt_LT.UTF-8 for my 'initdb' - and default collation was providing incorrect order too, as well as unique constraints were not enforcing dictionary-order rules.

postgres=# create database xx encoding 'utf8'; CREATE DATABASE postgres=# \c xx; You are now connected to database "xx". xx=# create table t1 (a varchar(255) primary key); NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "t1_pkey" for table "t1" CREATE TABLE xx=# insert into t1 values ('a'); INSERT 0 1 xx=# insert into t1 values ('b'); INSERT 0 1 xx=# insert into t1 values ('ą'); INSERT 0 1 xx=# select * from t1 order by a;  a

---

a  b  ą (3 rows)

You guys seem to talk about stuff you never used and never really understood. Good for you, probably much easier that way.

Sorry, I can't reproduce your error:

a=# create table t1 (a varchar(255) primary key); NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "t1_pkey" for table "t1" CREATE TABLE a=# insert into t1 values ('a'); INSERT 0 1 a=# insert into t1 values ('b'); INSERT 0 1 a=# insert into t1 values ('ą'); INSERT 0 1 a=# select * from t1 order by a; a --- a ą b (3 rows)

I suspect operator error, but if you want to submit your bug to http://www.postgresql.org/support/submitbug I'm sure someone will go over it with you.

On Wed, Nov 25, 2009 at 2:22 AM, Domas Mituzas midom.lists@gmail.com wrote:

Hi!

...

Please read my comment over again: "I can't imagine this is a query you want to run over and over again.  If it is, you'd probably want to use partitioning."

Which would make sense if no other queries are being ran :)

It'd make sense if most of your queries used one partition or the other, and not both. Kind of like Wikipedia's history/current tables, which are effectively using partitioning, though it's being done in the PHP (and any other language with software which tries to use the database) instead of being done in the database using rules and triggers (and thereby being accessible to software written in any language).

With PG though you can define an index on smaller subset, may be better than partitioning.

Not in this case. You want to physically move the data so you can access fewer pages, not just create an index on it. PG doesn't move data just because you create an index on it.

...

The word "DELETE" does not appear anywhere on that page I referred to. The examples on the page are all SELECTs.  Try again.

Argh, damn terminology, was thinking about partition drops. Anyway, those SELECTs are 'faster' if they hit partition key, but then, people usually use PKs as their partition keys, so it doesn't really matter :-)

Reread my messages now that you realize you were confused the first time: "In MySQL, you could achieve the same thing through clustering, however." MySQL clusters on the primary key. This is great, or at least it would be great if it didn't mean MySQL locks the whole table every time you use any DDL. Still no "create index concurrently", right?

...

Is there a particular problem you're having with them which is unsuitable for Wikipedia?

*shrug*, wrong native collations?

It's not PostgreSQL's fault if you've got buggy locales installed on your system.

Not using locale-specific character locality in unique matching (haha, I could use this argument in opposite, when talking about MySQL support :), etc

Exactly my point. PostgreSQL does by default exactly what Wikipedia wants to do with respect to uniqueness matching. You can still do it the other way by making an index on an expression, but in the case of Wikipedia, which is what I thought we were talking about, there's no need for that.

MySQL, on the other hand, doesn't give the option. You either choose binary, and lose the collation, or you drop the binary and have to drop the unique key constraint.

...

Sorry, I can't reproduce your error:

Because you didn't read what I wrote. I wrote I was using language- specific collation :)

Maybe you were using a *broken* language-specific collation? Do you get the same error when you use a locale-aware sorting program, like gnu sort?

It was collation error, not operator error. I just showed it to illustrate my point, that there's quite some work to get working collations (which usually involves building locales yourself). Do note, that once you have indexes in place, any locale change is really painful and requires full database rebuild. One of reasons we're still 'binary' is that nobody really wants to own the pain of maintaining charsets server-side. It is much bigger project, than most of people see, at our scale. Of course, one may just chose to believe, that there's silver bullet for everything.

The thing is, I never claimed there was a silver bullet. You asked for *any reason* to use PostgreSQL. I gave one. Just one, because that's all you asked for.

I'm sure you could find someone to "own the pain of maintaining charsets server-side". Anyone ask Gerard M if he knows somebody? If not, the WMF should hire someone, because the comment in the bug submission is right on that it's just embarrassing for an encyclopedia not to be able to sort things correctly. Just using a generic locale would be orders of magnitude better than using a binary collation. And you could keep the current hacks and kludges in place in each language until a proper locale for that language is written.

Domas points out:

Which would make sense if no other queries are being ran :) With PG though you can define an index on smaller subset, may be better than partitioning.

Exactly - this is a perfect use case for partial indexes, not for partitioning. The MW Postgres schema is already using some partial indexes, FWIW, e.g.

CREATE INDEX rc_timestamp_bot ON recentchanges (rc_timestamp) WHERE rc_bot = 0;

P.S. Where is PG's replication? How does it deal with DDL? :)

Define "replication" first :) There are a lot of replication options available, some of which handle DDL, some that do not.

P.P.S. Anyone running PG in production on a big website?

Yep. Course, you might also want to define "big"

I'll resist the urge to say too much more on this thread right now, and go back to watching from the sidelines.

I'm probably done myself. I just wanted to point out a few factual errors in Mr. Mituzas' email. I'm not so interested in the grey-area "which is better, which is worse" debate.

my emails are also just because someone said "any reason not to... " and "it should provide better performance" :)

Good day!

Domas

On Wed, Nov 25, 2009 at 16:17, Greg Sabino Mullane greg@endpoint.com wrote:

Great, so we can close the thread and agree that we'll move everything over to Oracle in the near future. :)

Oh yeah! And rewrite MediaWiki in PL/SQL while we're at it!

Christopher

On Wed, Nov 25, 2009 at 10:47 AM, Domas Mituzas midom.lists@gmail.com wrote:

Hi!

...

Great , so we can close the thread and agree that we'll move everything over to Oracle in the near future. :)

InnoDB has been part of Oracle since 2005... ;-)

And bought MySQL this past April, right?

Maybe they'll rename MySQL to Oracle Lite or Oracle Free Edition.

Top 10? Top 20? Because I did a quick count a while back and found at least six of the top ten used MySQL in some capacity. And two of the remaining four were owned by Microsoft. :) I'm no DBA, but that suggests to me that MySQL is pretty suitable for large websites, compared to the competition.

I don't think anyone is saying that MySQL is not suitable for large websites. However, the reasons why top websites (and non-top websites) are using a particular piece of technology is usually far more complex an answer than "best technology" or even "good technology". Exhibit A: IIS.

So I'm curious about this, actually. You'd think sites like Google and Yahoo! would be smart enough and have enough resources to use the best tools available.

They do. Google ditched all existing database and built their own system to handle their main stock and trade. For some things, they use MySQL, albeit a modified one.

Yahoo uses PostgreSQL (again, a heavily modified one):

http://www.informationweek.co/news/showArticle.jhtml?articleID=207801579

Keep in mind if popularity alone was a good criteria, we'd all be <strike>happily</strike> using Windows on our desktops. If all that mattered was technical superiority, we'd be running BeOS. :)

Frankly, the choice of using PHP as the language for MediaWiki has probably caused more problems over the years than the choice of database backend. :)

On Thu, Nov 26, 2009 at 12:23 AM, Greg Sabino Mullane greg@endpoint.com wrote:

Frankly, the choice of using PHP as the language for MediaWiki has probably caused more problems over the years than the choice of database backend. :)

so what are your suggestion?

use Java?

On Fri, Nov 27, 2009 at 6:58 AM, Aryeh Gregor Simetrical+wikilist@gmail.com wrote:

On Thu, Nov 26, 2009 at 8:29 AM, Ryan Chan ryanchan404@gmail.com wrote:

...

so what are your suggestion?

use Java?

Python!

In what aspect?

Just another scripting language!

On Tue, Nov 24, 2009 at 7:57 PM, Domas Mituzas midom.lists@gmail.com wrote:

Ryan writes:

...

Any reason I would like to ask is why not use PostgreSQL?

Any reason we should?

Actually the main problem with using utf8 is that most of language- specific collations are case-insensitive, which would mean lots of pain with case senstive->case insensitive transition (due to how indexes work, it is relatively difficult to have efficient sorting order different from equality rules).

psql (8.4.1) Type "help" for help.

a=# create table category (title varchar(255) not null primary key); NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "category_pkey" for table "category" CREATE TABLE a=# insert into category values ('Apple'); INSERT 0 1 a=# insert into category values ('Banana'); INSERT 0 1 a=# insert into category values ('Pear'); INSERT 0 1 a=# insert into category values ('banana'); INSERT 0 1 a=# insert into category values ('Orange'); INSERT 0 1 a=# insert into category values ('apple'); INSERT 0 1 a=# insert into category values ('Apple'); ERROR: duplicate key value violates unique constraint "category_pkey" a=# insert into category values ('APPLE'); INSERT 0 1 a=# select * from category order by title; title -------- apple Apple APPLE banana Banana Orange Pear (7 rows)