Optymalizacja mysql

[Gość thetox]

Dzisiaj zmieniłem serwer na Lighttpd i moje forum chodzi dużo lepiej

Ale chciałbym jeszcze z optymalizować mysql, baza danych wazy 1,5 GB

Serwer vps

 

Pamięć ram

Guaranteed RAM 2GB

Burstable RAM 4GB

virtualizacja OpenVZ

 

Mój plik

/etc/mysql/my.cnf

 

#
# The MySQL database server configuration file.
#
# You can copy this to one of:
# - "/etc/mysql/my.cnf" to set global options,
# - "~/.my.cnf" to set user-specific options.
# 
# One can use all long options that the program supports.
# Run program with --help to get a list of available options and with
# --print-defaults to see which it would actually understand and use.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html

# This will be passed to all mysql clients
# It has been reported that passwords should be enclosed with ticks/quotes
# escpecially if they contain "#" chars...
# Remember to edit /etc/mysql/debian.cnf when changing the socket location.
[client]
port		= 3306
socket		= /var/run/mysqld/mysqld.sock

# Here is entries for some specific programs
# The following values assume you have at least 32M ram

# This was formally known as [safe_mysqld]. Both versions are currently parsed.
[mysqld_safe]
socket		= /var/run/mysqld/mysqld.sock
nice		= 0

[mysqld]
#
# * Basic Settings
#

#
# * IMPORTANT
#   If you make changes to these settings and your system uses apparmor, you may
#   also need to also adjust /etc/apparmor.d/usr.sbin.mysqld.
#

user		= mysql
socket		= /var/run/mysqld/mysqld.sock
port		= 3306
basedir		= /usr
datadir		= /var/lib/mysql
tmpdir		= /tmp
skip-external-locking
#
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
bind-address		= 127.0.0.1
#
# * Fine Tuning
#
key_buffer		= 16M
max_allowed_packet	= 16M
thread_stack		= 192K
thread_cache_size   	= 8
# This replaces the startup script and checks MyISAM tables if needed
# the first time they are touched
myisam-recover 		= BACKUP
#max_connections    	= 100
#table_cache        	= 64
#thread_concurrency 	= 10
#
# * Query Cache Configuration
#
query_cache_limit	= 1M
query_cache_size    	= 16M
#
# * Logging and Replication
#
# Both location gets rotated by the cronjob.
# Be aware that this log type is a performance killer.
# As of 5.1 you can enable the log at runtime!
#general_log_file    	= /var/log/mysql/mysql.log
#general_log     		= 1

log_error            	= /var/log/mysql/error.log

# Here you can see queries with especially long duration
#log_slow_queries	= /var/log/mysql/mysql-slow.log
#long_query_time = 2
#log-queries-not-using-indexes
#
# The following can be used as easy to replay backup logs or for replication.
# note: if you are setting up a replication slave, see README.Debian about
#   	other settings you may need to change.
#server-id		= 1
#log_bin			= /var/log/mysql/mysql-bin.log
expire_logs_days	= 10
max_binlog_size 		= 100M
#binlog_do_db		= include_database_name
#binlog_ignore_db	= include_database_name
#
# * InnoDB
#
# InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/.
# Read the manual for more InnoDB related options. There are many!
#
# * Security Features
#
# Read the manual, too, if you want chroot!
# chroot = /var/lib/mysql/
#
# For generating SSL certificates I recommend the OpenSSL GUI "tinyca".
#
# ssl-ca=/etc/mysql/cacert.pem
# ssl-cert=/etc/mysql/server-cert.pem
# ssl-key=/etc/mysql/server-key.pem



[mysqldump]
quick
quote-names
max_allowed_packet	= 16M

[mysql]
#no-auto-rehash	# faster start of mysql but no tab completition

[isamchk]
key_buffer		= 16M

#
# * IMPORTANT: Additional settings that can override those from this file!
#   The files must end with '.cnf', otherwise they'll be ignored.
#
!includedir /etc/mysql/conf.d/

[Gość squeezer]

Zapraszam do zapoznania się z postem na moim blogu. Opisałem tam jakie informacje o serwerze dobrze by było zebrać i udostępnić jeśli chcesz, żeby ktoś był w stanie Ci skutecznie pomóc.

 

http://blog.ksiazek....serwerem-mysql/

 

Patrząc na Twoją konfigurację, wygląda na to, że nie wiele tam było ruszane - key_buffer rzędu 16M przy 2GB pamięci to niewiele. Nie będę jednak sugerował niczego, bo nie wiem jak wygląda ogólny stan Twojego serwera - ile pamięci jest wolnej, ile jest wykorzystywanej przez inne aplikacje. Zasugeruję 512MB na jakiś bufor, potem okaże się, że to za dużo, serwerowi pamięci zabraknie i będzie problem. Do tego konfiguracja to jedno. Pozostały jeszcze zapytania - włącz slowlog, zbierz dane, sprawdź co się wolno wykonuje.

