InnoDB and large post table

[Linda A]

My forum is, I believe, pushing 4 million posts. IPS has suggested moving from MyISAM to InnoDB. That sounds reasonable... but memory is a concern. Does anyone know of a good way to estimate the memory requirement to have such a large table set to InnoDB? My understanding is that InnoDB generally requires a lot more free RAM to play with.

[AutoItScript]

Generally you want to set innodb_buffer_pool_size to a value large enough to contain all your database for the best performance.  You can see the size of your DB from phpmyadmin or similar.

[Linda A]

Hmm, 11 GB. But surely the innodb buffer pool only refers to tables set as innodb tables? Most of the tables are MyISAM.

[RevengeFNF]

Hmm, 11 GB. But surely the innodb buffer pool only refers to tables set as innodb tables? Most of the tables are MyISAM.

​You should set all of them to innodb.

Even if you only have for example 8Gb of Ram, you could spend +/- 6Gb to Innodb Buffers.
Innodb is smart enough to put in the buffers the most used content.

[Linda A]

Really? Everything should be InnoDB? I'm just wondering at that since the default is MyISAM -- or is that MyISAM makes sense for small forums, but not for large ones?

Our server has 16GB of RAM, but I'm not sure of how much is being allocated to PHP and other services at any given time.

 

[RevengeFNF]

Really? Everything should be InnoDB? I'm just wondering at that since the default is MyISAM -- or is that MyISAM makes sense for small forums, but not for large ones?

Our server has 16GB of RAM, but I'm not sure of how much is being allocated to PHP and other services at any given time.

 

​If you only use your server to your IPB site, you can spend 80% of your Ram to Innodb Buffers.

One of the biggest advantages of Innodb, is that it doesn't have Table Locks like MyIsam. Only have Row Locks which is a lot better.

Also, you need to optimize your my.cnf to Innodb, but we can help you with that.

[Linda A]

Our current setup is two servers, which both run lighttpd and Varnish caching:

Server 1 does MySQL and the forum.

Server 2 does Wikimedia for a large and pretty popular wiki, while it sends its SQL requests to server 1.

Server 2 is the one with the most memory (16GB, while the other is, I think, 8GB), which is why I'd move MySQL there while also keeping the wiki in place.

 

Here's a related query -- the other suggestion from IPS was to upgrade from MySQL 5.5 to 5.6. A cursory look around suggests that there's some significant performance improvements, but when I looked at independent benchmarks people weren't too impressed. What have you found?

 

 

 

 

[RevengeFNF]

I suggest you to upgrade to MariaDB 10.0 instead 

[Christopher Bergsten]

Hi Linda,

What is the reason for you to look at other engines? did you encounter any problems with the current setup?

 

[Linda A]

Primarily because certain queries end up taking a long time to resolve, locking the table and backing everything up, sometimes cascading into PHP-FPM spawning the maximum number of children and basically choking down the whole server.

 

For example, a query such as this:

 

SELECT p.pid,t.tid, t.forum_id, t.last_post, t.topic_firstpost FROM ibf_posts p  LEFT JOIN ibf_topics t ON ( p.topic_id=t.tid )   WHERE p.author_id=78802 AND p.post_date > 1399371724 AND state != 'link' AND  p.queued=0  AND t.last_post > 1399371724 AND  t.approved=1  AND  t.topic_archive_status IN (0,3)  ORDER BY p.post_date DESC LIMIT 0,5000

 

Our server sometimes takes a very long time to resolve these (and sometimes it takes very little time -- I just ran the query now and it took .41 seconds). I have no idea what determines long/short queries, but in any case when we get a long query that takes tens of seconds to resolve, tables get locked and things pile up, sometimes to the point where the whole server chokes until various things are restarted.

Maybe our issue is not the engine, maybe there's something else that explains why we get inconsistent query times that lead to these issues and if we resolve that MyISAM will do just fine.

[RevengeFNF]

Probably it takes 0,41 seconds when the result is cached in Query Cache... Are you using it?
If its not cached, then it takes the time to process.

In the past i was using MyISAM with IPB and when i changed to InnoDB the speed improvement was very noticeable. Even more when i upgraded Mysql 5.5 to MariaDB 10.

[Linda A]

Hrm. I have an acquaintance who's an enterprise DBA and I'm pretty sure she manages a MariaDB system. I shall query her about it. I do know Wikimedia moved to MariaDB...

[AutoItScript]

Primarily because certain queries end up taking a long time to resolve, locking the table and backing everything up, sometimes cascading into PHP-FPM spawning the maximum number of children and basically choking down the whole server.

​It was locking on IPB4 that forced me to change from MySQL 5.5 MyISAM to MariaDB 10 + Innodb.  I had no problems at all on IPB3 but that was because topic views were updated in a separate table, then then the main topics table was updated on a schedule. So the topics table was only ever locked when a new post was being made, so it was quite happy on MyISAM.

For some reason this was removed in IPB4 and like in the early versions of IPB each topic view now writes to the topic table directly, this requires a table lock just to update the view count. So if you have a long running query you will suddenly see a queue of processes build up that are waiting for a topics table lock purely so they can update the view count. To see it in action on IPB 4 just open up phpmyadmin and watch the processes with auto-update on.  Click on Activity Stream -> Posts and then watch all the processes build up waiting to do views = views + 1 on the topics table - all those users will think they are "hanging".

After I saw that I switched to InnoDB.

[RevengeFNF]

I think Invision should recommends users to switch into Innodb when we are upgrading to IPS4.

[Linda A]

