Mysql high cpu usage tapi low memori usage

[twistedshells]

Bisa dicopy paste hasil top dan iostat

 

[cpserv]

Untuk cek kalo kebanyakan nulis di hardisk parameternya apa ya bos? maaf newbie.

yang paling penting sih bagian sini :

max_connections		=
table_cache		=
table_definition_cache	=
query_cache_limit	=
query_cache_size        =
query_cache_type	=
query_cache_min_res_unit =
tmp_table_size		=
max_heap_table_size	=

 

[1stserver]

Numpang nanya nih master" server mysql saya terjadi serperti ini : "Mysql high cpu usage tapi low memori usage"
solusinya gmna ya, biar pemakaian cpunya bisa dialihkan ke memori.

terimakasih.

pernah dulu mengalaminya, ternyata %iowait sangat tinggi juga .... lalu replace HDD dengan yang baru yg punya Cache lebih tinggi, kembali ke normal. semoga bisa membantu

 

[-sky-]

coba di paste dulu setup my.cnf nya sama httpd.cnf nya biar bisa ditau
kalo gak ada info itu susah ..itu sama saja main tebak tebakan

 

[lopikunik]

coba di paste dulu setup my.cnf nya sama httpd.cnf nya biar bisa ditau
kalo gak ada info itu susah ..itu sama saja main tebak tebakan

ini setup my.cnf nya :

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
port = 3306
#tmpdir = /tmp
tmpdir = /dev/shm
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

# SPECIAL CUSTOME CONFIG

skip-external-locking
skip-name-resolve

# * Fine Tunning
key_buffer = 50M
max_allowed_packet = 32M
thread_stack = 256K
sort_buffer_size = 1M
read_buffer_size = 1M
read_rnd_buffer_size = 1M
myisam_sort_buffer_size = 64M
join_buffer_size = 256K

# This replaces the startup script and checks MyISAM tables if needed
# the first time they are touched
myisam-recover = BACKUP
#max_connections = 5000
max_connections = 2000
#max_user_connections = 1000
max_connect_errors = 100
table_cache = 8012
thread_concurrency = 8
concurrent_insert = 2
thread_cache_size = 100 #recommend 5% of max_connections

## Table and TMP settings
max_heap_table_size = 10G #recommend same size as tmp_table_size
bulk_insert_buffer_size = 10G #recommend same size as tmp_table_size
tmp_table_size = 10G #recommend 1G min

#
# * Query Cache Configuration
#
query_cache_limit = 512K
query_cache_size = 32M
query_cache_type = 2

#
# * Access Time
#
interactive_timeout = 360
wait_timeout = 60
connect_timeout = 30

#
# * Logging and Replication
#
# Both location gets rotated by the cronjob.
# Be aware that this log type is a performance killer.
# As of 5.1 you can enable the log at runtime!
#general_log_file = /var/log/mysql/mysql.log
#general_log = 1

#log_error = /var/log/mysql/error.log

# Here you can see queries with especially long duration
#log_slow_queries = /var/log/mysql/mysql-slow.log
#long_query_time = 5
#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 = 30
max_binlog_size = 200M
#binlog_do_db = include_database_name
#binlog_ignore_db = include_database_name

#innodb_data_home_dir = /var/lib/mysql
#innodb_data_file_path = ibdata1:128M;ibdata2:10M:autoextend
#innodb_log_file_size = 512M #64G_RAM+ = 768, 24G_RAM+ = 512, 8G_RAM+ = 256, 2G_RAM+ = 128
#innodb_log_files_in_group = 4 #combined size of all logs <4GB. <2G_RAM = 2, >2G_RAM = 4
#innodb_buffer_pool_size = 16G #global buffer
#innodb_additional_mem_pool_size = 4M #global buffer
#innodb_status_file #extra reporting
#innodb_file_per_table #enable always
#innodb_flush_log_at_trx_commit = 2 #2/0 = perf, 1 = ACID
#innodb_table_locks = 0 #preserve table locks
#innodb_log_buffer_size = 128M #global buffer
#innodb_lock_wait_timeout = 60
#innodb_thread_concurrency = 16 #recommend 2x core quantity
#innodb_commit_concurrency = 16 #recommend 4x num disks
#innodb_flush_method = O_DIRECT #O_DIRECT = local/DAS, O_DSYNC = SAN/iSCSI
#innodb_support_xa = 0 #recommend 0, disable xa to negate extra disk flush
#skip-innodb-doublewrite

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid