my.conf tuning

[newmanyak]

Hi all! My system VDS 2GB ram 1 Core CPU

my.conf

[mysqld]
tmpdir=/tmp
safe-show-database
max_connections=300
max_user_connections=500
key_buffer_size=768M
myisam_sort_buffer_size=64M
join_buffer_size=1M
read_buffer_size=2M
sort_buffer_size=2M
table_cache=5000
thread_cache_size=64
wait_timeout=30
connect_timeout=45
tmp_table_size=950M
max_heap_table_size=64M
max_allowed_packet=256M
thread_concurrency=32
table_lock_wait_timeout=1
query_cache_limit=4M
query_cache_size=128M
query_cache_type=1
set-variable=local-infile=0
local-infile=0
innodb_file_per_table=1
[isamchk]
key_buffer=256M
sort_buffer_size=256M
read_buffer=2M
write_buffer=2M
[myisamchk]
key_buffer=64M
sort_buffer=64M
read_buffer=16M
write_buffer=16M

./mysqltuner.pl

mysqlcheck -o --all-databases and after 5 minutes, again fragmented tables

  >>  MySQLTuner 1.2.0 - Major Hayden <[email protected]>
 >>  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.1.61-cll
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 46M (Tables: 135)
[--] Data in InnoDB tables: 208K (Tables: 13)
[!!] Total fragmented tables: 1

-------- Security Recommendations  -------------------------------------------
[OK] All database users have passwords assigned

-------- Performance Metrics -------------------------------------------------
[--] Up for: 11d 8h 33m 21s (12M q [12.695 qps], 548K conn, TX: 232B, RX: 2B)
[--] Reads / Writes: 73% / 27%
[--] Total buffers: 906.0M global + 5.5M per thread (300 max threads)
[!!] Maximum possible memory usage: 2.5G (127% of installed RAM)
[OK] Slow queries: 0% (2/12M)
[OK] Highest usage of available connections: 4% (12/300)
[OK] Key buffer size / total MyISAM indexes: 768.0M/21.8M
[OK] Key buffer hit rate: 99.9% (84M cached / 64K reads)
[OK] Query cache efficiency: 60.6% (5M cached / 9M selects)
[!!] Query cache prunes per day: 3169
[OK] Sorts requiring temporary tables: 0% (8K temp sorts / 959K sorts)
[OK] Temporary tables created on disk: 3% (8K on disk / 296K total)
[OK] Thread cache hit rate: 99% (12 created / 548K connections)
[OK] Table cache hit rate: 26% (172 open / 649 opened)
[OK] Open file limit used: 3% (317/10K)
[OK] Table locks acquired immediately: 99% (6M immediate / 6M locks)
[OK] InnoDB data size / buffer pool: 208.0K/8.0M

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    Reduce your overall MySQL memory footprint for system stability
    Enable the slow query log to troubleshoot bad queries
Variables to adjust:
  *** MySQL's maximum memory usage is dangerously high ***

and some times it seems top -c

/usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --log-error=/var/lib/mysql/server1.com.err --pid-file=

pico server1.com.err last last lines...

130210 12:47:50 [Warning] '--safe-show-database' is deprecated and will be removed in a future release. Please use 'GRANT SHOW DATABASES' instead.

130210 12:47:50 [Warning] /usr/sbin/mysqld: Option '--set-variable' is deprecated. Use --variable-name=value instead.

130210 12:45:52 [Note] Event Scheduler: Purging the queue. 0 events
130210 12:45:53  InnoDB: Starting shutdown...
130210 12:45:58  InnoDB: Shutdown completed; log sequence number 0 119571
130210 12:45:58 [Note] /usr/sbin/mysqld: Shutdown complete

130210 12:45:58 mysqld_safe mysqld from pid file /var/lib/mysql/server1.com.pid ended
130210 12:47:50 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
130210 12:47:50 [Warning] '--safe-show-database' is deprecated and will be removed in a future release. Please use 'GRANT SHOW DATABASES' instead.
130210 12:47:50 [Warning] /usr/sbin/mysqld: Option '--set-variable' is deprecated. Use --variable-name=value instead.
130210 12:47:50 [Note] Plugin 'FEDERATED' is disabled.
130210 12:47:50  InnoDB: Initializing buffer pool, size = 8.0M
130210 12:47:50  InnoDB: Completed initialization of buffer pool
130210 12:47:50  InnoDB: Started; log sequence number 0 119571
130210 12:47:51 [Note] Event Scheduler: Loaded 0 events
130210 12:47:51 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.1.61-cll'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  MySQL Community Server (GPL)

