Performance problems

[Caez77]

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]

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]

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]

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]

What version of IPB is this?

Can you send me the full query via PM? That one you posted isn't complete.

[Luis Manson]

I dont know if you have any problems, but be sure to check varnish to reduce load, there is a thread here

[Grumpy]

You should read this
http://www.mysqlperformanceblog.com/2010/12/09/thinking-about-running-optimize-on-your-innodb-table-stop/

[Caez77]

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]

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]

didn't someone here mention they had issues with php-fpm causing issues like this?
may be misremembering though.