MySQL CPU Kullanımı my.cnf optimizasyonu

Holokost · 3 Ocak 2008

merhaba

core2duo e4400
2 gb ram

bir sunucum var

top çıktısı

Kod:

top - 14:17:51 up 4 days, 18:50,  1 user,  load average: 5.41, 7.14, 7.20
Tasks: 199 total,   4 running, 185 sleeping,   9 stopped,   1 zombie
Cpu(s): 88.9% us, 10.9% sy,  0.0% ni,  0.0% id,  0.0% wa,  0.2% hi,  0.0% si
Mem:   2065180k total,  1874072k used,   191108k free,    31064k buffers
Swap:  2096472k total,   176404k used,  1920068k free,   576384k cached

  PID USER      PR  NI %CPU    TIME+  %MEM  VIRT  RES  SHR S COMMAND
30370 mysql     15   0  140   4363:10  2.0  205m  39m 2400 S mysqld
12160 nobody    16   0   10   0:32.58  0.6 25996  12m 2624 S httpd
 6027 nobody    16   0    9   0:10.08  0.6 26328  12m 2640 S httpd
 6168 nobody    16   0    8   0:13.89  1.4 42060  28m 2640 S httpd
 6051 nobody    15   0    4   0:14.23  1.4 42136  28m 2972 S httpd
15504 nobody    15   0    4   0:00.95  0.6 25424  11m 2540 S httpd
 6067 nobody    16   0    4   0:10.64  0.7 28100  14m 2648 S httpd
 6072 nobody    16   0    3   0:00.50  0.6 24700  11m 2532 S httpd
 6073 nobody    17   0    2   0:10.66  0.6 25556  11m 2628 S httpd
 6050 nobody    16   0    2   0:20.59  1.4 41808  27m 2640 S httpd
 6146 nobody    16   0    2   0:08.12  0.7 27912  14m 2628 S httpd
11298 nobody    15   0    2   0:10.68  1.4 42052  28m 2644 S httpd
15493 nobody    15   0    2   0:00.52  0.5 24184  10m 2508 S httpd
 6049 nobody    16   0    1   0:14.49  1.4 42076  28m 2636 S httpd
 6284 nobody    15   0    1   0:19.30  0.7 27808  14m 2636 S httpd
 6043 nobody    16   0    1   0:18.61  1.4 41864  28m 3160 S httpd
 6052 nobody    15   0    1   0:14.67  1.4 41820  27m 2636 S httpd
 8694 nobody    15   0    1   0:10.64  0.6 25136  11m 2636 S httpd
 9682 nobody    15   0    1   0:07.90  0.7 27116  13m 2660 S httpd
15484 nobody    15   0    1   0:00.31  0.5 23212 9872 2468 S httpd
  464 root      15   0    0   1:27.99  0.0     0    0    0 S kjournald
 6019 nobody    15   0    0   0:11.58  1.4 42056  28m 2640 S httpd
 6169 nobody    15   0    0   0:22.57  1.4 41432  27m 2636 S httpd
    1 root      16   0    0   0:02.33  0.0  2236  468  436 S init
    2 root      RT   0    0   0:00.18  0.0     0    0    0 S migration/0
    3 root      34  19    0   0:00.02  0.0     0    0    0 R ksoftirqd/0
    4 root      RT   0    0   0:00.17  0.0     0    0    0 S migration/1
    5 root      34  19    0   0:00.00  0.0     0    0    0 S ksoftirqd/1
    6 root       5 -10    0   0:00.06  0.0     0    0    0 S events/0
    7 root       5 -10    0   0:00.06  0.0     0    0    0 S events/1
    8 root       7 -10    0   0:00.00  0.0     0    0    0 S khelper
    9 root      15 -10    0   0:00.00  0.0     0    0    0 S kacpid
   32 root       5 -10    0   0:00.00  0.0     0    0    0 S kblockd/0
   33 root       5 -10    0   0:00.00  0.0     0    0    0 S kblockd/1
   34 root      15   0    0   0:00.00  0.0     0    0    0 S khubd
   53 root      16   0    0  18:33.36  0.0     0    0    0 S kswapd0
   54 root      11 -10    0   0:00.00  0.0     0    0    0 S aio/0
   55 root       5 -10    0   0:00.00  0.0     0    0    0 S aio/1
  199 root      25   0    0   0:00.00  0.0     0    0    0 S kseriod
  429 root       8 -10    0   0:00.00  0.0     0    0    0 S ata/0
  430 root       8 -10    0   0:00.00  0.0     0    0    0 S ata/1

my.cnf çıktısı

Kod:

[mysqld]
set-variable = max_connections=500
safe-show-database

ne gibi bir optimizasyon önerirsiniz acaba
şimdiden teşekkürler

iyinet · 3 Ocak 2008

Holokost' Alıntı:

merhaba

core2duo e4400
2 gb ram

bir sunucum var

top çıktısı

Kod:

top - 14:17:51 up 4 days, 18:50,  1 user,  load average: 5.41, 7.14, 7.20
Tasks: 199 total,   4 running, 185 sleeping,   9 stopped,   1 zombie
Cpu(s): 88.9% us, 10.9% sy,  0.0% ni,  0.0% id,  0.0% wa,  0.2% hi,  0.0% si
Mem:   2065180k total,  1874072k used,   191108k free,    31064k buffers
Swap:  2096472k total,   176404k used,  1920068k free,   576384k cached

  PID USER      PR  NI %CPU    TIME+  %MEM  VIRT  RES  SHR S COMMAND
30370 mysql     15   0  140   4363:10  2.0  205m  39m 2400 S mysqld
12160 nobody    16   0   10   0:32.58  0.6 25996  12m 2624 S httpd
 6027 nobody    16   0    9   0:10.08  0.6 26328  12m 2640 S httpd
 6168 nobody    16   0    8   0:13.89  1.4 42060  28m 2640 S httpd
 6051 nobody    15   0    4   0:14.23  1.4 42136  28m 2972 S httpd
15504 nobody    15   0    4   0:00.95  0.6 25424  11m 2540 S httpd
 6067 nobody    16   0    4   0:10.64  0.7 28100  14m 2648 S httpd
 6072 nobody    16   0    3   0:00.50  0.6 24700  11m 2532 S httpd
 6073 nobody    17   0    2   0:10.66  0.6 25556  11m 2628 S httpd
 6050 nobody    16   0    2   0:20.59  1.4 41808  27m 2640 S httpd
 6146 nobody    16   0    2   0:08.12  0.7 27912  14m 2628 S httpd
