List:General Discussion« Previous MessageNext Message »
From:Claudio Nanni Date:April 1 2009 8:02pm
Subject:Re: Mysql High load CPU
View as plain text  
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 
> <claudio.nanni@stripped <mailto:claudio.nanni@stripped>> 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 <tadeudca@stripped
> <mailto:tadeudca@stripped>>
>
>         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 ####
>         ########################
>
>
>

Thread
Mysql High load CPUTadeu Alves1 Apr
  • Re: Mysql High load CPUClaudio Nanni1 Apr
Re: Mysql High load CPUClaudio Nanni1 Apr