Geeklog Forums

I'm deciding whether or not to use InnoDB for the fresh install I'm working on right now. I'm going to be setting up the newest geeklog version, and after completing my new template import my old database into it and move my domain to it.

InnoDB is supposed to be faster for larger sites per the doc notes. But at the cost of more complicated backups?

What is the recommended visitors/hour before InnoDB becomes the better choice? What kind of problems will I have with the backups?

---

www.antisource.com

There is no hard and fast limit. It is dependent on your hardware and how you are using geeklog. Most people choose one over the other based on personal preference. Only really huge sites end up choosing one over the other for performance reasons. Remember though, you can always move from InnoDB to MyISAM and back again...

-Vinny

Haha that doesn't make my decision any easier. Okay let me put it this way - last month I used up 500mb of bandwidth. Not a lot, but the month before was 350, befoer that was 200... however many unique visitors I got last month, I get an additional 1500. As I add more articles the growth rate will increase.

So let's say I end up serving 20gb of bandwidth (for a lean site). Your opinion, do you think InnoDB would help?

Also, what is the difference with backups when using InnoDB?

---

www.antisource.com

First, let me point out that I'm not the database expert on the team, so take this with a grain of salt (and someone please correct me if some or all of this is nonsense).

From what I understand, Geeklog's database layout has one problem that is causing problems on the really big sites, and that is the counters, e.g. the story counters. When they're updated, it blocks access to that table for a short time (when using MyISAM tables). Switching to InnoDB tables is one solution, the other would be to move those counters to a separate table.

The problem with the backups, as far as I understand, is that it's difficult to create a consistent backup when using InnoDB tables. The solution here would be to either lock the database when you're doing the backup, effectively blocking any write access for the time being (which includes sessions, i.e. the ability to log in) - or to buy InnoDB's backup solution.

I wasn't aware of the backup issue when I added InnoDB support. At the time, it seemed like the easier way to go than separating out the counters ...

Can't give you any numbers. The idea originally came from the guys who run macfixit.com which, at the time, had something like 250,000 users. AFAIK, they've moved the counters to separate tables now, though.

bye, Dirk

When do the story counters get updated? When I add a story, or everytime the page is served? I actually have removed the counts from my template, but I'm guessing that the php still has to do the calculations when the code is ran. Maybe you could add something in the config file to disable counters? Not just for display but for calculating stats. That would probably be a big boost in performance. Then the next issue would be blaine's plugin - since he's sharing the database, that will have writes too. If it were a separate database, that would fix the problem?

Maybe I will try out InnoDB just to try it out. And I could always convert it back like vinny said?

---

www.antisource.com

When the page is served.


Exactly.


Yeah, I guess that would work. It would render some of the stats useless, though.


Table, not database.

The forum plugin has the same problem with its view counters. But it's independent of the story counters.


Yes. There's a script (in the admin/install directory) to convert MyISAM tables to InnoDB tables that can trivially be adapted. Or simply do it in phpMyAdmin.

bye, Dirk