thanks all.

 

[newmanyak]

why nobody does not reply

 

[newmanyak]

Waiting for suggestions... 2GB + 2GB Swap ram vds

./mysqltuner.pl latest info mysql working time > 48h

How can i fix this..

Run OPTIMIZE TABLE to defragment tables for better performance?
mysqlcheck -o --all-databases and after 5 minutes, again fragmented tables

Reduce your overall MySQL memory footprint for system stability?
Enable the slow query log to troubleshoot bad queries?

 >>  MySQLTuner 1.2.0 - Major Hayden <[email protected]>
 >>  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.1.61-cll
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 46M (Tables: 135)
[--] Data in InnoDB tables: 208K (Tables: 13)
[!!] Total fragmented tables: 4

-------- Security Recommendations  -------------------------------------------
[OK] All database users have passwords assigned

-------- Performance Metrics -------------------------------------------------
[--] Up for: 1d 20h 50m 28s (1M q [11.394 qps], 82K conn, TX: 34B, RX: 353M)
[--] Reads / Writes: 72% / 28%
[--] Total buffers: 906.0M global + 5.5M per thread (300 max threads)
[!!] Maximum possible memory usage: 2.5G (127% of installed RAM)
[OK] Slow queries: 0% (3/1M)
[OK] Highest usage of available connections: 4% (12/300)
[OK] Key buffer size / total MyISAM indexes: 768.0M/21.5M
[OK] Key buffer hit rate: 99.5% (12M cached / 64K reads)
[OK] Query cache efficiency: 60.8% (800K cached / 1M selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 1% (1K temp sorts / 136K sorts)
[OK] Temporary tables created on disk: 3% (1K on disk / 42K total)
[OK] Thread cache hit rate: 99% (12 created / 82K connections)
[OK] Table cache hit rate: 21% (174 open / 813 opened)
[OK] Open file limit used: 7% (319/4K)
[OK] Table locks acquired immediately: 99% (889K immediate / 889K locks)
[OK] InnoDB data size / buffer pool: 208.0K/8.0M

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    Reduce your overall MySQL memory footprint for system stability
    Enable the slow query log to troubleshoot bad queries

latest pico /etc/my.cnf

[mysqld]
tmpdir=/tmp
skip-name-resolve
safe-show-database
max_connections=300
max_user_connections=500
key_buffer_size=768M
myisam_sort_buffer_size=64M
join_buffer_size=1M
read_buffer_size=2M
sort_buffer_size=2M
table_cache=2048
thread_cache_size=64
wait_timeout=30
connect_timeout=45
tmp_table_size=950M
max_heap_table_size=64M
max_allowed_packet=256M
thread_concurrency=32
table_lock_wait_timeout=1
query_cache_limit=4M
query_cache_size=64M
query_cache_type=1
local-infile=0
innodb_file_per_table=1
[isamchk]
key_buffer=128M
sort_buffer_size=128M
read_buffer=2M
write_buffer=2M
[myisamchk]
key_buffer=64M
sort_buffer=64M
read_buffer=16M
write_buffer=16M

pico /var/lib/mysql/server1.com.err

some warning alerts

130226 18:53:59 mysqld_safe mysqld from pid file /var/lib/mysql/server1.com.pid ended
130226 18:56:05 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
130226 18:56:05 [Warning] '--safe-show-database' is deprecated and will be removed in a future release. Please use 'GRANT SHOW DATABASES' instead.
130226 18:56:05 [Note] Plugin 'FEDERATED' is disabled.
130226 18:56:05  InnoDB: Initializing buffer pool, size = 8.0M
130226 18:56:05  InnoDB: Completed initialization of buffer pool
130226 18:56:05  InnoDB: Started; log sequence number 0 514228
130226 18:56:06 [Warning] 'user' entry '[email protected]' ignored in --skip-name-resolve mode.
130226 18:56:06 [Warning] 'user' entry '[email protected]' ignored in --skip-name-resolve mode.
130226 18:56:06 [Warning] 'db' entry 'user\_forumdb [email protected]' ignored in --skip-
130226 18:56:06 [Note] Event Scheduler: Loaded 0 events
130226 18:56:06 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.1.61-cll'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  MySQL Community Server (GPL)

 

[ES - George]

For slow query logging, add the below to your my.cnf

log-slow-queries=/var/lib/mysql/slow.log

Then run the below
touch /var/lib/mysql/slow.log
chmod 660 /var/lib/mysql/slow.log
chown mysql:mysql /var/lib/mysql/slow.log
Restart MySQL

 

[newmanyak]

For slow query logging, add the below to your my.cnf

log-slow-queries=/var/lib/mysql/slow.log

Then run the below
touch /var/lib/mysql/slow.log
chmod 660 /var/lib/mysql/slow.log
chown mysql:mysql /var/lib/mysql/slow.log
Restart MySQL

thanks you for reply. mysql is running from 17 hours but the log is empty

pico /var/lib/mysql/slow.log

/usr/sbin/mysqld, Version: 5.1.61-cll (MySQL Community Server (GPL)). started with:
Tcp port: 0  Unix socket: (null)
Time                 Id Command    Argument

 

[ES - George]

thanks you for reply. mysql is running from 17 hours but the log is empty

pico /var/lib/mysql/slow.log

/usr/sbin/mysqld, Version: 5.1.61-cll (MySQL Community Server (GPL)). started with:
Tcp port: 0  Unix socket: (null)
Time                 Id Command    Argument

Try changing log-slow-queries to slow-query-log in my.cnf and restarting.

 

[newmanyak]

Try changing log-slow-queries to slow-query-log in my.cnf and restarting.

./mysqltuner.pl I see again

-------- Recommendations -----------------------------------------------------
General recommendations:
Enable the slow query log to troubleshoot bad queries

Anyway I gave up from logs thanks

 

[anton_latvia]

Hm.. What is the exact problem? Looking at your original my.cnf.. I would lower these values:

key_buffer_size=256M
myisam_sort_buffer_size=64M
tmp_table_size=512M
max_heap_table_size=32M
max_allowed_packet=32M

if not even lower. Having those values set to big values does not always mean "speed and memory caching".

 

[newmanyak]

ok thanks anton, why mysqlcheck -o --all-databases and after 5 minutes, again fragmented tables

 >>  MySQLTuner 1.2.0 - Major Hayden <[email protected]>
 >>  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.1.61-cll
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 48M (Tables: 135)
[--] Data in InnoDB tables: 208K (Tables: 13)
[!!] Total fragmented tables: 25

-------- Security Recommendations  -------------------------------------------
[OK] All database users have passwords assigned

-------- Performance Metrics -------------------------------------------------
[--] Up for: 1d 8h 52m 44s (1M q [14.072 qps], 73K conn, TX: 31B, RX: 269M)
[--] Reads / Writes: 73% / 27%
[--] Total buffers: 362.0M global + 5.5M per thread (300 max threads)
[!!] Maximum possible memory usage: 2.0G (100% of installed RAM)
[OK] Slow queries: 0% (0/1M)
[OK] Highest usage of available connections: 5% (17/300)
[OK] Key buffer size / total MyISAM indexes: 256.0M/24.0M
[OK] Key buffer hit rate: 99.7% (10M cached / 32K reads)
[OK] Query cache efficiency: 61.4% (738K cached / 1M selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 1% (1K temp sorts / 119K sorts)
[OK] Temporary tables created on disk: 4% (1K on disk / 35K total)
[OK] Thread cache hit rate: 99% (17 created / 73K connections)
[OK] Table cache hit rate: 45% (188 open / 414 opened)
[OK] Open file limit used: 7% (333/4K)
[OK] Table locks acquired immediately: 99% (794K immediate / 794K locks)
[OK] InnoDB data size / buffer pool: 208.0K/8.0M

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    Reduce your overall MySQL memory footprint for system stability
    Enable the slow query log to troubleshoot bad queries