Configure mysql on dedicated server

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • Beor
    New Member
    • Dec 2007
    • 6
    • 4.0.0

    Configure mysql on dedicated server

    Hello

    I have problems with the configuration of mysql for vbulletin4, this is my machine:

    Hardware

    Procesador Intel Core2Duo
    2x 2.33+ GHz
    3 MB L2 - FSB 1066 MHz Arquitectura64 bits Memoria RAM 2 GB DDR2 Disco duro 2x 500 GB - SATA2 RAID 0/1NICFastEthernet
    What is the reccomended configuration for use with vb4 on my.cnf?

    Thanks
  • sven4o
    Member
    • Feb 2010
    • 56
    • 3.8.x

    #2
    Based on the provided details the optimal configuration which I can recommend you is:

    key_buffer_size=512M
    max_connections=500
    max_connect_errors=25
    connect_timeout=10
    interactive_timeout=20
    wait_timeout=10
    delayed_insert_timeout=10
    join_buffer_size=2M
    key_buffer=128M
    max_allowed_packet=32M
    sort_buffer_size=2M
    myisam_sort_buffer_size=1M
    read_buffer_size=1M
    read_rnd_buffer_size=2M
    table_cache=1024
    thread_cache_size=192
    query_cache_size=1024M
    query_cache_limit=64M
    query_cache_type=1
    tmp_table_size=256M
    max_tmp_tables=10
    long_query_time=1

    Please always make sure that the size of your RAM is equal or bigger that the following total usage:

    memory>=key_buffer+(sort_buffer_size+read_buffer_size)*max_connections

    Also, you can check the official MySQL documentation regarding the server optimization:



    Detailed explanation of the above system variables can be found at:

    SiteGround Technical Support Team Member.
    Check out our special vBulletin hosting package.

    Comment

    • Beor
      New Member
      • Dec 2007
      • 6
      • 4.0.0

      #3
      Thanks for the answer, this is correct?

      Code:
      [mysqld]
      set-variable=local-infile=0
      datadir=/var/lib/mysql
      socket=/var/lib/mysql/mysql.sock
      # Default to using old password format for compatibility with mysql 3.x
      # clients (those using the mysqlclient10 compatibility package).
      old_passwords=1
      log_slow_queries=/var/log/mysqld.slow.log
      key_buffer_size=512M
      max_connections=500
      max_connect_errors=25
      connect_timeout=10
      interactive_timeout=20
      wait_timeout=10
      delayed_insert_timeout=10
      join_buffer_size=2M
      key_buffer=128M
      max_allowed_packet=32M
      sort_buffer_size=2M
      myisam_sort_buffer_size=1M
      read_buffer_size=1M
      read_rnd_buffer_size=2M
      table_cache=1024
      thread_cache_size=192
      query_cache_size=1024M
      query_cache_limit=64M
      query_cache_type=1
      tmp_table_size=256M
      max_tmp_tables=10
      long_query_time=1
      
      [mysql.server]
      user=mysql
      basedir=/var/lib
      
      [mysqld_safe]
      log-error=/var/log/mysqld.log
      pid-file=/var/run/mysqld/mysqld.pid
      Cu

      Comment

      • Beor
        New Member
        • Dec 2007
        • 6
        • 4.0.0

        #4
        My cpu work at 100% with this configuration:

        Utilización del Servidor
        Carga CPU : 100%
        Carga RAM :24 %


        With 100 users online and 200-300 guests on last 15minutes

        Thx

        Comment

        • Beor
          New Member
          • Dec 2007
          • 6
          • 4.0.0

          #5
          at this moment i use this config:

          Code:
          [mysqld]
          key_buffer_size=512M
          max_connections=500
          max_connect_errors=25
          connect_timeout=20
          wait_timeout=10
          delayed_insert_timeout=10
          
          [mysql.server]
          user=mysql
          basedir=/var/lib
          
          [mysqld_safe]
          Lose changes to this file (y,n,^C)?
          File /etc/my.cnf not saved
          [root@ns363979 beor]# joe /etc/my.cnf
          Processing '/etc/joe/joerc'...Processing '/etc/joe/ftyperc'...done
          done
              I    /etc/my.cnf                  Row 29   Col 1   12:45  Ctrl-K H for help
          query_cache_size=1024M
          query_cache_type=1
          thread_cache_size=192
          sort_buffer_size=2M
          myisam_sort_buffer_size=1M
          join_buffer_size=2M
          key_buffer=128M
          read_buffer_size=1M
          read_rnd_buffer_size=2M
          table_cache=1024
          tmp_table_size=256M
          max_tmp_tables=10
          log_slow_queries=/var/log/mysqld.slow.log
          long_query_time=1
          
          [mysql.server]
          user=mysql
          basedir=/var/lib
          
          [mysqld_safe]
          log-error=/var/log/mysqld.log
          pid-file=/var/run/mysqld/mysqld.pid
          The cpu work at this moment on 50-60% and 21-23% of ram.

          And not receive more emails from error bd vbulletin

          Code:
              Database error in vBulletin 4.0.2:
             
            Invalid SQL:
            SELECT cache.originaltext as originaltext, cache.translated as translated FROM vbenterprisetranslator_cache_medium help, vbenterprisetranslator_cache_medium cache WHERE help.originaltext='Ya+lo+he+encontrado%2C+por+si+a+alguien+le+interesa.+Se+llama+plugin+enigma2pluginextensionspauli_0_6_12_all_rar+.+Con+este+plugin+consigues+que+las' AND help.tl='fr' AND cache.serie=help.serie;
             
            MySQL Error   : MySQL server has gone away
            Error Number  : 2006
            Request Date  : Thursday, March 18th 2010 @ 12:13:15 PM
            Error Date    : Thursday, March 18th 2010 @ 12:13:27 PM
            Script        : [URL="http://www.tododream.com/foro/http:/www.tododream.com/foro/dreambox-8000hd/140258-plugin-para-cambiar-las-funciones-de-las-teclas-del-mando-distancia-plugin-pauli.html"]http://www.tododream.com/foro/http://www.tododream.com/foro/dreambox-8000hd/140258-plugin-para-cambiar-las-funciones-de-las-teclas-del-mando-distancia-plugin-pauli.html[/URL]
            Referrer      : [URL]http://www.tododream.com/foro/dreambox-8000hd/?language=fr[/URL]
            IP Address    : 91.182.80.10
            Username      : No Registrado
            Classname     : vB_Database
            MySQL Version :

          Thanks

          Comment

          • yogesh
            Senior Member
            • May 2005
            • 471

            #6
            For optimization settings, read this thread, http://www.vbulletin.com/forum/showt...sts-19-03-2010 and post in that section.
            .

            Comment

            • hydn
              Senior Member
              • Sep 2005
              • 355

              #7
              query_cache_size=1024M
              !!!!!!!!??????!!!!!!!

              Please don't use the config below! There are several issues with it.

              Originally posted by sven4o
              Based on the provided details the optimal configuration which I can recommend you is:

              key_buffer_size=512M
              max_connections=500
              max_connect_errors=25
              connect_timeout=10
              interactive_timeout=20
              wait_timeout=10
              delayed_insert_timeout=10
              join_buffer_size=2M
              key_buffer=128M
              max_allowed_packet=32M
              sort_buffer_size=2M
              myisam_sort_buffer_size=1M
              read_buffer_size=1M
              read_rnd_buffer_size=2M
              table_cache=1024
              thread_cache_size=192
              query_cache_size=1024M
              query_cache_limit=64M
              query_cache_type=1
              tmp_table_size=256M
              max_tmp_tables=10
              long_query_time=1

              Please always make sure that the size of your RAM is equal or bigger that the following total usage:

              memory>=key_buffer+(sort_buffer_size+read_buffer_size)*max_connections

              Also, you can check the official MySQL documentation regarding the server optimization:



              Detailed explanation of the above system variables can be found at:

              http://dev.mysql.com/doc/refman/5.1/...variables.html

              Comment

              • hydn
                Senior Member
                • Sep 2005
                • 355

                #8
                As far a query cache goes please read this:


                I run a fairly large vb site on one box that does nightly remote backups. (WinServer 8 cores 4g ram bla bla bla). You can get a LOT of perfomance gains from proper tuning. However, hardware upgrades may often be needed even if your server is properly tuned.

                Here's my recommeded config:

                key_buffer_size=128M ###NOTE: Generous guesstimate based on the size of your forum/database as judged from the # threads/posts totals here: http://www.tododream.com/foro/forum.php
                max_connections=50
                join_buffer_size=64K
                max_allowed_packet=8M
                sort_buffer_size=256k
                myisam_sort_buffer_size=64M
                read_buffer_size=256K
                read_rnd_buffer_size=512K
                table_cache=512
                thread_cache_size=16
                query_cache_size=32M
                query_cache_limit=2M
                query_cache_type=1
                query_cache_min_res_unit = 1024 ### 2nd most important after size of qcache.
                tmp_table_size=64M
                max_heap_table_size=64M ### MUST be set or size of max_heap_table_size will be used and it's default is smaller than 64MB.
                concurrent_insert = 2
                low_priority_updates = 1
                thread_concurrency = 8
                Let me know how fast this makes things

                Here's my config:
                [client]
                protocol = pipe
                socket=mysql

                [mysql]
                character-set-server=latin1

                [mysqld]
                enable-named-pipe
                socket=mysql
                skip-innodb
                skip-show-database
                basedir="E:/mysql/"
                datadir="E:/mysql/Data/"
                character-set-server=latin1
                default-storage-engine=MYISAM
                sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
                max_connections=20
                max_allowed_packet=16M

                query_cache_type = 1
                query_cache_size = 32M
                query_cache_limit = 2M
                query_cache_min_res_unit = 1024

                table_cache=512
                tmp_table_size=128M
                max_heap_table_size=128M

                thread_cache_size=32
                thread_concurrency = 32

                myisam_max_sort_file_size=100G
                myisam_sort_buffer_size=205M

                key_buffer_size=256M
                read_buffer_size=512K
                read_rnd_buffer_size=1M
                sort_buffer_size=256K

                log-slow-queries="E:/mysql/data/slow.log"
                long_query_time=2
                concurrent_insert = 2
                low_priority_updates = 1
                Good luck!
                Last edited by hydn; Wed 7 Apr '10, 4:03pm.

                Comment

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