Webmaster Forumu

Geri git   Webmaster Forumu > Webmaster Kaynak Eğitim Tanıtım Hizmet > MySQL Veritabanı SQL

MySQL Veritabanı SQL SQL MySQL Access MSSQL Oracle Veritabani Database

Cevapla
 
LinkBack (9) Seçenekler Stil
  #41 (permalink)   iTrader 
Alt 2007-12-15, 21:40
iyinet - ait Kullanıcı Resmi (Avatar)
Paylaşım için teşekkürler
 
Üyelik tarihi: Sep 2002
Mesajlar: 6.217
Blog Entries: 2
Teşekkür: 2
Thanked 31 Times in 10 Posts
Tecrübe Puanı: 3
iTrader: %100/6
iyinet is just really niceiyinet is just really niceiyinet is just really niceiyinet is just really nice
Standart

Alıntı:
ENVER´isimli üyeden Alıntı Mesajı göster
Merhaba,

Makina bilgisi



çıktıda fazla uyarı var anlamadım memcached ve eaclator kurulu.
Buyrun:

thread_cache_size = 384
key_buffer = 512M
table_cache = 5000
low_priority_updates = 1
__________________
Forum Reklam Site Reklam
Alıntı ile Cevapla

  #42 (permalink)   iTrader 
Alt 2007-12-15, 22:05
iyinet - ait Kullanıcı Resmi (Avatar)
Paylaşım için teşekkürler
 
Üyelik tarihi: Sep 2002
Mesajlar: 6.217
Blog Entries: 2
Teşekkür: 2
Thanked 31 Times in 10 Posts
Tecrübe Puanı: 3
iTrader: %100/6
iyinet is just really niceiyinet is just really niceiyinet is just really niceiyinet is just really nice
Standart

Alıntı:
WoLeRiNe´isimli üyeden Alıntı Mesajı göster
Dual Xeon 2.4
4 GB ECC Ram



Şimdiden teşekkür ederim.
buyrun:

thread_cache_size = 384
max_connections = 500
query_cache_size = 64M
query_cache_limit = 4M
table_cache = 5000
low_priority_updates = 1
__________________
Forum Reklam Site Reklam
Alıntı ile Cevapla

  #43 (permalink)   iTrader 
Alt 2007-12-17, 22:47
Member
 
Üyelik tarihi: Sep 2006
Mesajlar: 345
Blog Entries: 1
Teşekkür: 2
Thanked 0 Times in 0 Posts
Tecrübe Puanı: 2
iTrader: %100/3
ENVER is an unknown quantity at this point
Standart

Alıntı:
iyinet´isimli üyeden Alıntı Mesajı göster
Buyrun:

thread_cache_size = 384
key_buffer = 512M
table_cache = 5000
low_priority_updates = 1
İyinet teşekkürler son değerleri girdikten sonra yine çalıştırdım. Çeşitli uyarılar mevcut yardımcı olursan sevinirim.

Alıntı:
[root@sunucu ~]# ./tuning-primer.sh

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

MySQL Version 4.1.22-standard i686

Uptime = 2 days 0 hrs 46 min 40 sec
Avg. qps = 95
Total Questions = 16692415
Threads Connected = 5

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/...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 50 out of 16692429 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 = 384
Current threads_cached = 157
Current threads_per_sec = 0
Historic threads_per_sec = 0
Your thread_cache_size is fine

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

MEMORY USAGE
Max Memory Ever Allocated : 1 G
Configured Max Per-thread Buffers : 4 G
Configured Max Global Buffers : 266 M
Configured Max Memory Limit : 4 G
Physical Memory : 3.95 G

Max memory limit exceeds 90% of physical memory


KEY BUFFER
Current MyISAM index space = 573 M
Current key_buffer_size = 128 M
Key cache miss rate is 1 : 42162
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 enabled
Current query_cache_size = 128 M
Current query_cache_used = 67 M
Current query_cache_limit = 8 M
Current Query cache Memory fill ratio = 52.50 %
Current query_cache_min_res_unit = 4 K
Query Cache is 24 % fragmented
Run "FLUSH QUERY CACHE" periodically to defragment the query cache memory
If you have many small queries lower 'query_cache_min_res_unit' to reduce fragmentation.

MySQL won't cache query results that are larger than query_cache_limit in size

SORT OPERATIONS
Current sort_buffer_size = 4 M
Current record/read_rnd_buffer_size = 764 K
Sort buffer seems to be fine

JOINS
Current join_buffer_size = 2.00 M
You have had 1310 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 = 10510 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 = 5000 tables
You have a total of 1044 tables
You have 1315 open tables.
The table_cache value seems to be fine

