The Community Forums

Interact with an entire community of cPanel & WHM users!
  1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

Invision Power Board Taking Over Server

Discussion in 'General Discussion' started by dkz, Mar 22, 2005.

  1. dkz

    dkz Well-Known Member

    Joined:
    Sep 10, 2004
    Messages:
    100
    Likes Received:
    0
    Trophy Points:
    16
    MYSQL Overloading Server Due To Invision Power Board

    Hello,

    I have a user that runs a Invision Power Board ver 1.3 and when left running it takes over MYSQL and makes the load sky rocket to over 90++. Until this issue started happening the load used to rarely go over 1 and usually hovered around .5 Once the account is suspended the server returns to normal. I host quite a few large forums running phpBB & vbulliten so I don't think the server is under powered. Here is the mysql readout at the time of the most recent over load. I caught it at a load of 20

    Code:
    | Id    | User           | Host                 | db             | Command | Time | State        | Info                                                                                                 |
    +-------+----------------+----------------------+----------------+---------+------+--------------+------------------------------------------------------------------------------------------------------+
    | 37441 | dfb_web14      | localhost            | dfb_01         | Query   | 454  | Sending data | SELECT COUNT(DISTINCT(t.tid)) as tcnt, COUNT(DISTINCT(p.pid)) as pcnt FROM ibf_posts p, ibf_topics t |
    | 37444 | dfb_web14      | localhost            | dfb_01         | Query   | 453  | Locked       | UPDATE ibf_topics SET views=views+1 WHERE tid='7002'                                                 |
    | 37452 | dfb_web14      | localhost            | dfb_01         | Query   | 451  | Locked       | SELECT t.*, f.topic_mm_id, f.name as forum_name, f.quick_reply, f.id as forum_id, f.read_perms, f.re |
    | 37462 | dfb_web14      | localhost            | dfb_01         | Query   | 447  | Locked       | SELECT t.*, f.topic_mm_id, f.name as forum_name, f.quick_reply, f.id as forum_id, f.read_perms, f.re |
    | 37489 | dfb_web14      | localhost            | dfb_01         | Query   | 433  | Locked       | SELECT COUNT(DISTINCT(t.tid)) as tcnt, COUNT(DISTINCT(p.pid)) as pcnt FROM ibf_posts p, ibf_topics t |
    | 37490 | dfb_web14      | localhost            | dfb_01         | Query   | 439  | Locked       | SELECT t.*, f.topic_mm_id, f.name as forum_name, f.quick_reply, f.id as forum_id, f.read_perms, f.re |
    | 37502 | dfb_web14      | localhost            | dfb_01         | Query   | 434  | Locked       | SELECT t.*, f.topic_mm_id, f.name as forum_name, f.quick_reply, f.id as forum_id, f.read_perms, f.re |
    | 37521 | dfb_web14      | localhost            | dfb_01         | Query   | 425  | Locked       | UPDATE ibf_topics SET views=views+1 WHERE tid='7002'                                                 |
    | 37524 | dfb_web14      | localhost            | dfb_01         | Query   | 391  | Locked       | SELECT * FROM ibf_topics WHERE forum_id='3' AND tid='6551'                                           |
    | 37541 | dfb_web14      | localhost            | dfb_01         | Query   | 418  | Locked       | SELECT COUNT(DISTINCT(t.tid)) as tcnt, COUNT(DISTINCT(p.pid)) as pcnt FROM ibf_posts p, ibf_topics t |
    | 37545 | dfb_web14      | localhost            | dfb_01         | Query   | 416  | Locked       | SELECT t.*, f.topic_mm_id, f.name as forum_name, f.quick_reply, f.id as forum_id, f.read_perms, f.re |
    | 37553 | dfb_web14      | localhost            | dfb_01         | Query   | 412  | Locked       | UPDATE ibf_topics SET views=views+1 WHERE tid='7002'                                                 |
    | 37563 | dfb_web14      | localhost            | dfb_01         | Query   | 409  | Locked       | SELECT COUNT(DISTINCT(t.tid)) as tcnt, COUNT(DISTINCT(p.pid)) as pcnt FROM ibf_posts p, ibf_topics t |
    | 37636 | dfb_web14      | localhost            | dfb_01         | Query   | 380  | Locked       | SELECT t.*, f.topic_mm_id, f.name as forum_name, f.quick_reply, f.id as forum_id, f.read_perms, f.re |
    | 37647 | dfb_web14      | localhost            | dfb_01         | Query   | 376  | Locked       | SELECT t.*, f.topic_mm_id, f.name as forum_name, f.quick_reply, f.id as forum_id, f.read_perms, f.re |
    | 37661 | dfb_web14      | localhost            | dfb_01         | Query   | 369  | Locked       | UPDATE ibf_topics SET views=views+1 WHERE tid='7002'                                                 |
    | 37682 | dfb_web14      | localhost            | dfb_01         | Query   | 358  | Locked       | SELECT COUNT(DISTINCT(t.tid)) as tcnt, COUNT(DISTINCT(p.pid)) as pcnt FROM ibf_posts p, ibf_topics t |
    | 37689 | dfb_web14      | localhost            | dfb_01         | Query   | 358  | Locked       | SELECT COUNT(DISTINCT(t.tid)) as tcnt, COUNT(DISTINCT(p.pid)) as pcnt FROM ibf_posts p, ibf_topics t |
    | 37692 | dfb_web14      | localhost            | dfb_01         | Query   | 336  | Locked       | SELECT COUNT(DISTINCT(t.tid)) as tcnt, COUNT(DISTINCT(p.pid)) as pcnt FROM ibf_posts p, ibf_topics t |
    | 37697 | dfb_web14      | localhost            | dfb_01         | Query   | 353  | Locked       | SELECT t.*, f.topic_mm_id, f.name as forum_name, f.quick_reply, f.id as forum_id, f.read_perms, f.re |
    | 37703 | dfb_web14      | localhost            | dfb_01         | Query   | 351  | Locked       | SELECT t.*, f.topic_mm_id, f.name as forum_name, f.quick_reply, f.id as forum_id, f.read_perms, f.re |
    | 37721 | dfb_web14      | localhost            | dfb_01         | Query   | 344  | Locked       | SELECT t.*, f.topic_mm_id, f.name as forum_name, f.quick_reply, f.id as forum_id, f.read_perms, f.re |
    | 37722 | dfb_web14      | localhost            | dfb_01         | Query   | 342  | Locked       | SELECT t.*, f.topic_mm_id, f.name as forum_name, f.quick_reply, f.id as forum_id, f.read_perms, f.re |
    | 37723 | dfb_web14      | localhost            | dfb_01         | Query   | 341  | Locked       | SELECT t.*, f.topic_mm_id, f.name as forum_name, f.quick_reply, f.id as forum_id, f.read_perms, f.re |
    | 37765 | dfb_web14      | localhost            | dfb_01         | Query   | 314  | Locked       | SELECT COUNT(DISTINCT(t.tid)) as tcnt, COUNT(DISTINCT(p.pid)) as pcnt FROM ibf_posts p, ibf_topics t |
    | 37820 | dfb_web14      | localhost            | dfb_01         | Query   | 283  | Locked       | UPDATE ibf_forums    SET last_title='Projekt "Nationales Dorf"', last_id='7171', last_post |
    | 37847 | dfb_web14      | localhost            | dfb_01         | Query   | 288  | Locked       | SELECT t.*, f.topic_mm_id, f.name as forum_name, f.quick_reply, f.id as forum_id, f.read_perms, f.re |
    | 37868 | dfb_web14      | localhost            | dfb_01         | Query   | 274  | Locked       | SELECT COUNT(DISTINCT(t.tid)) as tcnt, COUNT(DISTINCT(p.pid)) as pcnt FROM ibf_posts p, ibf_topics t |
    | 37870 | dfb_web14      | localhost            | dfb_01         | Query   | 277  | Locked       | SELECT t.*, f.topic_mm_id, f.name as forum_name, f.quick_reply, f.id as forum_id, f.read_perms, f.re |
    | 37890 | dfb_web14      | localhost            | dfb_01         | Query   | 268  | Locked       | SELECT t.*, f.topic_mm_id, f.name as forum_name, f.quick_reply, f.id as forum_id, f.read_perms, f.re |
    | 37903 | dfb_web14      | localhost            | dfb_01         | Query   | 261  | Locked       | SELECT COUNT(DISTINCT(t.tid)) as tcnt, COUNT(DISTINCT(p.pid)) as pcnt FROM ibf_posts p, ibf_topics t |
    | 37923 | dfb_web14      | localhost            | dfb_01         | Query   | 251  | Locked       | SELECT COUNT(DISTINCT(t.tid)) as tcnt, COUNT(DISTINCT(p.pid)) as pcnt FROM ibf_posts p, ibf_topics t |
    | 37928 | dfb_web14      | localhost            | dfb_01         | Query   | 223  | Locked       | SELECT * FROM ibf_topics WHERE forum_id='3' AND tid='6551'                                           |
    | 37929 | dfb_web14      | localhost            | dfb_01         | Query   | 246  | Locked       | SELECT COUNT(DISTINCT(t.tid)) as tcnt, COUNT(DISTINCT(p.pid)) as pcnt FROM ibf_posts p, ibf_topics t |
    
    I've been experminenting with the my.cnf to help this problem. Here is my current configuration.

    Code:
    
    [mysqld]
    datadir=/var/lib/mysql
    socket=/var/lib/mysql/mysql.sock
    skip-locking
    skip-innodb
    query_cache_limit=1M
    query_cache_size=32M
    query_cache_type=1
    max_connections=125
    max_user_connections=35
    interactive_timeout=100
    wait_timeout=100
    connect_timeout=10
    thread_cache_size=128
    key_buffer=16M
    join_buffer=1M
    max_allowed_packet=16M
    table_cache=256
    record_buffer=1M
    sort_buffer_size=2M
    read_buffer_size=1M
    max_connect_errors=10
    # Try number of CPU's*2 for thread_concurrency
    thread_concurrency=2
    myisam_sort_buffer_size=64M
    log-bin
    server-id=1
    
    [mysql.server]
    user=mysql
    basedir=/var/lib
    
    [safe_mysqld]
    err-log=/var/log/mysqld.log
    pid-file=/var/lib/mysql/mysql.pid
    open_files_limit=8192
    
    [mysqldump]
    quick
    max_allowed_packet=16M
    
    [mysql]
    no-auto-rehash
    #safe-updates
    
    [isamchk]
    key_buffer=64M
    sort_buffer=64M
    read_buffer=16M
    write_buffer=16M
    
    [myisamchk]
    key_buffer=64M
    sort_buffer=64M
    read_buffer=16M
    write_buffer=16M
    
    [mysqlhotcopy]
    interactive-timeout
    
    The database has also been optimized and repaired. The largest table in the database is just under 60 megs.


    I would still consider my self new to all this so if I ask some stupid questions you know why. Why do processes get locked and how can I stop them. I thought wait_timeout=100 would not allow any item sit in the mysql queue for more than 100 seconds but as you can see they are there for over 400 seconds. Also the server is running mysql 4.1 and running the latest cpanel relase update.


    It seems like a Invision forum problem. I know you're probably thinking this should be posted in an invision forum for tech support (and I porbably will) but those forums are mainly for the operation of the forum not the technical side. Besides the users here are more knowledgeable in the operation of mysql and excellent problem solvers!

    Anyway, thank you for the help.
     
    #1 dkz, Mar 22, 2005
    Last edited: Mar 22, 2005
  2. GOT

    GOT Get Proactive!

    Joined:
    Apr 8, 2003
    Messages:
    900
    Likes Received:
    0
    Trophy Points:
    16
    Location:
    Norfolk, VA
    cPanel Access Level:
    DataCenter Provider
    I am not running any servers with MySQL 4.1 yet, but the reason wait_timeout is not closing them is that they are locked, not sleeping. wait_timeout only closes sleeping processes.

    One thing in your my.cnf that I would DEFINITELY comment out is the log-bin line. That is only used for replication, and is not necessary for a standalone MySQL isntallation.

    I can't go through the my.cnf line by line right now, but you might consider backing it up, and replacing it with my-huge.cnf and, again, editing out the log-bin line, then restart MySQL.
     
  3. dkz

    dkz Well-Known Member

    Joined:
    Sep 10, 2004
    Messages:
    100
    Likes Received:
    0
    Trophy Points:
    16
    Thanks for the suggestion.

    I commented what you suggested. I have tried my-huge.cnf and it didn't help with this problem. I updated to 4.1 after this problem has started so I know it's not that.
    If you have any other suggestions, please let me know.

    Thanks.
     
  4. GOT

    GOT Get Proactive!

    Joined:
    Apr 8, 2003
    Messages:
    900
    Likes Received:
    0
    Trophy Points:
    16
    Location:
    Norfolk, VA
    cPanel Access Level:
    DataCenter Provider
    I wouldn't be suprised is commenting that out helped the situation. Be sure to restart mysql and let me know if it doesn't help.
     
  5. dkz

    dkz Well-Known Member

    Joined:
    Sep 10, 2004
    Messages:
    100
    Likes Received:
    0
    Trophy Points:
    16
    Unfortuntly it's not that easy. Here's another config I used that the same error occured. I also used the original my.cnf which only had 2 options in it.

    Code:
    [mysqld]
    max_user_connections = 25
    max_connections=75
    interactive_timeout=100
    wait_timeout=60
    connect_timeout=30
    thread_cache_size=8
    key_buffer=192M
    max_allowed_packet=2M
    table_cache=1024
    sort_buffer=2M
    record_buffer=2M
    thread_cache=8
    # Try number of CPU's*2 for thread_concurrency
    thread_concurrency=8
    myisam_sort_buffer_size=32M
    query_cache_limit=1M
    query_cache_size=16M
    query_cache_type=1
    
    [safe_mysqld]
    err-log=/var/log/mysqld.log
    open_files_limit=8192
    
    [mysqldump]
    quick
    
    
    [isamchk]
    key_buffer = 128M
    sort_buffer_size = 128M
    read_buffer = 2M
    write_buffer = 2M
    [myisamchk]
    key_buffer = 128M
    sort_buffer_size = 128M
    read_buffer = 2M
    write_buffer = 2M
    
    [mysqlhotcopy]
    interactive-timeout
    
     
  6. ttk_2k

    ttk_2k Well-Known Member

    Joined:
    Jan 22, 2005
    Messages:
    45
    Likes Received:
    0
    Trophy Points:
    6
    I would suggest this guy to either upgrade his IPB 1.3 to the latest release, or stop using 1.3.

    I've seen in many places such issues happened with IPB 1.3, the database is not good structured with that version, so IPS has optimized the database tables in IPB 2.0.3, I've had my last board (IPB 2.0.3) running with 100 online users within 15 minutes in average, and the server load was very fine and had never got any problem.

    Best luck!
     
  7. Gx-!

    Gx-! Registered

    Joined:
    Mar 20, 2005
    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    1
    IPB is a resource hog, both for the server and clients. some of the IPB's ive visited... my browsers resource use sky rocketed
     
  8. dkz

    dkz Well-Known Member

    Joined:
    Sep 10, 2004
    Messages:
    100
    Likes Received:
    0
    Trophy Points:
    16
    Thanks for the posts. They have been helpful. Is there any way to setup mysql so no processes get locked and all items stop after a minute or so?

    Thanks.
     
  9. dkz

    dkz Well-Known Member

    Joined:
    Sep 10, 2004
    Messages:
    100
    Likes Received:
    0
    Trophy Points:
    16
    The user decided to upgrade to IPB version 2.xx. I hope it fixes the problem...
     
  10. stdio

    stdio Member

    Joined:
    Oct 18, 2004
    Messages:
    19
    Likes Received:
    0
    Trophy Points:
    1
    Let us know if that fixed your problem.
     
  11. hostingmetro

    hostingmetro Active Member
    PartnerNOC

    Joined:
    May 30, 2003
    Messages:
    40
    Likes Received:
    0
    Trophy Points:
    6
    dkz,

    How did you get the first printout of the connections to the database? We usually have some servers with high load, usually caused by MySQL processes. Moodle is a resource hog for some reason.
     
  12. dkz

    dkz Well-Known Member

    Joined:
    Sep 10, 2004
    Messages:
    100
    Likes Received:
    0
    Trophy Points:
    16
    It looked like updating to IPB ver 2.xx worked. The server load has been fine ever since (about 5 days).


    Log into WHM & under MYSQL select Show MySQL Processes. I would also suggest installing htop. It's a lot more detailed than top and you can really narrow down the problem. http://htop.sourceforge.net/
     
  13. ckizer

    ckizer Well-Known Member

    Joined:
    Aug 12, 2001
    Messages:
    129
    Likes Received:
    0
    Trophy Points:
    16
    Why don't you just ask the user to check these options in his invision powerboard settings? There are settings to prevent it from doing this....
     
  14. dkz

    dkz Well-Known Member

    Joined:
    Sep 10, 2004
    Messages:
    100
    Likes Received:
    0
    Trophy Points:
    16
    Because I never used Invision and didn't know about them. I even posted the questions in a IPB support forum with no mention to this...
     
Loading...

Share This Page