Results 1 to 2 of 2
  1. #1
    Join Date
    Nov 2002
    Posts
    1

    Question Unanswered: mySQL chewing up 90% CPU, Vbulletin forums + apache on freeBSD

    I'm having problems since switching from a windows to a FreeBSD server running our VBulletin forums on mySQL. It's quite a large forum, users online between 70 - 100 at busiest, but since moving the speed has plummetted. mySQL takes up to around 90% of the CPU.

    the address to the forums is http://www.gurn.net/forums
    The slowest opperation is submitting new data to the DB, which can sometimes take up to a minute to reply or make a new post.
    The server is running a handful of other sites, but they are all quite small and hardly access mySQL at all.

    I have pasted in some info requested by someone on the VB forums when I asked for advice there.

    I am asking if anyone has suggestions of what might be causing this or things we could try to correct the problem? Any suggestions greatly appreciated as I have no idea what to do next!

    1. your server specs, such as mysql and php version

    P3 1.13GHz, 1GB RAM, 80GB HDD (dual CPU but SMP support is not yet built into the kernel)
    MySQL 3.23.51
    PHP 4.2.3

    2. if possible how mysql was compiled/installed

    MySQL was installed using the FreeBSD port at /usr/ports/databases/mysql323-server (after a cvsup to get the latest version) about 2 months go)

    3. your top stats

    Our peak hours have passed so this isn't as bad as it was earlier today.

    last pid: 28930; load averages: 0.82, 0.66, 0.60 up 8+07:40:53 18:32:39
    115 processes: 2 running, 108 sleeping, 5 zombie
    CPU states: 59.1% user, 0.0% nice, 40.7% system, 0.2% interrupt, 0.0% idle
    Mem: 160M Active, 539M Inact, 164M Wired, 28M Cache, 112M Buf, 112M Free
    Swap: 2032M Total, 136K Used, 2032M Free

    PID UID PRI NICE SIZE RES STATE TIME WCPU CPU COMMAND
    28667 88 53 0 290M 21900K RUN 5:55 76.37% 76.37% mysqld

    4. 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

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

    # The MySQL server
    [mysqld]
    port = 3306
    socket = /tmp/mysql.sock
    skip-locking
    set-variable = key_buffer=256M
    set-variable = max_allowed_packet=16M
    set-variable = max_connections=1024
    set-variable = table_cache=256
    set-variable = sort_buffer=1M
    set-variable = record_buffer=1M
    set-variable = myisam_sort_buffer_size=64M
    set-variable = thread_cache=8
    set-variable = thread_concurrency=8
    log-bin
    server-id = 1

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

    [mysql]
    no-auto-rehash

    [isamchk]
    set-variable = key_buffer=128M
    set-variable = sort_buffer=128M
    set-variable = read_buffer=2M
    set-variable = write_buffer=2M

    [myisamchk]
    set-variable = key_buffer=128M
    set-variable = sort_buffer=128M
    set-variable = read_buffer=2M
    set-variable = write_buffer=2M

    [mysqlhotcopy]
    interactive-timeout

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


    +--------------------------+---------+
    | Variable_name | Value |
    +--------------------------+---------+
    | Aborted_clients | 4 |
    | Aborted_connects | 0 |
    | Bytes_received | 599853 |
    | Bytes_sent | 7456409 |
    | Com_admin_commands | 0 |
    | Com_alter_table | 0 |
    | Com_analyze | 0 |
    | Com_backup_table | 0 |
    | Com_begin | 0 |
    | Com_change_db | 245 |
    | 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 | 4 |
    | Com_drop_db | 0 |
    | Com_drop_function | 0 |
    | Com_drop_index | 0 |
    | Com_drop_table | 0 |
    | Com_flush | 0 |
    | Com_grant | 0 |
    | Com_insert | 11 |
    | Com_insert_select | 0 |
    | 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 | 8 |
    | Com_replace_select | 0 |
    | Com_reset | 0 |
    | Com_restore_table | 0 |
    | Com_revoke | 0 |
    | Com_rollback | 519 |
    | Com_select | 2618 |
    | 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 | 99 |
    | Com_show_tables | 0 |
    | Com_show_variables | 0 |
    | Com_slave_start | 0 |
    | Com_slave_stop | 0 |
    | Com_truncate | 0 |
    | Com_unlock_tables | 0 |
    | Com_update | 554 |
    | Connections | 226 |
    | Created_tmp_disk_tables | 0 |
    | Created_tmp_tables | 81 |
    | Created_tmp_files | 0 |
    | Delayed_insert_threads | 0 |
    | Delayed_writes | 0 |
    | Delayed_errors | 0 |
    | Flush_commands | 1 |
    | Handler_delete | 0 |
    | Handler_read_first | 225 |
    | Handler_read_key | 22936 |
    | Handler_read_next | 4371909 |
    | Handler_read_prev | 0 |
    | Handler_read_rnd | 20770 |
    | Handler_read_rnd_next | 47727 |
    | Handler_update | 558 |
    | Handler_write | 3499 |
    | Key_blocks_used | 1520 |
    | Key_read_requests | 340831 |
    | Key_reads | 1520 |
    | Key_write_requests | 173 |
    | Key_writes | 169 |
    | Max_used_connections | 71 |
    | Not_flushed_key_blocks | 0 |
    | Not_flushed_delayed_rows | 0 |
    | Open_tables | 69 |
    | Open_files | 115 |
    | Open_streams | 0 |
    | Opened_tables | 75 |
    | Questions | 4110 |
    | Select_full_join | 0 |
    | Select_full_range_join | 0 |
    | Select_range | 633 |
    | Select_range_check | 0 |
    | Select_scan | 528 |
    | Slave_running | OFF |
    | Slave_open_temp_tables | 0 |
    | Slow_launch_threads | 0 |
    | Slow_queries | 7 |
    | Sort_merge_passes | 0 |
    | Sort_range | 559 |
    | Sort_rows | 20883 |
    | Sort_scan | 270 |
    | Table_locks_immediate | 3768 |
    | Table_locks_waited | 29 |
    | Threads_cached | 1 |
    | Threads_created | 72 |
    | Threads_connected | 71 |
    | Threads_running | 1 |
    | Uptime | 191 |
    +--------------------------+---------+


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

    It's shared hosting so there are other sites, but the others don't use MySQL very much.

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

    Max ~130, averages is about 70.

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

    http://brian.sharedserver.net/info.php

    9. 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 On
    MaxKeepAliveRequests 100
    KeepAliveTimeout 15
    MinSpareServers 5
    MaxSpareServers 10
    StartServers 5
    MaxClients 150
    MaxRequestsPerChild 0

    10. what version of vB are you running ?

    2.2.7

  2. #2
    Join Date
    Dec 2001
    Posts
    1
    I may not know much about MySQL (I'm just learning it myself), but I can tell you this much. The support for SMP under FreeBSD is flaky, at best. I'd advise you to remove any installed processor(s) that is an addition to the primary. Try to do that first and then recompile FreeBSD (If you compiled it with the second/multiple processors installed). Afterwards, see how much difference it may have made. I think it might not be the real culprit, however, doing so will minimize any unforeseen problems and also ensure a more stable working environment on your server.

    Cheers,
    Bean Bandit

    P.S. I forgot to add that if you wish to utilize SMP, then I'd rather you use Linux instead. A 'nix environment but with a lot better support and also hardware compatibility. Linux has come a long ways and is very robust in a server environment. Try Red Hat or SuSE if you are a novice and Slackware if you are comfortable with it. -BB
    Last edited by The Bean Bandit; 11-22-02 at 05:51.

Posting Permissions

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