Huge forum, big performance problems

  • Hello,

    I have high performance problems with my vBulletin, my MySQL server go away several times each day (when there’s too many people on my server). It takes all my RAM and use more than 350Mb of swap (i got more than 150 sleeping processes when it begin to be critical)

    I got a dedicated server
    I got a 1Ghz Dell server with 512Mb of RAM with 20Go EIDE non raid running on FreeBSD 4.5
    I got PHP 4.1.2 and MySQL 3.23.47
    I got 722 in top user activity
    I got 130 new threads by days and 815 new posts by days
    I got 25 000 users.


    My.cnf :

    [client]
    port = 3306
    socket = /tmp/mysql.sock

    # The MySQL server
    [mysqld]
    port = 3306
    socket = /tmp/mysql.sock
    set-variable = max_connections=500
    set-variable = key_buffer=256M
    set-variable = max_allowed_packet=1M
    set-variable = table_cache=16384
    set-variable = sort_buffer=4M
    set-variable = record_buffer=1M
    set-variable = myisam_sort_buffer_size=64M
    set-variable = thread_cache=20
    server-id = 1


    mysqladmin -u root -p variables :

    +---------------------------------+---------------------------------------------------------------------------------
    --------------------------------------------------------------------------+
    Variable_name Value

    +---------------------------------+---------------------------------------------------------------------------------
    --------------------------------------------------------------------------+
    back_log 50

    basedir /usr/local/

    bdb_cache_size 8388600

    bdb_log_buffer_size 918016

    bdb_home /var/db/mysql/

    bdb_max_lock 10000

    bdb_logdir

    bdb_shared_data OFF

    bdb_tmpdir /var/tmp/

    bdb_version Sleepycat Software: Berkeley DB 3.2.9a: (June 6, 2003)

    binlog_cache_size 32768

    character_set latin1

    character_sets latin1 dec8 dos german1 hp8 koi8_ru latin2 swe7 usa7 cp1251 danish hebrew win125
    1 estonia hungarian koi8_ukr win1251ukr greek win1250 croat cp1257 latin5
    concurrent_insert ON

    connect_timeout 5

    datadir /var/db/mysql/

    delay_key_write ON

    delayed_insert_limit 100

    delayed_insert_timeout 300

    delayed_queue_size 1000

    flush OFF

    flush_time 0

    have_bdb YES

    have_gemini NO

    have_innodb DISABLED

    have_isam YES

    have_raid NO

    have_openssl NO

    init_file

    innodb_additional_mem_pool_size 1048576

    innodb_buffer_pool_size 8388608

    innodb_data_file_path

    innodb_data_home_dir

    innodb_file_io_threads 4

    innodb_force_recovery 0

    innodb_thread_concurrency 8

    innodb_flush_log_at_trx_commit 1

    innodb_fast_shutdown ON

    innodb_flush_method

    innodb_lock_wait_timeout 50

    innodb_log_arch_dir

    innodb_log_archive OFF

    innodb_log_buffer_size 1048576

    innodb_log_file_size 5242880

    innodb_log_files_in_group 2

    innodb_log_group_home_dir

    innodb_mirrored_log_groups 1

    interactive_timeout 28800

    join_buffer_size 131072

    key_buffer_size 268431360

    language /usr/local/share/mysql/english/

    large_files_support ON

    log OFF

    log_update OFF

    log_bin ON

    log_slave_updates OFF

    log_long_queries ON

    long_query_time 10

    low_priority_updates OFF

    lower_case_table_names 0

    max_allowed_packet 1047552

    max_binlog_cache_size 4294967295

    max_binlog_size 1073741824

    max_connections 500

    max_connect_errors 10

    max_delayed_threads 20

    max_heap_table_size 16777216

    max_join_size 4294967295

    max_sort_length 1024

    max_user_connections 0

    max_tmp_tables 32

    max_write_lock_count 4294967295

    myisam_max_extra_sort_file_size 256

    myisam_max_sort_file_size 2047

    myisam_recover_options 0

    myisam_sort_buffer_size 67108864

    net_buffer_length 16384

    net_read_timeout 30

    net_retry_count 1000000

    net_write_timeout 60

    open_files_limit 0

    pid_file /var/db/mysql/host-4-40.pid

    port 3306

    protocol_version 10

    record_buffer 1044480

    record_rnd_buffer 1044480

    query_buffer_size 0

    safe_show_database OFF

    server_id 1

    slave_net_timeout 3600

    skip_locking ON

    skip_networking OFF

    skip_show_database OFF

    slow_launch_time 2

    socket /tmp/mysql.sock

    sort_buffer 4194296

    sql_mode 0

    table_cache 1793

    table_type MYISAM

    thread_cache_size 20

    thread_stack 65536

    transaction_isolation READ-COMMITTED

    timezone CEST

    tmp_table_size 33554432

    tmpdir /var/tmp/

    version 3.23.57-log

    wait_timeout 28800

    +---------------------------------+---------------------------------------------------------------------------------
    --------------------------------------------------------------------------+


    Extended status :

    +--------------------------+----------+
    Variable_name Value
    +--------------------------+----------+
    Aborted_clients 0
    Aborted_connects 0
    Bytes_received 1204573
    Bytes_sent 68424395
    Com_admin_commands 0
    Com_alter_table 0
    Com_analyze 0
    Com_backup_table 0
    Com_begin 0
    Com_change_db 557
    Com_change_master 0
    Com_check 0
    Com_commit 0
    Com_create_db 0
    Com_create_function 0
    Com_create_index 0
    Com_create_table 0
    Com_delete 27
    Com_drop_db 0
    Com_drop_function 0
    Com_drop_index 0
    Com_drop_table 0
    Com_flush 0
    Com_grant 0
    Com_insert 256
    Com_insert_select 1
    Com_kill 0
    Com_load 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 25
    Com_replace_select 0
    Com_reset 0
    Com_restore_table 0
    Com_revoke 0
    Com_rollback 0
    Com_select 6788
    Com_set_option 0
    Com_show_binlogs 0
    Com_show_create 0
    Com_show_databases 1
    Com_show_fields 6
    Com_show_grants 0
    Com_show_keys 0
    Com_show_logs 0
    Com_show_master_status 0
    Com_show_open_tables 0
    Com_show_processlist 0
    Com_show_slave_status 0
    Com_show_status 2
    Com_show_innodb_status 0
    Com_show_tables 2
    Com_show_variables 1
    Com_slave_start 0
    Com_slave_stop 0
    Com_truncate 0
    Com_unlock_tables 0
    Com_update 263
    Connections 465
    Created_tmp_disk_tables 182
    Created_tmp_tables 298
    Created_tmp_files 0
    Delayed_insert_threads 0
    Delayed_writes 0
    Delayed_errors 0
    Flush_commands 1
    Handler_delete 101
    Handler_read_first 413
    Handler_read_key 402392
    Handler_read_next 1081203
    Handler_read_prev 0
    Handler_read_rnd 14190
    Handler_read_rnd_next 498410
    Handler_update 1002
    Handler_write 3679
    Key_blocks_used 2952
    Key_read_requests 519453
    Key_reads 2788
    Key_write_requests 945
    Key_writes 263
    Max_used_connections 10
    Not_flushed_key_blocks 0
    Not_flushed_delayed_rows 0
    Open_tables 104
    Open_files 181
    Open_streams 0
    Opened_tables 114
    Questions 8389
    Select_full_join 3
    Select_full_range_join 58
    Select_range 614
    Select_range_check 0
    Select_scan 933
    Slave_running OFF
    Slave_open_temp_tables 0
    Slow_launch_threads 0
    Slow_queries 2
    Sort_merge_passes 0
    Sort_range 228
    Sort_rows 774783
    Sort_scan 499
    Table_locks_immediate 8784
    Table_locks_waited 2
    Threads_cached 8
    Threads_created 11
    Threads_connected 3
    Threads_running 2
    Uptime 490
    +--------------------------+----------+


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

    I don't know but i think more than 200


    12. what version of vB are you running ?
    vBulletin béta 6


    10. need the url not the content of the phpinfo.php


  • can you post the url to your actual mysqlinfo.php url (upload the mysqlinfo.php file to /admin/mysqlinfo.php - the file is located in vb Zip install folder's extras folder


  • Someone can help me please ? :(


  • I'll let George give you more detailed advice, but off-hand it looks like you need more RAM if you're swapping 350 Mb. You may need to go up to 1 Gb or even 2 Gb.


  • Sorry, i've done turn off my bulletin in order to upgrade it into vb3 beta 7 and i have forgot to transfer the file, it should be ok now, can you retry ?
    okay your mysqlinfo page looks fine but i'd try this new /etc/my.cnf below and restart mysql

    might also want to upgrade to mysql 3.23.58

    my.cnf for mysql 3.23.58

    [client]
    port = 3306
    socket = /tmp/mysql.sock

    [mysqld]
    port = 3306
    socket = /tmp/mysql.sock
    set-variable = max_connections=400
    set-variable = key_buffer=16M
    set-variable = myisam_sort_buffer_size=64M
    set-variable = join_buffer=1M
    set-variable = record_buffer=2M
    set-variable = sort_buffer=2M
    set-variable = table_cache=1024
    set-variable = thread_cache_size=64
    set-variable = wait_timeout=1800
    set-variable = connect_timeout=10
    set-variable = max_allowed_packet=16M
    set-variable = max_connect_errors=10

    [safe_mysqld]
    open_files_limit=8192

    [mysqldump]
    quick
    set-variable = max_allowed_packet=16M

    [myisamchk]
    set-variable = key_buffer=64M
    set-variable = sort_buffer=64M
    set-variable = read_buffer=16M
    set-variable = write_buffer=16M



    if you upgrade to mysql 4.0.15a use this my.cnf

    [client]
    port = 3306
    socket = /tmp/mysql.sock

    [mysqld]
    port = 3306
    socket = /tmp/mysql.sock
    max_connections = 500
    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 = 64
    wait_timeout = 1800
    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


    you may also need more memory, but i'd try this my.cnf change for a week or 2 to see


  • Thank you very much as it is a very big problem for us, our server is not available from 11 A.M to 11 P.M due to those problems and we are loosing a lot of our visitors.

    In addition excuse me for my poor english speaking, as you can see it is not my native language ;-)


  • sorry about delay.. i just killed my new computer's motherboard yesterday - i think it's dead :(

    as to your server your mysqlinfo you pm'd me the url isn't valid... page can not be found ?
    Sorry, i've done turn off my bulletin in order to upgrade it into vb3 beta 7 and i have forgot to transfer the file, it should be ok now, can you retry ?


  • You will need to wait for eva2000 who is the server guru around here.

    Also please note that we do this as a courtesy but optimizing servers is not really a part of vBulletin support.


  • Just a suggestion but maybe move to a more powerful machine with a higher CPU and more RAM. Or you could try upgrading the RAM on your current box, providing it's allowed.


  • Hi,

    It's a little bit faster but finally i got the same thing, more than 200 processes sleeping


  • Hi,

    I have send it to you in a P.MFor example i have right now performance problem :



    Thu Oct 16 14:01:09 CEST 2003

    last pid: 82909; load averages: 6.04, 6.06, 23.89 up 5+21:19:33 14:01:12
    249 processes: 6 running, 243 sleeping
    82382 mysql -6 0 335M 31668K biord 4:56 12.74% 12.74% mysqld

    Http processes currently running = 203
    Mysql processes currently running = 4
    Netstat information summary
    2 FIN_WAIT_1
    6 LISTEN
    12 TIME_WAIT
    13 CLOSE_WAIT
    13 FIN_WAIT_2
    72 ESTABLISHED

    +---------------------------+-----------------+
    Variable_name Value
    +---------------------------+-----------------+
    Aborted_clients 1
    Aborted_connects 1
    Bytes_received 2536823
    Bytes_sent 161403292
    Com_admin_commands 0
    Com_alter_table 0
    Com_analyze 0
    Com_backup_table 0
    Com_begin 0
    Com_change_db 1128
    Com_change_master 0
    Com_check 0
    Com_commit 0
    Com_create_db 0
    Com_create_function 0
    Com_create_index 0
    Com_create_table 0
    Com_delete 63
    Com_drop_db 0
    Com_drop_function 0
    Com_drop_index 0
    Com_drop_table 0
    Com_flush 0
    Com_grant 0
    Com_insert 467
    Com_insert_select 1
    Com_kill 0
    Com_load 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 20
    Com_replace_select 0
    Com_reset 0
    Com_restore_table 0
    Com_revoke 0
    Com_rollback 0
    Com_select 12810
    Com_set_option 0
    Com_show_binlogs 0
    Com_show_create 0
    Com_show_databases 0
    Com_show_fields 0
    Com_show_grants 0
    Com_show_keys 0
    Com_show_logs 0
    Com_show_master_status 0
    Com_show_open_tables 0
    Com_show_processlist 0
    Com_show_slave_status 0
    Com_show_status 2
    Com_show_innodb_status 0
    Com_show_tables 0
    Com_show_variables 2
    Com_slave_start 0
    Com_slave_stop 0
    Com_truncate 0
    Com_unlock_tables 0
    Com_update 600
    Connections 984
    Created_tmp_disk_tables 87
    Created_tmp_tables 399
    Created_tmp_files 0
    Delayed_insert_threads 0
    Delayed_writes 0
    Delayed_errors 0
    Flush_commands 1
    Handler_delete 114
    Handler_read_first 960
    Handler_read_key 2312154
    Handler_read_next 3986400
    Handler_read_prev 0
    Handler_read_rnd 77961
    Handler_read_rnd_next 2858672
    Handler_update 4063
    Handler_write 25687
    Key_blocks_used 3855
    Key_read_requests 4484584
    Key_reads 3795
    Key_write_requests 900
    Key_writes 578
    Max_used_connections 189
    Not_flushed_key_blocks 0
    Not_flushed_delayed_rows 0
    Open_tables 581 32% of table_cache in use
    Open_files 629
    Open_streams 0
    Opened_tables 588
    Questions 15884
    Select_full_join 3
    Select_full_range_join 100
    Select_range 1639
    Select_range_check 0
    Select_scan 1723
    Slave_running OFF
    Slave_open_temp_tables 0
    Slow_launch_threads 0
    Slow_queries 95 (execution time > 10 secs)
    Sort_merge_passes 0
    Sort_range 611
    Sort_rows 2574162
    Sort_scan 702
    Table_locks_immediate 17283
    Table_locks_waited 239
    Threads_cached 0
    Threads_created 190
    Threads_connected 190
    Threads_running 183
    Uptime 1115 18 mins 35 secs
    +---------------------------+-----------------+

    Key Reads/Key Read Requests = 0.000846 (Cache hit = 99.999154%)
    Key Writes/Key Write Requests = 0.642222
    Connections/second = 0.883 (/hour = 3177.040)
    KB received/second = 2.222 (/hour = 7997.489)
    KB sent/second = 141.363 (/hour = 508907.623)
    Temporary Tables Created/second = 0.358 (/hour = 1288.251)
    Opened Tables/second = 0.527 (/hour = 1898.475)
    Slow Queries/second = 0.085 (/hour = 306.726)
    % of slow queries = 0.598%
    Queries/second = 14.246 (/hour = 51284.664)


  • sorry about delay.. i just killed my new computer's motherboard yesterday - i think it's dead :(

    as to your server your mysqlinfo you pm'd me the url isn't valid... page can not be found ?


  • can you post the url to your actual mysqlinfo.php url (upload the mysqlinfo.php file to /admin/mysqlinfo.php - the file is located in vb Zip install folder's extras folder
    Hi,

    I have send it to you in a P.M


  • To get vB support on these forums you first need to register for Priority Forum Support. To do this, please click HERE (http://www.vbulletin.com/members/forums.php) and enter your email address in one of the boxes. You'll need to have your customer number and password to access the page. Thanks.


  • okay your mysqlinfo page looks fine but i'd try this new /etc/my.cnf below and restart mysql

    might also want to upgrade to mysql 3.23.58

    my.cnf for mysql 3.23.58


    if you upgrade to mysql 4.0.15a use this my.cnf


    you may also need more memory, but i'd try this my.cnf change for a week or 2 to see
    ok thank you very much, i'll tell you if it's ok now


  • Hi,


    It's ok now i have been registered







    • #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 Huge forum, big performance problems , Please add it free.

    Author: mike     03 10th, 2010 in portal.gzui.com     edit

    Jonas Brothers cd or what? Who's better for Nick Jonas - Miley Cyrus or Selena Gomez?