Sciencemadness Discussion Board
Not logged in [Login ]
Go To Bottom

Printable Version  
 Pages:  1    3  4
Author: Subject: Lengthy, time-consuming overhaul of forum software
Melgar
Anti-Spam Agent
*****




Posts: 2004
Registered: 23-2-2010
Location: Connecticut
Member Is Offline

Mood: Estrified

[*] posted on 14-2-2018 at 16:52
Lengthy, time-consuming overhaul of forum software


Okay, so the problem is that XMB (the software SM uses) has been more or less discontinued a long, long time ago. I kind of like the old-school feel of the site, but it would be better if we used an old-school forum software that's still supported.

I checked around a bit, and phpBB is an early forum software package that's still going strong, and seems familiar. Overhauling it would be a bitch though. A bunch of php code would need to be rewritten so that links stay working from outside this site. The theme would have to be remade for phpBB, and a lot of the text would have to have its tags edited. On the plus side, there's none of that BS with "quote" vs. "rquote", and where to put which end tags, so that's a bonus. Lots of other annoyances could finally be put to rest, including blowing up moderators' inboxes every time someone reports spam.

I looked into vBulletin too, but it seems a lot more bloated than it used to be, whereas phpBB has the same old-school feel that it always has.

I don't want to be overly critical, or give away too much to people who we'd prefer not have that info, but this software would have been outdated a decade ago, and won't even run on newer systems. Upgrading is pretty much a necessity. Polverone will back me up on this.

Fortunately, this is something I can do. I'm actually pretty handy with SQL, and have some time on my hands. Unlike most people who do this sort of thing, I'd accept donations of reagents and glassware in lieu of money. (PM me for details ;) )

I wouldn't expect anything upfront, but I should be able to put something together. This site runs on Linode, which, unsurprisingly, is an old-school host. It would probably be a good idea to switch hosts to AWS, because they make it really easy to add new features in a way that reduces their cost by understanding the priorities of commonly-used features. For instance, at my last job, we had a lot of large media files that were seldomly accessed, so those were put in the AWS S3 (simple storage service) system, which is a lot cheaper than using a dedicated drive. They have this really complex "a la carte" system of offering services, so learning how it works is daunting, but it ends up actually being really nice because you're only paying for what you need and what you use.

This is mainly a proposal of my recommended approach. Obviously I don't want to start work on it unless people are onboard. I really don't want things to reach the point where there's nobody in the world that's familiar with the software that this board uses to run. Right now, that seems to be our main line of defense against hackers though, so it isn't ALL bad. :)

phpBB is the software that I think most closely resembles XMB:

https://www.phpbb.com/

Here is phpBB's support forum, which obviously runs phpBB:

https://www.phpbb.com/community/

Keep in mind, themes would be matched as closely as possible, so try not to focus on the theme there.

Since I know how much we all love reading PDF files, here's a link to the phpBB documentation:

https://download.phpbb.com/pub/documentation/3.2/rhea_docume...

Feedback is welcome.




The first step in the process of learning something is admitting that you don't know it already.

I'm givin' the spam shields max power at full warp, but they just dinna have the power! We're gonna have to evacuate to new forum software!
View user's profile View All Posts By User
Vosoryx
Hazard to Others
***




Posts: 282
Registered: 18-6-2017
Location: British Columbia, Canada
Member Is Offline

Mood: Serial Apple Enjoyer

[*] posted on 14-2-2018 at 17:29


phpBB looks much more powerful than XMB.
I'm on board, but can't really offer much help.

Quote: Originally posted by Melgar  

Keep in mind, themes would be matched as closely as possible, so try not to focus on the theme there.


