Elkarte powered site performance, with specific attention to the database

October 24, 2016, 10:20:27 am

Many of us feel the pressures of getting reasonable performance from a site housed on a server with "limited" hardware. Sure, we'd all love a server with more hardware. So you know what I'm talking about!

Before we get started into the specifics, the following are a set of observations based upon some research done by yours truly. Don't take what I'm saying as gospel. A real sysadmin would rip everything I say to shreds. Your mileage may vary, yada yada. The intent is to present some info, thoughts, and conclusions based upon that info as we know it. It's up to you, the reader, to do your own research, form your own thoughts, and take your own risks.

These are simply my experiences.

First things first. Here's the setup: VPS, 64 bit Centos 6.8, two cores, 2G RAM. It powers a couple of small static page sites that get very little traffic at all. It also houses a site with around 500 pageviews per day. That's a new elk powered site gaining speed. Also there is a busier site with around 12k pageviews per day, and anywhere from 50-400 posts per day. Then there is the big girl, 1.1 M posts, 30k+ pageviews per day, post average around 250 per day, though that has declined recently. It runs php-fpm, with engintron to proxy nginx and apache. Then there is MySQL......

.....or there used to be. Here, that was step one. MySQL was replaced with 10.1.18-MariaDB. This alone reduced page loads by 7-8%. Seems many others share a similar experience. Again, don't take my word for it. Read up!!

This is where the lines blur with conventional wisdom. Here is a post most of us are familiar with:

http://www.simplemachines.org/community/index.php?topic=293441.0

One of the recommendations involves switching some, most, all tables to InnoDB. "Some, most, all" seems to depend on who's opinion you read. Based on our conventional wisdom, this seems a wise move. So I had done that long ago.

Now, like most of us, my arrangement involves always battling memory use compromises. If we believe what we've read on InnoDB, we realize the tables are normally significantly larger than say, MyISAM, or it's MariaDB improved counterpart Aria. Oh, and it's typically recommended we configure my.cnf such that innodb_buffer_pool_size is large enough to contain the entirety of all table indexes AND data in order to derive any benefit from InnoDB. That's ok. Or is it? In this scenario Big Girl's message table is around 1G alone!!! That's half my RAM by itself. Yikes. Anyone see a bad trend here?

Someone would be quick to say I need more hardware. They're correct. I also need more cash for more hardware. But not every problem is solved by throwing more cash at it.

Let's remember keeping that entire message table in memory means all posts are in memory. Posts that no one has read in days. Months. Years. Isn't this an exercise in futility? All sitting in RAM, taking up space that could be used by other data or processes. All making a balance more difficult to achieve.

Now let's consider InnoDB and how it operates. It has row level locking. This means when someone makes a post and simultaneously someone reads a different post, those transactions do not hold up one another. MyISAM/Aria have table level locking so reads and writes cannot occur simultaneously. Oooh! Low hanging fruit! InnoDB makes your forum faster, right? Not so fast....

There is plenty of info suggesting InnoDB reads are slower by anywhere from 4x to an order of magnitude. No big deal. After all, the row level locking more than makes up for that, right? Well...does it? In my case the database read:write ratio is about 4:1. Interesting, since the read:read speed ratio between those engines is about 4:1, or higher. In other words, even though InnoDB can do a couple of simultaneous operations, Aria could perform 4 (or more) operations in the same timespan. So there's that....

One of the touted advantages of InnoDB is the fact the indexes and data exist in memory if we've tuned it properly. That way we don't have to read or write to disk. Or at least that's what we're told. Ah the speed!! But wait! InnoDB is transactional. OK, we've heard that before. What does that mean? Here's the part "they" don't readily tell us...transactional means that each and every operation must be copied to disk before the next operation may begin. So we're running the disk each and every time anyway!! So much for the memory speed advantage....and let's not forget it's a memory hog.....

Then searches must be considered. Remember the read:read ratio? Yeah, it rears up big here.

With MyISAM, Linux largely manages what remains in RAM. By most accounts Linux does a great job of this. No need to keep that spot wrapped up if it won't be used!!!

The proposal here is an average forum may likely see performance improvements sticking with MyISAM/Aria. So far my numbers support it. Page load times have reduced by about 0.07 on average. Search times have dropped from 5 or 6 seconds to retrieve 100 results to less than a half second. Not to mention relinquishing the connection for the next user operation. Furthermore server loads have both decreased and stabilized, memory use is down, available cache is up, swap use has decreased.

Here is something worth mentioning. There is a downside. Since MyISAM is not transactional, should there be a crash data loss could occur. From everything I've read it shouldn't be a catastrophic loss. It should only be the last operation or so. I make frequent updates just in case....

So there you have it. Right, wrong, or indifferent. If you have thoughts, concerns, or questions, feel free to post up! I'm a hack at best! LOL!

Re: Elkarte powered site performance, with specific attention to the database

Reply #1 – October 24, 2016, 11:34:45 am

I have the feeling inno is not such an Holy Grail.
But it's just a feeling, nothing more.
Actually, very few technologies are holy grails having only advantages, but that's for another time.

Quote from: badmonkey – October 24, 2016, 10:20:27 amInnoDB is transactional. OK, we've heard that before. What does that mean? Here's the part "they" don't readily tell us...transactional means that each and every operation must be copied to disk before the next operation may begin. So we're running the disk each and every time anyway!! So much for the memory speed advantage....and let's not forget it's a memory hog.....

Just a note on this: transactional usually refers to write operations, not to read operations: each write operation is atomic and must be concluded before being "available" (for read).
So it should not affect read operations.

The comparison is interesting anyway.
I feel that more than the db engine, a good caching method would give a higher boost to any forum, even if caching in Elk could always be improved.

Re: Elkarte powered site performance, with specific attention to the database

Reply #2 – October 24, 2016, 01:39:24 pm

That's interesting info emanuele. Thanks!!

My main focus was memory management to prevent performance degradation. Hopefully with the added bonus of increased scalability. To emphasize raw site performance, you're absolutely right about the cache. Naturally there are other things that could be done as well.