TEMP TABLES
Current max_heap_table_size = 127 M
Current tmp_table_size = 128 M
Of 530867 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 = 1 M
Current table scan ratio = 760 : 1
read_buffer_size seems to be fine

TABLE LOCKING
Current Lock Wait ratio = 1 : 501
You may benefit from selective use of InnoDB.
Alıntı ile Cevapla

  #44 (permalink)   iTrader 
Alt 2007-12-17, 23:03
iyinet - ait Kullanıcı Resmi (Avatar)
Paylaşım için teşekkürler
 
Üyelik tarihi: Sep 2002
Mesajlar: 6.217
Blog Entries: 2
Teşekkür: 2
Thanked 31 Times in 10 Posts
Tecrübe Puanı: 3
iTrader: %100/6
iyinet is just really niceiyinet is just really niceiyinet is just really niceiyinet is just really nice
Standart

Alıntı:
ENVER´isimli üyeden Alıntı Mesajı göster
İyinet teşekkürler son değerleri girdikten sonra yine çalıştırdım. Çeşitli uyarılar mevcut yardımcı olursan sevinirim.
Herşey güzel ama key_buffer = 512M bunu atlamışşın, halen 128M görünüyor.
__________________
Forum Reklam Site Reklam
Alıntı ile Cevapla

  #45 (permalink)   iTrader 
Alt 2007-12-17, 23:20
Member
 
Üyelik tarihi: Sep 2006
Mesajlar: 345
Blog Entries: 1
Teşekkür: 2
Thanked 0 Times in 0 Posts
Tecrübe Puanı: 2
iTrader: %100/3
ENVER is an unknown quantity at this point
Standart

Alıntı:
iyinet´isimli üyeden Alıntı Mesajı göster
Herşey güzel ama key_buffer = 512M bunu atlamışşın, halen 128M görünüyor.
2 tane key_buffer değeri varmış acaba bi aksilik mi var my.cnf içeriğine bakmanız mümkün mü acaba

Kod:
[mysqld]
safe-show-database
#old_passwords
back_log = 75
skip-innodb
max_connections = 500
key_buffer = 512M
myisam_sort_buffer_size = 64M
join_buffer_size = 2M
read_buffer_size = 2M
sort_buffer_size = 4M
table_cache = 5000
thread_cache_size = 384
wait_timeout = 30
connect_timeout = 10
tmp_table_size = 128M
max_heap_table_size = 128M
max_allowed_packet = 64M
net_buffer_length = 16384
max_connect_errors = 10
thread_concurrency = 8
read_rnd_buffer_size = 786432
bulk_insert_buffer_size = 8M
query_cache_limit = 8M
query_cache_size = 128M
query_cache_type = 1
query_prealloc_size = 262144
query_alloc_block_size = 65536
transaction_alloc_block_size = 8192
transaction_prealloc_size = 4096
default-storage-engine = MyISAM
low_priority_updates = 1

[mysqld_safe]
nice = -10
open_files_limit = 8192

[mysqldump]
quick
max_allowed_packet = 16M

[myisamchk]
key_buffer = 512M
sort_buffer = 64M
read_buffer = 16M
write_buffer = 16M

[mysqlhotcopy]
interactive-timeout
Alıntı ile Cevapla

  #46 (permalink)   iTrader 
Alt 2007-12-17, 23:45
iyinet - ait Kullanıcı Resmi (Avatar)
Paylaşım için teşekkürler
 
Üyelik tarihi: Sep 2002
Mesajlar: 6.217
Blog Entries: 2
Teşekkür: 2
Thanked 31 Times in 10 Posts
Tecrübe Puanı: 3
iTrader: %100/6
iyinet is just really niceiyinet is just really niceiyinet is just really niceiyinet is just really nice
Standart

Alıntı:
ENVER´isimli üyeden Alıntı Mesajı göster
2 tane key_buffer değeri varmış acaba bi aksilik mi var my.cnf içeriğine bakmanız mümkün mü acaba

Kod:
[mysqld]
safe-show-database
#old_passwords
back_log = 75
skip-innodb
max_connections = 500
key_buffer = 512M
myisam_sort_buffer_size = 64M
join_buffer_size = 2M
read_buffer_size = 2M
sort_buffer_size = 4M
table_cache = 5000
thread_cache_size = 384
wait_timeout = 30
connect_timeout = 10
tmp_table_size = 128M
max_heap_table_size = 128M
max_allowed_packet = 64M
net_buffer_length = 16384
max_connect_errors = 10
thread_concurrency = 8
read_rnd_buffer_size = 786432
bulk_insert_buffer_size = 8M
query_cache_limit = 8M
query_cache_size = 128M
query_cache_type = 1
query_prealloc_size = 262144
query_alloc_block_size = 65536
transaction_alloc_block_size = 8192
transaction_prealloc_size = 4096
default-storage-engine = MyISAM
low_priority_updates = 1

