SecondSight Posted March 16, 2016 Share Posted March 16, 2016 Hello ! I've upgraded my board to version 4 and it's not doing well at all right now : it takes about 40/50 seconds to load a page at peak times, and often the board simply doesn't respond... => I get a list of mysql processes which grows longer and longer, until I stop the website or restart services. => The server load isn't that high : between 3 an 5 (perhaps because of the content being rebuilt ? - it used to be about 1.50 with IPB 3.4). => I see a long list of php lines when doing a Top (SSH) and the first line corresponds to a php or mysql command, using more than 90 % of CPU. IPB 3 was doing very well, mainly because I had the sessions table in memory, but now I don't know what to do regarding IPB 4 and performance. Moreover, I read that I can't place the sessions table in memory with IPB 4. So, can you give me basic pieces of advice to improve performance ? Thank you. Information : - Dedicated Server with 12 processors (Intel(R) Xeon(R) CPU E5-1650 0 @ 3.20GHz) and Centos 6. - PHP 5.5.31 - Zend Engine v2.5.0 with XCache v3.2.0 with the ionCube PHP Loader v4.7.5 with Zend Guard Loader v3.3 with XCache Cacher v3.2.0 - MySQL 5.6.29 Link to comment Share on other sites More sharing options...
RevengeFNF Posted March 16, 2016 Share Posted March 16, 2016 Your tables are in myisam or innodb? Link to comment Share on other sites More sharing options...
SecondSight Posted March 16, 2016 Author Share Posted March 16, 2016 All my tables are MyISAM. Link to comment Share on other sites More sharing options...
RevengeFNF Posted March 16, 2016 Share Posted March 16, 2016 Change them to innodb and optimize your mysql to it. You are having table locks, which slowdowns your whole board. Innodb only have row locks and not table locks. Link to comment Share on other sites More sharing options...
SecondSight Posted March 16, 2016 Author Share Posted March 16, 2016 Ok... Is it possible to change only the tables which cause the locks ? (sessions table, I suppose) Thank you. Link to comment Share on other sites More sharing options...
RevengeFNF Posted March 16, 2016 Share Posted March 16, 2016 Yes, you can do that. But you really should do it for all tables. Link to comment Share on other sites More sharing options...
tnn Posted March 16, 2016 Share Posted March 16, 2016 Right now I have about a dozen innodb tables and the rest MyISAM as I monitor and continue to change tables. After some research I found it was okay to do. But honestly I think you have other issues. Not sure how the cpu can be running at 90%. Is your forum big? Also, innodb uses more memory but from what I understand is overall better. And is your disk HHD or SSD? Link to comment Share on other sites More sharing options...
Flitterkill Posted March 16, 2016 Share Posted March 16, 2016 Don't forget after converting over there is a major impact from initial indexing for the new search index. If your forums are large you will have to give it some time for that to settle out. FYI IonCube is no longer needed for IPS 4 so unless you have other stuff that requires it (ditto for Zend Guard) those can ultimately be ditched if you are trying to get lean. Probably not hurting anything just sitting there though. Long term consider moving over to MariaDB instead of stock MySQL. Search the forums here for the low-down. Link to comment Share on other sites More sharing options...
ASTRAPI Posted March 16, 2016 Share Posted March 16, 2016 Check also some info here: Link to comment Share on other sites More sharing options...
SecondSight Posted March 17, 2016 Author Share Posted March 17, 2016 8 hours ago, Flitterkill said: Don't forget after converting over there is a major impact from initial indexing for the new search index. If your forums are large you will have to give it some time for that to settle out. My database is about 9 Gb big and the uploads directory is about the same size. 8 hours ago, tnn said: And is your disk HHD or SSD? It's an hybrid server with one SSD and two HDD (raid). I'm going to try innodb, but i'll do it slowly since I'm not conversant with it... Can you tell me which tables I should convert to innodb first ? (I'm going to monitor mysql tonight but if you can tell me some table names already, it will help). Than you ! Link to comment Share on other sites More sharing options...
RevengeFNF Posted March 17, 2016 Share Posted March 17, 2016 The post table, topic table, search index table, session table. This are the most important one. But again, you should do it for all. Link to comment Share on other sites More sharing options...
SecondSight Posted March 17, 2016 Author Share Posted March 17, 2016 yes, I will. But I need to do it slowly since I'm not conversant with innodb configuration. Link to comment Share on other sites More sharing options...
RevengeFNF Posted March 19, 2016 Share Posted March 19, 2016 On 17/03/2016 at 8:29 AM, SecondSight said: yes, I will. But I need to do it slowly since I'm not conversant with innodb configuration. We can help you with that. Link to comment Share on other sites More sharing options...
JEFF MACK Posted March 19, 2016 Share Posted March 19, 2016 @SecondSight A few weeks back I was having issues with my web host and not feeling secure with how my server was doing. I was researching hosts, etc and then when actually responding to other posts here in this forum I came across some good posts about performance from the member @ASTRAPI https://invisionpower.com/profile/116854-astrapi/ and the consulting they do for setting up the forum on servers. I sent ASTRAPI a PM and got a very quick response. We had a decent conversation about what I had and what my setup is like. Anyhow, I took the plunge and went all in with them. Service has been fantastic. You can check out my site here http://community.brickpicker.com . If I wasn't loading banners, the pages would probably be as fast as they are here on IPB. He pointed me to a solid host and a solid server that was in my budget. Then he installed this type of setup. Nginx with dynamic modules support maraiDB 10.1 php fpm 5.6 multiple pools for internal load balancing csf firewall Memcache Very diligent about contacting me about doing updates, so when there are new security fixes available he is right on it. Responses are very quick and he loves to do this stuff, really gets into it and wants to make sure you site is performing well and secure. Feel free to write me if you have any questions. I was very happy with their service and will be setting up others serves with them as well. Link to comment Share on other sites More sharing options...
SecondSight Posted March 20, 2016 Author Share Posted March 20, 2016 My posts table is 4 Gb big. I've seen there is a ibf_core_search_index table which is 5 Gb big... I will convert ibf_core_search_index first and see what's going on then. Link to comment Share on other sites More sharing options...
RevengeFNF Posted March 20, 2016 Share Posted March 20, 2016 When you convert your tables to innodb, post here the result of mysqltuner, so we can help you configuring your my.cnf Link to comment Share on other sites More sharing options...
SecondSight Posted March 21, 2016 Author Share Posted March 21, 2016 Hello :) Can you tell me how long it will take to convert a 5 Gb table to innodb, in your opinion ? I may perhaps choose to clone the structure of the existing myIsam table, convert it to innodb and then transfer the existing data, if converting takes too long... Thank you. Link to comment Share on other sites More sharing options...
RevengeFNF Posted March 21, 2016 Share Posted March 21, 2016 Just now, SecondSight said: Hello Can you tell me how long it will take to convert a 5 Gb table to innodb, in your opinion ? I may perhaps choose to clone the structure of the existing myIsam table, convert it to innodb and then transfer the existing data, if converting takes too long... Thank you. It dependes on your server hardware. But it will take some minutes, thats for sure. Make a clone of your database, convert the table and check the time it took. Then decide what is best Link to comment Share on other sites More sharing options...
ThomasS Posted March 22, 2016 Share Posted March 22, 2016 is it wise to change the tables to InnoDB performance-wise, even if the board is a lot smaller? I just checked and everything is myIsam... thining about changing it to InnoDB to avoid dead locks.... is there a reocommended guide how to do this properly (i'm on a hosted service, so no direct access to the my.cnf - should be done via phpmyadmin thanks in advance Link to comment Share on other sites More sharing options...
RevengeFNF Posted March 22, 2016 Share Posted March 22, 2016 4 hours ago, ThomasS said: is it wise to change the tables to InnoDB performance-wise, even if the board is a lot smaller? I just checked and everything is myIsam... thining about changing it to InnoDB to avoid dead locks.... is there a reocommended guide how to do this properly (i'm on a hosted service, so no direct access to the my.cnf - should be done via phpmyadmin thanks in advance If your database is small, you can do it with phpmyadmin without problems. Link to comment Share on other sites More sharing options...
SecondSight Posted March 23, 2016 Author Share Posted March 23, 2016 I tried today to convert the posts and the search tables (using a clone database) to innodb Regarding ibf_forums_posts, it took 11 minutes to finish. As for ibf_core_search_index, I got an error : mysql> ALTER TABLE ibf_core_search_index ENGINE=InnoDB; ERROR 1709 (HY000): Index column size too large. The maximum column size is 767 bytes. I read that the solution was to write this in my.cnf : innodb_large_prefix=ON innodb_file_format=Barracuda and restart mysql, but it didn't help : I still get the same error... Is there a solution ? Thank you. Link to comment Share on other sites More sharing options...
RevengeFNF Posted March 23, 2016 Share Posted March 23, 2016 57 minutes ago, SecondSight said: I tried today to convert the posts and the search tables (using a clone database) to innodb Regarding ibf_forums_posts, it took 11 minutes to finish. As for ibf_core_search_index, I got an error : mysql> ALTER TABLE ibf_core_search_index ENGINE=InnoDB; ERROR 1709 (HY000): Index column size too large. The maximum column size is 767 bytes. I read that the solution was to write this in my.cnf : innodb_large_prefix=ON innodb_file_format=Barracuda and restart mysql, but it didn't help : I still get the same error... Is there a solution ? Thank you. You can empty that table, convert it to innodb and then rebuild the search. Link to comment Share on other sites More sharing options...
SecondSight Posted March 23, 2016 Author Share Posted March 23, 2016 Hello ! I did this : TRUNCATE ibf_core_search_index but I when I try to convert the search table again, I get the same error message : mysql> ALTER TABLE ibf_core_search_index ENGINE=InnoDB; ERROR 1709 (HY000): Index column size too large. The maximum column size is 767 bytes. EDIT : I ran this first : mysql> ALTER TABLE ibf_core_search_index ROW_FORMAT=DYNAMIC; Then I restarted and it seems like it worked... Link to comment Share on other sites More sharing options...
Tracy Perry Posted March 27, 2016 Share Posted March 27, 2016 On 3/19/2016 at 3:53 PM, JEFF MACK said: Nginx with dynamic modules support maraiDB 10.1 php fpm 5.6 multiple pools for internal load balancing csf firewall Memcache Sounds like a standard CentMin Mod stack for CentOS. Link to comment Share on other sites More sharing options...
ipbfuck Posted April 4, 2016 Share Posted April 4, 2016 Hi! i also have all tables to myisam... how i can convert to innodb via script or phpmyadmin? Link to comment Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.