MySQL CPU Kullanımı my.cnf optimizasyonu

top - 15:02:31 up 138 days, 16:54,  1 user,  load average: 3.04, 2.93, 3.18
Tasks: 138 total,   1 running, 137 sleeping,   0 stopped,   0 zombie
Cpu(s): 26.8%us, 24.3%sy,  3.7%ni, 44.8%id,  0.2%wa,  0.0%hi,  0.3%si,  0.0%st
Mem:   2067348k total,  1926684k used,   140664k free,   208824k buffers
Swap:  2031608k total,    18420k used,  2013188k free,  1247084k cached

  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
 6401 mysql     15   0  327m  95m 3672 S   96  4.7   4660:28 mysqld
19382 madimagi  16   0  147m 7076 4728 S    1  0.3   0:00.02 php5
18303 nobody    15   0  8404 2888 1424 S    0  0.1   0:00.15 httpd
18892 nobody    15   0  8404 2832 1408 S    0  0.1   0:00.05 httpd
18903 nobody    15   0  8404 2856 1416 S    0  0.1   0:00.04 httpd
19254 root      15   0  2188 1056  804 R    0  0.1   0:00.04 top
19385 madimagi  16   0  147m 6772 4512 S    0  0.3   0:00.01 php5
    1 root      15   0  2060  528  504 S    0  0.0   0:43.87 init

[mysqld]
port                    = 3306
socket                  = /var/lib/mysql/mysql.sock
datadir                 = /var/lib/mysql
skip-locking
skip-innodb
safe-show-database
query_cache_limit=8M
query_cache_size=32M
query_cache_type=1
max_user_connections=200
max_connections=100
interactive_timeout=10
wait_timeout=15
connect_timeout=20
thread_cache_size=5
key_buffer=256M
join_buffer=4M
max_connect_errors=15
max_allowed_packet=16M
table_cache=1024
record_buffer=6M
sort_buffer_size=3M
read_buffer_size=2M
read_rnd_buffer_size=2M
thread_concurrency=4
myisam_sort_buffer_size=64M
tmp_table_size=128M
low_priority_updates=1

