newmanyak

Member
Feb 21, 2013
11
0
1
cPanel Access Level
Website Owner
Hi all! My system VDS 2GB ram 1 Core CPU

my.conf

Code:
[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

Code:
  >>  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
Code:
/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.

Code:
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.
 
Last edited:

newmanyak

Member
Feb 21, 2013
11
0
1
cPanel Access Level
Website Owner
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?

Code:
 >>  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

Code:
[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

Code:
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

Well-Known Member
PartnerNOC
Jun 12, 2011
179
25
68
UK
cPanel Access Level
DataCenter Provider
Twitter
For slow query logging, add the below to your my.cnf

Code:
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

Member
Feb 21, 2013
11
0
1
cPanel Access Level
Website Owner
For slow query logging, add the below to your my.cnf

Code:
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

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

ES - George

Well-Known Member
PartnerNOC
Jun 12, 2011
179
25
68
UK
cPanel Access Level
DataCenter Provider
Twitter
thanks you for reply. mysql is running from 17 hours but the log is empty

pico /var/lib/mysql/slow.log

Code:
/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

Member
Feb 21, 2013
11
0
1
cPanel Access Level
Website Owner
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

Well-Known Member
PartnerNOC
May 11, 2004
390
12
168
Latvia
cPanel Access Level
Root Administrator
Hm.. What is the exact problem? Looking at your original my.cnf.. I would lower these values:

Code:
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

Member
Feb 21, 2013
11
0
1
cPanel Access Level
Website Owner
ok thanks anton, why mysqlcheck -o --all-databases and after 5 minutes, again fragmented tables

Code:
 >>  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