11298 nobody    15   0    2   0:10.68  1.4 42052  28m 2644 S httpd
15493 nobody    15   0    2   0:00.52  0.5 24184  10m 2508 S httpd
 6049 nobody    16   0    1   0:14.49  1.4 42076  28m 2636 S httpd
 6284 nobody    15   0    1   0:19.30  0.7 27808  14m 2636 S httpd
 6043 nobody    16   0    1   0:18.61  1.4 41864  28m 3160 S httpd
 6052 nobody    15   0    1   0:14.67  1.4 41820  27m 2636 S httpd
 8694 nobody    15   0    1   0:10.64  0.6 25136  11m 2636 S httpd
 9682 nobody    15   0    1   0:07.90  0.7 27116  13m 2660 S httpd
15484 nobody    15   0    1   0:00.31  0.5 23212 9872 2468 S httpd
  464 root      15   0    0   1:27.99  0.0     0    0    0 S kjournald
 6019 nobody    15   0    0   0:11.58  1.4 42056  28m 2640 S httpd
 6169 nobody    15   0    0   0:22.57  1.4 41432  27m 2636 S httpd
    1 root      16   0    0   0:02.33  0.0  2236  468  436 S init
    2 root      RT   0    0   0:00.18  0.0     0    0    0 S migration/0
    3 root      34  19    0   0:00.02  0.0     0    0    0 R ksoftirqd/0
    4 root      RT   0    0   0:00.17  0.0     0    0    0 S migration/1
    5 root      34  19    0   0:00.00  0.0     0    0    0 S ksoftirqd/1
    6 root       5 -10    0   0:00.06  0.0     0    0    0 S events/0
    7 root       5 -10    0   0:00.06  0.0     0    0    0 S events/1
    8 root       7 -10    0   0:00.00  0.0     0    0    0 S khelper
    9 root      15 -10    0   0:00.00  0.0     0    0    0 S kacpid
   32 root       5 -10    0   0:00.00  0.0     0    0    0 S kblockd/0
   33 root       5 -10    0   0:00.00  0.0     0    0    0 S kblockd/1
   34 root      15   0    0   0:00.00  0.0     0    0    0 S khubd
   53 root      16   0    0  18:33.36  0.0     0    0    0 S kswapd0
   54 root      11 -10    0   0:00.00  0.0     0    0    0 S aio/0
   55 root       5 -10    0   0:00.00  0.0     0    0    0 S aio/1
  199 root      25   0    0   0:00.00  0.0     0    0    0 S kseriod
  429 root       8 -10    0   0:00.00  0.0     0    0    0 S ata/0
  430 root       8 -10    0   0:00.00  0.0     0    0    0 S ata/1

my.cnf çıktısı

Kod:

[mysqld]
set-variable = max_connections=500
safe-show-database

ne gibi bir optimizasyon önerirsiniz acaba
şimdiden teşekkürler

Bu şekilde bir başlayalım, 2 gün sonra buradaki http://forum.iyinet.com/mysql-veritabani-sql/77047-tuning-primer-sh-mysql-tuning-scripti.html scripti çalıştırın ve o başlık altına ek optimizasyon için çıktıyı ekleyin.

[mysqld]
safe-show-database
skip-locking
skip-innodb
skip-name-resolve
key_buffer = 64M
max_connections = 300
table_cache = 5000
thread_cache_size = 384
low_priority_updates = 1
query_cache_size = 16M
query_cache_type = 1
query_cache_limit = 4M

Holokost · 3 Ocak 2008

teşekkürler iyinet

ceyhun81 · 16 Ocak 2008

Merhaba ;

Yeni bir üyeyim. Sıkıntıları yaşamaya başladıktan sonra, böyle bir forumu bulduğuma sevindim. Tüm xcache, mysql optimizasyonu ve bu konu içindeki sayfaları okudum

Yeniden doğmuş gibi oldum..

Yardımınızı isteyeceğim konu;

Sunucularımız türkiyede, mysql yükü çok fazla, 100 farklı site ortalama 20 mb mysqle sahip, mod_gzip açarak kullandığımız 10 Mbit hattı 4Mbitlere düşürdük fakat bu, acayip bir server load yaptı.
Öğlen vakitlerinde load average: 61.23, 55.47, 41.45 değerlerini bile görür olduk.. Gerçekten çok sıkıntılıyım yardımlarınızı bekliyorum..

Önerilerinizi bekliyorum..

Sunucu Bilgisi :
Processor #1 Name: Intel(R) Core(TM)2 CPU 6320 @ 1.86GHz
Processor #2 Name: Intel(R) Core(TM)2 CPU 6320 @ 1.86GHz
Memory: 2065564k/2088896k available (1876k kernel code, 22380k reserved, 759k data, 184k init, 1171392k highmem)

Linux Centos 4.5

TOP
__________

HTML:

top - 17:19:16 up 57 days, 16:38,  1 user,  load average: 1.23, 1.47, 1.45
Tasks: 221 total,   1 running, 220 sleeping,   0 stopped,   0 zombie
Cpu(s): 14.9% us,  5.9% sy,  0.0% ni, 75.0% id,  3.9% wa,  0.3% hi,  0.0% si
Mem:   2066800k total,  2003788k used,    63012k free,   103348k buffers
Swap:  4192956k total,      160k used,  4192796k free,   883092k cached

  PID USER      PR  NI %CPU    TIME+  %MEM  VIRT  RES  SHR S COMMAND
31546 nobody    15   0    7   0:00.99  0.8 32108  17m 3972 S httpd
 3644 mysql     15   0    6  28:29.61  9.3  267m 187m 3368 S mysqld
31548 nobody    15   0    6   0:00.20  0.9 34360  18m 3664 S httpd
31345 nobody    16   0    4   0:00.78  0.9 34308  18m 3924 S httpd
 4811 nobody    16   0    4   0:36.68  1.2 39248  25m 5024 S httpd
31556 nobody    16   0    3   0:00.16  0.9 33860  18m 3836 S httpd
 4833 nobody    16   0    2   0:36.24  1.2 39148  24m 4540 S httpd
31539 nobody    15   0    2   0:00.06  0.8 31532  15m 3324 S httpd
29346 nobody    15   0    1   0:07.69  1.0 34476  19m 4036 S httpd
31553 nobody    15   0    1   0:00.05  0.8 31532  15m 3320 S httpd
 4812 nobody    15   0    1   0:42.04  1.1 37632  23m 4732 S httpd
 4818 nobody    15   0    0   0:42.84  1.2 37648  23m 5196 S httpd
 4838 nobody    15   0    0   0:32.71  1.2 37856  23m 4644 S httpd
 4840 nobody    16   0    0   0:36.78  1.1 37784  23m 4560 S httpd
