1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

mysqli added. server load / sql spikes increased

Discussion in 'Server Configuration and Hosting' started by Mr.Rick, May 11, 2013.

  1. Mr.Rick

    Mr.Rick Active Member

    anyone else seeing something like this?

    CPU load has ran at around 0.85 forever, years. Traffic has increased but with cdn, css styles and optimization this box has been rock steady.

    When we went to migrate to xF mysql needed to be recompiled with i ? (not a sys admin).
    Since then cpu load has been about 4x normal

    Mysql has taken up upto 250% of cpu in spikes which is new.

    Slow query log showing nothing. The items that were showing up from a pluggin weve had installed for ages has been removed.
    Intially we thought "Enable Delayed Insert SQL Queries" was the issue, so the 4 xF sites have had it disabled.


    4 quad core cpus - Intel(R) Xeon(R) CPU E5520 @ 2.27GHz
    6gb memory
    raid drives
    httpd 2.2.24 (Unix)
    mysql 5.0.96
    Server load 8.95 (16 CPUs) as of posting this

    NOTE: its using upto 50% cpu at times for this:
    /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/web2.xxxxxxx.com.pid --skip-external-locking
  2. Slavik

    Slavik XenForo Moderator Staff Member

    To be honest, very hard to diagnose something like that, mysqli shouldn't represent the behavior your experiencing.

    I would suggest wiping down your configurations and re-compiling them from scratch to ensure everything is correct.
  3. Slavik

    Slavik XenForo Moderator Staff Member

    Also, 6gb memory on a 16 core system is that a typo?
  4. CyclingTribe

    CyclingTribe Well-Known Member

    Check to see if you have - log_queries_not_using_indexes = 1 - in my.cnf

    If you have, comment it out (or remove it) and restart MySQL.

    Shaun :D
  5. Mr.Rick

    Mr.Rick Active Member

    Hi Slavik, yes, 6 gbs of ram on this box.
    This box has been a rock for some time. Since the sys admin recompiled sql for xF it struggles.

    Clickfinity, this is how my.cnf appears. That line doesn't appear.

    Last night alone my box notified me of 12high load situations

    L: 1 minute load average
    P: number of processes
    % : Swap usage
    PPS in: Packets per second inbound
    PPS out: Packets per second outbound.

    Only thing that we have changed is the recompile for mysql. As part of the trouble shooting we went as far as (not in this exact order):
    - remove similar threads from the big boards
    - removing (not disabling) certain querie intensive pluggins.
    - shutting down the most active bb
    - shutting off xF sites (low traffic anyways)
    - commissioned another box (vps) and moved 10 sites off (low traffic, tons of static files)

    I think I may need some sys admin references. The guy I have been working with since 2005 isn't readily available :/
  6. Mike

    Mike XenForo Developer Staff Member

    CyclingTribe likes this.
  7. Mike

    Mike XenForo Developer Staff Member

    Wait, I just noticed "skip-innodb" in there, so I assume you don't even have that running. That's not ideal - all the tables probably are MyISAM. I don't see a huge amount of MyISAM tuning either, which can make really significant differences.

    But generally, you want InnoDB these days. Aside from having a lot of better features, it tends to be faster.
  8. Mr.Rick

    Mr.Rick Active Member

    Hi Mike

    I almost wonder if the setup is in place because of the vB3 sites we run on that box??
    Now the box is a mix of xF and vB3.

    Much of the tuning, changes are above my head and I think I need to find someone available to help out who has an understanding of vB and xF. With the exception of 1 sys admin that I work with (when around), the others arent familiar with the applications.
  9. Slavik

    Slavik XenForo Moderator Staff Member

    You should enable InnoDB and then convert your XenForo tables to it (apart from the small few which arn't).

    Also, installing MariaDB should be of benefit to you if you arn't already using it, which offers much optimised MyISAM and InnoDB Performance.
    CyclingTribe likes this.
  10. CyclingTribe

    CyclingTribe Well-Known Member

    I'd increase key_buffer to 1000M and reduce tmp_table from 1000 to 128M - but as others have said, enable InnoDB and convert the respective XF tables, then come back and ask for tuning help. (y)

    Shaun :D
  11. Mr.Rick

    Mr.Rick Active Member

    I need to be thoughtful of the vB3 installations on the box. Do you foresee these changes causing issues for those installations?

    This is what my cpu is living through right now, spikes as high as 250%
  12. The Forum Heroes

    The Forum Heroes Well-Known Member

    > mysql 5.0.96

    I'd recommend upgrading to MySQL 5.5 and change the default SQL daemon to Percona.
    Adam Howard likes this.
  13. Slavik

    Slavik XenForo Moderator Staff Member

    Slightly reworked my.cnf also.

    Using many of the values you already had, altered a couple, added innoDB stuff. Anyone spot anything I may have missed? Think I got it all there. The innoDB buffer pool I will change once knowing how much memory is available to play with.

    # GENERAL #
    max_connections = 600
    wait_timeout = 3600
    connect_timeout = 800
    max_allowed_packet = 64M
    max_connect_errors = 10
    bulk_insert_buffer_size = 8M
    query_prealloc_size = 16384
    query_alloc_block_size = 16384
    thread_concurrency = 4
    tmpdir = /usr/tmp
    long_query_time = 5
    ft_min_word_len = 2
    myisam_sort_buffer_size = 32M
    tmp_table_size = 128M
    max_heap_table_size = 32M
    join_buffer_size = 2M
    read_buffer_size = 2M
    sort_buffer_size = 2M
    table_open_cache = 1800
    thread_cache_size = 384
    key_buffer_size = 1024M
    query_cache_limit = 2M
    query_cache_size = 64M
    query_cache_type = 1
    # INNODB #
    innodb_flush_method            = O_DIRECT
    innodb_log_files_in_group      = 2
    innodb_log_file_size          = 256M
    innodb_flush_log_at_trx_commit = 2
    innodb_file_per_table          = 1
    innodb_buffer_pool_size        = 2G
    CyclingTribe and Adam Howard like this.
  14. p4guru

    p4guru Well-Known Member

  15. Slavik

    Slavik XenForo Moderator Staff Member

    I can say this is one poorly server :D

    Just spent 5 hours getting things up to a mostly workable level. A few changes to make tomorow, and some small things to investigate, but, we're getting there!
    Rehan and CyclingTribe like this.
  16. Floren

    Floren Well-Known Member

    I would look at disks performance, what is the utilisation %?
    You can't do magic with MySQL if your disks are slow like turtles.
  17. Slavik

    Slavik XenForo Moderator Staff Member

    After investigating, it seems the issue is lying with apache and not with mysql. (though ive updated everything to mysql 5.5 with Percona)

    Ive also added MPM forks and various other tweaks to see if it will help, but I think the core issue is just voume of traffic on an out the box apache setup.

    Anyway, things seem better in terms of the loads arn't spiking for the moment and have sysstat on 10 minute intervals writing out loads to see how it behaves over the next 24 hours.
    Mr.Rick and CyclingTribe like this.
  18. Tracy Perry

    Tracy Perry Well-Known Member

    nginx for the world. :D
  19. p4guru

    p4guru Well-Known Member

    Nginx or OpenLiteSpeed ? :D
  20. Slavik

    Slavik XenForo Moderator Staff Member

    I'll set up Nginx as a reverse proxy to start,

    OLS seems a little, fresh... for the moment :)
    Adam Howard likes this.

Share This Page