When installing the merge system (beta 5), the following queries are ran:
ALTER TABLE mybb_posts ADD import_pid int NOT NULL default '0';
ALTER TABLE mybb_posts ADD import_uid int NOT NULL default '0';
This causes double the time to do it, which is a major issue for large post tables.
A better query would be:
ALTER TABLE mybb_posts ADD import_pid int NOT NULL default '0', import_uid int NOT NULL default '0';
Then you get both fields added, and it does it in the one query, reducing the time needed to run it.
I suspect the same applies for all fields added to tables, and again at the removal stages.
(those queries take ~18h each to run on my post table, so just adding the columns takes a huge amount of time where nothing else can occur for 36h, then removing them again after will take probably around 24h each, so that will be done manually so that it is only one query being ran, not two)
If those queries are taking that long then you simply need better hardware imo
It's not the length of time, I know about my hardware. It is the fact that double the amount of needed queries are being ran and that they would be better done in single queries per table instead of multiple queries per table. Of course, you can completely ignore the suggestion and recommendation, but if you want to be able to improve the merge system and attract more people to convert existing sites to MyBB then you need to be willing and able to take constructive recommendations from the people using the script.
Well if you look at where the code is to add those columns, what would be your recommendation to rewrite it? How do you know that combing it would actually make it any faster? Comma separated queries, as far as I know, are simply split up by the mysql daemon and run as individual queries, no?
alter table works by creating a new table with the desired new layout, and then copying all the data from the old table in the new table. with two separate alter table statements you get this whole copy process twice. the larger the table the longer it takes.
(2009-06-12, 11:05 AM)frostschutz Wrote: [ -> ]alter table works by creating a new table with the desired new layout, and then copying all the data from the old table in the new table. with two separate alter table statements you get this whole copy process twice. the larger the table the longer it takes.
Do you have sources to back this up? Just curious.
http://dev.mysql.com/doc/refman/5.1/en/alter-table.html Wrote:In most cases, ALTER TABLE works by making a temporary copy of the original table. The alteration is performed on the copy, and then the original table is deleted and the new one is renamed.
...
You can issue multiple ADD, ALTER, DROP, and CHANGE clauses in a single ALTER TABLE statement, separated by commas. This is a MySQL extension to standard SQL, which allows only one of each clause per ALTER TABLE statement.
The copy thing alone makes alter table a hell of an expensive operation, especially on huge tables - if it contains 10GB data, 10GB are copied to alter it. Certainly something you should avoid altogether if you can, or at least not do on a daily basis...
From this point of view it might actually be better to put this data into a separate table and not run alter table at all. Not sure what import_uid / pid are used for, haven't looked at the code.
I created a test postings table in MySQL / MyISAM with 1 million entries (postings that have all the text 'foobar', so much less data than you usually see in a forum), and ran the above alter statements on it.
Running the two separate alter statements took 14.462 seconds, combining the two into one (MHyranos suggestion is missing the ADD after the ,) reduces this time to 7.385 seconds, i.e. half the time. If you make your own benchmark with a reasonably large table you should be getting similar results.
I guess MySQL managers like phpMyAdmin also let you delete, or add more than just one column in a single step, not just for comfort reasons, but also simply because doing it for each column separately takes so much more time to complete.
Ditch the alter table completely if you can... especially if you just remove the columns afterwards, it would be better using a completely separate table with a new relation pid, import_pid, import_uid.
Those trackers are definately required. Having a separate table however, for tracking the posts table fields, might be a feasible idea.
Alright, I have fixed this in the trunk - Whenever you pop on MSN, I'll send the update to you to test MHryano
This bug has been fixed in the latest version of the MyBB Merge System.
Please note the latest code is not live on the site or for download. An update for the MyBB Merge System will be released which contains this fix.
With regards,
The MyBB Group