Migrate all posts_b, posts_meta etc tables to one giant posts table

[ctrlcctrlv]

This is extremely important for performance. Anybody that can help with this wins major points.

We would also need a migration script, check out install.php for how we've done that in the past @ vichan.

--- Want to back this issue? **[Post a bounty on it!](https://www.bountysource.com/issues/4700712-migrate-all-posts_b-posts_meta-etc-tables-to-one-giant-posts-table?utm_campaign=plugin&utm_content=tracker%2F6417251&utm_medium=issues&utm_source=github)** We accept bounties via [Bountysource](https://www.bountysource.com/?utm_campaign=plugin&utm_content=tracker%2F6417251&utm_medium=issues&utm_source=github).

[anonfagola]

Too lazy to do this myself rn. So to do this, make a posts table with the same thing as the postsX table, but have 2 extra rows: "board" (used for the board used on) and "idfb" (id for board, when used for No. 5743 and stuff). To convert it RIGHT NOW, use a PHP for loop to go through all tables starting with posts and have them insert the posts, with "board" being what was after posts_, and "idfb" being the id column.

[czaks]

nope, you can do create table posts select (... a union select query ...), this will offload the hurdle of joining everything to mysql, so it will be a lot faster

[czaks]

but a major problem here is that a lot of code is written with assumption that all posts are in different tables. so a major rewrite has to happen

[anonfagola]

@czaks What are all the files that use the current system of posts?

[czaks]

anonfagola:

[chax@zbox 8chan]$ grep -Rl posts_%s . ./boards.php ./claim-old.php ./expire.php ./inc/functions.php ./inc/instance-config.php ./inc/mod/pages.php ./install.php ./post.php ./search.php ./templates/themes/catalog/theme.php ./templates/themes/recent/theme.php ./templates/themes/sitemap/theme.php ./templates/themes/ukko/theme.php ./tools/delete-stray-images.php ./tools/rebuild.php ./tools/recount-bumps.php ./tools/stats.php [chax@zbox 8chan]$

[ctrlcctrlv]

Yup. But there's no way around it, we simply cannot expect to keep UNION ALL'ing 3000 tables to track across boards.

This is a major design flaw, at least for a site with many boards.

[ctrlcctrlv]

As far as the code goes, the changes will be monotonous — they'll just take a lot of time. Just changing %s to WHERE board = board: everywhere it's mentioned.

[ctrlcctrlv]

why was this closed?

[czaks]

haha, you did a merge 2 hours ago and it included a commit "fixes #29", so github treated it as if this commit fixed issue #29. i will do fixes vichan-devel#29 in the future, to prevent similar issues.

[fourfivesix]

I'll begin working on this.

[fourfivesix]

@czaks would there be any problems in using InnoDB in this posts table? Because I will have to use transactions and MyISAM doesn't seem to support it.

[czaks]

myisam is good for tables where there are only inserts and no deletes

innodb/xtradb won't cause any problems. there may be issues with fulltext search in certain mysql/mariadb versions, but we don't use it.

also, think about using aria of mariadb. 8chan should already use mariadb.

[ctrlcctrlv]

@czaks actually we switched from arch to ubuntu at the request of 2channel

bleeding edge is too risky for a large site, i agree with them

we could however still compile mariadb if it has a big performance advantage though

[czaks]

well, RHEL 7 and a recent OpenSUSE/SuSE have mariadb instead of mysql, is it stable enough then? it's just ubuntu sticking with mysql, like with upstart, mir, unity, ..., ok, i don't want to start a flamewar now. MySQL has now just Oracle and Canonical backing it, community moved on to MariaDB.

i'm not aware of performance improvements by just moving from MySQL to MariaDB, but moving to either InnoDB/XtraDB or Aria storage engine may be a big win, since MyISAM wasn't designed for this sort of workload.

[fourfivesix]

@czaks @ctrlcctrlv I'll probably keep using InnoDB then.

Here's what I have so far (there's many more changes in my computer, just not ready for commits yet) https://github.com/fourfivesix/8chan/compare/posts-table

[czaks]

why can't you just add an SQL string field string board to the unified schema and make both board and post_id an unique id?

[czaks]

looking at your code, i will add the following:

• why leave the sprintf, when you can actually get rid of it?
• replace posts_%s with posts actually, and not posts. ```` is a special tinyboard syntax, that means: add a table prefix.

[fourfivesix]

@czaks

why can't you just add an SQL string field string board to the unified schema and make both board and post_id an unique id?

I think that was what I intended to do here: https://github.com/fourfivesix/8chan/compare/posts-table#diff-af635eb3e6ad9b49cb0f2433ffeb3248R109 Am I doing it wrong?

why leave the sprintf, when you can actually get rid of it?

You're right, I'll remove it.

replace posts_%s with posts actually, and not posts. `` is a special tinyboard syntax, that means: add a table prefix.

Didn't know that, thanks!

[fourfivesix]

@ctrlcctrlv @czaks I think I'm done changing all occurrences of posts_%s and their related consequences. I have pushed those commits to https://github.com/fourfivesix/8chan/compare/posts-table.

I would appreciate if you guys could review my changes, most of them have not been tested yet and I'm not confident that I have found all the places that needed to be modified.

Now I'll work on the migration script. After that I'll work with @ctrlcctrlv in setting up a dev installation and testing the shit out of it.

[ctrlcctrlv]

very nice dude

I'll begin looking it over. I've also got some patches in my -dev to push. We could add them together into a major site update.

[fourfivesix]

@ctrlcctrlv good idea!