[mysqld_safe]
nice = -10
open_files_limit = 8192

[mysqldump]
quick
max_allowed_packet = 16M

[myisamchk]
key_buffer = 512M
sort_buffer = 64M
read_buffer = 16M
write_buffer = 16M

[mysqlhotcopy]
interactive-timeout
Benim verdiğim tüm değerler sadece [mysqld] bölümü içindir.
[mysqld] bölümü altında 1 tane key_buffer var o da 512M, 128M nereden geliyor acaba?
__________________
Forum Reklam Site Reklam
Alıntı ile Cevapla

  #47 (permalink)   iTrader 
Alt 2007-12-17, 23:48
Member
 
Üyelik tarihi: Sep 2006
Mesajlar: 345
Blog Entries: 1
Teşekkür: 2
Thanked 0 Times in 0 Posts
Tecrübe Puanı: 2
iTrader: %100/3
ENVER is an unknown quantity at this point
Standart

ilk veriyi girdiğimde alttaki kısmı düzeltmişim
Alıntı:
[myisamchk]
key_buffer = 512M
sort_buffer = 64M
read_buffer = 16M
write_buffer = 16M
mysqld kısmını yeni 512 yaptım
Alıntı ile Cevapla

  #48 (permalink)   iTrader 
Alt 2007-12-18, 00:08
iyinet - ait Kullanıcı Resmi (Avatar)
Paylaşım için teşekkürler
 
Üyelik tarihi: Sep 2002
Mesajlar: 6.217
Blog Entries: 2
Teşekkür: 2
Thanked 31 Times in 10 Posts
Tecrübe Puanı: 3
iTrader: %100/6
iyinet is just really niceiyinet is just really niceiyinet is just really niceiyinet is just really nice
Standart

Alıntı:
ENVER´isimli üyeden Alıntı Mesajı göster
ilk veriyi girdiğimde alttaki kısmı düzeltmişim


mysqld kısmını yeni 512 yaptım
Bu durumda 2 gün sonra tekrar bakarız, şimdilik key_buffer haricinde herşey iyi görünüyor.
__________________
Forum Reklam Site Reklam
Alıntı ile Cevapla

  #49 (permalink)   iTrader 
Alt 2007-12-20, 17:57
WoLeRiNe - ait Kullanıcı Resmi (Avatar)
Senior Member
 
Üyelik tarihi: Oct 2004
Nerden: IstanbuL
Mesajlar: 701
Teşekkür: 0
Thanked 0 Times in 0 Posts
Tecrübe Puanı: 3
iTrader: %0/0
WoLeRiNe is on a distinguished road
Standart

Alıntı:
-- MYSQL PERFORMANCE TUNING PRIMER --
- By: Matthew Montgomery -

MySQL Version 5.0.45-community-log i686

Uptime = 2 days 5 hrs 8 min 39 sec
Avg. qps = 111
Total Questions = 21319801
Threads Connected = 5

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/...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 = 3 sec.
You have 555 out of 21319859 that take longer than 3 sec. to complete
The slow query log is enabled.
Your long_query_time seems to be fine

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

MAX CONNECTIONS
Current max_connections = 500
Current threads_connected = 1
Historic max_used_connections = 44
The number of used connections is 8% of the configured maximum.
You are using less than 10% of your configured max_connections.
Lowering max_connections could help to avoid an over-allocation of memory
See "MEMORY USAGE" section to make sure you are not over-allocating

MEMORY USAGE
Max Memory Ever Allocated : 385 M
Configured Max Per-thread Buffers : 2 G
Configured Max Global Buffers : 202 M
Configured Max Memory Limit : 2 G
Physical Memory : 3.90 G
Max memory limit seem to be within acceptable norms

KEY BUFFER
Current MyISAM index space = 846 M
Current key_buffer_size = 128 M
Key cache miss rate is 1 : 4071
Key buffer fill ratio = 99.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 enabled
Current query_cache_size = 64 M
Current query_cache_used = 51 M
Current query_cache_limit = 4 M
Current Query cache Memory fill ratio = 81.08 %
Current query_cache_min_res_unit = 4 K
Query Cache is 11 % fragmented
Run "FLUSH QUERY CACHE" periodically to defragment the query cache memory
If you have many small queries lower 'query_cache_min_res_unit' to reduce fragmentation.
However, 230763 queries have been removed from the query cache due to lack of memory
Perhaps you should raise query_cache_size
MySQL won't cache query results that are larger than query_cache_limit in size

