Announcement

Collapse
No announcement yet.

Just Upgraded My Server But Still Getting Errors

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • Just Upgraded My Server But Still Getting Errors

    Ok, I finally got a new server. [See specs below] However, my users are still getting errors that the DB is busy or the dreadful error message. If anyone can help me tweek my system, much appreciated. Today I had about 175 users on at once.

    vBulletin 2.2.1 Settings:

    Users: 67,706
    Posts: 474,561 [Today I'm pruning...]

    Simultaneous Sessions: 300
    *NIX Load Limit: 5.00
    GZIP compression level: 1

    Note: Attachments are turned off.

    [config.php]
    $usepconnect = 0;

    Hardware:

    1 GHZ PIII
    512 MB RAM

    Software:
    Linux 6.2
    Apache 1.3.22
    PHP 4.0.6
    MySQL 3.23.36

    Other:
    GZip Enabled
    Zend Optimizer
    my_phpinfo.php
    Mysql extended status script

    I will post my info below this thread...

  • #2
    top

    8:33pm up 2 days, 20:48, 1 user, load average: 0.69, 1.13, 1.60
    87 processes: 86 sleeping, 1 running, 0 zombie, 0 stopped
    CPU states: 6.0% user, 4.0% system, 0.0% nice, 3.2% idle
    Mem: 523864K av, 464592K used, 59272K free, 0K shrd, 28608K buff
    Swap: 136512K av, 0K used, 136512K free 274716K cached

    mysqladmin -uroot -p variables

    Code:
    | Variable_name           | Value
                                                                                                           |
    | ansi_mode               | OFF
                                                                                                           |
    | back_log                | 50
                                                                                                           |
    | basedir                 | /
                                                                                                           |
    | binlog_cache_size       | 32768
                                                                                                           |
    | character_set           | latin1
                                                                                                           |
    | concurrent_insert       | ON
                                                                                                           |
    | connect_timeout         | 5
                                                                                                           |
    | datadir                 | /var/lib/mysql/
                                                                                                           |
    | delay_key_write         | ON
                                                                                                           |
    | delayed_insert_limit    | 100
                                                                                                           |
    | delayed_insert_timeout  | 300
                                                                                                           |
    | delayed_queue_size      | 1000
                                                                                                           |
    | flush                   | OFF
                                                                                                           |
    | flush_time              | 0
                                                                                                           |
    | have_bdb                | NO
                                                                                                           |
    | have_gemini             | NO
                                                                                                           |
    | have_innobase           | NO
                                                                                                           |
    | have_isam               | YES
                                                                                                           |
    | have_raid               | NO
                                                                                                           |
    | have_ssl                | NO
                                                                                                           |
    | init_file               |
                                                                                                           |
    | interactive_timeout     | 28800
                                                                                                           |
    | join_buffer_size        | 131072
                                                                                                           |
    | key_buffer_size         | 268431360
                                                                                                           |
    | language                | /usr/share/mysql/english/
                                                                                                           |
    | large_files_support     | ON
                                                                                                           |
    | locked_in_memory        | OFF
                                                                                                           |
    | log                     | OFF
                                                                                                           |
    | log_update              | OFF
                                                                                                           |
    | log_bin                 | ON
                                                                                                           |
    | log_slave_updates       | OFF
                                                                                                           |
    | 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_recover_options  | OFF
                                                                                                           |
    | myisam_sort_buffer_size | 67108864
                                                                                                           |
    | net_buffer_length       | 16384
                                                                                                           |
    | net_read_timeout        | 30
                                                                                                           |
    | net_retry_count         | 10
                                                                                                           |
    | net_write_timeout       | 60
                                                                                                           |
    | open_files_limit        | 0
                                                                                                           |
    | pid_file                | /var/lib/mysql/www.sohh.com.pid
                                                                                                           |
    | port                    | 3306
                                                                                                           |
    | protocol_version        | 10
                                                                                                           |
    | record_buffer           | 1044480
                                                                                                           |
    | query_buffer_size       | 0
                                                                                                           |
    | safe_show_database      | OFF
                                                                                                           |
    | server_id               | 1
                                                                                                           |
    | skip_locking            | ON
                                                                                                           |
    | skip_networking         | OFF
                                                                                                           |
    | skip_show_database      | OFF
                                                                                                           |
    | slow_launch_time        | 2
                                                                                                           |
    | socket                  | /var/lib/mysql/mysql.sock
                                                                                                           |
    | sort_buffer             | 1048568
                                                                                                           |
    | table_cache             | 256
                                                                                                           |
    | table_type              | MYISAM
                                                                                                           |
    | thread_cache_size       | 8
                                                                                                           |
    | thread_stack            | 65536
                                                                                                           |
    | transaction_isolation   | READ-COMMITTED
                                                                                                           |
    | timezone                | EST
                                                                                                           |
    | tmp_table_size          | 1048576
                                                                                                           |
    | tmpdir                  | /tmp/
                                                                                                           |
    | version                 | 3.23.36-log
                                                                                                           |
    | wait_timeout            | 28800
                                                                                                           |

    Comment


    • #3
      mysqladmin -uroot -p extended-status
      Code:
      +--------------------------+------------+
      | Variable_name            | Value      |
      +--------------------------+------------+
      | Aborted_clients          | 6          |
      | Aborted_connects         | 4          |
      | Bytes_received           | 463562634  |
      | Bytes_sent               | 3667424512 |
      | Connections              | 126123     |
      | Created_tmp_disk_tables  | 190        |
      | Created_tmp_tables       | 49350      |
      | Created_tmp_files        | 2          |
      | Delayed_insert_threads   | 0          |
      | Delayed_writes           | 0          |
      | Delayed_errors           | 0          |
      | Flush_commands           | 1          |
      | Handler_delete           | 22106      |
      | Handler_read_first       | 683        |
      | Handler_read_key         | 19761847   |
      | Handler_read_next        | 314434210  |
      | Handler_read_prev        | 1373       |
      | Handler_read_rnd         | 25109264   |
      | Handler_read_rnd_next    | 860099256  |
      | Handler_update           | 469921     |
      | Handler_write            | 2056224    |
      | Key_blocks_used          | 12511      |
      | Key_read_requests        | 113722912  |
      | Key_reads                | 10325      |
      | Key_write_requests       | 260912     |
      | Key_writes               | 188239     |
      | Max_used_connections     | 149        |
      | Not_flushed_key_blocks   | 0          |
      | Not_flushed_delayed_rows | 0          |
      | Open_tables              | 256        |
      | Open_files               | 307        |
      | Open_streams             | 0          |
      | Opened_tables            | 755        |
      | Questions                | 2755810    |
      | Select_full_join         | 61         |
      | Select_full_range_join   | 0          |
      | Select_range             | 507728     |
      | Select_range_check       | 0          |
      | Select_scan              | 485391     |
      | Slave_running            | OFF        |
      | Slave_open_temp_tables   | 0          |
      | Slow_launch_threads      | 36         |
      | Slow_queries             | 167        |
      | Sort_merge_passes        | 1          |
      | Sort_range               | 536989     |
      | Sort_rows                | 25741534   |
      | Sort_scan                | 258349     |
      | Table_locks_immediate    | 2945139    |
      | Table_locks_waited       | 8647       |
      | Threads_cached           | 7          |
      | Threads_created          | 1482       |
      | Threads_connected        | 1          |
      | Threads_running          | 1          |
      | Uptime                   | 96460      |
      +--------------------------+------------+
      mysqladmin -uroot -p --version
      mysqladmin Ver 8.18 Distrib 3.23.36, for pc-linux-gnu on i686

      my.cnf
      my.cnf
      I used the basic my-large.cnf that came with MySQL. I choose this one because it said for servers with 512MB RAM use this one. And I added the following lines to it:

      set-variable = max_connections=500

      I hope I've given everyone enough to work with.

      Comment


      • #4
        2 problems you have is you set your key_buffer too high 250+MB and your table_cache too low

        try replaceing my.cnf with
        [client]
        port = 3306
        socket = /var/lib/mysql/mysql.sock

        [mysqld]
        port = 3306
        socket = /var/lib/mysql/mysql.sock
        skip-locking
        set-variable = max_connections=450
        set-variable = key_buffer=16M
        set-variable = join_buffer=4M
        set-variable = record_buffer=4M
        set-variable = sort_buffer=6M
        set-variable = table_cache=1024
        set-variable = thread_cache_size=256

        [myisamchk]
        set-variable = key_buffer=128M
        set-variable = sort_buffer=128M
        set-variable = read_buffer=16M
        set-variable = write_buffer=16M
        restart mysql
        :: Always Back Up Forum Database + Attachments BEFORE upgrading !
        :: Nginx SPDY SSL - World Flags Demo [video results]
        :: vBulletin hacked forums: Clean Up Guide for VPS/Dedicated hosting users [ vbulletin.com blog summary ]

        Comment


        • #5
          Before I do this, should I just copy what you put and save it, or change each variable using vi?

          Comment


          • #6
            Originally posted by ibeblunt
            Before I do this, should I just copy what you put and save it, or change each variable using vi?
            just delete existing my.cnf file and create a new one in /etc/my.cnf and copy and paste what i recommended
            :: Always Back Up Forum Database + Attachments BEFORE upgrading !
            :: Nginx SPDY SSL - World Flags Demo [video results]
            :: vBulletin hacked forums: Clean Up Guide for VPS/Dedicated hosting users [ vbulletin.com blog summary ]

            Comment


            • #7
              Done.

              Thanks.

              I will let you know how this works out.

              Also, I know you didn't mention these settings, but should I change any of my vBulletin settings, like:

              Simultaneous Sessions: 300
              *NIX Load Limit: 5.00
              GZIP compression level: 1

              Note: Attachments are turned off.

              $usepconnect = 0;

              I've read somewhere on this site that the the use of persistant connections work well on dedicated servers but not very well on shared space.

              Once again thanks!

              Comment


              • #8
                personally, i'd turn of session limiting and enable pconnects=1

                see how that would work
                :: Always Back Up Forum Database + Attachments BEFORE upgrading !
                :: Nginx SPDY SSL - World Flags Demo [video results]
                :: vBulletin hacked forums: Clean Up Guide for VPS/Dedicated hosting users [ vbulletin.com blog summary ]

                Comment


                • #9
                  brief summary

                  Ok, I turned off session limiting and turned persistant connections. I setthe *NIX Load Limit at 10.00 [I hope this means that any request that takes over 10 seconds to process will be turned away.]

                  At 4:13pm (peek time) I got the following stats:

                  5.43, 5.85, 5.06 / 218

                  5.71, 5.53, 5.06 / 227

                  5.07, 5.39, 5.03 / 226

                  I have my server sending me stats every hour on the hour as well. I noticed the following stats immediately:

                  BEFORE THE MOD TO MY.CNF

                  12:00pm up 3 days, 12:14, 0 users, load average: 1.46, 1.16, 1.19
                  96 processes: 95 sleeping, 1 running, 0 zombie, 0 stopped
                  CPU states: 8.9% user, 3.9% system, 0.0% nice, 2.1% idle
                  Mem: 523864K av, 445128K used, 78736K free, 0K shrd, 21488K buff
                  Swap: 136512K av, 0K used, 136512K free 249552K cached

                  1:00pm up 3 days, 13:14, 1 user, load average: 1.42, 1.57, 1.53
                  131 processes: 129 sleeping, 1 running, 1 zombie, 0 stopped
                  CPU states: 9.2% user, 3.9% system, 0.0% nice, 2.8% idle
                  Mem: 523864K av, 495948K used, 27916K free, 0K shrd, 13564K buff
                  Swap: 136512K av, 0K used, 136512K free 248260K cached

                  AFTER THE MOD TO MY.CNF

                  2:00pm up 3 days, 14:14, 1 user, load average: 1.81, 1.96, 2.14
                  211 processes: 210 sleeping, 1 running, 0 zombie, 0 stopped
                  CPU states: 9.6% user, 4.0% system, 0.0% nice, 3.2% idle
                  Mem: 523864K av, 452104K used, 71760K free, 0K shrd, 12292K buff
                  Swap: 136512K av, 0K used, 136512K free 246860K cached

                  3:00pm up 3 days, 15:14, 1 user, load average: 1.91, 1.63, 1.80
                  277 processes: 276 sleeping, 1 running, 0 zombie, 0 stopped
                  CPU states: 10.0% user, 4.0% system, 0.0% nice, 3.7% idle
                  Mem: 523864K av, 519540K used, 4324K free, 0K shrd, 12212K buff
                  Swap: 136512K av, 0K used, 136512K free 238212K cached

                  I guess it's inevitable to get some users turned away, but I look at HardForum and get jealous.

                  Do you have a wish list at Amazon or anything?
                  Last edited by ibeblunt; Wed 12th Dec '01, 2:20pm.

                  Comment


                  • #10
                    Glad i could help

                    don't have a Amazon wish list at the moment but do have a Donate button on my forums http://animeboards.com/index.php?s=
                    :: Always Back Up Forum Database + Attachments BEFORE upgrading !
                    :: Nginx SPDY SSL - World Flags Demo [video results]
                    :: vBulletin hacked forums: Clean Up Guide for VPS/Dedicated hosting users [ vbulletin.com blog summary ]

                    Comment


                    • #11
                      I'd take it since you didn't tell me to adjust anything that those stats posted above are good. What should I keep an eye on?

                      Comment


                      • #12
                        all you need to keep an eye on is this link http://www.sohh.com/apps/mysql.php

                        - memory swap usage
                        and the following from that page

                        Key Reads/Key Read Requests = 0.000324 (Cache hit = 99.999676%)
                        Key Writes/Key Write Requests = 0.779213
                        Connections/second = 0.721 (/hour = 2595.004)
                        KB received/second = 8.068 (/hour = 29043.096)
                        KB sent/second = 135.033 (/hour = 486119.242)
                        Temporary Tables Created/second = 0.848 (/hour = 3054.516)
                        Opened Tables/second = 0.020 (/hour = 73.127)
                        Slow Queries/second = 0.009 (/hour = 31.293)
                        % of slow queries = 0.018%
                        Queries/second = 47.023 (/hour = 169281.362)

                        Max_used_connections
                        Open_tables
                        Open_files
                        Open_streams
                        Opened_tables
                        Slow_queries
                        Threads_cached
                        Threads_created
                        Threads_connected

                        take a read of some of my sig links especially this one http://i4net.tv/marticle/get.php?act...le&articleid=3

                        and

                        http://www.mysql.com/doc/O/p/Optimising_the_Server.html
                        :: Always Back Up Forum Database + Attachments BEFORE upgrading !
                        :: Nginx SPDY SSL - World Flags Demo [video results]
                        :: vBulletin hacked forums: Clean Up Guide for VPS/Dedicated hosting users [ vbulletin.com blog summary ]

                        Comment


                        • #13
                          Yea! Things are rocking now. I still haven't beat my peek of 339 users on at once, but nevertheless the pages are coming up MEGA FAST now.

                          Not like I know how off-hand, but what if a script was made to tweek mySQL...Like a person could enter thier info and then the proper my.cnf could be generated? I could produce the script if someone could produce the math.

                          Comment


                          • #14
                            Originally posted by ibeblunt
                            Yea! Things are rocking now. I still haven't beat my peek of 339 users on at once, but nevertheless the pages are coming up MEGA FAST now.

                            Not like I know how off-hand, but what if a script was made to tweek mySQL...Like a person could enter thier info and then the proper my.cnf could be generated? I could produce the script if someone could produce the math.
                            glad to hear it's working

                            unfortunately there ain't no real formula it's trial and error and from experience and depends on a persons hardware and how mysql is used
                            :: Always Back Up Forum Database + Attachments BEFORE upgrading !
                            :: Nginx SPDY SSL - World Flags Demo [video results]
                            :: vBulletin hacked forums: Clean Up Guide for VPS/Dedicated hosting users [ vbulletin.com blog summary ]

                            Comment


                            • #15
                              Hey! You have all the extra stuff in your script too .. http://www.sohh.com/apps/mysql.php

                              Mind sharing your script?

                              Comment

                              widgetinstance 262 (Related Topics) skipped due to lack of content & hide_module_if_empty option.
                              Working...
                              X