VPS - Optymalizacja

[Mis 0]

Witam, posiadam VPS z Firmy logout.pl o parametrach:

• 10 GB dysku twardego
• 50 GB transferu miesięcznego
• 256 MB - gwarantowany RAM
• 512 MB - maksymalny RAM
• Procesor - 1 Ghz

 

Zainstalowanego na nim mam Debian 5.0 Lenny oraz:

 

Apache2, MySQL 5, phpmyadmin, vnstat, htop, iftop, cron

 

Obciążenie procesora waha się od 1% do 10%

Load Jest na Poziomie od 0.02 do 0.42

Użycie RAM jest na poziomie od 300 do 400MB (Średnio 342MB)

 

mysqladmin -p status

Uptime: 131250 Threads: 1 Questions: 258740 Slow queries: 0 Opens: 3072 Flush tables: 1 Open tables: 64 Queries per second avg: 1.971

 

Na tym VPS'ie hostowane są 3 mini stronki opierające się na skryptach takich jak:

 

AMXBANS 5.1rc

PsychoStats 3.1

DiabloMod 2.0

 

Wąskim gardłem wszystkich 3 stronek jest mysql, bo głównie do niego lecą zapytania (od vps+osobnych 3 serwerów gier+cron co 10 min dogrywa logi do mysql)

 

Moje pytanie to:

Jak zoptymalizować MySQL aby nie łapał zacinek (czasami potrafi go przyciąć nawet na 10 sekund) [Obydwie maszynki stoją w LiveNecie] oraz żeby pożerał mniej ramu ;f

 

Liczę na wasza pomoc

 

Pozdrawiam Michał Duda

[Gość N3T5kY]

Jaki masz aktualny my.cnf?

 

Jaka jest wielkość baz?

[www.ionic.pl 535]

Wyłącz innodb jesli Ci nei potrzebne,

 

Jeśli mysql pracuje tak z 1-2 dni to daj wynik:

cd /tmp/ && wget http://mysqltuner.pl/mysqltuner.pl
chmod +x mysqltuner.pl
./mysqltuner.pl

[Mis 0]

Jaki masz aktualny my.cnf?

 

#
# The MySQL database server configuration file.
#
[client]
port		= 3306
socket		= /var/run/mysqld/mysqld.sock

[mysqld_safe]
socket		= /var/run/mysqld/mysqld.sock
nice		= 0

[mysqld]
user		= mysql
pid-file	= /var/run/mysqld/mysqld.pid
socket		= /var/run/mysqld/mysqld.sock
port		= 3306
basedir		= /usr
datadir		= /var/lib/mysql
tmpdir		= /tmp
language	= /usr/share/mysql/polish
#skip-external-locking
#skip-networking
#bind-address		= 91.210.128.97
key_buffer		= 16M
max_allowed_packet	= 16M
thread_stack		= 1M
thread_cache_size	= 128
# 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_limit 	= 1M
query_cache_size 	= 16M
#log		= /var/log/mysql/mysql.log
#log_slow_queries	= /var/log/mysql/mysql-slow.log
#long_query_time = 2
#log-queries-not-using-indexes
#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
skip-bdb
#skip-innodb
# chroot = /var/lib/mysql/
# 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

# [MYSQL_CLUSTER]
# ndb-connectstring=127.0.0.1

!includedir /etc/mysql/conf.d/

 

Jaka jest wielkość baz?

 

fragerzy_amxbans - 136,7 KB

fragerzy_diablo - 207,2 KB

fragerzy_staty - 5,5 MB

 

 

 

Wyłącz innodb jesli Ci nei potrzebne,

 

Jeśli mysql pracuje tak z 1-2 dni to daj wynik:

cd /tmp/ && wget http://mysqltuner.pl/mysqltuner.pl
chmod +x mysqltuner.pl
./mysqltuner.pl

 

root@vz1883:/tmp# ./mysqltuner.pl

>> MySQLTuner 1.0.1 - Major Hayden <major@mhtx.net>
>> Bug reports, feature requests, and downloads at http://mysqltuner.com/
>> Run with '--help' for additional options and output filtering

-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.0.51a-24+lenny2
[OK] Operating on 32-bit architecture with less than 2GB RAM

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 2M (Tables: 64)
[!!] InnoDB is enabled but isn't being used
[!!] Total fragmented tables: 6

-------- Performance Metrics -------------------------------------------------
[--] Up for: 2d 3h 4m 26s (337K q [1.836 qps], 41K conn, TX: 127M, RX: 58M)
[--] Reads / Writes: 48% / 52%
[--] Total buffers: 58.0M global + 3.5M per thread (100 max threads)
[OK] Maximum possible memory usage: 408.0M (79% of installed RAM)
[OK] Slow queries: 0% (0/337K)
[OK] Highest usage of available connections: 7% (7/100)
[OK] Key buffer size / total MyISAM indexes: 16.0M/3.4M
[OK] Key buffer hit rate: 99.3% (2M cached / 16K reads)
[OK] Query cache efficiency: 45.8% (77K cached / 168K selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 3K sorts)
[!!] Temporary tables created on disk: 35% (12K on disk / 35K total)
[OK] Thread cache hit rate: 99% (7 created / 41K connections)
[!!] Table cache hit rate: 1% (64 open / 4K opened)
[OK] Open file limit used: 11% (122/1K)
[OK] Table locks acquired immediately: 100% (194K immediate / 194K locks)

-------- Recommendations -----------------------------------------------------
General recommendations:
Add skip-innodb to MySQL configuration to disable InnoDB
Run OPTIMIZE TABLE to defragment tables for better performance
Enable the slow query log to troubleshoot bad queries
When making adjustments, make tmp_table_size/max_heap_table_size equal
Reduce your SELECT DISTINCT queries without LIMIT clauses
Increase table_cache gradually to avoid file descriptor limits
Variables to adjust:
tmp_table_size (> 32M)
max_heap_table_size (> 16M)
table_cache (> 64)