SORT OPERATIONS
Current sort_buffer_size = 1 M
Current record/read_rnd_buffer_size = 1020 K
Sort buffer seems to be fine

JOINS
Current join_buffer_size = 1.00 M
You have had 2465 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 = 20510 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 = 10000 tables
You have a total of 4093 tables
You have 8084 open tables.
The table_cache value seems to be fine

TEMP TABLES
Current max_heap_table_size = 16 M
Current tmp_table_size = 32 M
Of 1026975 temp tables, 4% 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 = 1020 K
Current table scan ratio = 4 : 1
read_buffer_size seems to be fine

TABLE LOCKING
Current Lock Wait ratio = 1 : 1915
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'.
Yeni durum bu. Yorumlarsanız sevinirim. Teşekkürler.
Alıntı ile Cevapla

  #50 (permalink)   iTrader 
Alt 2007-12-20, 18:06
iyinet - ait Kullanıcı Resmi (Avatar)
Paylaşım için teşekkürler
 
Üyelik tarihi: Sep 2002
Mesajlar: 6.217
Blog Entries: 2
Teşekkür: 2
Thanked 31 Times in 10 Posts
Tecrübe Puanı: 3
iTrader: %100/6
iyinet is just really niceiyinet is just really niceiyinet is just really niceiyinet is just really nice
Standart

Alıntı:
WoLeRiNe´isimli üyeden Alıntı Mesajı göster
Yeni durum bu. Yorumlarsanız sevinirim. Teşekkürler.
Herşey gayet güzel görünüyor.
max connection ı biraz azaltıp oradan kazandığımız belleği key buffer a verebiliriz.

Bu şekilde değiştirebilirsin.

Alıntı:
max_connections = 200
key_buffer = 256M
__________________
Forum Reklam Site Reklam
Alıntı ile Cevapla

Cevapla

LinkBacks (?)
LinkBack to this Thread: http://forum.iyinet.com/mysql-veritabani-sql/77047-tuning-primer-sh-mysql-tuning-scripti-mysql-performans-raporu.html
Konuyu Başlatan For Type Tarih
iyinet webmaster forumu 2008 seo yarışması - iyinet webmaster forumu 2008 seo yarışması Mysql Performans Testi This thread Pingback 2008-05-27 19:14
Turkwm.com: 5 dakikada hızlı mysql server optimizasyonu This thread Refback 2008-05-15 16:20
Turkwm.com This thread Refback 2008-05-15 13:42
2007 Aralk -Webmaster Kaynak Sitesi This thread Refback 2008-01-15 06:13
tuning-primer.sh mysql tuning scripti - Google - Webmaster Forum / Google Webmaster Fan This thread Refback 2008-01-11 19:23
Mysql Tuning Scripti tuning-primer.sh | Webmaster Blogu This thread Pingback 2007-12-23 21:44
Load sorunu yaşayanlar için genel tartışma konusu - vBulletin SEO Forums This thread Refback 2007-12-12 10:58
Linux & Unix -Webmaster Kaynak Sitesi This thread Refback 2007-12-11 08:55
mysql optimizastonu iin tuning-primer.sh scripti -Webmaster Kaynak Sitesi This thread Pingback 2007-12-06 19:03

Seçenekler
Stil

Yetkileriniz
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is Açık
Smileler Açık
[IMG] Kodları Açık
HTML-KodlarıKapalı
Trackbacks are Açık
Pingbacks are Açık
Refbacks are Açık

Benzer Konular
Konu Konuyu Başlatan Forum Cevaplar Son Mesaj
Tuning Merkezi trebuchet55 Yeni Site Tanıtımları 0 2008-02-24 15:21
Sanal Tuning Alexis Yeni Site Tanıtımları 6 2008-01-18 04:07
sadece tuning, sanal tuning siteleri ile - PR4 Alexis Google PR Link Değişimi 0 2007-05-11 00:50
Tuning Siteleri . Com sverdlow Yeni Site Tanıtımları 2 2006-11-19 11:00
Mysql Asp Performans Sorusu disconnect MySQL Veritabanı SQL 6 2006-10-04 22:16


Bütün Zaman Ayarları WEZ +2 olarak düzenlenmiştir. Şu Anki Saat: 10:22 .


Powered by vBulletin® Version 3.7.2
Copyright ©2000 - 2008, Jelsoft Enterprises Ltd.
Content Relevant URLs by vBSEO 3.1.0

Firefox