Need help configuring my.cnf for my server..

Filed under: nnxj.com — smith @ March 10, 2010 edit
  • Hi there,

    I was wondering if anybody could help me with this slight problem.

    I need help optimizing mysql for vbulletin. The server is also used for massive downloading so I need to find ways of reducing serverload.

    Server Info:
    Redhat Linux 8.0
    Intel Celeron 2200 mhz
    256 ram (will upgrade soon..hopefully)
    60 GB HD
    apache 1.3.27
    PHP Version 4.3.1
    mysql 4.0.12
    phpa installed

    Can anyone suggest a good my.cnf file setup?

    Any help would be greatfully appreciated.

    Thanks,
    Scott


  • replace or create a /etc/my.cnf file with the below contents and restart mysql

    Thanks George you are an amazingly nice guy! The world needs more people like you.


  • if you can please provide the following in this format

    1. is this on dedicated or shared virual server
    2. your server specs. For example:

    cpu speed/type single or dual cpus): dual p3 1ghz
    how much memory installed: 512mb ram
    hard drive type/configuration: 60GB EIDE non raid
    linux distributor or windows version: redhat linux 7.3
    apache/IIS version: apache 1.3.27
    PHP version: php 4.3.1
    MySQL version: mysql 3.23.56


    3. if you use mysql 4.x instead of mysql 3.23.x, do you have any innodb type databases/tables on your server ?
    4. if possible how mysql was compiled/installed
    5. your top stats
    6. your mysql configuration variables located at /etc/my.cnf or c:my.cnf if on Windows server if you don't have that file you need to log into telnet and as root user type

    mysqladmin -u root -p variables

    copy and paste output here

    7. your mysql extended-status output either still telnet as root user type

    mysqladmin -u root -p extended-status

    copy and paste output here

    or preferred is to installed extended-status output script which is either located

    - in your vB 2.2.6 or higher vB version's zip file extra's folder, upload mysqlinfo.php script to your site or if you're on an pre vB 2.2.6 install go to
    - http://vbulletin.com/forum/showthread.php?threadid=3477 and install that scrip making sure to edit $mysqllogin line with your own mysqlusername and password

    and post url to that here

    8. oh and is your vB the only thing on the server? or other scripts? sites?

    9. how many average and max concurrent users on your vB forum ?

    10. create a file named phpinfo.php and place this code in it and post the url/link to it from your web site

    phpinfo();
    ?>

    i.e. yourdomain.com/phpinfo.php

    11. if you run Apache and you have your own dedicated server or access to your httpd.conf (apache configuration file) can you post the values you have set for the following :

    KeepAlive
    MaxKeepAliveRequests
    KeepAliveTimeout
    MinSpareServers
    MaxSpareServers
    StartServers
    MaxClients

    12. what version of vB are you running ?


  • http://www.vbulletin.com/forum/showthread.php?threadid=70117


  • replace or create a /etc/my.cnf file with the below contents and restart mysql


    [mysqld]
    max_connections = 400
    key_buffer = 16M
    myisam_sort_buffer_size = 64M
    join_buffer_size = 1M
    read_buffer_size = 1M
    sort_buffer_size = 2M
    table_cache = 1024
    thread_cache_size = 128
    wait_timeout = 14400
    connect_timeout = 10
    max_allowed_packet = 16M
    max_connect_errors = 10
    query_cache_limit = 1M
    query_cache_size = 32M
    query_cache_type = 1
    skip-innodb

    [mysqld_safe]
    open_files_limit = 8192

    [mysqldump]
    quick
    max_allowed_packet = 16M

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


  • 1) Dedicated

    2) Listed in previous post

    3) Dont know..

    4) Compiled by whm

    5) 2:25pm up 16:52, 1 user, load average: 1.00, 0.78, 0.58
    90 processes: 88 sleeping, 2 running, 0 zombie, 0 stopped
    CPU states: 24.5% user, 2.7% system, 0.0% nice, 72.7% idle
    Mem: 223068K av, 207248K used, 15820K free, 0K shrd, 23048K buff
    Swap: 1052248K av, 25512K used, 1026736K free 124908K cached

    PID USER PRI NI SIZE RSS SHARE STAT %CPU %MEM TIME COMMAND
    10030 root 15 0 4084 4084 3688 S 20.8 1.8 1:22 httpd
    9214 mysql 15 0 22228 6252 2192 S 5.0 2.8 17:16 mysqld

    6) [mysqld]
    set-variable = max_connections=450

    7) +--------------------------+------------+
    Variable_name Value
    +--------------------------+------------+
    Aborted_clients 1
    Aborted_connects 1
    Bytes_received 105740838
    Bytes_sent 1294105303
    Com_admin_commands 0
    Com_alter_table 107
    Com_analyze 0
    Com_backup_table 0
    Com_begin 0
    Com_change_db 31254
    Com_change_master 0
    Com_check 0
    Com_commit 0
    Com_create_db 1
    Com_create_function 0
    Com_create_index 0
    Com_create_table 4
    Com_delete 2184
    Com_delete_multi 0
    Com_drop_db 0
    Com_drop_function 0
    Com_drop_index 0
    Com_drop_table 0
    Com_flush 6
    Com_grant 0
    Com_ha_close 0
    Com_ha_open 0
    Com_ha_read 0
    Com_insert 5982
    Com_insert_select 671
    Com_kill 0
    Com_load 0
    Com_load_master_data 0
    Com_load_master_table 0
    Com_lock_tables 0
    Com_optimize 0
    Com_purge 0
    Com_rename_table 0
    Com_repair 0
    Com_replace 1151
    Com_replace_select 26
    Com_reset 0
    Com_restore_table 0
    Com_revoke 0
    Com_rollback 0
    Com_select 488782
    Com_set_option 65
    Com_show_binlog_events 0
    Com_show_binlogs 0
    Com_show_create 65
    Com_show_databases 9
    Com_show_fields 65
    Com_show_grants 0
    Com_show_keys 0
    Com_show_logs 0
    Com_show_master_status 0
    Com_show_new_master 0
    Com_show_open_tables 0
    Com_show_processlist 99
    Com_show_slave_hosts 0
    Com_show_slave_status 0
    Com_show_status 2
    Com_show_innodb_status 0
    Com_show_tables 1
    Com_show_variables 3
    Com_slave_start 0
    Com_slave_stop 0
    Com_truncate 0
    Com_unlock_tables 0
    Com_update 66381
    Connections 31394
    Created_tmp_disk_tables 1044
    Created_tmp_tables 12582
    Created_tmp_files 0
    Delayed_insert_threads 0
    Delayed_writes 0
    Delayed_errors 0
    Flush_commands 1
    Handler_commit 0
    Handler_delete 3608
    Handler_read_first 138421
    Handler_read_key 3317288
    Handler_read_next 2726459
    Handler_read_prev 59530
    Handler_read_rnd 2981478
    Handler_read_rnd_next 6692789
    Handler_rollback 0
    Handler_update 90829
    Handler_write 175615
    Key_blocks_used 1871
    Key_read_requests 14750868
    Key_reads 1468
    Key_write_requests 38524
    Key_writes 33028
    Max_used_connections 9
    Not_flushed_key_blocks 0
    Not_flushed_delayed_rows 0
    Open_tables 102
    Open_files 164
    Open_streams 0
    Opened_tables 459
    Questions 628247
    Qcache_queries_in_cache 0
    Qcache_inserts 0
    Qcache_hits 0
    Qcache_lowmem_prunes 0
    Qcache_not_cached 0
    Qcache_free_memory 0
    Qcache_free_blocks 0
    Qcache_total_blocks 0
    Rpl_status NULL
    Select_full_join 2249
    Select_full_range_join 645
    Select_range 85712
    Select_range_check 0
    Select_scan 90795
    Slave_open_temp_tables 0
    Slave_running OFF
    Slow_launch_threads 0
    Slow_queries 7
    Sort_merge_passes 0
    Sort_range 47759
    Sort_rows 3049751
    Sort_scan 49978
    Table_locks_immediate 745478
    Table_locks_waited 90
    Threads_cached 8
    Threads_created 10
    Threads_connected 2
    Threads_running 1
    Uptime 29857
    +--------------------------+------------+

    8) Only one other small php download script. Script itself is small but downloads consume massive bandwidth.

    9) Average is around 50 Max is around 200

    10) Removed

    11) KeepAlive On
    MaxKeepAliveRequests 100
    KeepAliveTimeout 15
    MinSpareServers 5
    MaxSpareServers 10
    StartServers 5
    MaxClients 150

    12) 2.3.0







    • #If you have any other info about this subject , Please add it free.#
    Your name:
    E-mail:
    Telphone:

    Your comments:


    If you have any other info about Need help configuring my.cnf for my server.. , Please add it free.