Pages : 1
#1 Le 28/05/2013, à 21:12
- iMODiRiS
Aide pour config mysql pour forum qui lag
Bonjour,
J'ai un serveur dédié DEDIBOX PRO
Dell PowerEdge R210 avec un Xeon L3426 (4 coeurs à 1.86 GHz)
16 Go de DDR3 ECC
2 disques durs de 2 To
ubuntu v10.04_LTS-server (64BITS)
Dessus est hebergé principalement un forum PHPBB
Fréquentation moyenne 25.000 visiteurs par jour.
J'ai des gros problemes de lags quand je depasse 1000 connection simultannées sur 15 min (chiffres phpbb)
SQL depasse les 100% d'uc
J'ai lancé un mysqltuner
-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.1.69-0ubuntu0.10.04.1
[OK] Operating on 64-bit architecture
-------- Storage Engine Statistics -------------------------------------------
[--] Status: -Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 1G (Tables: 112)
[--] Data in InnoDB tables: 128K (Tables: 8)
[!!] Total fragmented tables: 19
-------- Performance Metrics -------------------------------------------------
[--] Up for: 1d 0h 11m 38s (19M q [223.139 qps], 1M conn, TX: 507B, RX: 3B)
[--] Reads / Writes: 85% / 15%
[--] Total buffers: 1.5G global + 2.7M per thread (151 max threads)
[OK] Maximum possible memory usage: 1.9G (12% of installed RAM)
[OK] Slow queries: 0% (98/19M)
[OK] Highest usage of available connections: 70% (106/151)
[OK] Key buffer size / total MyISAM indexes: 1.0G/1.5G
[OK] Key buffer hit rate: 99.3% (1B cached / 11M reads)
[OK] Query cache efficiency: 43.2% (4M cached / 11M selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 2% (16K temp sorts / 753K sorts)
[!!] Joins performed without indexes: 691
[OK] Temporary tables created on disk: 7% (32K on disk / 434K total)
[OK] Thread cache hit rate: 91% (104K created / 1M connections)
[!!] Table cache hit rate: 0% (64 open / 865K opened)
[OK] Open file limit used: 8% (82/1K)
[!!] Table locks acquired immediately: 92%
[OK] InnoDB data size / buffer pool: 128.0K/8.0M
-------- Recommendations -----------------------------------------------------
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
Enable the slow query log to troubleshoot bad queries
Adjust your join queries to always utilize indexes
Increase table_cache gradually to avoid file descriptor limits
Optimize queries and/or use InnoDB to reduce lock wait
Variables to adjust:
join_buffer_size (> 128.0K, or always use indexes with joins)
table_cache (> 64)Voici l'etat de mon top en ce moment 15 secondes pour afficher une page
Au total il y a 1197 utilisateurs en ligne :: 434 enregistrés, 80 invisibles et 683 invités (basées sur les utilisateurs actifs des 15 dernières minutes)
Le record du nombre d’utilisateurs en ligne est de 1619, le 02 Mai 2013, 22:56
top - 22:17:17 up 1 day, 28 min, 1 user, load average: 33.89, 28.49, 24.09
Tasks: 619 total, 39 running, 579 sleeping, 0 stopped, 1 zombie
Cpu(s): 90.3%us, 8.7%sy, 0.0%ni, 0.4%id, 0.0%wa, 0.0%hi, 0.6%si, 0.0%st
Mem: 16457556k total, 14850532k used, 1607024k free, 53856k buffers
Swap: 2096472k total, 2312k used, 2094160k free, 6097208k cached
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
974 mysql 20 0 2248m 470m 4716 S 224 2.9 1207:22 mysqld
827 root 20 0 4248 768 384 R 17 0.0 0:03.88 gzip
20609 forum-ec 20 0 1211m 98m 36m R 15 0.6 2:51.86 php5-cgi
30038 forum-ec 20 0 1204m 89m 31m R 13 0.6 0:47.27 php5-cgi
32738 forum-ec 20 0 1183m 66m 29m R 13 0.4 0:08.40 php5-cgi
20483 forum-ec 20 0 1205m 96m 39m S 11 0.6 3:43.07 php5-cgi
30153 forum-ec 20 0 1206m 90m 30m R 11 0.6 0:45.23 php5-cgi
491 forum-ec 20 0 1202m 79m 22m S 10 0.5 0:06.26 php5-cgi
29696 signatur 20 0 1165m 34m 12m R 10 0.2 0:18.95 php5-cgi
29996 signatur 20 0 1162m 29m 11m R 10 0.2 0:14.93 php5-cgi
30260 forum-ec 20 0 1204m 89m 33m S 10 0.6 0:39.24 php5-cgi
30041 forum-ec 20 0 1204m 88m 32m R 9 0.6 0:32.82 php5-cgi
30057 forum-ec 20 0 1184m 69m 33m S 9 0.4 0:31.30 php5-cgi
30043 forum-ec 20 0 1209m 96m 35m S 9 0.6 0:55.88 php5-cgi
32733 forum-ec 20 0 1205m 82m 23m R 9 0.5 0:06.01 php5-cgi
32679 forum-ec 20 0 1203m 78m 21m R 9 0.5 0:07.94 php5-cgi
16133 forum-ec 20 0 1216m 107m 39m R 8 0.7 4:10.91 php5-cgi
16161 forum-ec 20 0 1212m 104m 40m R 8 0.7 4:16.93 php5-cgi
30109 forum-ec 20 0 1210m 96m 34m R 8 0.6 0:52.34 php5-cgi
30158 forum-ec 20 0 1209m 95m 34m S 8 0.6 0:36.59 php5-cgi
32675 forum-ec 20 0 1203m 77m 20m R 8 0.5 0:06.61 php5-cgi
29942 signatur 20 0 1165m 35m 13m S 8 0.2 0:22.97 php5-cgi
475 forum-ec 20 0 1205m 78m 19m R 8 0.5 0:04.31 php5-cgi
492 forum-ec 20 0 1205m 82m 22m R 8 0.5 0:05.27 php5-cgi
15677 forum-ec 20 0 1204m 99m 42m R 8 0.6 4:53.91 php5-cgi
30106 forum-ec 20 0 1201m 89m 33m R 8 0.6 0:52.03 php5-cgi
30165 forum-ec 20 0 1211m 99m 35m R 8 0.6 0:48.52 php5-cgi
348 forum-ec 20 0 1183m 62m 25m S 8 0.4 0:04.78 php5-cgi
20538 forum-ec 20 0 1211m 99m 35m S 8 0.6 3:16.20 php5-cgi
29544 signatur 20 0 1167m 36m 13m R 8 0.2 0:25.14 php5-cgi
30035 forum-ec 20 0 1208m 94m 33m S 8 0.6 0:33.29 php5-cgi
32676 forum-ec 20 0 1204m 85m 28m S 8 0.5 0:09.68 php5-cgi
32739 forum-ec 20 0 1204m 83m 24m S 8 0.5 0:07.76 php5-cgi
389 forum-ec 20 0 1204m 83m 25m R 7 0.5 0:04.49 php5-cgi
32745 forum-ec 20 0 1201m 80m 24m R 7 0.5 0:06.03 php5-cgi
518 forum-ec 20 0 1201m 79m 24m S 7 0.5 0:04.88 php5-cgi
29695 signatur 20 0 1168m 37m 13m R 7 0.2 0:22.47 phVoici mon my.cf
[client]
port = 3306
socket = /var/run/mysqld/mysqld.sock
[mysqld_safe]
socket = /var/run/mysqld/mysqld.sock
nice = 0
[mysqld]
user = mysql
socket = /var/run/mysqld/mysqld.sock
port = 3306
basedir = /usr
datadir = /var/lib/mysql
tmpdir = /tmp
skip-external-locking
bind-address = 127.0.0.1
key_buffer = 1024M
max_allowed_packet = 16M
thread_stack = 192K
thread_cache_size = 8
myisam-recover = BACKUP
query_cache_limit = 6M
query_cache_size = 512M
log_error = /var/log/mysql/error.log
expire_logs_days = 10
max_binlog_size = 100M
innodb_file_per_table = 1
[mysqldump]
quick
quote-names
max_allowed_packet = 16M
[mysql]
[isamchk]
key_buffer = 64M
!includedir /etc/mysql/conf.d/Je ne sais pas trop quoi améliorer et je solicite votre aide.
Merci
Dernière modification par iMODiRiS (Le 28/05/2013, à 21:24)
Hors ligne
#2 Le 29/05/2013, à 18:39
- gargoris
Re : Aide pour config mysql pour forum qui lag
Hello,
Je viens de valider mon inscription sur ce forum...
Comme indiqué sur ton forum :
Installe sur le serveur tuning-primer.sh (http://www.day32.com/MySQL/) que tu lances avec la commande ./tuning-primer.sh
Ce script teste la configuration de mysql et te propose des modifications de la config... Ça fait pas tout mais c'est un bon début.
Hors ligne
Pages : 1