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

XF 1.2 Database Crashes Every Time Total Online Users Cross 6200+

Discussion in 'Troubleshooting and Problems' started by TheBigK, Jan 21, 2014.

  1. TheBigK

    TheBigK Well-Known Member

    I've just observed that in the past ~2 hours, every time the total count of online users (guests) crosses about 6200; MySQL crashes. I then login to cPanel and restart mySQL to bring the site back up.

    My host says they'll optimise MySQL. But could there be any other reason?

    PS: My limited knowledge of things tells me it must be the xf_session table that is crashing.
     
  2. MattW

    MattW Well-Known Member

    You need to look in your MySQL error logs to see what it's happening
     
  3. CyclingTribe

    CyclingTribe Well-Known Member

    If some of those 6200 include multiple unwanted connections from bots and scrapers you should be able to mitigate it a little by blocking the ones you don't want (or won't benefit from) in robots.txt
     
    TheBigK likes this.
  4. TheBigK

    TheBigK Well-Known Member

    Not sure, but the traffic is continuously growing and I've restarted mysql for 5th time now. Why is it that the moment 'user' count grows beyond 6000; the server becomes unstable. I'm already using the latest 'cache' plugin from @xfrocks. I thought it'd be all fine and handle all the traffic without any problem. :(
     
  5. HWS

    HWS Well-Known Member

    You really need to look into your mysql error log to answer that question seriouly and find a solution.

    If you post the last 50 line of the log, I am sure someone can help you out.
     
  6. TheBigK

    TheBigK Well-Known Member

    Any specific information I should be looking for?
     
  7. HWS

    HWS Well-Known Member

    Just the last lines before the crash to see what happened.

    The error log is usually almost empty anyway.
     
    TheBigK likes this.
  8. MattW

    MattW Well-Known Member

    It would be good to see a copy of your my.cnf file to see how MySQL is currently tuned.
     
  9. TheBigK

    TheBigK Well-Known Member

    Let me post the config file details. Gotta make my way to the file :)
     
  10. RoldanLT

    RoldanLT Well-Known Member

    Innodb must be optimize in your my.cnf
    Most important is innodb_buffer_pool_size
     
  11. TheBigK

    TheBigK Well-Known Member

    @RoldanLT & @MattW here we go -

    Code:
    [mysqld]
    max_connections = 150
    skip-external-locking
    key_buffer = 256M
    innodb_buffer_pool_size = 256M
    open_files_limit = 8190
    table_cache = 3184
    myisam_sort_buffer_size = 64M
    query_cache_size = 32M
    max_heap_table_size = 64M
    tmp_table_size = 64M
    thread_cache_size = 16
    wait_timeout = 45
    interactive_timeout = 600
    slow_query_log = 1
    slow_query_log_file = /var/lib/mysql/slow.log
    
    
    
    [mysqldump]
    quick
    max_allowed_packet = 16M
    
    [mysql]
    no-auto-rehash
    
    [isamchk]
    key_buffer = 128M
    sort_buffer_size = 64M
    read_buffer = 2M
    write_buffer = 2M
    
    [myisamchk]
    key_buffer = 128M
    sort_buffer_size = 64M
    read_buffer = 2M
    write_buffer = 2M
    
     
  12. MattW

    MattW Well-Known Member

    That's not very well optimized for InnoDB. How large is your database?
     
  13. TheBigK

    TheBigK Well-Known Member

    ~1 GiB
     
  14. RoldanLT

    RoldanLT Well-Known Member

    Digital Doctor likes this.
  15. MattW

    MattW Well-Known Member

    This is what I'd suggest starting off with

    Code:
    #InnoDB
    innodb_file_per_table=1
    innodb_buffer_pool_size = 2G
    innodb_additional_mem_pool_size = 32M
    innodb_log_files_in_group = 2
    innodb_log_file_size = 256M
    innodb_log_buffer_size = 8M
    innodb_flush_log_at_trx_commit = 2
    Because you are changing the log file size, you will need to stop MySQL, delete ib_logfile0 and ib_logfile1 in /var/lib/mysql and then restart MySQL
     
    RoldanLT likes this.
  16. TheBigK

    TheBigK Well-Known Member

    Yes, I do have 8GB RAM.
     
  17. TheBigK

    TheBigK Well-Known Member

    Could you guys quickly tell me how does increasing the innodb_buffer_pool_size help?
     
  18. MattW

    MattW Well-Known Member

    TheBigK likes this.
  19. Moshe1010

    Moshe1010 Well-Known Member

  20. MattW

    MattW Well-Known Member

    You can set the buffer pool to be approx 80% of the RAM available on the server if you wanted to.
     
    RoldanLT likes this.

Share This Page