And by switching to InnoDB, we mean switching everything to InnoDB? I'm just concerned about putting everything there when it's maybe not necessary for performance reasons. Trying to figure out how to make sure my 16GB is sufficient to handle hundreds of PHP threads + a large InnoDB database that could require as much as 11.5 GB of buffer.

 

 

[RevengeFNF]

And by switching to InnoDB, we mean switching everything to InnoDB? I'm just concerned about putting everything there when it's maybe not necessary for performance reasons. Trying to figure out how to make sure my 16GB is sufficient to handle hundreds of PHP threads + a large InnoDB database that could require as much as 11.5 GB of buffer.

 

 

​Believe me, its better to have Innodb with 4Gb Buffer than having Myisam.

Like i said in the other posts, your users don't check your whole database everyday right? Innodb is smart and puts on the buffer your most used content.

Off course, the ideal solution is to fit the whole DB in the buffer, but if you don't have the resources, you can put less without any problem.

 

Imagine a site with a Database of 500Gb. Do you think they will put a server(or multiple servers) with 500Gb of Ram for it? If they are rich, yes...

[ASTRAPI]

I have a customer with 60gb database using Innodb and yes we use 64gb buffer for it without any problems

The server has 128gb ram so there is not a problem

Myisam is faster in general and good for small forums that they don't suffer from table locking....

But for big ones Innodb is a must !

If you have the money you can go for MARIADB Galera Cluster (works perfect) so you will get (50% about) better performance instant by adding a second server and (66% about) faster if you add a third one and so on.

You can also use for a temp location a tmpfs partition as it will work much faster but keep in mind that you will need about 2x or 3x more than the size of your db to be sure that all sql functions/operations can run without any problems

[Linda A]

Yes, I can imagine having a 64GB RAM buffer works very well with a 60GB DB! Unfortunately, not really an option, but maybe if we can free up 8GB of 16GB, that'll be enough for InnoDB (especially after nuking almost all the bloated notifications table, since calculated DB size is now 9GB rather than 11.5GB).

Is MariaDB really that much better than MySQL 5.6, though? I was looking around and some of the benchmarks I've seen have been somewhat inconsistent. MariaDB is better at utilizing high numbers of threads, but I'm honestly not sure how many threads our server would generally use.

[AutoItScript]

I think as long as you get on some variant of MySQL 5.6 and then innodb you'll be better off. Every single one of my perf problems on IPB 4 has come down to table locking. Yes, the queries that are causing the locks can and hopefully will be improved as time goes on. But I might as well mitigate it as well. And InnoDB doesn't significantly need more resources, it's just explained that way. In MyISAM you have a key_buffer for indexes only, but then you rely on having OS memory for caching disk access. InnoDB just allows you to set aside memory for index AND data caching before you hit the OS, it's just more obvious where it's being used.

MariaDB seems to be the way the wind is blowing these days, lots of linux distributions have it installed rather than MySQL so I decided that I might as well go that way as my next upgrade to Centos 7 will have MariaDB on there anyway.

 

[Linda A]

I think I'll talk it over with the sysadmin and see how it performs when we move the tables to InnoDB.

 

Besides the buffer size, are there any important tweaks to default innodb settings that need to be made to make sure performance will be optimal?

[RevengeFNF]

I think I'll talk it over with the sysadmin and see how it performs when we move the tables to InnoDB.

 

Besides the buffer size, are there any important tweaks to default innodb settings that need to be made to make sure performance will be optimal?

​innodb_file_per_table
innodb_flush_method=O_DIRECT
innodb_flush_log_at_trx_commit = 0
innodb_buffer_pool_size =12G
innodb_log_file_size=256M
innodb_read_io_threads=8
innodb_write_io_threads=8
innodb_buffer_pool_instances=12
innodb_buffer_pool_dump_at_shutdown = 1
innodb_buffer_pool_load_at_startup = 1
transaction-isolation = READ-COMMITTED

 

In your case, regarding Innodb, i would put those values.

Caution with: innodb_flush_log_at_trx_commit setting to 0, will give you a good boost of performance, but in case of hardware/software failure you can loose at most, the last second of data, If 1 single second is a lot important to you, remove that setting.
The Log Size, i think 256M i fine, but you can put it higher.
I set 12G for the buffer, because i think 4Gb is enough for the rest. If you think its not, lower the buffer. Also the pool instance, set it according to the Buffer.

 

When i was using Mysql 5.5 with MyISAM, normally my Mysql would resposne in average in 200 to 300ms. Sometimes it would go up to seconds when there was a table lock.

Since i moved to MariaDB 10.0(im now using 10.1 version but its beta), my database responds in less than 50ms.

[xtech]

Yes MariaDB is great for both InnoDB and MyISAM. I use MariaDB 10 too, but i think PerconaDB has a little edge over it regarding InnoDB.

[AutoItScript]

Doesn't MariaDB 10 actually use the Percona plugin for InnoDB?

150515 22:28:14 [Note] InnoDB: Highest supported file format is Barracuda.
150515 22:28:14 [Note] InnoDB: 128 rollback segment(s) are active.
150515 22:28:14 [Note] InnoDB: Waiting for purge to start
150515 22:28:14 [Note] InnoDB:  Percona XtraDB (http://www.percona.com) 5.6.23-72.1 started; log sequence number 257648069189
2015-05-15 22:28:14 7fcf9edf7700 InnoDB: Loading buffer pool(s) from .//ib_buffer_pool

 

[Skipy7]

Since i moved to MariaDB 10.0(im now using 10.1 version but its beta), my database responds in less than 50ms.

​What OS version you running MariaDB 10 on as I could not get it to run on my CentOS 7 install.

[AutoItScript]

Centos 6. What was the issue?