31538 nobody    17   0    0   0:00.31  0.9 34204  18m 3716 S httpd
31543 nobody    15   0    0   0:00.21  0.8 31832  16m 3700 S httpd

my.cnf
________

HTML:

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
skip-locking
query_cache_limit=1M
query_cache_size=128M
query_cache_type=1
max_user_connections=1000
max_connections=1500
interactive_timeout=10
wait_timeout=10
connect_timeout=10
thread_cache_size=256
key_buffer=16M
join_buffer=1M
max_allowed_packet=1024M
table_cache=1024
record_buffer=1M
sort_buffer_size=2M
read_buffer_size=2M
max_connect_errors=10
# Try number of CPU's*2 for thread_concurrency
thread_concurrency=16
myisam_sort_buffer_size=64M
#log-bin
server-id=1

[mysql.server]
user=mysql
basedir=/var/lib

[safe_mysqld]
err-log=/var/log/mysqld.log
pid-file=/var/lib/mysql/mysql.pid
open_files_limit=8192
local-infile=off

ceyhun81 · 16 Ocak 2008

az önce belirttiğim veriler, çok normal bi ana ait bir veri, mod_gzip kapatıldı.

Mod_gzip açtığımızda load average 5,6 hatta berlirttiğim gibi 60 ı gördüm..

Whmden baktığımda şuanda memory used: %50 olarak gözüküyor..

2GB ram daha eklemem faydalı olur mu acaba?

iyinet · 16 Ocak 2008

ceyhun81' Alıntı:
az önce belirttiğim veriler, çok normal bi ana ait bir veri, mod_gzip kapatıldı.

Mod_gzip açtığımızda load average 5,6 hatta berlirttiğim gibi 60 ı gördüm..

Whmden baktığımda şuanda memory used: %50 olarak gözüküyor..

2GB ram daha eklemem faydalı olur mu acaba?

mod_gzip yoğun CPU kullanan bir uygulamadır.
Bellek arttırmanız elbette genel bir fayda sağlar, ancak bu eklenti yüksek load u düşürmez.
Sisteminiz swap da yapmadığına göre, sorun CPU ların yetersizliği gibi görünüyor.

Bu konuyu okuyunuz: http://forum.iyinet.com/mysql-veritabani-sql/77047-tuning-primer-sh-mysql-tuning-scripti.html
Buradaki scritin çıktısını o konu altına atınız, Mysql i elimizden geldiğince optimize edelim.

ceyhun81 · 17 Ocak 2008

teşekkürler iyinet,

o konuyu okumuştum ve tuning-primer.sh ı sunucuya kurdum.. henüz 20 saatfalan oldu verileri sanırım 48 saat sonra koymam mı daha doğru olur?

teşekkürler

iyinet · 18 Ocak 2008

ceyhun81' Alıntı:
teşekkürler iyinet,

o konuyu okumuştum ve tuning-primer.sh ı sunucuya kurdum.. henüz 20 saatfalan oldu verileri sanırım 48 saat sonra koymam mı daha doğru olur?

teşekkürler

Evet 48 saat ama, 1 hafta beklemeniz bence en iyisi.

faantastic · 7 Şubat 2008

Pentium D 3.0 İşlemci 2 gb Ram Plesk Panel, vbulletin forum+zoints seo+thread tags kurulu 14-15k tekil giriş, 148 bin konu, 316 bin mesaj... serverda tek site var ve cpu+ram kullanımı çok fazla, server günlük resetlemek zorunda kalıyor, günlük kasma ve kilitlenme oluyor, site db sayfası veriyor, ssh ile dahi bağlanmak ya çok zor oluyor yada bağlanmıyor. top ve my.cnf aşağıda ki gibidir yardımlarınızı bekliyorum neler yapmalıyım;

top çıktısı:

Kod:

top - 16:42:35 up 1 day, 10 min,  3 users,  load average: 6.41, 4.97, 5.19
Tasks: 129 total,   2 running, 127 sleeping,   0 stopped,   0 zombie
Cpu(s): 80.8%us,  6.9%sy,  0.0%ni, 12.3%id,  0.0%wa,  0.0%hi,  0.0%si,  0.0%st
Mem:   2051724k total,  1293940k used,   757784k free,    19592k buffers
Swap:  4192956k total,   167304k used,  4025652k free,   573304k cached

  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
27760 mysql     15   0  355m  78m 4500 S  101  3.9  62:41.56 mysqld
 9472 apache    15   0  296m  22m 3660 S    8  1.1   0:04.59 httpd
  822 apache    15   0  304m  30m 4880 S    6  1.5   1:31.99 httpd
 9876 apache    15   0  297m  23m 3628 S    6  1.2   0:03.87 httpd
 9930 apache    15   0  296m  23m 4056 S    6  1.2   0:03.80 httpd
10056 apache    15   0  297m  24m 4120 S    6  1.2   0:02.61 httpd
 8490 apache    15   0  302m  28m 3684 S    5  1.4   0:14.24 httpd
 9291 apache    15   0  298m  24m 3660 S    5  1.2   0:07.11 httpd
 9372 apache    15   0  297m  23m 3612 S    5  1.2   0:06.71 httpd
 9399 apache    15   0  299m  25m 4064 S    5  1.3   0:08.83 httpd