[Gość thetox]

No to jeszcze raz

 

 ps auxf
USER   	PID %CPU %MEM	VSZ   RSS TTY  	STAT START   TIME COMMAND
root     	1  0.0  0.0  23308  1520 ?    	Ss   10:05   0:00 init
root 	14200  0.0  0.0  49256  1088 ?    	Ss   10:05   0:00 /usr/sbin/sshd
root 	30046  0.0  0.0  70608  3248 ?    	Ss   11:33   0:00  \_ sshd: root@pts/0
root 	30258  0.0  0.0  19416  2176 pts/0	Ss   11:33   0:00  	\_ -bash
root  	3506  0.0  0.0  15244  1116 pts/0	R+   12:15   0:00      	\_ ps auxf
proftpd  19633  0.0  0.0  69844  1888 ?    	Ss   10:05   0:00 proftpd: (accepting connections)
www-data 19664  0.1  0.1  91128  4744 ?    	S	10:05   0:12 /usr/sbin/lighttpd -f /etc/lighttpd/lighttpd.conf
www-data 19670  0.0  0.3 380216 15868 ?    	Ss   10:05   0:00  \_ /usr/bin/php-cgi
www-data 19742  1.5  1.2 398420 51732 ?    	S	10:05   1:59  |   \_ /usr/bin/php-cgi
www-data 19753  1.4  1.3 401288 58632 ?    	S	10:05   1:52  |   \_ /usr/bin/php-cgi
www-data 19755  1.3  1.4 405864 62824 ?    	S	10:05   1:48  |   \_ /usr/bin/php-cgi
www-data 19757  1.4  1.5 403764 62928 ?    	S	10:05   1:53  |   \_ /usr/bin/php-cgi
www-data 19674  0.0  0.3 380216 15868 ?    	Ss   10:05   0:00  \_ /usr/bin/php-cgi
www-data 19743  3.2  1.3 400112 56992 ?    	S	10:05   4:12  	\_ /usr/bin/php-cgi
www-data 19744  3.2  1.5 403996 64104 ?    	S	10:05   4:11  	\_ /usr/bin/php-cgi
www-data 19746  3.2  1.4 401780 61812 ?    	R	10:05   4:16  	\_ /usr/bin/php-cgi
www-data 19754  3.1  1.2 399296 54384 ?    	S	10:05   4:08  	\_ /usr/bin/php-cgi
root 	20023  0.0  0.0  25792  1556 ?    	Ss   10:05   0:00 /usr/sbin/ntpd -p /var/run/ntpd.pid -g -u 105:104
mysql	28001 14.9  1.8 263512 78568 ?    	Ssl  10:16  17:48 /usr/sbin/mysqld
root 	17446  0.0  0.0  37200  2252 ?    	Ss   11:38   0:00 /usr/lib/postfix/master
postfix  17448  0.0  0.0  39424  2400 ?    	S	11:38   0:00  \_ qmgr -l -t fifo -u
postfix  17449  0.0  0.0  39264  2240 ?    	S	11:38   0:00  \_ pickup -l -t fifo -u -c
postfix  19806  0.0  0.0  41800  3168 ?    	S	11:39   0:00  \_ tlsmgr -l -t unix -u -c
postfix   3441  0.0  0.0  46064  3796 ?    	S	12:15   0:00  \_ smtpd -n smtp -t inet -u -c -o stress=
postfix   3443  0.0  0.0  39264  2200 ?    	S	12:15   0:00  \_ proxymap -t unix -u
postfix   3445  0.0  0.0  39264  2200 ?    	S	12:15   0:00  \_ anvil -l -t unix -u -c
postfix   3451  0.0  0.0  39276  2228 ?    	S	12:15   0:00  \_ trivial-rewrite -n rewrite -t unix -u -c
root 	18311  0.0  0.0  16900   720 ?    	Ss   11:39   0:00 /usr/sbin/dovecot -c /etc/dovecot/dovecot.conf
root 	18312  0.0  0.0  75064  3188 ?    	S	11:39   0:00  \_ dovecot-auth
root 	18314  0.0  0.0  74932  3244 ?    	S	11:39   0:00  \_ dovecot-auth -w
dovecot  18341  0.0  0.0  18696  2100 ?    	S	11:39   0:00  \_ pop3-login
dovecot  18342  0.0  0.0  18696  2096 ?    	S	11:39   0:00  \_ pop3-login
dovecot  18343  0.0  0.0  18712  2112 ?    	S	11:39   0:00  \_ imap-login
dovecot  18344  0.0  0.0  18712  2112 ?    	S	11:39   0:00  \_ imap-login
dovecot  18345  0.0  0.0  18712  2108 ?    	S	11:39   0:00  \_ imap-login
dovecot  28163  0.0  0.0  18696  2096 ?    	S	11:57   0:00  \_ pop3-login

 

 

 

 

 free
        	total   	used   	free 	shared	buffers 	cached
