Has anybody seen this: http://lists.mysql.com/mysql/202489 ? Like Inspector Renault in "Casablanca", if this is true, I am shocked!
I'm upgrading a Drupal 5 site with > 19M business listings to Drupal 6. The url_alias table has twice that many rows. ./modules/system.install takes several passes over this table, and what should have taken a few hours at most is taking days!
If only I had seen this sooner, I would have dumped and emptied url_alias, exported and deleting all the nodes, run the update, then reload everything. Of course, as someone already noted (and I have found out), reloading 19M nodes can take a long time (something like half a day on our server).
Since the workaround that Rolando Edwards suggests in the thread is so straightforward, why couldn't mySQL's authors have implemented "ALTER TABLE ADD/DROP INDEX" that way instead?
Comments
InnoDB; XtraDB
have you thought about InnoDB (or better yet XtraDB)? I've added an index to the node_counter totalcount column within reasonable time with a database of 650k nodes.
Might be worth it to look into.
http://mariadb.org/
Well, this guy had to do some
Well, this guy had to do some re-indexing with an innoDB table and he also had to go through hoops: http://www.xaprb.com/blog/2006/06/14/how-to-re-index-a-large-database-ta....
The thing is, I just want to run "drush updatedb" and let the Drupal core do it's thing with all the schema changes in the .install files. I don't want to have to re-write the whole damn thing myself!
Of course, I could have taken the alternate strategy: start out with a virgin D6 install and start adding modules and importing content. But I thought going the type-"drush updatedb"-go-to-bed route would be faster! Boy, was I wrong!
I'm doing a workaround now. I just dumped the entire url_alias table and it took minutes (37M rows, 4.7G with indexes). Now I'm going to empty the table, run "drush update", then reload it. Shouldn't be too bad!
InnoDB
Added an index on all 4 columns of the url_alias table; it has 1.31 Million rows in it... took about 2-3 minutes (InnoDB). That article is 4 years old; a lot changes in that time frame.
MySQL 5.1.33 on XP running XAMPP
I've been researching innodb,
I've been researching innodb, but I'm concerned about the memory requirements. The server has 8G which sounds like a lot, but it can get spread thin. The primary key of url_alias is not a 'natural' key, so InnoDB might cause a lot of churning since the access pattern to a table like that would be expected to be pretty random.
Is there a simple test that you know of that would help me ascertain memory requirements? I read that it depends on the size of the 'working set' vs. the size of the buffer pool, but I can't find anything on how to gauge the size of the working set. For example, someone had this to say: http://www.mysqlperformanceblog.com/2008/08/07/predicting-performance-im.... Also, see page 312 of High Performance MySQL by Scwartz et. al. (http://books.google.com/books?id=BL0NNoFPuAQC&lpg=PA312&ots=CNXNCxqH9T&d...)
I'm getting the feeling that the only way to know for sure is to try it, but my url_alias table has 37M rows, so dabbling can get expensive. I began re-loading the table last night after 10pm and it's only about 64% finished now.
I keep reading that the improved performance of innoDB has to be weighed against the increased complexity and tuning required. The site I inherited isn't expected to create more than a handful of new nodes each day, so the write-lock penalty of myISAM might not be that onerous.