Jump to content

Performance problems


Recommended Posts

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
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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 (

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

Archived

This topic is now archived and is closed to further replies.

  • Recently Browsing   0 members

    • No registered users viewing this page.
×
×
  • Create New...