10114 apache    15   0  295m  21m 3592 S    5  1.1   0:02.11 httpd
28988 apache    15   0  304m  29m 4924 S    5  1.5   1:28.06 httpd
 7196 apache    15   0  298m  24m 4124 S    5  1.2   0:21.62 httpd
 8700 apache    15   0  299m  25m 3676 S    3  1.3   0:10.77 httpd
 2591 apache    16   0  304m  30m 4820 S    2  1.5   1:00.70 httpd
    1 root      15   0 10304  428  396 S    0  0.0   0:00.76 init
    2 root      RT   0     0    0    0 S    0  0.0   0:00.02 migration/0
    3 root      34  19     0    0    0 S    0  0.0   0:00.02 ksoftirqd/0
    4 root      RT   0     0    0    0 S    0  0.0   0:00.00 watchdog/0
    5 root      RT   0     0    0    0 S    0  0.0   0:00.01 migration/1
    6 root      34  19     0    0    0 S    0  0.0   0:00.03 ksoftirqd/1
    7 root      RT   0     0    0    0 S    0  0.0   0:00.00 watchdog/1
    8 root      10  -5     0    0    0 S    0  0.0   0:00.00 events/0
    9 root      10  -5     0    0    0 S    0  0.0   0:00.00 events/1
   10 root      10  -5     0    0    0 S    0  0.0   0:00.00 khelper
   27 root      10  -5     0    0    0 S    0  0.0   0:00.00 kthread
   32 root      10  -5     0    0    0 S    0  0.0   0:00.11 kblockd/0
   33 root      10  -5     0    0    0 S    0  0.0   0:00.09 kblockd/1
   34 root      14  -5     0    0    0 S    0  0.0   0:00.00 kacpid
  118 root      14  -5     0    0    0 S    0  0.0   0:00.00 cqueue/0
  119 root      14  -5     0    0    0 S    0  0.0   0:00.00 cqueue/1
  122 root      11  -5     0    0    0 S    0  0.0   0:00.00 khubd
  124 root      10  -5     0    0    0 S    0  0.0   0:00.00 kseriod
  196 root      10  -5     0    0    0 S    0  0.0   1:16.52 kswapd0
  197 root      17  -5     0    0    0 S    0  0.0   0:00.00 aio/0
  198 root      17  -5     0    0    0 S    0  0.0   0:00.00 aio/1
  337 root      12  -5     0    0    0 S    0  0.0   0:00.00 kpsmoused

my.cnf

Kod:

[mysqld]
set-variable=local-infile=0
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1

[mysql.server]
user=mysql
basedir=/var/lib

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

tuning ile aldığım rapor gerçi 48 saat olmadı ama bilgi açısından yararı olur sanırım;

Kod:

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

MySQL Version 5.0.22 x86_64

Uptime = 0 days 1 hrs 29 min 25 sec
Avg. qps = 31
Total Questions = 168931
Threads Connected = 1

Warning: Server has not been running for at least 48hrs.
It may not be safe to use 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
Current long_query_time = 10 sec.
You have 122 out of 168943 that take longer than 10 sec. to complete
The slow query log is NOT enabled.
Your long_query_time may be too high, I typically set this under 5 sec.

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

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

MEMORY USAGE
Max Memory Ever Allocated : 160 M
Configured Max Per-thread Buffers : 274 M
Configured Max Global Buffers : 17 M
Configured Max Memory Limit : 292 M
Physical Memory : 1.95 G
Max memory limit seem to be within acceptable norms

KEY BUFFER
Current MyISAM index space = 464 M
Current key_buffer_size = 7 M
Key cache miss rate is 1 : 114
Key buffer fill ratio = 100.00 %
You could increase key_buffer_size
It is safe to raise this up to 1/4 of total system memory;
assuming this is a dedicated database server.

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 record/read_rnd_buffer_size = 256 K
Sort buffer seems to be fine

JOINS
Current join_buffer_size = 132.00 K
You have had 443 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 271 tables
You have 64 open tables.
Current table_cache hit rate is 1%, 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 3171 temp tables, 12% 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 = 128 K
Current table scan ratio = 13584 : 1
You have a high ratio of sequential access requests to SELECTs
You may benefit from raising read_buffer_size and/or improving your use of index                                              es.

TABLE LOCKING
Current Lock Wait ratio = 1 : 225
You may benefit from selective use of InnoDB.
If you have long running SELECT's against MyISAM tables and perform
frequent updates consider setting 'low_priority_updates=1'
If you have a high concurrency of inserts on Dynamic row-length tables
consider setting 'concurrent_insert=2'.

Holokost · 14 Şubat 2008

merhaba iyinet yeni sunucumun mysql optimizasyonunda yardımcı olur musunuz

AMD Athlon 64 X2 5200+
4096 DDR2 Ram
Centos 4.x
cPanel

Top

Kod:

top - 08:05:19 up 1 day, 57 min,  1 user,  load average: 12.57, 8.79, 7.26
Tasks: 222 total,  21 running, 197 sleeping,   0 stopped,   4 zombie
Cpu(s): 58.0%us,  7.4%sy,  0.2%ni, 33.7%id,  0.4%wa,  0.0%hi,  0.2%si,  0.0%st
Mem:   3500440k total,  3293824k used,   206616k free,   195588k buffers
Swap:  2031608k total,       80k used,  2031528k free,  1850356k cached

  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
 2617 mysql     15   0 53732  27m 2732 S   86  0.8 988:22.30 mysqld
18616 nobody    16   0 23820  14m 2168 R   34  0.4   1:45.20 httpd
28699 nobody    16   0 23820  14m 2168 R   11  0.4   1:18.40 httpd
 1609 nobody    16   0 18332 9492 1844 R   10  0.3   0:01.30 httpd
18747 nobody    15   0 23872  15m 2564 S   10  0.4   2:49.81 httpd
21716 nobody    15   0 23400  14m 2164 S    8  0.4   0:26.61 httpd
  339 nobody    15   0 23156  14m 1852 S    6  0.4   0:01.99 httpd
 1189 nobody    16   0 19752  10m 1932 S    6  0.3   0:00.36 httpd
 2141 nobody    16   0 16916 8036 1784 R    6  0.2   0:00.10 httpd
 8360 nobody    15   0 23912  14m 2164 S    6  0.4   1:19.82 httpd
24567 nobody    17   0 24872  15m 1936 R    6  0.5   0:08.04 httpd
25081 nobody    25   0  6548 2612 1108 R    6  0.1  14:11.80 perl
 2295 root      16   0 19640 9592 1576 S    2  0.3   0:00.01 cpsrvd-ssl
 2298 root      18   0 15656 6328 3220 R    2  0.2   0:00.01 xml-api