[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash

[isamchk]
key_buffer=128M
sort_buffer=128M
read_buffer=32M
write_buffer=32M

[myisamchk]
key_buffer=128M
sort_buffer=128M
read_buffer=32M
write_buffer=32M

[mysqlhotcopy]
interactive-timeout

mysqld is alive

        -- MYSQL PERFORMANCE TUNING PRIMER --
             - By: Matthew Montgomery -

MySQL Version 5.0.67-community i686

Uptime = 2 days 14 hrs 49 min 28 sec
Avg. qps = 27
Total Questions = 6260648
Threads Connected = 8

Server has been running for over 48hrs.
It should be safe to follow these recommendations

To find out more information on how each of these
runtime variables effects performance visit:
http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html
Visit http://www.mysql.com/products/enterprise/advisors.html
for info about MySQL's Enterprise Monitoring and Advisory Service

SLOW QUERIES
The slow query log is NOT enabled.
Current long_query_time = 10 sec.
You have 5 out of 6260689 that take longer than 10 sec. to complete
Your long_query_time may be too high, I typically set this under 5 sec.

BINARY UPDATE LOG
The binary update log is NOT enabled.
You will not be able to do point in time recovery
See http://dev.mysql.com/doc/refman/5.0/en/point-in-time-recovery.html

WORKER THREADS
Current thread_cache_size = 5
Current threads_cached = 4
Current threads_per_sec = 0
Historic threads_per_sec = 0
Your thread_cache_size is fine

MAX CONNECTIONS
Current max_connections = 100
Current threads_connected = 2
Historic max_used_connections = 33
The number of used connections is 33% of the configured maximum.
Your max_connections variable seems to be fine.

MEMORY USAGE
Max Memory Ever Allocated : 667 M
Configured Max Per-thread Buffers : 1 G
Configured Max Global Buffers : 298 M
Configured Max Memory Limit : 1 G
Physical Memory : 1.97 G
Max memory limit seem to be within acceptable norms

KEY BUFFER
Current MyISAM index space = 21 M
Current key_buffer_size = 256 M
Key cache miss rate is 1 : 25525
Key buffer fill ratio = 7.00 %
Your key_buffer_size seems to be too high.
Perhaps you can use these resources elsewhere

QUERY CACHE
Query cache is enabled
Current query_cache_size = 32 M
Current query_cache_used = 2 M
Current query_cache_limit = 8 M
Current Query cache Memory fill ratio = 7.24 %
Current query_cache_min_res_unit = 4 K
Your query_cache_size seems to be too high.
Perhaps you can use these resources elsewhere
MySQL won't cache query results that are larger than query_cache_limit in size

SORT OPERATIONS
Current sort_buffer_size = 3 M
Current read_rnd_buffer_size = 2 M
Sort buffer seems to be fine

JOINS
Current join_buffer_size = 4.00 M
You have had 0 queries where a join could not use an index properly
Your joins seem to be using indexes properly
join_buffer_size >= 4 M
This is not advised

OPEN FILES LIMIT
Current open_files_limit = 4096 files
The open_files_limit should typically be set to at least 2x-3x
that of table_cache if you have heavy MyISAM usage.
Your open_files_limit value seems to be fine

TABLE CACHE
Current table_cache value = 1024 tables
You have a total of 90 tables
You have 135 open tables.
The table_cache value seems to be fine

TEMP TABLES
Current max_heap_table_size = 16 M
Current tmp_table_size = 128 M
Of 337730 temp tables, 0% were created on disk
Effective in-memory tmp_table_size is limited to max_heap_table_size.
Created disk tmp tables ratio seems fine

TABLE SCANS
Current read_buffer_size = 2 M
Current table scan ratio = 407 : 1
read_buffer_size seems to be fine

TABLE LOCKING
Current Lock Wait ratio = 1 : 20
You may benefit from selective use of InnoDB.
If you have a high concurrency of inserts on Dynamic row-length tables
consider setting 'concurrent_insert=2'.

        -- MYSQL PERFORMANCE TUNING PRIMER --
             - By: Matthew Montgomery -

MySQL Version 5.0.58 i686

Uptime = 42 days 9 hrs 39 min 35 sec
Avg. qps = 53
Total Questions = 194875628
Threads Connected = 2

Server has been running for over 48hrs.
It should be safe to follow these recommendations

To find out more information on how each of these
runtime variables effects performance visit:
http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html
Visit http://www.mysql.com/products/enterprise/advisors.html
for info about MySQL's Enterprise Monitoring and Advisory Service

SLOW QUERIES
The slow query log is NOT enabled.
Current long_query_time = 10 sec.
You have 411 out of 194875649 that take longer than 10 sec. to complete
Your long_query_time may be too high, I typically set this under 5 sec.

BINARY UPDATE LOG
The binary update log is NOT enabled.
You will not be able to do point in time recovery
See http://dev.mysql.com/doc/refman/5.0/en/point-in-time-recovery.html

WORKER THREADS
Current thread_cache_size = 0
Current threads_cached = 0
Current threads_per_sec = 2
Historic threads_per_sec = 0
Threads created per/sec are overrunning threads cached
You should raise thread_cache_size

MAX CONNECTIONS
Current max_connections = 100
Current threads_connected = 2
Historic max_used_connections = 101
The number of used connections is 101% of the configured maximum.
You should raise max_connections

MEMORY USAGE
Max Memory Ever Allocated : 289 M
Configured Max Per-thread Buffers : 268 M
Configured Max Global Buffers : 17 M
Configured Max Memory Limit : 286 M
Physical Memory : 1.97 G
Max memory limit seem to be within acceptable norms

KEY BUFFER
Current MyISAM index space = 516 M
Current key_buffer_size = 7 M
Key cache miss rate is 1 : 92
Key buffer fill ratio = 63.00 %
Your key_buffer_size seems to be fine

QUERY CACHE
Query cache is supported but not enabled
Perhaps you should set the query_cache_size

SORT OPERATIONS
Current sort_buffer_size = 2 M
Current read_rnd_buffer_size = 256 K
Sort buffer seems to be fine

JOINS
Current join_buffer_size = 132.00 K
You have had 4854 queries where a join could not use an index properly
You should enable "log-queries-not-using-indexes"
Then look for non indexed joins in the slow query log.
If you are unable to optimize your queries you may want to increase your
join_buffer_size to accommodate larger joins in one pass.

Note! This script will still suggest raising the join_buffer_size when
ANY joins not using indexes are found.

OPEN FILES LIMIT
Current open_files_limit = 1024 files
The open_files_limit should typically be set to at least 2x-3x
that of table_cache if you have heavy MyISAM usage.
Your open_files_limit value seems to be fine

TABLE CACHE
Current table_cache value = 64 tables
You have a total of 1186 tables
You have 64 open tables.
Current table_cache hit rate is 0%, while 100% of your table cache is in use
You should probably increase your table_cache

TEMP TABLES
Current max_heap_table_size = 16 M
Current tmp_table_size = 32 M
Of 1962334 temp tables, 40% were created on disk
Effective in-memory tmp_table_size is limited to max_heap_table_size.
Perhaps you should increase your tmp_table_size and/or max_heap_table_size
to reduce the number of disk-based temporary tables
Note! BLOB and TEXT columns are not allow in memory tables.
If you are using these columns raising these values might not impact your
ratio of on disk temp tables.

TABLE SCANS
Current read_buffer_size = 128 K
Current table scan ratio = 0 : 1
read_buffer_size seems to be fine

TABLE LOCKING
Current Lock Wait ratio = 1 : 5555
Your table locking seems to be fine

web:~# cat /proc/cpuinfo
processor       : 0
vendor_id       : GenuineIntel
cpu family      : 6
model           : 26
model name      : Intel(R) Core(TM) i7 CPU         920  @ 2.67GHz
stepping        : 5
cpu MHz         : 1600.000
cache size      : 8192 KB
physical id     : 0
siblings        : 8
core id         : 0
cpu cores       : 4
apicid          : 0
initial apicid  : 0
fpu             : yes
fpu_exception   : yes
cpuid level     : 11
wp              : yes
flags           : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht tm pbe syscall nx rdtscp lm constant_tsc arch_perfmon pebs bts rep_good pni monitor ds_cpl vmx est tm2 ssse3 cx16 xtpr sse4_1 sse4_2 popcnt lahf_lm ida
bogomips        : 5350.65
clflush size    : 64
cache_alignment : 64
address sizes   : 36 bits physical, 48 bits virtual
power management:

processor       : 1
vendor_id       : GenuineIntel
cpu family      : 6
model           : 26
model name      : Intel(R) Core(TM) i7 CPU         920  @ 2.67GHz
stepping        : 5
cpu MHz         : 1600.000
cache size      : 8192 KB
physical id     : 0
siblings        : 8
core id         : 1
cpu cores       : 4
apicid          : 2
initial apicid  : 2
fpu             : yes
fpu_exception   : yes
cpuid level     : 11
wp              : yes
flags           : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht tm pbe syscall nx rdtscp lm constant_tsc arch_perfmon pebs bts rep_good pni monitor ds_cpl vmx est tm2 ssse3 cx16 xtpr sse4_1 sse4_2 popcnt lahf_lm ida
bogomips        : 5346.45
clflush size    : 64
cache_alignment : 64
address sizes   : 36 bits physical, 48 bits virtual
power management:

processor       : 2
vendor_id       : GenuineIntel
cpu family      : 6
model           : 26
model name      : Intel(R) Core(TM) i7 CPU         920  @ 2.67GHz
stepping        : 5
cpu MHz         : 1600.000
cache size      : 8192 KB
physical id     : 0
siblings        : 8
core id         : 2
cpu cores       : 4
apicid          : 4
initial apicid  : 4
fpu             : yes
fpu_exception   : yes
cpuid level     : 11
wp              : yes
flags           : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht tm pbe syscall nx rdtscp lm constant_tsc arch_perfmon pebs bts rep_good pni monitor ds_cpl vmx est tm2 ssse3 cx16 xtpr sse4_1 sse4_2 popcnt lahf_lm ida
bogomips        : 5346.45
clflush size    : 64
cache_alignment : 64
address sizes   : 36 bits physical, 48 bits virtual
power management:

processor       : 3
vendor_id       : GenuineIntel
cpu family      : 6
model           : 26
model name      : Intel(R) Core(TM) i7 CPU         920  @ 2.67GHz
stepping        : 5
cpu MHz         : 1600.000
cache size      : 8192 KB
physical id     : 0
siblings        : 8
core id         : 3
cpu cores       : 4
apicid          : 6
initial apicid  : 6
fpu             : yes
fpu_exception   : yes
cpuid level     : 11
wp              : yes
flags           : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht tm pbe syscall nx rdtscp lm constant_tsc arch_perfmon pebs bts rep_good pni monitor ds_cpl vmx est tm2 ssse3 cx16 xtpr sse4_1 sse4_2 popcnt lahf_lm ida
bogomips        : 5346.45
clflush size    : 64
cache_alignment : 64
address sizes   : 36 bits physical, 48 bits virtual
power management:

processor       : 4
vendor_id       : GenuineIntel
cpu family      : 6
model           : 26
model name      : Intel(R) Core(TM) i7 CPU         920  @ 2.67GHz
stepping        : 5
cpu MHz         : 1600.000
cache size      : 8192 KB
physical id     : 0
siblings        : 8
core id         : 0
cpu cores       : 4
apicid          : 1
initial apicid  : 1
fpu             : yes
fpu_exception   : yes
cpuid level     : 11
wp              : yes
flags           : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht tm pbe syscall nx rdtscp lm constant_tsc arch_perfmon pebs bts rep_good pni monitor ds_cpl vmx est tm2 ssse3 cx16 xtpr sse4_1 sse4_2 popcnt lahf_lm ida
bogomips        : 5346.45
clflush size    : 64
cache_alignment : 64
address sizes   : 36 bits physical, 48 bits virtual
power management:

processor       : 5
vendor_id       : GenuineIntel
cpu family      : 6
model           : 26
model name      : Intel(R) Core(TM) i7 CPU         920  @ 2.67GHz
stepping        : 5
cpu MHz         : 1600.000
cache size      : 8192 KB
physical id     : 0
siblings        : 8
core id         : 1
cpu cores       : 4
apicid          : 3
initial apicid  : 3
fpu             : yes
fpu_exception   : yes
cpuid level     : 11
wp              : yes
flags           : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht tm pbe syscall nx rdtscp lm constant_tsc arch_perfmon pebs bts rep_good pni monitor ds_cpl vmx est tm2 ssse3 cx16 xtpr sse4_1 sse4_2 popcnt lahf_lm ida
bogomips        : 5346.45
clflush size    : 64
cache_alignment : 64
address sizes   : 36 bits physical, 48 bits virtual
power management:

processor       : 6
vendor_id       : GenuineIntel
cpu family      : 6
model           : 26
model name      : Intel(R) Core(TM) i7 CPU         920  @ 2.67GHz
stepping        : 5
cpu MHz         : 1600.000
cache size      : 8192 KB
physical id     : 0
siblings        : 8
core id         : 2
cpu cores       : 4
apicid          : 5
initial apicid  : 5
fpu             : yes
fpu_exception   : yes
cpuid level     : 11
wp              : yes
flags           : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht tm pbe syscall nx rdtscp lm constant_tsc arch_perfmon pebs bts rep_good pni monitor ds_cpl vmx est tm2 ssse3 cx16 xtpr sse4_1 sse4_2 popcnt lahf_lm ida
bogomips        : 5346.45
clflush size    : 64
cache_alignment : 64
address sizes   : 36 bits physical, 48 bits virtual
power management:

processor       : 7
vendor_id       : GenuineIntel
cpu family      : 6
model           : 26
model name      : Intel(R) Core(TM) i7 CPU         920  @ 2.67GHz
stepping        : 5
cpu MHz         : 1600.000
cache size      : 8192 KB
physical id     : 0
siblings        : 8
core id         : 3
cpu cores       : 4
apicid          : 7
initial apicid  : 7
fpu             : yes
fpu_exception   : yes
cpuid level     : 11
wp              : yes
flags           : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht tm pbe syscall nx rdtscp lm constant_tsc arch_perfmon pebs bts rep_good pni monitor ds_cpl vmx est tm2 ssse3 cx16 xtpr sse4_1 sse4_2 popcnt lahf_lm ida
bogomips        : 5346.45
clflush size    : 64
cache_alignment : 64
address sizes   : 36 bits physical, 48 bits virtual
power management:

web:~#

web:~# uname -a
Linux web.x.net 2.6.26-2-amd64 #1 SMP Fri Aug 14 07:12:04 UTC 2009 x86_64 GNU/Linux
web:~#

top - 07:48:05 up 15 days, 12:05,  1 user,  load average: 0.00, 0.02, 0.00
Tasks: 139 total,   1 running, 138 sleeping,   0 stopped,   0 zombie
Cpu(s):  0.0%us,  0.0%sy,  0.0%ni, 99.9%id,  0.0%wa,  0.0%hi,  0.0%si,  0.0%st
Mem:   8194012k total,   753168k used,  7440844k free,   160840k buffers
Swap:  4200888k total,        0k used,  4200888k free,   370628k cached

web:~# free
             total       used       free     shared    buffers     cached
Mem:       8194012     753144    7440868          0     160840     370628
-/+ buffers/cache:     221676    7972336
Swap:      4200888          0    4200888

[client]
port            = 3306
socket          = /var/run/mysqld/mysqld.sock
[mysqld_safe]
socket          = /var/run/mysqld/mysqld.sock
nice            = 0
[mysqld]
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

bind-address            = 127.0.0.1

key_buffer              = 16M
max_allowed_packet      = 16M

thread_stack            = 128K
thread_cache_size       = 8

myisam-recover          = BACKUP

query_cache_limit       = 1M
query_cache_size        = 16M
expire_logs_days        = 10

max_binlog_size         = 100M

skip-bdb
#skip-innodb

[mysqldump]
quick
quote-names
max_allowed_packet      = 16M

[mysql]
#no-auto-rehash # faster start of mysql but no tab completition

[isamchk]
key_buffer              = 16M

!includedir /etc/mysql/conf.d/

top - 00:44:40 up 42 min,  1 user,  load average: 1.39, 1.52, 1.33
Mem:   1048576k total,   755192k used,   293384k free,        0k buffers
Swap:        0k total,        0k used,        0k free,        0k cached

  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
  668 mysql     20   0 97.9m  15m 4844 S   27  1.6   5:40.41 mysqld
  631 apache    20   0 45916  25m 4096 S   15  2.5   1:42.31 httpd
 1932 apache    20   0 46660  26m 4100 S    0  2.5   1:41.25 httpd
 2608 apache    20   0 46252  25m 4080 S    0  2.5   1:35.58 httpd
 2616 apache    20   0 46512  25m 4076 S    0  2.5   1:26.42 httpd
 3035 apache    20   0 45896  25m 4096 S    0  2.5   1:26.25 httpd
 2610 apache    20   0 45672  25m 4080 S    0  2.5   1:19.60 httpd
 3458 apache    20   0 43140  22m 4076 S   13  2.2   1:12.26 httpd
 4336 apache    20   0 43696  23m 4080 S    0  2.3   1:09.67 httpd
 4745 apache    20   0 45672  25m 4076 R   16  2.5   0:55.54 httpd
 5508 apache    20   0 45788  25m 4076 R   27  2.5   0:48.27 httpd
 5676 apache    20   0 45672  25m 4076 S   31  2.5   0:47.47 httpd
 6167 apache    20   0 45936  25m 4076 R   34  2.5   0:42.27 httpd
 7336 apache    20   0 45904  25m 4076 S    0  2.5   0:24.76 httpd
 7574 apache    20   0 45676  25m 4036 S    0  2.5   0:24.46 httpd
 7584 apache    20   0 46308  25m 4068 S    0  2.5   0:19.30 httpd
 7944 apache    20   0 45672  25m 4072 S   15  2.5   0:17.46 httpd
 7932 apache    20   0 46152  25m 3768 S    0  2.5   0:13.74 httpd
 7919 apache    20   0 44248  23m 4040 R   25  2.3   0:12.96 httpd
 7920 apache    20   0 46808  26m 4036 S    3  2.6   0:10.42 httpd
 8488 apache    20   0 45360  24m 4056 S   17  2.4   0:05.60 httpd
  788 drweb     20   0 41000  36m 1620 S    0  3.6   0:05.18 drwebd.real
 8665 apache    20   0 46528  25m 3768 S   10  2.5   0:05.04 httpd
 8744 apache    20   0 46220  25m 3748 S   13  2.5   0:04.50 httpd
 8826 apache    20   0 46484  25m 3760 S   19  2.5   0:03.48 httpd
  783 psaadm    20   0 47888  18m  12m S    0  1.8   0:00.74 httpsd