|
|
|||||||
| MySQL Veritabanı SQL SQL MySQL Access MSSQL Oracle Veritabani Database |
 |
|
|
LinkBack | Seçenekler | Stil |
2008-01-03, 14:21
|
||||
|
||||
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
Kod:
[mysqld] set-variable = max_connections=500 safe-show-database şimdiden teşekkürler
__________________
 Dmoz indexli PR1 PR2 PR3 PR4 Linkler[Satılık] msn kullanmıyorum msn istemeyin 
|
|
2008-01-03, 16:49
|
||||
|
||||
|
Alıntı:
[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
|
|
2008-01-16, 16:28
|
|||
|
|||
|
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-Kodu:
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 ________ HTML-Kodu:
[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
|
|
2008-01-16, 16:30
|
|||
|
|||
|
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?
|
|
2008-01-16, 18:20
|
||||
|
||||
|
Alıntı:
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-verita...g-scripti.html Buradaki scritin çıktısını o konu altına atınız, Mysql i elimizden geldiğince optimize edelim.
|
|
2008-01-18, 17:21
|
||||
|
||||
|
Alıntı:
|
|
2008-02-07, 15:52
|
|||
|
|||
|
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
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 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'.
Konu faantastic tarafından (2008-02-07 Saat 16:00 ) değiştirilmiştir..
|
|
2008-02-14, 14:06
|
||||
|
||||
|
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
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
__________________
Dmoz indexli PR1 PR2 PR3 PR4 Linkler[Satılık] msn kullanmıyorum msn istemeyin
|
|
| 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