19791 root      15   0 19628 8936 1004 S    2  0.3   0:00.04 cpsrvd-ssl
    1 root      15   0  2044  640  552 S    0  0.0   0:01.06 init
    2 root      RT   0     0    0    0 S    0  0.0   0:00.15 migration/0
    3 root      34  19     0    0    0 R    0  0.0   0:01.06 ksoftirqd/0
    4 root      RT   0     0    0    0 S    0  0.0   0:00.00 watchdog/0
    5 root      RT   0     0    0    0 S    0  0.0   0:00.17 migration/1
    6 root      34  19     0    0    0 R    0  0.0   0:00.00 ksoftirqd/1
    7 root      RT   0     0    0    0 S    0  0.0   0:00.00 watchdog/1
    8 root      10  -5     0    0    0 S    0  0.0   0:00.01 events/0
    9 root      10  -5     0    0    0 S    0  0.0   0:00.00 events/1
   10 root      10  -5     0    0    0 S    0  0.0   0:00.00 khelper
   11 root      10  -5     0    0    0 S    0  0.0   0:00.00 kthread
   15 root      10  -5     0    0    0 S    0  0.0   0:00.28 kblockd/0
   16 root      10  -5     0    0    0 S    0  0.0   0:00.05 kblockd/1
   17 root      15  -5     0    0    0 S    0  0.0   0:00.00 kacpid
  122 root      15  -5     0    0    0 S    0  0.0   0:00.00 cqueue/0
  123 root      15  -5     0    0    0 S    0  0.0   0:00.00 cqueue/1
  126 root      10  -5     0    0    0 S    0  0.0   0:00.00 khubd
  128 root      10  -5     0    0    0 S    0  0.0   0:00.00 kseriod
  196 root      19   0     0    0    0 S    0  0.0   0:08.85 pdflush
  197 root      10  -5     0    0    0 S    0  0.0   0:01.47 kswapd0
  198 root      17  -5     0    0    0 S    0  0.0   0:00.00 aio/0
  199 root      17  -5     0    0    0 S    0  0.0   0:00.00 aio/1

my.cnf

Kod:

[mysqld]
safe-show-database
skip-locking
skip-innodb
skip-name-resolve

datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1

[mysql.server]
user=mysql
basedir=/var/lib

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

Teşekkürler

Raiden · 15 Şubat 2008

benim ki de bu şekilde: default ayarlı hali

sunucu: p4 3 GHZ 1 GB ram

[mysqld]
set-variable=local-infile=0
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
old_passwords=1
max_allowed_packet=64M
query_cache_size=256M
query_cache_type=1
max_connections=300

[mysql.server]
user=mysql
basedir=/var/lib

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

bu şekilde gerçi cpu %0.3 gibi kullanıyor bu halde bişey yapmaya gerek var mı ? yoksa şuan böyle kullanmaya devam edeyimmi adminim?

bir de php hızlandırıcı kurulması etki eder mi hız açısından ?

Lord21 · 20 Şubat 2008

my.cnf:

The MySQL server
[mysqld]
default-character-set=latin5
port = 3306
socket = /var/lib/mysql/mysql.sock
skip-locking
key_buffer = 16M
max_allowed_packet = 1M
table_cache = 64
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M

#innodb_data_home_dir = /var/lib/mysql/
#innodb_data_file_path = ibdata1:10M:autoextend
#innodb_log_group_home_dir = /var/lib/mysql/
#innodb_log_arch_dir = /var/lib/mysql/
# You can set .._buffer_pool_size up to 50 - 80 %
# of RAM but beware of setting memory usage too high
#innodb_buffer_pool_size = 16M
#innodb_additional_mem_pool_size = 2M
# Set .._log_file_size to 25 % of buffer pool size
#innodb_log_file_size = 5M
#innodb_log_buffer_size = 8M
#innodb_flush_log_at_trx_commit = 1
#innodb_lock_wait_timeout = 50

[mysqldump]
quick
max_allowed_packet = 16M

[isamchk]
key_buffer = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M

[myisamchk]
key_buffer = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M

Top değerleride bunlar:

top - 09:35:45 up 3 days, 10:32, 1 user, load average: 4.22, 3.02, 2.27
Tasks: 151 total, 4 running, 147 sleeping, 0 stopped, 0 zombie
Cpu(s): 46.4% us, 6.4% sy, 0.0% ni, 20.3% id, 26.6% wa, 0.2% hi, 0.2% si
Mem: 4128176k total, 3736976k used, 391200k free, 117300k buffers
Swap: 2096472k total, 200k used, 2096272k free, 2877656k cached

PID USER PR NI %CPU TIME+ %MEM VIRT RES SHR S COMMAND
13861 apache 16 0 38 0:17.04 0.4 286m 16m 6400 R httpd
15011 mysql 15 0 35 1885:51 1.3 176m 51m 5480 S mysqld
14418 apache 16 0 13 0:06.84 0.5 290m 20m 7736 S httpd
14421 apache 15 0 10 0:00.59 0.5 289m 20m 7992 S httpd
13834 apache 16 0 5 0:00.55 0.6 289m 22m 10m R httpd
12662 apache 16 0 1 0:03.46 0.6 289m 25m 12m S httpd
12656 apache 18 0 1 0:00.91 0.3 284m 11m 4088 S httpd
13754 apache 16 0 1 0:03.52 0.4 285m 15m 7616 S httpd
14292 apache 15 0 1 0:00.26 0.4 285m 14m 6044 S httpd
14492 apache 16 0 1 0:00.04 0.3 285m 13m 5060 S httpd
12886 root 15 0 0 0:00.62 0.0 1960 1048 784 R top
14042 apache 15 0 0 0:00.69 0.4 285m 15m 7424 S httpd
14493 apache 15 0 0 0:00.01 0.2 282m 7444 1564 S httpd
1 root 15 0 0 0:00.64 0.0 1704 552 472 S init
2 root RT 0 0 0:00.02 0.0 0 0 0 S migration/0
3 root 34 19 0 0:00.02 0.0 0 0 0 S ksoftirqd/0
4 root RT 0 0 0:00.00 0.0 0 0 0 S watchdog/0

Server dual 2 core 6600
4GB ram..

2 tane vbulletinli site taşıyor sadece.
Sayfaya girişler oldukça yavaş.
xcache kurulu.

Bir optimizasyon yapabilirmiyiZ?

iyinet · 21 Şubat 2008

ShanGaL' Alıntı:
my.cnf:

The MySQL server
[mysqld]
default-character-set=latin5
port = 3306
socket = /var/lib/mysql/mysql.sock
skip-locking
key_buffer = 16M
max_allowed_packet = 1M
table_cache = 64
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M

#innodb_data_home_dir = /var/lib/mysql/
#innodb_data_file_path = ibdata1:10M:autoextend
#innodb_log_group_home_dir = /var/lib/mysql/
#innodb_log_arch_dir = /var/lib/mysql/
# You can set .._buffer_pool_size up to 50 - 80 %
# of RAM but beware of setting memory usage too high
#innodb_buffer_pool_size = 16M
#innodb_additional_mem_pool_size = 2M
# Set .._log_file_size to 25 % of buffer pool size
#innodb_log_file_size = 5M
#innodb_log_buffer_size = 8M
#innodb_flush_log_at_trx_commit = 1
#innodb_lock_wait_timeout = 50

[mysqldump]
quick
max_allowed_packet = 16M

[isamchk]
key_buffer = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M

[myisamchk]
key_buffer = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M

Top değerleride bunlar:

