Caez77 Posted August 19, 2012 Posted August 19, 2012 Hello I Have some performance problem with my board. MySQL Query are slow longer And I have problem with Alter table. When i Would like to optimize my table for cleaning it my table post is locked for 3 hours For InnoDB tables, OPTIMIZE TABLE is mapped to ALTER TABLE, which rebuilds the table to update index statistics and free unused space in the clustered index. I have installed percona-server-5.5 and optimize my MySQL configuration MySQL is running on a separate server (Bixeon E5205, 12GB RAM and 2*74Go SATA RAPTOR 10000rpm) I have more than 7 millions posts for 165000 members. It is a Good idea to using partitioning table of MySQL ? It is possible on IPb to separate read / write query ? I think it's a good idea for having one MySQL writing master and some MySQL slave for read query with a load balling system. With Master/Master replication, i'm limited with 2 server and I have to change the auto increment value. The other way is to use MySQL Cluster, everyone have trying it with IPB ? Sorry for my poor english
PeterUK Posted August 19, 2012 Posted August 19, 2012 Do you have an example of a slow running query? With that many posts and members, it sounds like you might be running into the same problem I was, which I've since solved. Do you have any very large topics on your board? For example ones which contain over 10,000 posts in a single topic? There should be no reason you need to do any MySQL clustering or anything with a board that size. We handle 5 million posts, over 250,000 members on a similar specced server just fine. Please let me know about the queries and if it's what I think it is then I'll have a solution for you.
Caez77 Posted August 19, 2012 Author Posted August 19, 2012 Yes I Have big topics the biggest is about 20 000 posts. The majority of topics have less than 1000 posts I'm just activate the slow query log with 1 second, before it was configured to 5 seconds Most pages are displayed in 1 or 2 seconds I'm posting and new message with my slow-query log Thanks for your answer.
Caez77 Posted August 19, 2012 Author Posted August 19, 2012 Just an example of a query we take, in moment 10s : SELECT p.*,t.*,m.member_group_id, m.mgroup_others,cca.* FROM ibf_posts p LEFT JOIN ibf_topics t ON ( t.tid=p.topic_id ) LEFT JOIN ibf_members m ON ( m.member_id=p.author_id ) LEFT JOIN ibf_content_cache_posts cca ON ( cca.cache_content_id=p.pid ) WHERE p.queued=0 AND t.approved=1 AND p.author_id=31499 AND p.new_topic=0 AND t.forum_id IN (
PeterUK Posted August 19, 2012 Posted August 19, 2012 What version of IPB is this? Can you send me the full query via PM? That one you posted isn't complete.
Luis Manson Posted August 20, 2012 Posted August 20, 2012 I dont know if you have any problems, but be sure to check varnish to reduce load, there is a thread here
Grumpy Posted August 20, 2012 Posted August 20, 2012 You should read this http://www.mysqlperformanceblog.com/2010/12/09/thinking-about-running-optimize-on-your-innodb-table-stop/
Caez77 Posted August 20, 2012 Author Posted August 20, 2012 I'm not using Varnish for the moment. I'm using Nginx + php-fpm I'm using IPB3.1 and i can't perform the upgrade for the moment For the moment, i have separate my innodb table with the innodb_per_table option @PeterUK i'm sending the querys today
PeterUK Posted August 20, 2012 Posted August 20, 2012 You shouldn't need to do table partitioning either, your machine, properly configured, should be able to handle those queries. A few things: 1) Have you run the Database Index Checker from the Admin CP? 2) Are you running Sphinx for search (if you're not, on a board your size you should seriously consider it)? The problems I was describing with our board were with 3.3.X (and probably 3.2.X, we skipped that series). We never had long running queries in 3.1.X so I don't believe my solution will help you but I'll take a look anyway.
Dmacleo Posted August 20, 2012 Posted August 20, 2012 didn't someone here mention they had issues with php-fpm causing issues like this? may be misremembering though.
Recommended Posts
Archived
This topic is now archived and is closed to further replies.