Max client hatası Apache ile ilgili Saldırı olduğu anda değerden fazla client bağlanmaya Çalıştığında veriyordur. Max Client servera göre arttırılabilir.
MySQL de bunlarıda ekleyip restartla
query_cache_limit = 6M
long_query_time = 4
httpd.conf ve my.conf optimizasyon
15
●4.755
- 10-10-2008, 11:30:40
Administrator - 08-11-2008, 23:32:52 Üyeliği durdurulduroot@Linux [~]# ./tuning-primer.sh
mysqld is alive
-- MYSQL PERFORMANCE TUNING PRIMER --
- By: Matthew Montgomery -
MySQL Version 5.0.51a-community i686
Uptime = 0 days 8 hrs 51 min 41 sec
Avg. qps = 289
Total Questions = 9236641
Threads Connected = 2
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:
MySQL :: MySQL 5.0 Reference Manual :: 5.1.3 System Variables
Visit MySQL :: MySQL Enterprise Advisors
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 0 out of 9236742 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 MySQL :: MySQL 5.0 Reference Manual :: 6.3 Point-in-Time Recovery
WORKER THREADS
Current thread_cache_size = 0
Current threads_cached = 0
Current threads_per_sec = 11
Historic threads_per_sec = 7
Threads created per/sec are overrunning threads cached
You should raise thread_cache_size
MAX CONNECTIONS
Current max_connections = 2048
Current threads_connected = 1
Historic max_used_connections = 24
The number of used connections is 1% 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 : 82 M
Configured Max Per-thread Buffers : 5 G
Configured Max Global Buffers : 17 M
Configured Max Memory Limit : 5 G
Physical Memory : 7.92 G
Max memory limit seem to be within acceptable norms
KEY BUFFER
Current MyISAM index space = 105 M
Current key_buffer_size = 7 M
Key cache miss rate is 1 : 42
Key buffer fill ratio = 42.00 %
Your key_buffer_size seems to be too high.
Perhaps you can use these resources elsewhere
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 3128 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 = 10240 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 1038 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 110436 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 = 128 K
Current table scan ratio = 776 : 1
read_buffer_size seems to be fine
TABLE LOCKING
Current Lock Wait ratio = 1 : 37
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'.
bu scripti çaliştirdiğimda çikan sonuç budur buna göre bir my.cnf alabilirmiyim bazen load seviyelerim çok artiyor ve top -c çektiğimde mysql kullanimim % 100 % 200 olabiliyor Quad core 2.4 9500 işlemcim ve 8 gb ram var şimdiden teşekkürler - 10-11-2008, 10:16:40 AdministratorŞuanda Kullandıgınız my.cnf unuzuda yazarsanız ona göre my.cnf vereyim.x1y2 adlı üyeden alıntı: mesajı görüntüle
- 11-11-2008, 09:34:47 AdministratorBunlar en ufak saldırıda sunucunuzun daha hızlı down olması için saldırana %100 kolaylık sağlar.x1y2 adlı üyeden alıntı: mesajı görüntüle
max_user_connections : mysql sunucusuna eş zamanlı yapılan bağlantı sayısını belirler.Bence gerekmedikçe 256'yı geçmemeli ama duruma ve sisteme göre değişir
max_connections : Bir istemcinin eş zamanlı olarak suncuya yaptığı azami bağlantı sayısını belirler.Too Many connection hatasına neden olan değişken budur.
Linux veya Solaris sistemler için tavsiye edilen değer 500-1000 arasıdır.
tabi bunlar sisteminize ve mysql ihtiyaçlarına göre değişir
My.cnf dosyanızı yapamıyorum çünkü
"Uptime = 0 days 8 hrs 51 min 41 sec"
8 saat açık kaldıktan sonra bu Rapor alınmış.
Lütfen MySQL 'i Restartlamadan 2gün çalıştırıp daha sonra Raporu alıp burada koyunuz
O zaman vereceğim my.cnf 'un size çok büyük katkısı olacaktır - 16-03-2009, 15:28:58mysql 24 saatten fazla restart edilmeden çalıştı
mysqld is alive -- MYSQL PERFORMANCE TUNING PRIMER -- - By: Matthew Montgomery - MySQL Version 5.0.67-community i686 ./tuning-primer.sh: line 389: bc: command not found ./tuning-primer.sh: line 390: bc: command not found ./tuning-primer.sh: line 391: bc: command not found ./tuning-primer.sh: line 392: bc: command not found ./tuning-primer.sh: line 393: bc: command not found ./tuning-primer.sh: line 394: bc: command not found Uptime = days hrs min sec Avg. qps = 6 Total Questions = 509730 Threads Connected = 82 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 The slow query log is NOT enabled. Current long_query_time = 10 sec. You have 2203 out of 509757 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 = 1 Historic threads_per_sec = 0 Your thread_cache_size is fine MAX CONNECTIONS Current max_connections = 100 Current threads_connected = 80 Historic max_used_connections = 101 The number of used connections is 101% of the configured maximum. You should raise max_connections MEMORY USAGE ./tuning-primer.sh: line 1205: bc: command not found ./tuning-primer.sh: line 1206: bc: command not found ./tuning-primer.sh: line 1230: bc: command not found ./tuning-primer.sh: line 1233: bc: command not found ./tuning-primer.sh: line 1234: bc: command not found ./tuning-primer.sh: line 1236: bc: command not found ./tuning-primer.sh: line 1238: [: -gt: unary operator expected ./tuning-primer.sh: line 351: [: max_memoryHR: integer expression expected ./tuning-primer.sh: line 357: [: max_memoryHR: integer expression expected ./tuning-primer.sh: line 363: [: max_memoryHR: integer expression expected ./tuning-primer.sh: line 370: export: `0=max_memoryHR': not a valid identifier Max Memory Ever Allocated : bytes ./tuning-primer.sh: line 351: [: per_thread_buffersHR: integer expression expected ./tuning-primer.sh: line 357: [: per_thread_buffersHR: integer expression expected ./tuning-primer.sh: line 363: [: per_thread_buffersHR: integer expression expected ./tuning-primer.sh: line 370: export: `0=per_thread_buffersHR': not a valid identifier Configured Max Per-thread Buffers : bytes ./tuning-primer.sh: line 351: [: global_buffersHR: integer expression expected ./tuning-primer.sh: line 357: [: global_buffersHR: integer expression expected ./tuning-primer.sh: line 363: [: global_buffersHR: integer expression expected ./tuning-primer.sh: line 370: export: `0=global_buffersHR': not a valid identifier Configured Max Global Buffers : bytes ./tuning-primer.sh: line 351: [: total_memoryHR: integer expression expected ./tuning-primer.sh: line 357: [: total_memoryHR: integer expression expected ./tuning-primer.sh: line 363: [: total_memoryHR: integer expression expected ./tuning-primer.sh: line 370: export: `0=total_memoryHR': not a valid identifier Configured Max Memory Limit : bytes ./tuning-primer.sh: line 332: bc: command not found Physical Memory : G Max memory limit seem to be within acceptable norms KEY BUFFER ./tuning-primer.sh: line 332: bc: command not found ./tuning-primer.sh: line 647: bc: command not found ./tuning-primer.sh: line 648: bc: command not found ./tuning-primer.sh: line 332: bc: command not found Current MyISAM index space = M ./tuning-primer.sh: line 332: bc: command not found Current key_buffer_size = M Key cache miss rate is 1 : 348 Key buffer fill ratio = % ./tuning-primer.sh: line 685: [: -ge: unary operator expected ./tuning-primer.sh: line 689: [: -le: unary operator expected 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 ./tuning-primer.sh: line 332: bc: command not found Current sort_buffer_size = M ./tuning-primer.sh: line 332: bc: command not found Current read_rnd_buffer_size = K Sort buffer seems to be fine JOINS ./tuning-primer.sh: line 332: bc: command not found Current join_buffer_size = K You have had 64 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 1029 tables You have 149 open tables. Current table_cache hit rate is 0%, while 232% of your table cache is in use You should probably increase your table_cache TEMP TABLES ./tuning-primer.sh: line 332: bc: command not found Current max_heap_table_size = M ./tuning-primer.sh: line 332: bc: command not found Current tmp_table_size = M Of 33774 temp tables, 74% 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 ./tuning-primer.sh: line 332: bc: command not found Current read_buffer_size = K Current table scan ratio = 809 : 1 read_buffer_size seems to be fine TABLE LOCKING Current Lock Wait ratio = 1 : 26 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'.root@host [~]# cat /etc/my.cnf [mysqld] skip-bdb skip-innodb
- 05-05-2009, 00:00:57Merhaba bende yardiminizi alabilirmiyim mysql ayarlarindan hic anlamiyorum .
Sistemimiz,
Parallels Plesk Panel version 9.0.1
Operating system Linux 2.6.18-92.el5PAE
CPU GenuineIntel, Intel(R) Core(TM)2 Quad CPU Q6600 @ 2.40GHz
Average load 1.58; 1.24; 0.97
4 GB RAM
Delicated server uzerinde suan tek sitemiz bulunmaktadir.
[1;34m-- MYSQL PERFORMANCE TUNING PRIMER -- - By: Matthew Montgomery - MySQL Version 5.0.45 i686 Uptime = 14 days 5 hrs 6 min 59 sec Avg. qps = 87 Total Questions = 107259074 Threads Connected = 16 Server has been running for over 48hrs. It should be safe to follow these recommendations [31mTo find out more information on how each of these [31mruntime variables effects performance visit: [1;34mhttp://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html [1;34mVisit http://www.mysql.com/products/enterprise/advisors.html [1;34mfor info about MySQL's Enterprise Monitoring and Advisory Service [1;34mSLOW QUERIES The slow query log is [1;31mNOT enabled. Current long_query_time = 10 sec. You have [1;31m453 out of [1;31m107259108 that take longer than 10 sec. to complete [31mYour long_query_time may be too high, I typically set this under 5 sec. [1;34mBINARY UPDATE LOG The binary update log is [1;31mNOT enabled. [31mYou will not be able to do point in time recovery [33mSee http://dev.mysql.com/doc/refman/5.0/en/point-in-time-recovery.html [1;34mWORKER THREADS Current thread_cache_size = 0 Current threads_cached = 0 Current threads_per_sec = 14 Historic threads_per_sec = 12 [31mThreads created per/sec are overrunning threads cached [31mYou should raise thread_cache_size [1;34mMAX CONNECTIONS Current max_connections = 100 Current threads_connected = 10 Historic max_used_connections = 101 The number of used connections is [31m101% of the configured maximum. [31mYou should raise max_connections [1;34mMEMORY 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 : 3.94 G [32mMax memory limit seem to be within acceptable norms [1;34mKEY BUFFER Current MyISAM index space = 55 M Current key_buffer_size = 7 M Key cache miss rate is 1 : 362 Key buffer free ratio = 0 % [1;31mYou 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. [1;34mQUERY CACHE [31mQuery cache is supported but not enabled [31mPerhaps you should set the query_cache_size [1;34mSORT OPERATIONS Current sort_buffer_size = 2 M Current read_rnd_buffer_size = 256 K [32mSort buffer seems to be fine [1;34mJOINS Current join_buffer_size = 132.00 K You have had 484 queries where a join could not use an index properly [31mYou have had 40 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. [1;31mNote! This script will still suggest raising the join_buffer_size when [1;31mANY joins not using indexes are found. [1;34mOPEN FILES LIMIT Current open_files_limit = 65535 files [33mThe open_files_limit should typically be set to at least 2x-3x [33mthat of table_cache if you have heavy MyISAM usage. [32mYour open_files_limit value seems to be fine [1;34mTABLE CACHE Current table_cache value = 64 tables You have a total of 800 tables You have [1;31m64 open tables. Current table_cache hit rate is [1;31m0%, while [1;31m100% of your table cache is in use [31mYou should probably increase your table_cache [1;34mTEMP TABLES Current max_heap_table_size = 16 M Current tmp_table_size = 32 M Of 1098615 temp tables, 17% were created on disk [33mEffective in-memory tmp_table_size is limited to max_heap_table_size. [32mCreated disk tmp tables ratio seems fine [1;34mTABLE SCANS Current read_buffer_size = 128 K Current table scan ratio = 83 : 1 [32mread_buffer_size seems to be fine [1;34mTABLE LOCKING Current Lock Wait ratio = [31m1 : 64 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'.
My.conf
[mysqld] set-variable=local-infile=0 datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock user=mysql # Default to using old password format for compatibility with mysql 3.x # clients (those using the mysqlclient10 compatibility package). old_passwords=1 [mysqld_safe] log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid
ne tur bir ayar yapmam gerekmektedir yardimci olacak arkadaslara tesekkur ederim.