top - 09:35:45 up 3 days, 10:32, 1 user, load average: 4.22, 3.02, 2.27
Tasks: 151 total, 4 running, 147 sleeping, 0 stopped, 0 zombie
Cpu(s): 46.4% us, 6.4% sy, 0.0% ni, 20.3% id, 26.6% wa, 0.2% hi, 0.2% si
Mem: 4128176k total, 3736976k used, 391200k free, 117300k buffers
Swap: 2096472k total, 200k used, 2096272k free, 2877656k cached

PID USER PR NI %CPU TIME+ %MEM VIRT RES SHR S COMMAND
13861 apache 16 0 38 0:17.04 0.4 286m 16m 6400 R httpd
15011 mysql 15 0 35 1885:51 1.3 176m 51m 5480 S mysqld
14418 apache 16 0 13 0:06.84 0.5 290m 20m 7736 S httpd
14421 apache 15 0 10 0:00.59 0.5 289m 20m 7992 S httpd
13834 apache 16 0 5 0:00.55 0.6 289m 22m 10m R httpd
12662 apache 16 0 1 0:03.46 0.6 289m 25m 12m S httpd
12656 apache 18 0 1 0:00.91 0.3 284m 11m 4088 S httpd
13754 apache 16 0 1 0:03.52 0.4 285m 15m 7616 S httpd
14292 apache 15 0 1 0:00.26 0.4 285m 14m 6044 S httpd
14492 apache 16 0 1 0:00.04 0.3 285m 13m 5060 S httpd
12886 root 15 0 0 0:00.62 0.0 1960 1048 784 R top
14042 apache 15 0 0 0:00.69 0.4 285m 15m 7424 S httpd
14493 apache 15 0 0 0:00.01 0.2 282m 7444 1564 S httpd
1 root 15 0 0 0:00.64 0.0 1704 552 472 S init
2 root RT 0 0 0:00.02 0.0 0 0 0 S migration/0
3 root 34 19 0 0:00.02 0.0 0 0 0 S ksoftirqd/0
4 root RT 0 0 0:00.00 0.0 0 0 0 S watchdog/0

Server dual 2 core 6600
4GB ram..

2 tane vbulletinli site taşıyor sadece.
Sayfaya girişler oldukça yavaş.
xcache kurulu.

Bir optimizasyon yapabilirmiyiZ?

Buradaki scriptin çıktısını da alabilirmiyim.

iyinet · 21 Şubat 2008

Raiden' Alıntı:
benim ki de bu şekilde: default ayarlı hali

sunucu: p4 3 GHZ 1 GB ram

[mysqld]
set-variable=local-infile=0
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
old_passwords=1
max_allowed_packet=64M
query_cache_size=256M
query_cache_type=1
max_connections=300

[mysql.server]
user=mysql
basedir=/var/lib

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

bu şekilde gerçi cpu %0.3 gibi kullanıyor bu halde bişey yapmaya gerek var mı ? yoksa şuan böyle kullanmaya devam edeyimmi adminim?

bir de php hızlandırıcı kurulması etki eder mi hız açısından ?

Relication kullanıyormusunuz.
Ek olarak http://forum.iyinet.com/mysql-veritabani-sql/77047-tuning-primer-sh-mysql-tuning-scripti.html çıktısını da verirmisiniz.

iyinet · 21 Şubat 2008

Holokost' Alıntı:

merhaba iyinet yeni sunucumun mysql optimizasyonunda yardımcı olur musunuz

AMD Athlon 64 X2 5200+
4096 DDR2 Ram
Centos 4.x
cPanel

Top

Kod:

top - 08:05:19 up 1 day, 57 min,  1 user,  load average: 12.57, 8.79, 7.26
Tasks: 222 total,  21 running, 197 sleeping,   0 stopped,   4 zombie
Cpu(s): 58.0%us,  7.4%sy,  0.2%ni, 33.7%id,  0.4%wa,  0.0%hi,  0.2%si,  0.0%st
Mem:   3500440k total,  3293824k used,   206616k free,   195588k buffers
Swap:  2031608k total,       80k used,  2031528k free,  1850356k cached

  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
 2617 mysql     15   0 53732  27m 2732 S   86  0.8 988:22.30 mysqld
18616 nobody    16   0 23820  14m 2168 R   34  0.4   1:45.20 httpd
28699 nobody    16   0 23820  14m 2168 R   11  0.4   1:18.40 httpd
 1609 nobody    16   0 18332 9492 1844 R   10  0.3   0:01.30 httpd
18747 nobody    15   0 23872  15m 2564 S   10  0.4   2:49.81 httpd
21716 nobody    15   0 23400  14m 2164 S    8  0.4   0:26.61 httpd
  339 nobody    15   0 23156  14m 1852 S    6  0.4   0:01.99 httpd
 1189 nobody    16   0 19752  10m 1932 S    6  0.3   0:00.36 httpd
 2141 nobody    16   0 16916 8036 1784 R    6  0.2   0:00.10 httpd
 8360 nobody    15   0 23912  14m 2164 S    6  0.4   1:19.82 httpd
24567 nobody    17   0 24872  15m 1936 R    6  0.5   0:08.04 httpd
25081 nobody    25   0  6548 2612 1108 R    6  0.1  14:11.80 perl
 2295 root      16   0 19640 9592 1576 S    2  0.3   0:00.01 cpsrvd-ssl
 2298 root      18   0 15656 6328 3220 R    2  0.2   0:00.01 xml-api
19791 root      15   0 19628 8936 1004 S    2  0.3   0:00.04 cpsrvd-ssl
    1 root      15   0  2044  640  552 S    0  0.0   0:01.06 init
    2 root      RT   0     0    0    0 S    0  0.0   0:00.15 migration/0
    3 root      34  19     0    0    0 R    0  0.0   0:01.06 ksoftirqd/0
    4 root      RT   0     0    0    0 S    0  0.0   0:00.00 watchdog/0
    5 root      RT   0     0    0    0 S    0  0.0   0:00.17 migration/1
    6 root      34  19     0    0    0 R    0  0.0   0:00.00 ksoftirqd/1
    7 root      RT   0     0    0    0 S    0  0.0   0:00.00 watchdog/1
    8 root      10  -5     0    0    0 S    0  0.0   0:00.01 events/0
    9 root      10  -5     0    0    0 S    0  0.0   0:00.00 events/1
   10 root      10  -5     0    0    0 S    0  0.0   0:00.00 khelper
   11 root      10  -5     0    0    0 S    0  0.0   0:00.00 kthread
   15 root      10  -5     0    0    0 S    0  0.0   0:00.28 kblockd/0
   16 root      10  -5     0    0    0 S    0  0.0   0:00.05 kblockd/1
   17 root      15  -5     0    0    0 S    0  0.0   0:00.00 kacpid
  122 root      15  -5     0    0    0 S    0  0.0   0:00.00 cqueue/0
  123 root      15  -5     0    0    0 S    0  0.0   0:00.00 cqueue/1
  126 root      10  -5     0    0    0 S    0  0.0   0:00.00 khubd
  128 root      10  -5     0    0    0 S    0  0.0   0:00.00 kseriod
  196 root      19   0     0    0    0 S    0  0.0   0:08.85 pdflush
  197 root      10  -5     0    0    0 S    0  0.0   0:01.47 kswapd0
  198 root      17  -5     0    0    0 S    0  0.0   0:00.00 aio/0
  199 root      17  -5     0    0    0 S    0  0.0   0:00.00 aio/1

