From: Claudio Nanni
Date: April 1 2009 3:14am
Subject: Re: Mysql High load CPU
List-Archive: http://lists.mysql.com/mysql/216960
Message-Id: <53bcf3a60903312014k73bd5d91lbe7acf66e5249daf@mail.gmail.com>
MIME-Version: 1.0
Content-Type: multipart/alternative; boundary=000e0cd247e4aaf4a5046675b752

--000e0cd247e4aaf4a5046675b752
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

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=3D1000M  (with 8 client threads you finish your ram)

set it to something between 256K and 8 M

sort_buffer_size=3D1M

also

read_rnd_buffer_size=3D270M

again is too high, try with same principle (256K - 8M)

read_rnd_buffer_size=3D1M


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 Mys=
ql
> 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=AE Xeon=AE 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/quote=
s
> # escpecially if they contain "#" chars...
> # Remember to edit /etc/mysql/debian.cnf when changing the socket locatio=
n.
> [client]
> port =3D 3306
> socket =3D /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 =3D /var/run/mysqld/mysqld.sock
> nice =3D -18
> [mysqld]
> #
> # * Basic Settings
> #
> user =3D mysql
> pid-file =3D /var/run/mysqld/mysqld.pid
> socket =3D /var/run/mysqld/mysqld.sock
> port =3D 3306
> basedir =3D /usr
> datadir =3D /var/lib/mysql
> tmpdir =3D /tmp
> language =3D /usr/share/mysql/english
> skip-external-locking
> #
> # For compatibility to other Debian packages that still use
> # libmysqlclient10 and libmysqlclient12.
> #old_passwords =3D 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 =3D 127.0.0.1
> #
> # * Fine Tuning do MY.CNF #
> #The size of the buffer used for index blocks. Increase this to get bette=
r
> 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=3D1500M alterado no dia da divisao dos servidores
> key_buffer_size=3D2000M
> #Each thread that needs to do a sort allocates a buffer of this size.
> #sort_buffer_size=3D700M alterado no dia da divisao dos servidores
> sort_buffer_size=3D1000M
>
> #If no specific storage engine/table type is defined in an SQL-Create
> statement the default type will be used.
> default-storage-engine=3Dinnodb
> #Used to help MySQL to decide when to use the slow but safe key cache ind=
ex
> create method.
> myisam_max_extra_sort_file_size=3D300k
> #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=3D2M
> #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=3D100M
> #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 8=
0%
> 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=3D6000M
> #Size of a memory pool InnoDB uses to store data dictionary information a=
nd
> other internal data structures. A sensible value for this might be 2M, bu=
t
> 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 t=
o
> allocate memory from the operating system, and write warning messages to
> the
> MySQL error log.
> innodb_additional_mem_pool_size=3D400M
> #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 cras=
h.
> The combined size of log files must be less than 4 GB on 32-bit computers=
.
> The default is 5M.
> innodb_log_file_size=3D214M
> #The size of the buffer which InnoDB uses to write log to the log files o=
n
> 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=3D300M
> #Specifies when log files are flushed to disk.
> innodb_flush_log_at_trx_commit=3D1
> #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=3D5
> #Helps in performance tuning in heavily concurrent environments.
> innodb_thread_concurrency=3D18
> #Avoid double buffering and reduce swap pressure, in most cases this
> setting
> improves performance.
> innodb_flush_method=3DO_DIRECT
>
> #The memory allocated to store results from old queries.
> query_cache_size=3D1000M
> #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=3D10
> #The number of segonds the mysqld server will close the connection after =
5
> seconds sleep
> wait_timeout=3D5
> #The number of simultaneous clients allowed.
> max_connections=3D200
> #antigo valor 100 alterado para teste de carga
> #The maximum number of active connections for a single user (0 =3D no lim=
it).
> max_user_connections=3D400
> #antigo valor 700 alterado para teste de carga
> #Set the default character set.
> default-character-set=3Dutf8
> #Permits the application to give the threads system a hint for the desire=
d
> number of threads that should be run at the same time
> thread_concurrency=3D18
> #antigo valor 14 alterado para teste de carga
> #How many threads we should keep in a cache for reuse.
> thread_cache_size=3D70
> #antigo valor 70 alterado para teste de carga
> #The stack size for each thread.
> thread_stack=3D256k
> #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=3D256k
> #antigo valor 256k alterado para teste de carga
> #Syntax: sql-mode=3Doption[,option[,option...]] where option can be one o=
f:
> REAL_AS_FLOAT, PIPES_AS_CONCAT,ANSI_QUOTES, IGNORE_SPACE,
> ONLY_FULL_GROUP_BY,NO_UNSIGNED_SUBTRACTION.
> transaction-isolation=3DREAD-COMMITTED
> sql-mode=3DSTRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
> #When reading rows in sorted order after a sort, the rows are read throug=
h
> this buffer to avoid a disk seeks. If not set, then it's set to the value
> of
> record_buffer.
> read_rnd_buffer_size=3D270M
> #read_rnd_buffer_size=3D200M valor alterado apos a divisao dos servidores
>
> #The number of open tables for all threads.
> table_cache=3D512
> #If an in-memory temporary table exceeds this size, MySQL will
> automatically
> convert it to an on-disk MyISAM table.
> tmp_table_size=3D500M
> #tmp_table_size=3D400M valor alterado apos a divisao dos servidores
>
> #Maximum number of temporary tables a client can keep open at a time.
> max_tmp_tables=3D90
> #antigo valor 90 alterado para teste de carga
> #
> #key_buffer =3D 16M
> #max_allowed_packet =3D 128M
> #thread_stack =3D 128K
> #thread_cache_size =3D 8
> # This replaces the startup script and checks MyISAM tables if needed
> # the first time they are touched
> myisam-recover =3D BACKUP
> #max_connections =3D 100
> #table_cache =3D 64
> #thread_concurrency =3D 10
> #
> # * Query Cache Configuration
> #
> #query_cache_limit =3D 1M
> #
> #
> # * Logging and Replication
> #
> # Both location gets rotated by the cronjob.
> # Be aware that this log type is a performance killer.
> #log =3D /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 =3D /var/log/mysql/mysql-slow.log
> #long_query_time =3D 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 abou=
t
> # other settings you may need to change.
> #server-id =3D 1
> #log_bin =3D /var/log/mysql/mysql-bin.log
> expire_logs_days =3D 10
> max_binlog_size =3D 100M
> #binlog_do_db =3D include_database_name
> #binlog_ignore_db =3D 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 =3D /var/lib/mysql/
> #
> # For generating SSL certificates I recommend the OpenSSL GUI "tinyca".
> #
> # ssl-ca=3D/etc/mysql/cacert.pem
> # ssl-cert=3D/etc/mysql/server-cert.pem
> # ssl-key=3D/etc/mysql/server-key.pem
>
>
> [mysqldump]
> quick
> quote-names
> #max_allowed_packet =3D 16M
> [mysql]
> #no-auto-rehash # faster start of mysql but no tab completition
> [isamchk]
> key_buffer =3D 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=3D127.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 ####
> ########################
>

--000e0cd247e4aaf4a5046675b752--