Mem:   	4194304 	794068	3400236      	0      	0      	0
-/+ buffers/cache: 	794068	3400236
Swap:        	0      	0      	0

 

 

 

vmstat 1 10
procs -----------memory---------- ---swap-- -----io---- -system-- ----cpu----
r  b   swpd   free   buff  cache   si   so	bi	bo   in   cs us sy id wa
1  0  	0 3390816  	0  	0	0	0	90   155	0  552  3  1 91  5
1  0  	0 3390812  	0  	0	0	0 	4 	0	0 16036  3  1 94  2
0  0  	0 3390812  	0  	0	0	0 	0 	0	0 18808  4  0 96  0
1  0  	0 3390812  	0  	0	0	0 	8 	0	0 15825  1  0 98  1
0  0  	0 3390812  	0  	0	0	0 	8	88	0 13590  1  0 98  0
0  0  	0 3390812  	0  	0	0	0 	0 	0	0 17742  2  0 98  0
0  0  	0 3390812  	0  	0	0	0 	0 	0	0 15814  6  1 93  0
0  0  	0 3390812  	0  	0	0	0 	0 	0	0 17916  0  0 100  0
1  0  	0 3391488  	0  	0	0	0	16 	0	0 16938  1  0 98  0
0  0  	0 3391488  	0  	0	0	0	40   100	0 18548  1  0 99  0

 

 

 

mysql> SHOW FULL PROCESSLIST;
+-------+------+-----------+------+---------+------+----------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Id	| User | Host  	| db   | Command | Time | State      	| Info                                                                                                                                                                                                                                                                                                                                	|
+-------+------+-----------+------+---------+------+----------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 12741 | root | localhost | NULL | Query   |	0 | NULL       	| SHOW FULL PROCESSLIST                                                                                                                                                                                                                                                                                                               	|
| 12752 | vb   | localhost | vb   | Query   |	0 | Sorting result | SELECT
                                                   	st.*, sd.founded
                                           	FROM
                                                   	vb_seoqueries_data sd
                                           	LEFT JOIN
                                                   	vb_seoqueries_terms st
                                                   	ON
                                                   	sd.stid = st.stid
                                           	WHERE
                                                   	st.ban = 0
                                                   	AND
                                                   	sd.page_type = 'showthread'
                                                   	AND
                                                   	sd.page_id = 232562
                                           	ORDER BY
                                                   	sd.founded DESC LIMIT 25 |
+-------+------+-----------+------+---------+------+----------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.13 sec)

mysql>

[Gość squeezer]

Z tego co podesłałeś wynika, że serwer w zasadzie się nudzi (ewentualnie akurat na taki okres trafiłeś). Pamięci wolnej jest sporo, więc zacząłbym od podbicia kilku buforów:

 

Jeśli baza danych jest w MyISAM, to key_buffer ustawiamy na 384MB. Jeśli baza jest w InnoDB, innodb_buffer_pool_size ustawiamy na 512MB. Do tego można podbić wielkość cache zapytań (zakładając, że w ogóle jest wykorzystywane). Sprawdź ile masz wolnego Qcache_free_memory (SHOW GLOBAL STATUS LIKE 'Qcache_free_memory' Jeśli niewiele, ustaw query_cache_size na 64 - 128MB. Obserwuj, czy faktycznie jest wykorzystywana - ile jest odczytów na sekundę, jaki jest stosunek insertów do czyszczenia cache (np. ze względu na to, że tabela została zmodyfikowana). Tego typu dane znajdziesz w mysqlreport. Sprawdź jak się ma stosunek otwartych tabel:

 

SHOW GLOBAL STATUS LIKE 'Open_tables';

 

do wielkości cache:

 

SHOW GLOBAL VARIABLES LIKE 'table_open_cache';

 

Podbij w razie potrzeby.

 

Do tego zorganizuj sobie logowanie wolnych zapytań i przeglądnij je - mk-query-digest i dwa kolejne posty, zaczynając od tego:

 

http://blog.ksiazek....ry-digest-cz-1/

 

powinny Ci trochę w tym pomóc.

 

Dobrze też by było, żebyś sobie zdebugował działanie serwera w momencie, w którym Ci zwalnia. Podstawowe pytanie to czy dzieje się tak cały czas, czy tylko w pewnych okresach? Jeśli są to jakieś momenty, to jak wygląda wtedy obciążenie serwera (user, system, I/O wait)? Czy w tym momencie wystarcza pamięci, czy serwer zaczyna się swapować? Jakie zapytania wtedy są wykonywane (SHOW FULL PROCESSLIST;)? Jak wygląda plan tych zapytań (EXPLAIN)?