my.cnf

Kod:

[mysqld]
safe-show-database
skip-locking
skip-innodb
skip-name-resolve

datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1

[mysql.server]
user=mysql
basedir=/var/lib

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

Teşekkürler

http://forum.iyinet.com/mysql-veritabani-sql/77047-tuning-primer-sh-mysql-tuning-scripti.html çıktısını da alabilirmiyim.

Lord21 · 25 Şubat 2008

ShanGaL' Alıntı:
my.cnf:

The MySQL server
[mysqld]
default-character-set=latin5
port = 3306
socket = /var/lib/mysql/mysql.sock
skip-locking
key_buffer = 16M
max_allowed_packet = 1M
table_cache = 64
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M

#innodb_data_home_dir = /var/lib/mysql/
#innodb_data_file_path = ibdata1:10M:autoextend
#innodb_log_group_home_dir = /var/lib/mysql/
#innodb_log_arch_dir = /var/lib/mysql/
# You can set .._buffer_pool_size up to 50 - 80 %
# of RAM but beware of setting memory usage too high
#innodb_buffer_pool_size = 16M
#innodb_additional_mem_pool_size = 2M
# Set .._log_file_size to 25 % of buffer pool size
#innodb_log_file_size = 5M
#innodb_log_buffer_size = 8M
#innodb_flush_log_at_trx_commit = 1
#innodb_lock_wait_timeout = 50

[mysqldump]
quick
max_allowed_packet = 16M

[isamchk]
key_buffer = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M

[myisamchk]
key_buffer = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M

Top değerleride bunlar:

top - 09:35:45 up 3 days, 10:32, 1 user, load average: 4.22, 3.02, 2.27
Tasks: 151 total, 4 running, 147 sleeping, 0 stopped, 0 zombie
Cpu(s): 46.4% us, 6.4% sy, 0.0% ni, 20.3% id, 26.6% wa, 0.2% hi, 0.2% si
Mem: 4128176k total, 3736976k used, 391200k free, 117300k buffers
Swap: 2096472k total, 200k used, 2096272k free, 2877656k cached

PID USER PR NI %CPU TIME+ %MEM VIRT RES SHR S COMMAND
13861 apache 16 0 38 0:17.04 0.4 286m 16m 6400 R httpd
15011 mysql 15 0 35 1885:51 1.3 176m 51m 5480 S mysqld
14418 apache 16 0 13 0:06.84 0.5 290m 20m 7736 S httpd
14421 apache 15 0 10 0:00.59 0.5 289m 20m 7992 S httpd
13834 apache 16 0 5 0:00.55 0.6 289m 22m 10m R httpd
12662 apache 16 0 1 0:03.46 0.6 289m 25m 12m S httpd
12656 apache 18 0 1 0:00.91 0.3 284m 11m 4088 S httpd
13754 apache 16 0 1 0:03.52 0.4 285m 15m 7616 S httpd
14292 apache 15 0 1 0:00.26 0.4 285m 14m 6044 S httpd
14492 apache 16 0 1 0:00.04 0.3 285m 13m 5060 S httpd
12886 root 15 0 0 0:00.62 0.0 1960 1048 784 R top
14042 apache 15 0 0 0:00.69 0.4 285m 15m 7424 S httpd
14493 apache 15 0 0 0:00.01 0.2 282m 7444 1564 S httpd
1 root 15 0 0 0:00.64 0.0 1704 552 472 S init
2 root RT 0 0 0:00.02 0.0 0 0 0 S migration/0
3 root 34 19 0 0:00.02 0.0 0 0 0 S ksoftirqd/0
4 root RT 0 0 0:00.00 0.0 0 0 0 S watchdog/0

Server dual 2 core 6600
4GB ram..

2 tane vbulletinli site taşıyor sadece.
Sayfaya girişler oldukça yavaş.
xcache kurulu.

Bir optimizasyon yapabilirmiyiZ?

Buda istediğiniz çıktı:

[root@server ~]# ./tuning-primer.sh

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

MySQL Version 5.0.54-log i686

Uptime = 7 days 18 hrs 57 min 34 sec
Avg. qps = 57
Total Questions = 38554678
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 293 out of 38554706 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 enabled
The expire_logs_days is not set.
The mysqld will retain the entire binary log until RESET MASTER or PURGE MASTER LOGS commands are run manually
Setting expire_log_days will allow you to remove old binary logs automatically
See http://dev.mysql.com/doc/refman/5.0/en/purge-master-logs.html

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

MAX CONNECTIONS
Current max_connections = 100
Current threads_connected = 3
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 : 186 M
Configured Max Per-thread Buffers : 158 M
Configured Max Global Buffers : 26 M
Configured Max Memory Limit : 184 M
Physical Memory : 3.93 G
Max memory limit seem to be within acceptable norms

KEY BUFFER
Current MyISAM index space = 536 M
Current key_buffer_size = 16 M
Key cache miss rate is 1 : 87
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 = 512 K
Current read_rnd_buffer_size = 508 K
Sort buffer seems to be fine

JOINS
Current join_buffer_size = 132.00 K
You have had 2426 queries where a join could not use an index properly
You have had 8 joins without keys that check for key usage after each row
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 1075 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 1790199 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 = 252 K
Current table scan ratio = 47 : 1
read_buffer_size seems to be fine

TABLE LOCKING
Current Lock Wait ratio = 1 : 491
You may benefit from selective use of InnoDB.
If you have long running SELECT's against MyISAM tables and perform
frequent updates consider setting 'low_priority_updates=1'
If you have a high concurrency of inserts on Dynamic row-length tables
consider setting 'concurrent_insert=2'.

Raiden · 26 Şubat 2008

