List:General Discussion« Previous MessageNext Message »
From:Claudio Nanni Date:April 1 2009 3:14am
Subject:Re: Mysql High load CPU
View as plain text  
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>

> 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