Whoops - i'm talking about themes. How many people don't use the sciencemadness theme? I tried out the dark theme for a while (It's my theme on discord and youtube) but I disliked the XMB version of the dark theme. We could run a poll to see how many people use the standard theme. If it's only one or two main themes used, then switching it over would be no problem.


My question:
Would we be able to keep our usernames/posts? Is it possible to update the software while keeping all 16 years of posting history? One of my favourite things about SM is the decade and a half of conversations (even if some of them are kinda stupid long. For example, I was contemplating condensing the resolutions of the toluene ->benzaldehyde down to a useful, readable document and not have it 24 pages.)

I'm really keen on this idea, but can't offer much. But i'll be there if I can.

Edit:

For further discussions (if we decide to do this), since SM isn't great for quick back and forth conversing, should we set up another group on something like discord? If so, i'm happy to set that up.

[Edited on 15-2-2018 by Vosoryx]




"Open your mind son, before someone opens it for you." - Dr. Walter Bishop
View user's profile View All Posts By User
Melgar
Anti-Spam Agent
*****




Posts: 2004
Registered: 23-2-2010
Location: Connecticut
Member Is Offline

Mood: Estrified

[*] posted on 14-2-2018 at 18:09


Quote: Originally posted by Vosoryx  
Would we be able to keep our usernames/posts? Is it possible to update the software while keeping all 16 years of posting history? One of my favourite things about SM is the decade and a half of conversations (even if some of them are kinda stupid long. For example, I was contemplating condensing the resolutions of the toluene ->benzaldehyde down to a useful, readable document and not have it 24 pages.)

Yes, it's just a huge pain in the ass. What I'd do, is to do a database dump, import it into an SQL database on a new server, then write a bunch of scripts to convert the old database to match the format of the new one, and make sure everything is linked properly. When everyone is satisfied with how the new site looks, I'd do a final database dump, run all the scripts again on the most recent data, then switch the DNS servers over to the new IP address. The old site would live on at least for a time, accessible only by IP address, in case we end up forgetting something.

This is something I've actually done twice before now, with a similar stack, and I figure if I don't do it, nobody ever will. Which would be a shame.

Quote:
I'm really keen on this idea, but can't offer much. But i'll be there if I can.

Edit:

For further discussions (if we decide to do this), since SM isn't great for quick back and forth conversing, should we set up another group on something like discord? If so, i'm happy to set that up.

[Edited on 15-2-2018 by Vosoryx]

Sure. I plan to set it up on the latest LTS version of Ubuntu, but AWS has all sorts of its own services that might be better for discord. It'd also be possible to set up a system for uploading large files using your SM login, then have the files stored on S3. phpBB has a million different extensions too, and new ones are constantly being developed, unlike with XMB.

The way I'd planned to do it, there's really no risk of losing data, since two sites would be running until we've determined that we're sure that nothing is missing. I'm less sure about the wiki, but that'd just be a matter of moving everything over as-is, and I can't imagine it being very involved.

Themes can both be imported and edited, so it should be possible to match things pretty closely to the default theme here. Just so the site is recognizable, if nothing else.

[Edited on 2/15/18 by Melgar]




The first step in the process of learning something is admitting that you don't know it already.

I'm givin' the spam shields max power at full warp, but they just dinna have the power! We're gonna have to evacuate to new forum software!
View user's profile View All Posts By User
Vosoryx
Hazard to Others
***




Posts: 282
Registered: 18-6-2017
Location: British Columbia, Canada
Member Is Offline

Mood: Serial Apple Enjoyer

[*] posted on 14-2-2018 at 18:28


Right then - just cuz it took me about three minutes i set up a discord channel.
For those of you who don't know, discord is like skype but actually works. It does require an account, but that's easy.
This link should never expire:

https://discord.gg/KqMqDnX

I doubt anyone would be interested in moving conversation over there, but just in case. It allows talking, and text-chatting is way easier.

Edit:
Call me amateur (that's fine) but what does LTS and AWS stand for? As mentioned, i'm not great on the whole programming/computer lingo thing. I'm hoping that I can help in an organizational manner, as that seems to be the only thing I can do well.

[Edited on 15-2-2018 by Vosoryx]




"Open your mind son, before someone opens it for you." - Dr. Walter Bishop
View user's profile View All Posts By User
Melgar
Anti-Spam Agent
*****




Posts: 2004
Registered: 23-2-2010
Location: Connecticut
Member Is Offline

Mood: Estrified

[*] posted on 14-2-2018 at 18:59


LTS is "Long Term Support". Ubuntu releases a new version every six months, but only an LTS version every two years. LTS is considered more stable, and bugfixes for LTS versions are given a much higher priority.

AWS is "Amazon Web Services". Basically, they're able to charge the lowest prices by making their services "a la carte". That is, you add services as you need them, and scale up when you need it. About half of the internet runs on AWS, and when there's an outage every few years, the internet becomes practically useless. Their pricing schemes can be confusing, but only because they charge based on what services cost them, which isn't necessarily intuitive to customers. For storing large files, for example, they charge based on both file size and how often it's downloaded.

The trouble with discord is that it doesn't generate the sort of lengthy discussions that are preserved here for posterity. So I'm a bit ambivalent.




The first step in the process of learning something is admitting that you don't know it already.

I'm givin' the spam shields max power at full warp, but they just dinna have the power! We're gonna have to evacuate to new forum software!
View user's profile View All Posts By User
Vosoryx
Hazard to Others
***




Posts: 282
Registered: 18-6-2017
Location: British Columbia, Canada
Member Is Offline

Mood: Serial Apple Enjoyer

[*] posted on 14-2-2018 at 19:23


Quote: Originally posted by Melgar  

The trouble with discord is that it doesn't generate the sort of lengthy discussions that are preserved here for posterity. So I'm a bit ambivalent.


Yeah I thought about that, but seen as how updating the site is going to mostly involve short conversations, not in depth scientific discussions, I thought it might be appropriate. And it's I guess we'll find out.


I want to clarify my idea here, because it might be easy to confuse.
I just made the discord to talk about the update, not as a replacement. I was just thinking that a lot of the update will be talking, conversations and debugging, and discord could be an easier place to talk about that because:

A. Messaging is faster and easier. You're right, it's not good (at all) for long, in depth discussions. But:
"Yeah I ran into problem X. Here's a screenshot. Any ideas?"
Is way faster and easier on discord.

B. So much of the change is going to be talking, I don't want to annoy the users of this site during the change who are talking about actual science. It would be needless clutter - and we could talk endlessly on discord without bothering anyone.

Again, I don't really mind. I just put it out there because i'm trying to help out.




"Open your mind son, before someone opens it for you." - Dr. Walter Bishop
View user's profile View All Posts By User
Melgar
Anti-Spam Agent
*****




Posts: 2004
Registered: 23-2-2010
Location: Connecticut
Member Is Offline

Mood: Estrified

[*] posted on 14-2-2018 at 20:08


Ok, for anyone else reading this, I logged into the discord server, and we're talking there. I'll try and keep it open if possible.



The first step in the process of learning something is admitting that you don't know it already.

I'm givin' the spam shields max power at full warp, but they just dinna have the power! We're gonna have to evacuate to new forum software!
View user's profile View All Posts By User
woelen
Super Administrator
*********




Posts: 8027
Registered: 20-8-2005
Location: Netherlands
Member Is Offline

Mood: interested

[*] posted on 15-2-2018 at 04:42


First let me say that I am absolutely positive on someone having the forum migrated to a more modern platforum, which still is supported by developers and has regular (security) updates.

If the forum is transferred, then keep in mind that besides user accounts, threads and individual posts we have other things:
- embedded pictures, available from threads
- references to other posts
- quotes from other posts (you may have to translate the quote and rquote tags)
- LaTeX-like formatted formulas
- Tags, which change appearance of text (bigger, smaller, bold, italics, superscript, subscript, color)

We also have things around the forums (FAQ, library of many many books, Wiki, publications)

I can imagine that it is possible to transfer all these things to the new forum, but we have to be careful that EVERYTHING is transferred to the new forum software.

If you are willing to do this, then I certainly would like it. I also am willing to donate something in return (maybe a nice package of small quantities of different reagent(s) like iodate, bromate, or some metal salts, I have many interesting inorganic compounds, which can be shipped internationally without risk or hassle).

If we transfer the software, then we will need an extensive testing period before we really switch and probably it would also be wise to have someone else review the code of the migration software and/or data of the migrated database and maybe also review the suggested migration plan.
I myself am a software engineer having quite deep knowledge of SQL, ASP, JSP, Java/C/C#/C++, Servlet containers, and I am willing to review things. I, however, am too busy to help with actual migration. I am willing though to do some sparring (e.g. by email or in a special thread).

You also will need Polverone's support, because he has system access and low-level database access.



[Edited on 15-2-18 by woelen]




The art of wondering makes life worth living...
Want to wonder? Look at https://woelen.homescience.net
View user's profile Visit user's homepage View All Posts By User
Melgar
Anti-Spam Agent
*****




Posts: 2004
Registered: 23-2-2010
Location: Connecticut
Member Is Offline

Mood: Estrified

[*] posted on 15-2-2018 at 13:44


Right. What I have right now is a test database, where all the user passwords hashes are reset to the same thing. Trouble is, it's on a virtual machine, and it's a hassle to move files back and forth and such. Also, it's running the 2010 version of Ubuntu, which doesn't seem to work well with IPv6 addresses, along with a host of other problems.

I spun up a 2016 LTS version of Ubuntu on my AWS account. It's only free below 8GB, but you can pay by the hour if that's all you need, so I upped it to 100GB. Also set up an S3 bucket for storing static files. It's easy enough to do a find/replace on any links to internal files, and possible to set up a redirect so that external links (to static files) still work.

Fortunately, most of these issues are easy to solve via global find/replace, regular expressions, and scripts. I'm partial to Ruby, so most of my scripts use that.

For anyone that would like to help, but isn't much for web programming, maybe look up extensions and themes for phpBB that would work as counterparts to what's here.

I really tried to work with this virtual machine, but it felt like I was blowing a decade's worth of dust off of it. I'm hoping Polverone doesn't mind using AWS. It's the best host to use if you want to control costs and don't know exactly what you'll need when you start out. I had a 8GB EBS drive, and scaled it to 100GB when I saw the size of the database dump, for example. And that doesn't include static files, which would be hosted on S3, for a lot less than regular storage. I set up an FTP server that will automatically put uploaded files into S3, but I haven't decided how that will work with authentication. I'd imagine there's some phpBB extension that allows file uploading, but haven't looked into it much.

Right now, I'm trying to upload an 8GB file over a connection that can upload about 5 MB every minute. According to my calculations, the upload should take 26 hours.

So, how's everyone doing?




The first step in the process of learning something is admitting that you don't know it already.

I'm givin' the spam shields max power at full warp, but they just dinna have the power! We're gonna have to evacuate to new forum software!
View user's profile View All Posts By User
aga
Forum Drunkard
*****




Posts: 7030
Registered: 25-3-2014
Member Is Offline


[*] posted on 15-2-2018 at 14:07


Polverone Owns this board.

Basically it is His decision, not a consensus.

I admire some of what you're trying to do.

Chances are that Other things are happening at the moment.

There is no way on earth that Polverone would choose something that originated on SM (probably) given the invasive Evil.

He'd more likely go with his own choice, seeing as it's HIS board and HIS money.




View user's profile View All Posts By User
woelen
Super Administrator
*********




Posts: 8027
Registered: 20-8-2005
Location: Netherlands
Member Is Offline

Mood: interested

[*] posted on 15-2-2018 at 14:20


Have a look at hetzner.de for cloud servers. A quad-core CPU, 16 GByte of RAM and 80 GByte SSD system costs appr. EUR 15 per month. With 2 CPU's, 8 GByte RAM you'll pay less than EUR 10 per month.

Things like backups, taking image snapshots and so on also is arranged. You provide your own OS (e.g. Ubuntu 16.0x LTS) and can run whatever you like. You have 500 MBit/s internet access and can use many TBytes per month of data.

Hosting is in Germany. I have very good experience with this.

https://www.hetzner.com/cloud

I have no other relation with Hetzner than just being a happy user.




The art of wondering makes life worth living...
Want to wonder? Look at https://woelen.homescience.net
View user's profile Visit user's homepage View All Posts By User
madcedar
Hazard to Others
***




Posts: 116
Registered: 10-9-2009
Member Is Offline

Mood: No Mood

[*] posted on 15-2-2018 at 15:16


Quote: Originally posted by woelen  

https://www.hetzner.com/cloud

I have no other relation with Hetzner than just being a happy user.


Thank you for the link Woelen, nice find.
View user's profile View All Posts By User
RogueRose
International Hazard
*****




Posts: 1595
Registered: 16-6-2014
Member Is Offline


[*] posted on 15-2-2018 at 15:21


I've installed and done some admin on phpBB 2 and 3 and found it a nice platform, easy to install, admin, etc and it has a very nice support community. There are lots of nice themes, plugins, mods, etc and there are LOTS of developers who will gladly make custom ones for a very reasonable price. It seems to be maybe the second most popular forum software next to vBulletin when it comes to mods and support (at least in my research back in ~2014).

I would imagine migration to be pretty simple and found a few of my migrations to be painless, though it was only thread content, members, forum lists and didn't do PM's or thread data, which is kind of important in this forum.

I also used vBulletin 3, 4 and 5 and thought 5 was a major step back for them and I stopped using it there and focused on phpBB 2 (before 3 came out in official release). IDK if they have changed much since 5 but I know it seemed to be a general consensus that 5 was not as "easy" or "nice" as the earlier versions and there was something about security if I remember correctly. While I do like the layout a lot, it is basically my favorite forum software, I don't know if it is really any better than phpBB.

[Edited on 2-15-2018 by RogueRose]
View user's profile View All Posts By User
RogueRose
International Hazard
*****




Posts: 1595
Registered: 16-6-2014
Member Is Offline


[*] posted on 15-2-2018 at 15:31


Have you looked into XenForo? It is being run on a forum I use and it seems pretty nice and they moved from vBulletin ver 3.XX. It supposedly works very well on small or mobile screens, which I've heard can be difficult (and some complain about this forum related to that issue). Also, supposedly the same coders from vBulletin came over to XenForo, so there may be some decent experience behind it.
View user's profile View All Posts By User
Melgar
Anti-Spam Agent
*****




Posts: 2004
Registered: 23-2-2010
Location: Connecticut
Member Is Offline

Mood: Estrified

[*] posted on 15-2-2018 at 19:07


I've been talking with Polverone, and he helped me get the virtual machine running with test data. He's been meaning to upgrade the software for years now, but it's an enormous amount of work to transition the database to another bulletin board software. I'm willing to give it a shot, and am using AWS because I'm familiar with it, and it's a lot easier for me to work with than a virtual machine. Ultimately, I'd just be developing some scripts, which I'd hand over to Polverone, and which he could then use to transition everything to phpBB.

I appreciate the suggestions for hosts, however AWS is really what all the professionals use, because you don't pay for any services you don't use, and because it can be scaled up as needed. For the main disk drive, price is $0.10 per GB used per month. For large, static files, pricing is $0.03 per GB per month. And storage is the main thing that you pay for. That doesn't preclude Polverone or anyone else from going with a different host anyway, but it makes things easier and cheaper for me, as I figure out what sort of system parameters the site would gain the most benefit from.

I'm really only looking into the possibility of transitioning to phpBB anyway. It might not even be possible. Right now, I'm just putting the public test database onto a remote server so that I can do a better analysis of it and better understand my options. I tried to do that on the virtual machine, but it's just too unwieldily, and I don't have a lot of the analysis tools I'm used to using. But XMB isn't necessarily compatible with the newer releases.

Now that I see how everything is set up, it's clear that only Polverone is able to make changes to the live version, which isn't necessarily a problem. But that means that the only thing I could potentially solve in XMB is the forum spam, and he'd have to do the rest. I can only make recommendations if I understand how the database is set up.

But if I'm going to bother messing around with that, I may as well also look into the possibility of migrating to phpBB while I'm at it, since that would solve dozens of problems at once (though it'd be a lot of work).

I looked into other forum software, but my top priority was keeping a similar look and feel to the site. I like phpBB because it's about the same age as XMB, so it's still pretty old-school looking. When I think of chemistry, I don't necessarily think of polished, high-tech stuff. I think of JACS articles from 1910, chemists recklessly discovering new generations of unstable explosives, and stained wooden benchtops. It's has sort of a steampunk vibe to it, I guess. And the word "madness" hearkens back to that day, even.

If we change any other parts of the site in any capacity, I feel like it should make this site look like it was designed in the 1890s, not the 1990s.

Of course these are all just my own opinions, and you're all welcome to ridicule them.

[Edited on 2/16/18 by Melgar]




The first step in the process of learning something is admitting that you don't know it already.

I'm givin' the spam shields max power at full warp, but they just dinna have the power! We're gonna have to evacuate to new forum software!
View user's profile View All Posts By User
woelen
Super Administrator
*********




Posts: 8027
Registered: 20-8-2005
Location: Netherlands
Member Is Offline

Mood: interested

[*] posted on 16-2-2018 at 00:02


Melgar, what you describe sounds wonderful. Having an 1890's look would just be awesome. I indeed think that fits sciencemadness very well. It would be nice to have a retro look and feel, and at the same time showing high level/quality and responsibility.

For me, the precise hosting system is not important, as long as it can be maintained easily, is future-proof and reliable. Just discuss it with Polverone, as the site's technical owner and creator.

Migration to phpBB certainly must be possible. I gave Polverone a link to a commercial service who can do it for us and who only ask money, once everything is set up correctly and tested. It would be even better if you could do it. You know many peculiarities of the forum and the community.

I cannot help with my time, but I can help with some nice stuff to play with for you, so that in times when migration to phpBB becomes too hot for a while you can cool down with some relaxed and fun chem-playing :P




The art of wondering makes life worth living...
Want to wonder? Look at https://woelen.homescience.net
View user's profile Visit user's homepage View All Posts By User
NEMO-Chemistry
International Hazard
*****




Posts: 1559
Registered: 29-5-2016
Location: UK
Member Is Offline

Mood: No Mood

[*] posted on 16-2-2018 at 04:39


I got a lot of experience with phpbb, it has alot of power and add on features. Its extremely easy to migrate most platforms too, V3.2 has some problems, big problems that if you join the developer club on the forum, you get to learn about.

From memory Polverone knows his php, thats going to be handy. The often touted Xenforo is shite beyond comprehension, i wish people wouldnt keep recommending that shit. Woltlab buliten board is better than xenforo in many ways, but they sting you for add ons and the core still has problems.

If it was sorted i would go wolab myself, but i really dont like the business model behind it. So i also use phpbb 3.2. I would use the Git files though, watch the plugins really carefully, some have horrendous security holes.

But the main ones you likely want for here are good, banning people effectively is pretty easy, spammers are quickly dealt with if you grab the ban hammer plugin. Others that are more persistent will need you to go into the backend and fiddle around a bit.

SQL wise phpbb apart from the password side of things, is easy to transfer other boards to. I have migrated from xenforo and woltlab without the plugins and bridges with zero problem.

I have also migrated VB boards to phpbb, those are the hardest to do, VB has some awful quirks and migrating it is a waste of time and effort.

SMB i have never used, i have not seen the sql structure. But its old, so i am guessing the transfer should be easy. If you want a hand or some testing let me know, I got a couple of VPS sitting about doing nothing and a server at home for testing.

You will need to rewrite the front code of the phpbb, well actually its more of a edit. The main defacers have scripts that look for some of the back links in phpbb3.2, take those out and your not on the radar.

Even the latest build (git build), of phpbb3 is not so different from the other cores, which is good, its a solid core tried and tested, just about all problems with security are plug in related, except one hole they are still trying to fix.

But its good software, surprising its been free all this time! If like many here you can code php then it knocks xenforo out the water. Xenforo is for those that need training wheels on a forum software, its over priced and over rated. Its used a great deal because most forums of size are owned by marketing companies.

Its kind of set up for them, its really easy to do adds etc, which you dont need here.

I take it you have joined area 51 on the phpbb forum?
View user's profile View All Posts By User
Melgar
Anti-Spam Agent
*****




Posts: 2004
Registered: 23-2-2010
Location: Connecticut
Member Is Offline

Mood: Estrified

[*] posted on 19-2-2018 at 11:11


Okay, so I pulled the virtual machine into an AWS server, and got it running there. Logged into it, and dumped the database. It was about 9GB in size. This seemed way too large. Turns out, file attachments are stored in the database rather than as files. While this is somewhat easier to configure and get working, and is fine for small attachments here and there, there would be significant speed and cost benefits to storing large static files on S3 rather than on the higher-write-speed SSD that the database is stored on. I already set up an S3 science madness repo, and currently have the virtual machine (13 GB file) hosted on it:

https://s3.amazonaws.com/sciencemadness/SM-offline2.ova

Straightforward, no? It's optimized for hosting large static files, that you write once, and where you read the entire file whenever you access it. Once you host it, you can't edit it either, but you can remove it and replace it with a new one. It's perfect for things like attachments though, that you don't want to edit anyway. And cost is about $0.03 per GB per month.

Right now, I'm trying to migrate the database into PostgreSQL, which is somewhat more stable, faster, and more powerful than mySQL. It does have a steeper learning curve, but I'm more familiar with using it already, and Polverone has said that he prefers it as well. Also, phpBB is compatible with both. To convert the database to work with phpBB, I'm going to have to use some pretty fancy SQL, and that's something Postgres excels at.

edit: I exported just the attachments table, then set all the attachment data to null, then exported the database again. (ie, exported the entire database minus the attachment data that was stored in it) Now it's 273 MB as opposed to 9 GB. Far more manageable. :D

The plan would be to parse the attachments table into actual files and host those in a normal file hosting system rather than have them be columns in a database.

[Edited on 2/19/18 by Melgar]




The first step in the process of learning something is admitting that you don't know it already.

I'm givin' the spam shields max power at full warp, but they just dinna have the power! We're gonna have to evacuate to new forum software!
View user's profile View All Posts By User
Melgar
Anti-Spam Agent
*****




Posts: 2004
Registered: 23-2-2010
Location: Connecticut
Member Is Offline

Mood: Estrified

[*] posted on 19-2-2018 at 19:37


Okay, so work is underway on converting the XMB database into something useable with phpBB. Trouble is, mySQL uses a bunch of weird workarounds for implementing features that PostgreSQL does much better, and even with their "PostgreSQL compatibility" export, is still incompatible with Postgres without manually editing the schema. A bunch of things would be much easier to do manually than they would be to script. Some things, I'm not sure how important they'd be to transition over, like polls, for instance. We don't use them much. Fortunately, I got pretty good at using regular expressions, which allow for very flexible find and replace functionality, but it's still not simple or quick.

Some things I definitely want to preserve, like external links to discussions on this site. That's a high priority. External links to files that are hosted here seem like a lower priority though. For internal links, it should work fine to just modify the database text to point to the new URL. And I can't imagine external super common.

I'm also setting this up in a way that should drastically lower the hosting costs for Polverone, as long as he doesn't mind switching from Linode to AWS. He still hasn't read my last three U2U messages, so I guess I don't know where he stands on that. But considering his job, there's no way he doesn't already have experience with it.

I definitely promise to pay attention to detail. I have many fond memories of typing some sort of reaction into Google and getting excited when I'd see that one of the results was a discussion from SM. And the two times I posted undocumented reactions and people didn't believe me at first. Then further experimentation by members vindicated me. I certainly wouldn't want to see some half-witted script kiddie break anything.

Okay, now I'm motivated to work on the site some more again. :D Even if it does mean manually fixing all of mySQL's ancient index creation SQL. At least it only needs to be done once, and then it'd be used as a template for any future importing and exporting.




The first step in the process of learning something is admitting that you don't know it already.

I'm givin' the spam shields max power at full warp, but they just dinna have the power! We're gonna have to evacuate to new forum software!
View user's profile View All Posts By User
JJay
International Hazard
*****




Posts: 3440
Registered: 15-10-2015
Member Is Offline


[*] posted on 19-2-2018 at 22:58


MySQL's Postgres export mode really is horrible. But why use Postgres? It's a forum; you don't need common table expressions and time series analysis. MySQL is optimized for document retrievals and whatnot. It actually has better performance than Postgres when doing simple queries of the sort used by forum software.





View user's profile View All Posts By User
Melgar
Anti-Spam Agent
*****




Posts: 2004
Registered: 23-2-2010
Location: Connecticut
Member Is Offline

Mood: Estrified

[*] posted on 19-2-2018 at 23:42


Main reason is that I have four years of experience using Postgres, and Polverone prefers using it too. It's not actually that annoying though, I just had to separate the schema (CREATE TABLEs mostly) from the data SQL. The hard part of doing the upgrade is going to be linking the whole thing together in whatever way phpBB does it. I foresee having to do a lot of complex SQL, and possibly even writing some SQL functions. I wouldn't know where to start with mySQL, but I'm somewhat of an expert in Postgres.

Straight out of the box with no optimization, maybe mySQL works better, but if you use a bunch of partial indices for the most common queries, and use some of the more recently-released indexing algorithms as well, I can't imagine you wouldn't get better performance from Postgres.

Mainly though, it'd be a lot less work for me since I already know it, and even if I was just as experienced with mySQL, the work is mostly going to be in figuring out how to convert the old data into the new format.




The first step in the process of learning something is admitting that you don't know it already.

I'm givin' the spam shields max power at full warp, but they just dinna have the power! We're gonna have to evacuate to new forum software!
View user's profile View All Posts By User
JJay
International Hazard
*****




Posts: 3440
Registered: 15-10-2015
Member Is Offline


[*] posted on 20-2-2018 at 00:58


You do realize that if you use a "bunch" of indexes, you use up cache space and consume more disk bandwidth, right? You also increase the distance that the read-write heads have to travel.

I have in excess of 20 years of database experience, Postgres about 4, MySQL more like 12. I'd say use Postgres over MySQL for almost anything else, but MySQL is really probably better for forums and blogs.




View user's profile View All Posts By User
Melgar
Anti-Spam Agent
*****




Posts: 2004
Registered: 23-2-2010
Location: Connecticut
Member Is Offline

Mood: Estrified

[*] posted on 20-2-2018 at 02:44


Oh, interesting. Would you be interested in auditing the test server once I get everything working?

The difference in performance would be so minor that nobody would be able to tell, I'm sure you'd agree. Like I said, main reason I'm using it is because I know how to. A mySQL database configured by someone who doesn't know what he's doing will certainly run slower than a Postgres database configured by someone who knows what he's doing. And the Postgres query planner will just ignore indices that don't improve lookup speed. The only real downside to having a lot of them is that write speed is reduced, since both the tables and the indices have to be written to.

Quote:
You also increase the distance that the read-write heads have to travel.

Only on magnetic disks. It's been a while since I've seen a host not offer SSD-based storage for the primary disk. :P

Granted, this probably wouldn't apply here, but have you seen anything recently about how the optimizations to Postgres GIN have made it much more useful for multicolumn indexing? At my last job, I was able to slash 100+ MB indexes down to like 16 MB. The main downside is that you can't sort using that index, and you'll only see gains like that when the number of distinct values in the column is relatively small. Anyway, article is here:

http://hlinnaka.iki.fi/2014/03/28/gin-as-a-substitute-for-bi...

Any suggestions on how to implement HTTPS? Most sites stick to one or the other, and usually HTTPS. On the other hand, sometimes when you connect to a public wifi and you need to bring up the screen that shows you that "connect" button, it won't come up if you go to an HTTPS site. There are fewer and fewer sites that have any components that aren't encrypted. So I've always gone to sciencemadness.org when HTTPS was interfering with my ability to log on to public wifi. But that's probably not the best reason to choose a system. :P




The first step in the process of learning something is admitting that you don't know it already.

I'm givin' the spam shields max power at full warp, but they just dinna have the power! We're gonna have to evacuate to new forum software!
View user's profile View All Posts By User
JJay
International Hazard
*****




Posts: 3440
Registered: 15-10-2015
Member Is Offline


[*] posted on 20-2-2018 at 11:43


I've done migrations from MySQL to Postgres and have some idea of how hard they actually are and how long they should take.

You can actually get higher throughput for less if you use HDDs on Amazon: https://aws.amazon.com/blogs/aws/amazon-ebs-update-new-cold-...

Of course, this discussion is entirely academic. Amazon has database services that would usually provide better performance than a database server running on a highly optimized and expensive general-purpose compute instance.




View user's profile View All Posts By User
RawWork
Hazard to Others
***




Posts: 167
Registered: 10-2-2018
Member Is Offline


[*] posted on 20-2-2018 at 11:56


I noticed everybody today uses amazon. All android apps, even google ones. When I used NoRoot Firewall to monitor my connections, I expected I will see different domains being accessed for each app or developer (e.g. google.com or similar for google, facebook.com for facebook) but unfortunately almost all apps accesed the same domain: *.amazonaws.com
Second most common was *.1e400.net...
View user's profile View All Posts By User
 Pages:  1    3  4

  Go To Top