Raiden' Alıntı:
benim ki de bu şekilde: default ayarlı hali

sunucu: p4 3 GHZ 1 GB ram

[mysqld]
set-variable=local-infile=0
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
old_passwords=1
max_allowed_packet=64M
query_cache_size=256M
query_cache_type=1
max_connections=300

[mysql.server]
user=mysql
basedir=/var/lib

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

bu şekilde gerçi cpu %0.3 gibi kullanıyor bu halde bişey yapmaya gerek var mı ? yoksa şuan böyle kullanmaya devam edeyimmi adminim?

bir de php hızlandırıcı kurulması etki eder mi hız açısından ?

Kod:

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

MySQL Version 4.1.20 i686

Uptime = 3 days 19 hrs 11 min 31 sec
Avg. qps = 19
Total Questions = 6314660
Threads Connected = 1

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/4.1/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 6 out of 6314674 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/4.1/en/point-in-time-recovery.html

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

MAX CONNECTIONS
Current max_connections = 300
Current threads_connected = 1
Historic max_used_connections = 92
The number of used connections is 30% of the configured maximum.
Your max_connections variable seems to be fine.

MEMORY USAGE
Max Memory Ever Allocated : 521 M
Configured Max Per-thread Buffers : 806 M
Configured Max Global Buffers : 273 M
Configured Max Memory Limit : 1 G
Physical Memory : 1007.61 M

Max memory limit exceeds 90% of physical memory

KEY BUFFER
Current MyISAM index space = 105 M
Current key_buffer_size = 7 M
Key cache miss rate is 1 : 67
Key buffer fill ratio = 49.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 = 256 M
Current query_cache_used = 39 M
Current query_cache_limit = 1 M
Current Query cache Memory fill ratio = 15.55 %
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 = 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 4343 queries where a join could not use an index properly
You have had 11 joins without keys that check for key usage after each row
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 = 1510 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 2522 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 14475 temp tables, 26% 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 = 2636 : 1
read_buffer_size seems to be fine

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

bu da benim ki adminim

iyinet · 26 Şubat 2008

Raiden' Alıntı:

Kod:

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

MySQL Version 4.1.20 i686

Uptime = 3 days 19 hrs 11 min 31 sec
Avg. qps = 19
Total Questions = 6314660
Threads Connected = 1

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/4.1/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 6 out of 6314674 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/4.1/en/point-in-time-recovery.html

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

MAX CONNECTIONS
Current max_connections = 300
Current threads_connected = 1
Historic max_used_connections = 92
The number of used connections is 30% of the configured maximum.
Your max_connections variable seems to be fine.

MEMORY USAGE
Max Memory Ever Allocated : 521 M
Configured Max Per-thread Buffers : 806 M
Configured Max Global Buffers : 273 M
Configured Max Memory Limit : 1 G
Physical Memory : 1007.61 M

Max memory limit exceeds 90% of physical memory

KEY BUFFER
Current MyISAM index space = 105 M
Current key_buffer_size = 7 M
Key cache miss rate is 1 : 67
Key buffer fill ratio = 49.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 = 256 M
Current query_cache_used = 39 M
Current query_cache_limit = 1 M
Current Query cache Memory fill ratio = 15.55 %
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 = 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 4343 queries where a join could not use an index properly
You have had 11 joins without keys that check for key usage after each row
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 = 1510 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 2522 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 14475 temp tables, 26% 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 = 2636 : 1
read_buffer_size seems to be fine

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

bu da benim ki adminim

[mysqld] altini asagidaki sekilde degistir, diger bolumler ayni kalsin.
innodb kullaniyorsan, ek ayarlar onerebilirim.

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
old_passwords=1
max_allowed_packet=64M
query_cache_size=50M
query_cache_type=1
query_cache_limit = 4M
max_connections=300
skip-locking
skip-name-resolve
key_buffer = 32M
table_cache = 5000
thread_cache_size = 128
low_priority_updates = 1

Raiden · 26 Şubat 2008

smf ve hazır portallar mevcut onlarda sanırım mutlaka vardır innodb seçeneği ona göre yazabilirseniz sevinirim. teşekkür ederim

iyinet · 26 Şubat 2008

ShanGaL' Alıntı:
Buda istediğiniz çıktı:

[root@server ~]# ./tuning-primer.sh

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

MySQL Version 5.0.54-log i686

Uptime = 7 days 18 hrs 57 min 34 sec
Avg. qps = 57
Total Questions = 38554678
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 293 out of 38554706 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 enabled
The expire_logs_days is not set.
The mysqld will retain the entire binary log until RESET MASTER or PURGE MASTER LOGS commands are run manually
Setting expire_log_days will allow you to remove old binary logs automatically
See http://dev.mysql.com/doc/refman/5.0/en/purge-master-logs.html

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

MAX CONNECTIONS
Current max_connections = 100
Current threads_connected = 3
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 : 186 M
Configured Max Per-thread Buffers : 158 M
Configured Max Global Buffers : 26 M
Configured Max Memory Limit : 184 M
Physical Memory : 3.93 G
Max memory limit seem to be within acceptable norms

KEY BUFFER
Current MyISAM index space = 536 M
Current key_buffer_size = 16 M
Key cache miss rate is 1 : 87
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 = 512 K
Current read_rnd_buffer_size = 508 K
Sort buffer seems to be fine

JOINS
Current join_buffer_size = 132.00 K
You have had 2426 queries where a join could not use an index properly
You have had 8 joins without keys that check for key usage after each row
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 1075 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 1790199 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 = 252 K
Current table scan ratio = 47 : 1
read_buffer_size seems to be fine

TABLE LOCKING
Current Lock Wait ratio = 1 : 491
You may benefit from selective use of InnoDB.
If you have long running SELECT's against MyISAM tables and perform
frequent updates consider setting 'low_priority_updates=1'
If you have a high concurrency of inserts on Dynamic row-length tables
consider setting 'concurrent_insert=2'.

[mysqld] altini asagidaki sekilde degistir, diger bolumler ayni kalsin.

[mysqld]
default-character-set=latin5
port = 3306
socket = /var/lib/mysql/mysql.sock
skip-locking
skip-name-resolve
query_cache_size=50M
query_cache_type=1
query_cache_limit = 4M
key_buffer = 32M
max_allowed_packet = 64M
max_connections=300
table_cache = 5000
thread_cache_size = 128
low_priority_updates = 1

MySQL CPU Kullanımı my.cnf optimizasyonu

0

Root

0

0

0

Root

0

Root

0

0

0

0

Root

Root

Root

0

0

Root

0

Root