|
|
|||||||
| MySQL Veritabanı SQL SQL MySQL Access MSSQL Oracle Veritabani Database |
 |
|
|
LinkBack (3) | Seçenekler | Stil |
2008-02-15, 18:57
|
||||
|
||||
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 ? Konu Raiden tarafından (2008-02-15 Saat 18:59 ) değiştirilmiştir.. 
|
|
2008-02-20, 09:37
|
|||
|
|||
|
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?
|
|
2008-02-21, 17:10
|
||||
|
||||
|
Alıntı:
|
|
2008-02-21, 17:11
|
||||
|
||||
|
Alıntı:
Relication kullanıyormusunuz. Ek olarak http://forum.iyinet.com/mysql-verita...g-scripti.html çıktısını da verirmisiniz.
|
|
2008-02-21, 17:15
|
||||
|
||||
|
Alıntı:
|
|
2008-02-25, 12:37
|
|||
|
|||
|
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/...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/...ster-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'.
|
|
2008-02-26, 09:25
|
||||
|
||||
|
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
|
|
2008-02-26, 19:46
|
||||
|
||||
|
Alıntı:
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
|
|
2008-02-26, 19:55
|
||||
|
||||
|
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
|
|
2008-02-26, 19:56
|
||||
|
||||
|
Alıntı:
[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
|
|
LinkBacks (?)
LinkBack to this Thread: http://forum.iyinet.com/mysql-veritabani-sql/29477-mysql-cpu-kullan-m-my-cnf-optimizasyonu.html
|
||||
| Konuyu Başlatan | For | Type | Tarih | |
| Load sorunu yaşayanlar için genel tartışma konusu - vBulletin SEO Forums | This thread | Pingback | 2007-12-05 17:27 | |
| Vbseo işlemciyi çok kasıyor - Page 2 - vBulletin SEO Forums | This thread | Pingback | 2007-06-14 21:38 | |
| Vbseo işlemciyi çok kasıyor - vBulletin SEO Forums | This thread | Pingback | 2007-04-13 23:00 | |
| Seçenekler | |
| Stil | |
|
|
|
Benzer Konular
|
||||
| Konu | Konuyu Başlatan | Forum | Cevaplar | Son Mesaj |
| mysql in visual basic ile kullanılması | Anonymous | MySQL Veritabanı SQL | 5 | 2007-08-29 23:00 |
| 2 Ay Öde 3 Ay Kullan Kampanyaları | Uzak444 | Dedicated CoLocation | 0 | 2007-01-17 16:33 |
| $50 lık adwords hesapları 13 ytl kullanıma hazır.. | firar | Google Adwords | 4 | 2006-10-31 07:59 |
| 2 Ay Öde 3 Ay Kullan Kampanyası | Uzak444 | Dedicated CoLocation | 0 | 2006-10-20 00:00 |
| 3 Ay Peşin Öde, 1 ay ücretsiz kullan! Dedicated ve VPS | LNWServers | Dedicated CoLocation | 17 | 2006-06-09 20:14 |
Normal
