+-------+----------+-----------+----------+---------+------+----------------------+------------------------------------------------------------------------------------------------------+ | Id | User | Host | db | Command | Time | State | Info | +-------+----------+-----------+----------+---------+------+----------------------+------------------------------------------------------------------------------------------------------+ | 17408 | sb_video | localhost | sb_video | Query | 4258 | Copying to tmp table | SELECT tag_id, tag, safe_tag, COUNT(*) as numvids FROM pm_tags GROUP BY safe_tag ORDER BY numvids DE | | 26659 | sb_video | localhost | sb_video | Query | 3934 | Copying to tmp table | SELECT tag_id, tag, safe_tag, COUNT(*) as numvids FROM pm_tags GROUP BY safe_tag ORDER BY numvids DE | | 30637 | root | localhost | sb_video | Query | 1578 | Locked | REPAIR TABLE `pm_tags` | | 30951 | sb_video | localhost | sb_video | Query | 1693 | Copying to tmp table | SELECT tag_id, tag, safe_tag, COUNT(*) as numvids FROM pm_tags GROUP BY safe_tag ORDER BY numvids DE | | 31064 | sb_video | localhost | sb_video | Query | 1837 | Copying to tmp table | SELECT tag_id, tag, safe_tag, COUNT(*) as numvids FROM pm_tags GROUP BY safe_tag ORDER BY numvids DE | | 31095 | sb_video | localhost | sb_video | Query | 1691 | Copying to tmp table | SELECT tag_id, tag, safe_tag, COUNT(*) as numvids FROM pm_tags GROUP BY safe_tag ORDER BY numvids DE | | 31192 | sb_video | localhost | sb_video | Query | 1835 | Copying to tmp table | SELECT tag_id, tag, safe_tag, COUNT(*) as numvids FROM pm_tags GROUP BY safe_tag ORDER BY numvids DE | | 31246 | sb_video | localhost | sb_video | Query | 1691 | Copying to tmp table | SELECT tag_id, tag, safe_tag, COUNT(*) as numvids FROM pm_tags GROUP BY safe_tag ORDER BY numvids DE | | 31398 | sb_video | localhost | sb_video | Query | 1692 | Copying to tmp table | SELECT tag_id, tag, safe_tag, COUNT(*) as numvids FROM pm_tags GROUP BY safe_tag ORDER BY numvids DE | | 31768 | sb_video | localhost | sb_video | Query | 1694 | Copying to tmp table | SELECT tag_id, tag, safe_tag, COUNT(*) as numvids FROM pm_tags GROUP BY safe_tag ORDER BY numvids DE | | 31797 | sb_video | localhost | sb_video | Query | 2864 | Copying to tmp table | SELECT tag_id, tag, safe_tag, COUNT(*) as numvids FROM pm_tags GROUP BY safe_tag ORDER BY numvids DE | | 31850 | sb_video | localhost | sb_video | Query | 1690 | Copying to tmp table | SELECT tag_id, tag, safe_tag, COUNT(*) as numvids FROM pm_tags GROUP BY safe_tag ORDER BY numvids DE | | 31979 | sb_video | localhost | sb_video | Query | 1689 | Copying to tmp table | SELECT tag_id, tag, safe_tag, COUNT(*) as numvids FROM pm_tags GROUP BY safe_tag ORDER BY numvids DE | | 32110 | sb_video | localhost | sb_video | Query | 1694 | Copying to tmp table | SELECT tag_id, tag, safe_tag, COUNT(*) as numvids FROM pm_tags GROUP BY safe_tag ORDER BY numvids DE | | 32186 | sb_video | localhost | sb_video | Query | 1692 | Copying to tmp table | SELECT tag_id, tag, safe_tag, COUNT(*) as numvids FROM pm_tags GROUP BY safe_tag ORDER BY numvids DE | | 32250 | sb_video | localhost | sb_video | Query | 1692 | Copying to tmp table | SELECT tag_id, tag, safe_tag, COUNT(*) as numvids FROM pm_tags GROUP BY safe_tag ORDER BY numvids DE | | 32498 | sb_video | localhost | sb_video | Query | 1691 | Copying to tmp table | SELECT tag_id, tag, safe_tag, COUNT(*) as numvids FROM pm_tags GROUP BY safe_tag ORDER BY numvids DE | | 32606 | sb_video | localhost | sb_video | Query | 1838 | Copying to tmp table | SELECT tag_id, tag, safe_tag, COUNT(*) as numvids FROM pm_tags GROUP BY safe_tag ORDER BY numvids DE | | 33078 | sb_video | localhost | sb_video | Query | 1839 | Copying to tmp table | SELECT tag_id, tag, safe_tag, COUNT(*) as numvids FROM pm_tags GROUP BY safe_tag ORDER BY numvids DE | | 34636 | sb_video | localhost | sb_video | Query | 1689 | Copying to tmp table | SELECT tag_id, tag, safe_tag, COUNT(*) as numvids FROM pm_tags GROUP BY safe_tag ORDER BY numvids DE | | 35289 | sb_video | localhost | sb_video | Query | 1842 | Copying to tmp table | SELECT tag_id, tag, safe_tag, COUNT(*) as numvids FROM pm_tags GROUP BY safe_tag ORDER BY numvids DE | | 35395 | sb_video | localhost | sb_video | Query | 1693 | Copying to tmp table | SELECT tag_id, tag, safe_tag, COUNT(*) as numvids FROM pm_tags GROUP BY safe_tag ORDER BY numvids DE | | 35514 | sb_video | localhost | sb_video | Query | 1693 | Copying to tmp table | SELECT tag_id, tag, safe_tag, COUNT(*) as numvids FROM pm_tags GROUP BY safe_tag ORDER BY numvids DE | | 36195 | sb_video | localhost | sb_video | Query | 1694 | Copying to tmp table | SELECT tag_id, tag, safe_tag, COUNT(*) as numvids FROM pm_tags GROUP BY safe_tag ORDER BY numvids DE | | 36488 | sb_video | localhost | sb_video | Query | 1693 | Copying to tmp table | SELECT tag_id, tag, safe_tag, COUNT(*) as numvids FROM pm_tags GROUP BY safe_tag ORDER BY numvids DE | | 36933 | sb_video | localhost | sb_video | Query | 1692 | Copying to tmp table | SELECT tag_id, tag, safe_tag, COUNT(*) as numvids FROM pm_tags GROUP BY safe_tag ORDER BY numvids DE |
اتمنى الاقي حل لسيرفر واللود
آخر تعديل بواسطة ye4ever في 25-03-2011 الساعة 07:13 PM.
السلام عليكم ورحمة الله وبركاته
حبيت اساعدك عزيزي لتقم بها بنفسك بواسطة السكريبت كل ما عليك هو اتباع الرابط التالي واعتذر للاخوة المشرفين عن وضع رابط خارجي http://www.traidnt.net/Portal/Server...cnf-mysql.html
في أمان الله
بس صار السيرفر بطيئ جداً مدري وش سوت الشركه المهم استخدمة سكربت Tuning-Primer.sh
وهذه النتائج
على قرائة سكربت Tuning-Primer.sh
كود PHP:
-- MYSQL PERFORMANCE TUNING PRIMER -- - By: Matthew Montgomery -
MySQL Version 5.0.92-community-log x86_64
Uptime = 2 days 9 hrs 13 min 58 sec Avg. qps = 78 Total Questions = 16218384 Threads Connected = 14
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 enabled. Current long_query_time = 10 sec. You have 41175 out of 16218532 that take longer than 10 sec. to complete Your long_query_time seems to be fine
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 = 4 Current threads_cached = 2 Current threads_per_sec = 3 Historic threads_per_sec = 3 Threads created per/sec are overrunning threads cached You should raise thread_cache_size
MAX CONNECTIONS Current max_connections = 400 Current threads_connected = 16 Historic max_used_connections = 227 The number of used connections is 56% of the configured maximum. Your max_connections variable seems to be fine.
INNODB STATUS Current InnoDB index space = 208 K Current InnoDB data space = 144 K Current InnoDB buffer pool free = 90 % Current innodb_buffer_pool_size = 8 M Depending on how much space your innodb indexes take up it may be safe to increase this value to up to 2 / 3 of total system memory
MEMORY USAGE Max Memory Ever Allocated : 9.89 G Configured Max Per-thread Buffers : 15.77 G Configured Max Global Buffers : 970 M Configured Max Memory Limit : 16.71 G Physical Memory : 23.52 G Max memory limit seem to be within acceptable norms
KEY BUFFER Current MyISAM index space = 748 M Current key_buffer_size = 768 M Key cache miss rate is 1 : 5166 Key buffer free ratio = 68 % Your key_buffer_size seems to be fine
QUERY CACHE Query cache is enabled Current query_cache_size = 192 M Current query_cache_used = 17 M Current query_cache_limit = 64 M Current Query cache Memory fill ratio = 9.23 % 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 = 16 M Current read_rnd_buffer_size = 8 M Sort buffer seems to be fine
JOINS Current join_buffer_size = 16.00 M You have had 9 queries where a join could not use an index properly join_buffer_size >= 4 M This is not advised You should enable "log-queries-not-using-indexes" Then look for non indexed joins in the slow query log.
OPEN FILES LIMIT Current open_files_limit = 12288 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 = 4096 tables You have a total of 862 tables You have 1490 open tables. The table_cache value seems to be fine
TEMP TABLES Current max_heap_table_size = 256 M Current tmp_table_size = 256 M Of 1691235 temp tables, 8% were created on disk Created disk tmp tables ratio seems fine
TABLE SCANS Current read_buffer_size = 128 K Current table scan ratio = 24397 : 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 indexes.
TABLE LOCKING Current Lock Wait ratio = 1 : 12 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.
بس صار السيرفر بطيئ جداً مدري وش سوت الشركه المهم استخدمة سكربت Tuning-Primer.sh
وهذه النتائج
على قرائة سكربت Tuning-Primer.sh
كود PHP:
-- MYSQL PERFORMANCE TUNING PRIMER -- - By: Matthew Montgomery -
MySQL Version 5.0.92-community-log x86_64
Uptime = 2 days 9 hrs 13 min 58 sec Avg. qps = 78 Total Questions = 16218384 Threads Connected = 14
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 enabled. Current long_query_time = 10 sec. You have 41175 out of 16218532 that take longer than 10 sec. to complete Your long_query_time seems to be fine
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 = 4 Current threads_cached = 2 Current threads_per_sec = 3 Historic threads_per_sec = 3 Threads created per/sec are overrunning threads cached You should raise thread_cache_size
MAX CONNECTIONS Current max_connections = 400 Current threads_connected = 16 Historic max_used_connections = 227 The number of used connections is 56% of the configured maximum. Your max_connections variable seems to be fine.
INNODB STATUS Current InnoDB index space = 208 K Current InnoDB data space = 144 K Current InnoDB buffer pool free = 90 % Current innodb_buffer_pool_size = 8 M Depending on how much space your innodb indexes take up it may be safe to increase this value to up to 2 / 3 of total system memory
MEMORY USAGE Max Memory Ever Allocated : 9.89 G Configured Max Per-thread Buffers : 15.77 G Configured Max Global Buffers : 970 M Configured Max Memory Limit : 16.71 G Physical Memory : 23.52 G Max memory limit seem to be within acceptable norms
KEY BUFFER Current MyISAM index space = 748 M Current key_buffer_size = 768 M Key cache miss rate is 1 : 5166 Key buffer free ratio = 68 % Your key_buffer_size seems to be fine
QUERY CACHE Query cache is enabled Current query_cache_size = 192 M Current query_cache_used = 17 M Current query_cache_limit = 64 M Current Query cache Memory fill ratio = 9.23 % 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 = 16 M Current read_rnd_buffer_size = 8 M Sort buffer seems to be fine
JOINS Current join_buffer_size = 16.00 M You have had 9 queries where a join could not use an index properly join_buffer_size >= 4 M This is not advised You should enable "log-queries-not-using-indexes" Then look for non indexed joins in the slow query log.
OPEN FILES LIMIT Current open_files_limit = 12288 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 = 4096 tables You have a total of 862 tables You have 1490 open tables. The table_cache value seems to be fine
TEMP TABLES Current max_heap_table_size = 256 M Current tmp_table_size = 256 M Of 1691235 temp tables, 8% were created on disk Created disk tmp tables ratio seems fine
TABLE SCANS Current read_buffer_size = 128 K Current table scan ratio = 24397 : 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 indexes.
TABLE LOCKING Current Lock Wait ratio = 1 : 12 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.
المواضيع المشابهه 
