Announcement

Collapse
No announcement yet.

Excessive database errors with beta2, suggestions?"

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

  • Excessive database errors with beta2, suggestions?"

    Hi, I just upgraded to beta 3 and I get lots of "cant conenct" errors. For example:
    Database error in vBulletin Control Panel: Link-ID == false, connect failed
    mysql error:
    mysql error number:
    Date: Friday 16th of March 2001 07:27:15 PM
    Script: /forum/admin/index.php
    Referer: http://www.adolescentadulthood.com/f...?action=search
    Here are my server specs. Do I need more RAM or am I just not optimizing the system? The errors are frequent and are quite irritating. If this keeps up, I'll have to revert to 1.x

    Webhost: Rackspace
    Operating System : RedHat Linux 6.2
    Processor : 650 Mhz
    Memory : 256MB RAM
    Hard Drive 1 : 20GB EIDE
    IP Address(es) : 1 IP
    Bandwidth : 75GB/Month

    top load average monitoring

    7:54pm up 111 days, 12:19, 2 users, load average: 21.87, 17.58, 16.79
    224 processes: 211 sleeping, 12 running, 0 zombie, 1 stopped
    CPU states: 37.6% user, 53.6% system, 8.6% nice, 0.0% idle
    Mem: 257680K av, 255512K used, 2168K free, 189080K shrd, 5240K buff
    Swap: 136512K av, 42484K used, 94028K free 10704K cached

    mysql status

    Uptime: 9422894 Threads: 57 Questions: 206053885 Slow queries: 182166 Opens:
    709455 Flush tables: 1 Open tables: 63
    mysqladmin: Unknown command: 'mysql'


    Please help me decide. DO I need optimization or is it imperative I get more RAM?

  • #2
    I think more RAM is probably a good idea - those load averages are pretty high.
    Have you optimised your configuaration before? If not post in the Server Config forum with those specs and hopefully someone there will be able to help you optimise it.
    If optimisation doesn't help then certainly RAM is the way to go.

    Comment


    • #3
      you're with rackspace which means you have a AMD Duron 650 mhz cpu not a P3 650 mhz cpu

      also looking at your slow queries from mysql you're table_cache may not be optimised. also your swap file usage is kinda high so more ram might be needed

      I was with rackspace once with a AMD K6/2 500 , 384mb ram and had all sorts of problems

      get the extended status script i have in my sig and put it on your server and let's see what's going on with mysql

      also what settings do you have in your /etc/my.cnf file ?
      :: 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


      • #4
        THanks for all your help. I'll post info when I can get it. However, where can I find the my.cnf file? It doesnt seem to be in /etc

        Comment


        • #5
          The following command should show any my.cnf files on your system:
          Code:
          find / -name my.cnf -print
          It's possible there isn't one if it wasn't installed or modified. Also, can you post the output of the following commands:
          Code:
          mysqladmin -uroot -ppassword variables
          mysqladmin -uroot -ppassword extended-status

          Comment


          • #6
            The URL of my status script. Thanks! I really appreciate your advice. Please tell me what I should do now.

            http://www.adolescentadulthood.com/status.php

            Comment


            • #7
              Originally posted by theprof
              The URL of my status script. Thanks! I really appreciate your advice. Please tell me what I should do now.

              http://www.adolescentadulthood.com/status.php
              the status script basically shows the same info as the command chris suggested

              mysqladmin -uroot -ppassword extended-status

              seems in the status.php script you forgot to specify your mysql root password in it - this line

              $mysqllogin = "-uroot -pyourrootpassword";

              yourrootpassword = you root password
              :: 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


              • #8
                +----------------------------+---------------------------+
                | Variable_name | Value |
                +----------------------------+---------------------------+
                | back_log | 5 |
                | connect_timeout | 5 |
                | basedir | / |
                | datadir | /var/lib/mysql/ |
                | delayed_insert_limit | 100 |
                | delayed_insert_timeout | 300 |
                | delayed_queue_size | 1000 |
                | join_buffer | 131072 |
                | flush_time | 0 |
                | key_buffer | 8388600 |
                | language | /usr/share/mysql/english/ |
                | log | OFF |
                | log_update | OFF |
                | long_query_time | 10 |
                | low_priority_updates | OFF |
                | max_allowed_packet | 1048576 |
                | max_connections | 100 |
                | max_connect_errors | 10 |
                | max_delayed_insert_threads | 20 |
                | max_join_size | 4294967295 |
                | max_sort_length | 1024 |
                | max_write_lock_count | 4294967295 |
                | net_buffer_length | 16384 |
                | pid_file | /var/lib/mysql/mysqld.pid |
                | port | 3306 |
                | protocol_version | 10 |
                | record_buffer | 131072 |
                | skip_locking | ON |
                | skip_networking | OFF |
                | socket | /var/lib/mysql/mysql.sock |
                | sort_buffer | 2097144 |
                | table_cache | 64 |
                | thread_stack | 65536 |
                | tmp_table_size | 1048576 |
                | tmpdir | /tmp/ |
                | version | 3.22.32 |
                | wait_timeout | 28800 |
                +----------------------------+---------------------------+

                +--------------------------+------------+
                | Variable_name | Value |
                +--------------------------+------------+
                | Aborted_clients | 66 |
                | Aborted_connects | 80 |
                | Created_tmp_tables | 3854503 |
                | Delayed_insert_threads | 0 |
                | Delayed_writes | 0 |
                | Delayed_errors | 0 |
                | Flush_commands | 1 |
                | Handler_delete | 744260 |
                | Handler_read_first | 8257499 |
                | Handler_read_key | 308770342 |
                | Handler_read_next | 183190153 |
                | Handler_read_rnd | 2268463733 |
                | Handler_update | 8403001 |
                | Handler_write | 71105485 |
                | Key_blocks_used | 7822 |
                | Key_read_requests | 803927325 |
                | Key_reads | 777823 |
                | Key_write_requests | 12240031 |
                | Key_writes | 10541403 |
                | Max_used_connections | 100 |
                | Not_flushed_key_blocks | 0 |
                | Not_flushed_delayed_rows | 0 |
                | Open_tables | 63 |
                | Open_files | 91 |
                | Open_streams | 0 |
                | Opened_tables | 719452 |
                | Questions | 206236451 |
                | Running_threads | 25 |
                | Slow_queries | 183880 |
                | Uptime | 9430083 |
                +--------------------------+------------+

                Comment


                • #9
                  Ok, i've changed it. My bad

                  http://www.adolescentadulthood.com/status.php

                  Comment


                  • #10
                    okay looks like you're using mysql default settings - you could tweak it some but with 100 max connections being used already you might wanna upgrade the memory to 512mb (that's gonna be expensive at rackspace )

                    do you have a /etc/my.cnf file ?

                    things you can do before a ram upgrade

                    1. upgrade mysql from 3.22.32 to 3.23.32 (ask Chris about that he helped me with that when i was on rackspace )

                    2. now in your my.cnf file located at /etc/my.cnf - you should be able to log into webmin's file manager to edit the my.cnf as i did it that way

                    in it
                    set-variable = max_connections=150
                    set-variable = join_buffer=3M
                    set-variable = key_buffer=16M
                    set-variable = record_buffer=4M
                    set-variable = sort_buffer=5M
                    set-variable = table_cache=256
                    now i'll pulling these out of the air from trial and error - not sure if 256mb ram would be okay with upping those settings in my.cnf
                    :: 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
                      Originally posted by theprof
                      Ok, i've changed it. My bad

                      http://www.adolescentadulthood.com/status.php
                      woah 111+ days uptime nice

                      forgot to mention when finished changing the my.cnf file restart mysql


                      /etc/rc.d/init.d/mysql stop
                      /etc/rc.d/init.d/mysql start
                      :: 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


                      • #12
                        Since upgrading to beta 3, we've had countless thousands of the same types of errors on both of our forums!

                        It's slowing down now. We've worked with suggestions from here as well as an unusually helpful tech from our host. I'll list what all we've done, in hopes it may help you and anyone else that has experienced this. I'll copy our tech's comments for you.

                        ....................
                        First, we increased max connections from 100 to 256.

                        ....................

                        We then received errors such as this:
                        mysql error: Can't create/write to file '/var/tmp/#sql6e84_2021_0.MYD'
                        (Errcode: 9)

                        vB said "It is probably in /var/mysql and /var is generally partitioned to something smallish"

                        So our tech then did this:

                        "I just moved your MySQL databases from /var/db/mysql to /usr/local/db/mysql -
                        effectively eliminating the problem with space on the /var partition. Also, I've set your mysqld process to run as root again... this will set all file descriptor limits to infinite. We'll see if that solves it =)"

                        .......................

                        The problems continued (although much less frequent) but connection problems occurred again. I reported this to our tech and he said this:

                        "Funky, indeed. I purged out the /var/tmp directory... we'll see if that helps.

                        The problems with connectivity to the MySQL DB might very well be related to the PHP version. I can stick the most recent version of PHP on there, and that
                        might cure it. PHP devel sites and threads seem to indicate that the fix is in the newer version.."

                        He then installed PHP 4.0.5-dev. At that moment, our forums ceased functioning due to a conflict between the vB script and this version of PHP. vB suggested removing 'connection_timeout() or ' from line 175 in functions.php and then it worked perfectly.

                        .........................

                        The next day, the errors began again. Our technician did this:

                        "I've set an hourly timer to clear out /var/tmp, and converted your table formats to the new MYISAM style that is used in 3.23 SQL."


                        We've been error free for over 36 hours now.

                        Comment


                        • #13
                          in theprof's case he can't set max_connections too high as he doesn't have enough ram at 256mb

                          setting it to 150 as i suggested to start would be okay - set it too high and you'd be swapping to disk more with 100+ connections theprof gets and only 256mb ram
                          :: 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


                          • #14
                            We only have 256 mg ram as well. We're on a unix with dual 800mhz processors. Have we done a bad thing by increasing our max connections?

                            Comment


                            • #15
                              Originally posted by grumpy
                              We only have 256 mg ram as well. We're on a unix with dual 800mhz processors. Have we done a bad thing by increasing our max connections?
                              it all depends on your load, max_connections use (shown in the mysql status scrip) , if you're eating into your swap file, etc

                              it's all trial and error and i use the mysql status script as a guide to what i need to change my settings to depending on my load, visitors, max connections etc

                              i.e. currently for me i have a dual p3 866, 512mb ram and 18gb 10k scsi hdd

                              my.cnf for me is

                              set-variable = max_connections=200
                              set-variable = join_buffer=6M
                              set-variable = key_buffer=16M
                              set-variable = record_buffer=6M
                              set-variable = sort_buffer=8M
                              set-variable = table_cache=256
                              set-variable = max_allowed_packet=1M
                              set-variable = thread_stack=128K
                              my extended status is
                              Sat Mar 17 04:52:40 PST 2001
                              +--------------------------+------------+
                              | Variable_name | Value |
                              +--------------------------+------------+
                              | Aborted_clients | 16067 |
                              | Aborted_connects | 3 |
                              | Bytes_received | 298655964 |
                              | Bytes_sent | 1477122224 |
                              | Connections | 17123 |
                              | Created_tmp_disk_tables | 27 |
                              | Created_tmp_tables | 39632 |
                              | Created_tmp_files | 6 |
                              | Delayed_insert_threads | 0 |
                              | Delayed_writes | 0 |
                              | Delayed_errors | 0 |
                              | Flush_commands | 1 |
                              | Handler_delete | 10536 |
                              | Handler_read_first | 48648 |
                              | Handler_read_key | 14349746 |
                              | Handler_read_next | 61413235 |
                              | Handler_read_prev | 0 |
                              | Handler_read_rnd | 16283748 |
                              | Handler_read_rnd_next | 404950093 |
                              | Handler_update | 172662 |
                              | Handler_write | 1849719 |
                              | Key_blocks_used | 15582 |
                              | Key_read_requests | 47246358 |
                              | Key_reads | 63872 |
                              | Key_write_requests | 149016 |
                              | Key_writes | 135303 |
                              | Max_used_connections | 50 |
                              | Not_flushed_key_blocks | 0 |
                              | Not_flushed_delayed_rows | 0 |
                              | Open_tables | 83 | 32% of table_cache in use
                              | Open_files | 128 |
                              | Open_streams | 0 |
                              | Opened_tables | 89 |
                              | Questions | 2067554 |
                              | Select_full_join | 577 |
                              | Select_full_range_join | 0 |
                              | Select_range | 153356 |
                              | Select_range_check | 0 |
                              | Select_scan | 553789 |
                              | Slave_running | OFF |
                              | Slave_open_temp_tables | 0 |
                              | Slow_launch_threads | 0 |
                              | Slow_queries | 1 | (execution time > 10 secs)
                              | Sort_merge_passes | 3 |
                              | Sort_range | 209058 |
                              | Sort_rows | 21922475 |
                              | Sort_scan | 347132 |
                              | Threads_cached | 0 |
                              | Threads_created | 17122 |
                              | Threads_connected | 15 |
                              | Threads_running | 1 |
                              | Uptime | 236556 | 2 days 17 hrs 42 mins 36 secs
                              +--------------------------+------------+

                              Key Reads/Key Read Requests = 0.001352 (Cache hit = 99.998648%)
                              Key Writes/Key Write Requests = 0.907976
                              Connections/second = 0.072 (/hour = 260.584)
                              KB received/second = 1.233 (/hour = 4438.533)
                              KB sent/second = 6.098 (/hour = 21952.549)
                              Temporary Tables Created/second = 0.168 (/hour = 603.135)
                              Opened Tables/second = 0.000 (/hour = 1.354)
                              Slow Queries/second = 0.000 (/hour = 0.015)
                              % of slow queries = 0.000%
                              Queries/second = 8.740 (/hour = 31464.830)
                              key stuff is in bold
                              and is explained in some of the links in my sig ie

                              http://vbulletin.com/forum/showthread.php?threadid=3452

                              http://www.mysql.com/information/pre...719/index.html

                              and variables are explained at

                              http://www.mysql.com/documentation/m...SHOW_VARIABLES
                              :: 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

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