Results 1 to 8 of 8
  1. #1
    Join Date
    Jul 2007
    Location
    Online
    Posts
    53

    Exclamation mysql optimization for VB forum

    I have a friend which owns a 2k alexa rank forum using VB

    The server hangs and he gets mysql connection error in logs
    Database error in vBulletin : mysql_connect() [<a href='function.mysql-connect'>function.mysql-connect</a>]: Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock'
    The server is a dual Pentium D with 4GB RAM

    currently 600 users online and 20% RAM used

    in rush hours there are 2000 users online and 50% RAM used


    We want to optimize mysql - this is the current my.cnf



    [mysqld]
    safe-show-database
    old_passwords
    back_log = 75
    skip-innodb
    max_connections = 2000
    key_buffer = 80M
    myisam_sort_buffer_size = 64M
    join_buffer_size = 1M
    read_buffer_size = 1M
    sort_buffer_size = 3M
    table_cache = 2500
    thread_cache_size = 384
    wait_timeout = 15
    connect_timeout = 10
    tmp_table_size = 64M
    max_heap_table_size = 64M
    max_allowed_packet = 64M
    net_buffer_length = 16384
    max_connect_errors = 10
    thread_concurrency = 8
    read_rnd_buffer_size = 786432
    bulk_insert_buffer_size = 8M
    query_cache_limit = 4M
    query_cache_size = 64M
    query_cache_type = 1
    query_prealloc_size = 262144
    query_alloc_block_size = 65536
    transaction_alloc_block_size = 8192
    transaction_prealloc_size = 4096
    default-storage-engine = MyISAM

    [mysql.server]
    user=mysql
    basedir=/var/lib
    old-passwords = 1

    [mysqld_safe]
    err-log=/var/log/mysqld.log
    pid-file=/var/run/mysqld/mysqld.pid
    nice = -5
    open_files_limit = 8192

    [mysqldump]
    quick
    max_allowed_packet = 16M

    [myisamchk]
    key_buffer = 64M
    sort_buffer = 64M
    read_buffer = 64M
    write_buffer = 64M

    [mysqlhotcopy]
    interactive-timeout

    Your help will be appreciated.
    And we became three!

  2. #2
    Join Date
    Feb 2008
    Location
    Austin, Texas
    Posts
    272
    Is MySQL actually terminating and need to be restarted or does it just stop responding to connections for a period of time?
    ██ HermeTek Network Solutions
    ██ Network design, security, and implementation
    ██ BSD & Linux consulting, training, and hosting
    ██ https://www.hermetek.com | 1.866.235.1288

  3. #3
    Join Date
    Jul 2007
    Location
    Online
    Posts
    53
    The server stops responding and reboot is the only thing to bring it back
    And we became three!

  4. #4
    Join Date
    Feb 2008
    Location
    Austin, Texas
    Posts
    272
    I would start with greatly increasing your key_buffer_size and enabling slow query logging. It sounds like it may also be a segfault issue, so recheck your logs (including syslog, messages depending on your OS) for signs of such. If you are running out of ram, you may not have that in your logs (and you shouldn't be, anyway) but check during peak usage hours to see if you are using swap space. If the machine is in a datacenter, have a support technician console it the next time it locks instead of just rebooting it. It may be printing call traces or other interesting errors to the screen.
    ██ HermeTek Network Solutions
    ██ Network design, security, and implementation
    ██ BSD & Linux consulting, training, and hosting
    ██ https://www.hermetek.com | 1.866.235.1288

  5. #5
    Join Date
    Jul 2007
    Location
    Online
    Posts
    53
    Thanks for your tip. I increased it to be

    max_connections = 3000
    key_buffer = 512M
    Will see in peak hours how will this perform

    Also I'd like to add that the server only serves this forum. no other sites.
    And we became three!

  6. #6
    Join Date
    Feb 2008
    Location
    Austin, Texas
    Posts
    272
    Yeah, I'm thinking it's not so much a MySQL issue as a general server issue since the entire server locks up just after that error in the log files. If it were a MySQL issue you would just find the daemon itself terminating unless it is eating all of your available resources.
    ██ HermeTek Network Solutions
    ██ Network design, security, and implementation
    ██ BSD & Linux consulting, training, and hosting
    ██ https://www.hermetek.com | 1.866.235.1288

  7. #7
    To tweak and optimize your my.cnf, you could just use a tool like tuning-primer.sh : day32.com/MySQL/tuning-primer.sh
    What it does:
    This script takes information from "SHOW STATUS LIKE..." and "SHOW VARIABLES LIKE..."
    to produce sane recomendations for tuning server variables.
    It is compatable with all versions of MySQL 3.23 and higher (including 5.1).

    # Currently it handles recomendations for the following: Slow Query Log
    # Max Connections
    # Worker Threads
    # Key Buffer
    # Query Cache
    # Sort Buffer
    # Joins
    # Temp Tables
    # Table (Open & Definition) Cache
    # Table Locking
    # Table Scans (read_buffer)
    # Innodb Status
    Great tool.

  8. #8
    Join Date
    Dec 2006
    Posts
    477
    Your max connections looked too high at 2000, let alone 3000 - A Pentium D isn't going to cope with that load. Your probably better off setting a much lower max connections so that when the server becomes saturated, you start turning down connections so it gets a chance to complete whatever is delaying it and recover, rather than accepting more and more work when its already swamped.

    As for diagnosing the problem, try running http://hackmysql.com/mysqlreport then reading http://hackmysql.com/mysqlreportguide

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •