From: Claudio Nanni Date: April 1 2009 8:02pm Subject: Re: Mysql High load CPU List-Archive: http://lists.mysql.com/mysql/216978 Message-Id: <49D3C852.6070106@gmail.com> MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 8bit Tadeu Alves wrote: > Thanks for this heads up i was thinking into some of these i was > looking into the page that has the calculation of each looking into > mysqlperformance blog > > it's a good idea of add more memory into this variables? > > query_cache_size= 1000M to 2000M and > innodb_buffer_pool_size= 6000M to 8000M for the query_cache_size you should see the current query cache efficiency using the Qcache_xxxxx status variables, if you have already a very good performance(http://www.mysqlperformanceblog.com/2006/07/27/mysql-query-cache/) probably is not the case to dedicate an additional gigabyte to it. the InnoDB buffer pool as a generic rule could be sized to 50%-80% of the available physical RAM, in your case it is already to its maximum recommended size. remember that some buffers are general, some are per connected thread like 'join_buffer_size', 'sort_buffer_size','read_buffer_size','read_rnd_buffer_size', so their total size will be [threads_connected * (size of all per connection buffers)] Cheers Claudio > > our maximum users connections are about 400 simultaneous users > > On Wed, Apr 1, 2009 at 12:14 AM, Claudio Nanni > > wrote: > > Hi, > probably your system is swapping on disk, > immediately reduce the sort_buffer_size, it is a per connection > buffer, and your setting is way too high: > > > sort_buffer_size=1000M (with 8 client threads you finish your ram) > > set it to something between 256K and 8 M > > sort_buffer_size=1M > > also > > > read_rnd_buffer_size=270M > > again is too high, try with same principle (256K - 8M) > > read_rnd_buffer_size=1M > > > these are the first quick fixes I would do. > > Cheers > > Claudio > > > 2009/4/1 Tadeu Alves > > > Helo there guys today ive got a brig problem my server that > runs only Mysql > is undegoind a very load, the server is ok but memory and cpu > usage are very > high > mys server configuration is a > > 2x Quad Core Intel® Xeon® E5450, 2x6MB Cache, 3.0GHz, 1333MHz FSB > 8GB 800MHz Memory > 2x SAS 73GB 15000RPM in RAID 1 > > and i'm running Myslq Server version: 5.1.32-0.dotdeb.1 (Debian) > The server runs mostly InnoD files it has a little Mysam tables > > Oh yeah I use "moodle 1.7.2+" if anyone knows this :D > > bellow is my.cnf file my server is a Ubuntu 8.04 Tls Version > (with all > updates and upgrades) > > Really need help cause server may crash and this cannot heapen. > > > ##################### > ###### my.cnf ####### > ##################### > # > # The MySQL database server configuration file. > # > # You can copy this to one of: > # - "/etc/mysql/my.cnf" to set global options, > # - "~/.my.cnf" to set user-specific options. > # > # One can use all long options that the program supports. > # Run program with --help to get a list of available options > and with > # --print-defaults to see which it would actually understand > and use. > # > # For explanations see > # http://dev.mysql.com/doc/mysql/en/server-system-variables.html > # This will be passed to all mysql clients > # It has been reported that passwords should be enclosed with > ticks/quotes > # escpecially if they contain "#" chars... > # Remember to edit /etc/mysql/debian.cnf when changing the > socket location. > [client] > port = 3306 > socket = /var/run/mysqld/mysqld.sock > # Here is entries for some specific programs > # The following values assume you have at least 32M ram > # This was formally known as [safe_mysqld]. Both versions are > currently > parsed. > [mysqld_safe] > socket = /var/run/mysqld/mysqld.sock > nice = -18 > [mysqld] > # > # * Basic Settings > # > user = mysql > pid-file = /var/run/mysqld/mysqld.pid > socket = /var/run/mysqld/mysqld.sock > port = 3306 > basedir = /usr > datadir = /var/lib/mysql > tmpdir = /tmp > language = /usr/share/mysql/english > skip-external-locking > # > # For compatibility to other Debian packages that still use > # libmysqlclient10 and libmysqlclient12. > #old_passwords = 1 > # > # Instead of skip-networking the default is now to listen only on > # localhost which is more compatible and is not less secure. > #bind-address = 127.0.0.1 > # > # * Fine Tuning do MY.CNF # > #The size of the buffer used for index blocks. Increase this > to get better > index handling (for all reads and multiple writes) to as much > as you can > afford; 64M on a 256M machine that mainly runs MySQL is quite > common. > #key_buffer_size=1500M alterado no dia da divisao dos servidores > key_buffer_size=2000M > #Each thread that needs to do a sort allocates a buffer of > this size. > #sort_buffer_size=700M alterado no dia da divisao dos servidores > sort_buffer_size=1000M > > #If no specific storage engine/table type is defined in an > SQL-Create > statement the default type will be used. > default-storage-engine=innodb > #Used to help MySQL to decide when to use the slow but safe > key cache index > create method. > myisam_max_extra_sort_file_size=300k > #Don't use the fast sort index method to created index if the > temporary file > would get bigger than this. > myisam_max_sort_file_size=2M > #The buffer that is allocated when sorting the index when > doing a REPAIR or > when creating indexes with CREATE INDEX or ALTER TABLE. > myisam_sort_buffer_size=100M > #The bigger you set this the less disk I/O is needed to access > data in > tables. On a dedicated database server you may set this > parameter up to 80% > of the machine physical memory size. Do not set it too large, > though, > because competition of the physical memory may cause paging in > the operating > system. > innodb_buffer_pool_size=6000M > #Size of a memory pool InnoDB uses to store data dictionary > information and > other internal data structures. A sensible value for this > might be 2M, but > the more tables you have in your application the more you will > need to > allocate here. If InnoDB runs out of memory in this pool, it > will start to > allocate memory from the operating system, and write warning > messages to the > MySQL error log. > innodb_additional_mem_pool_size=400M > #Size of each log file in a log group in megabytes. Sensible > values range > from 1M to 1/n-th of the size of the buffer pool specified > below, where n is > the number of log files in the group. The larger the value, > the less > checkpoint flush activity is needed in the buffer pool, saving > disk I/O. But > larger log files also mean that recovery will be slower in > case of a crash. > The combined size of log files must be less than 4 GB on > 32-bit computers. > The default is 5M. > innodb_log_file_size=214M > #The size of the buffer which InnoDB uses to write log to the > log files on > disk. Sensible values range from 1M to 8M. A big log buffer > allows large > transactions to run without a need to write the log to disk > until the > transaction commit. Thus, if you have big transactions, making > the log > buffer big will save disk I/O. > innodb_log_buffer_size=300M > #Specifies when log files are flushed to disk. > innodb_flush_log_at_trx_commit=1 > #Number of file I/O threads in InnoDB. Normally, this should > be 4, but on > Windows disk I/O may benefit from a larger number. > innodb_file_io_threads=5 > #Helps in performance tuning in heavily concurrent environments. > innodb_thread_concurrency=18 > #Avoid double buffering and reduce swap pressure, in most > cases this setting > improves performance. > innodb_flush_method=O_DIRECT > > #The memory allocated to store results from old queries. > query_cache_size=1000M > #antigo valor 500M alterado para teste de carga > #antigo valor 800M -> 1000 alterado para teste de carga > > #The number of seconds the mysqld server is waiting for a > connect packet > before responding with 'Bad handshake' > connect_timeout=10 > #The number of segonds the mysqld server will close the > connection after 5 > seconds sleep > wait_timeout=5 > #The number of simultaneous clients allowed. > max_connections=200 > #antigo valor 100 alterado para teste de carga > #The maximum number of active connections for a single user (0 > = no limit). > max_user_connections=400 > #antigo valor 700 alterado para teste de carga > #Set the default character set. > default-character-set=utf8 > #Permits the application to give the threads system a hint for > the desired > number of threads that should be run at the same time > thread_concurrency=18 > #antigo valor 14 alterado para teste de carga > #How many threads we should keep in a cache for reuse. > thread_cache_size=70 > #antigo valor 70 alterado para teste de carga > #The stack size for each thread. > thread_stack=256k > #antigo valor 256k alterado para teste de carga > #Each thread that does a sequential scan allocates a buffer of > this size for > each table it scans. If you do many sequential scans, you may > want to > increase this value. > read_buffer_size=256k > #antigo valor 256k alterado para teste de carga > #Syntax: sql-mode=option[,option[,option...]] where option can > be one of: > REAL_AS_FLOAT, PIPES_AS_CONCAT,ANSI_QUOTES, IGNORE_SPACE, > ONLY_FULL_GROUP_BY,NO_UNSIGNED_SUBTRACTION. > transaction-isolation=READ-COMMITTED > sql-mode=STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION > #When reading rows in sorted order after a sort, the rows are > read through > this buffer to avoid a disk seeks. If not set, then it's set > to the value of > record_buffer. > read_rnd_buffer_size=270M > #read_rnd_buffer_size=200M valor alterado apos a divisao dos > servidores > > #The number of open tables for all threads. > table_cache=512 > #If an in-memory temporary table exceeds this size, MySQL will > automatically > convert it to an on-disk MyISAM table. > tmp_table_size=500M > #tmp_table_size=400M valor alterado apos a divisao dos servidores > > #Maximum number of temporary tables a client can keep open at > a time. > max_tmp_tables=90 > #antigo valor 90 alterado para teste de carga > # > #key_buffer = 16M > #max_allowed_packet = 128M > #thread_stack = 128K > #thread_cache_size = 8 > # This replaces the startup script and checks MyISAM tables if > needed > # the first time they are touched > myisam-recover = BACKUP > #max_connections = 100 > #table_cache = 64 > #thread_concurrency = 10 > # > # * Query Cache Configuration > # > #query_cache_limit = 1M > # > # > # * Logging and Replication > # > # Both location gets rotated by the cronjob. > # Be aware that this log type is a performance killer. > #log = /var/log/mysql/mysql.log > # > # Error logging goes to syslog. This is a Debian improvement :) > # > # Here you can see queries with especially long duration > #log_slow_queries = /var/log/mysql/mysql-slow.log > #long_query_time = 2 > #log-queries-not-using-indexes > # > # The following can be used as easy to replay backup logs or for > replication. > # note: if you are setting up a replication slave, see > README.Debian about > # other settings you may need to change. > #server-id = 1 > #log_bin = /var/log/mysql/mysql-bin.log > expire_logs_days = 10 > max_binlog_size = 100M > #binlog_do_db = include_database_name > #binlog_ignore_db = include_database_name > # > # * InnoDB > # > # InnoDB is enabled by default with a 10MB datafile in > /var/lib/mysql/. > # Read the manual for more InnoDB related options. There are many! > # > # * Security Features > # > # Read the manual, too, if you want chroot! > # chroot = /var/lib/mysql/ > # > # For generating SSL certificates I recommend the OpenSSL GUI > "tinyca". > # > # ssl-ca=/etc/mysql/cacert.pem > # ssl-cert=/etc/mysql/server-cert.pem > # ssl-key=/etc/mysql/server-key.pem > > > [mysqldump] > quick > quote-names > #max_allowed_packet = 16M > [mysql] > #no-auto-rehash # faster start of mysql but no tab completition > [isamchk] > key_buffer = 16M > # > # * NDB Cluster > # > # See /usr/share/doc/mysql-server-*/README.Debian for more > information. > # > # The following configuration is read by the NDB Data Nodes > (ndbd processes) > # not from the NDB Management Nodes (ndb_mgmd processes). > # > # [MYSQL_CLUSTER] > # ndb-connectstring=127.0.0.1 > > # > # * IMPORTANT: Additional settings that can override those > from this file! > # The files must end with '.cnf', otherwise they'll be ignored. > # > !includedir /etc/mysql/conf.d > # > ######################## > ##### end of my.cnf #### > ######################## > > >