MySQL Optimize For Server

jazz1611 · Oct 25, 2013

Hi,

I have Dedicated Server with E3-1230v2, 8GB RAM, 2TB HDD SATA2 (RAID 0) for Shared Hosting. I have fewer large database ex: 200Mb - 500Mb and mysql used 43% of memory

/http://i.imgur.com/iP84nHs.png

Code:

top - 22:27:47 up 4 days, 20:58,  1 user,  load average: 3.94, 5.30, 5.06
Tasks: 256 total,   1 running, 255 sleeping,   0 stopped,   0 zombie
Cpu(s):  0.9%us,  1.9%sy, 13.1%ni, 76.8%id,  7.2%wa,  0.0%hi,  0.2%si,  0.0%st
Mem:   8031044k total,  7318368k used,   712676k free,   112324k buffers
Swap:        0k total,        0k used,        0k free,  2616332k cached

There is result of mysqltuner.pl

Code:

[email protected] [~]# ./mysqltuner.pl

 >>  MySQLTuner 1.2.0 - Major Hayden <[email protected]>
 >>  Bug reports, feature requests, and downloads at [url]http://mysqltuner.com/[/url]
 >>  Run with '--help' for additional options and output filtering

-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.5.32-cll
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 1G (Tables: 2429)
[--] Data in InnoDB tables: 1G (Tables: 2119)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
[--] Data in MEMORY tables: 31M (Tables: 344)
[!!] Total fragmented tables: 264

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

-------- Performance Metrics -------------------------------------------------
[--] Up for: 4d 20h 50m 54s (140M q [333.208 qps], 2M conn, TX: 886B, RX: 21B)
[--] Reads / Writes: 76% / 24%
[--] Total buffers: 962.0M global + 3.5M per thread (300 max threads)
[OK] Maximum possible memory usage: 2.0G (25% of installed RAM)
[OK] Slow queries: 2% (3M/140M)
[OK] Highest usage of available connections: 22% (68/300)
[OK] Key buffer size / total MyISAM indexes: 512.0M/259.6M
[OK] Key buffer hit rate: 100.0% (1B cached / 159K reads)
[OK] Query cache efficiency: 82.2% (100M cached / 122M selects)
[!!] Query cache prunes per day: 2457854
[OK] Sorts requiring temporary tables: 0% (10K temp sorts / 5M sorts)
[!!] Joins performed without indexes: 23267
[OK] Temporary tables created on disk: 19% (1M on disk / 7M total)
[OK] Thread cache hit rate: 99% (68 created / 2M connections)
[!!] Table cache hit rate: 3% (5K open / 144K opened)
[OK] Open file limit used: 44% (4K/10K)
[OK] Table locks acquired immediately: 99% (38M immediate / 38M locks)
[!!] InnoDB data size / buffer pool: 1.0G/256.0M

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    Adjust your join queries to always utilize indexes
    Increase table_cache gradually to avoid file descriptor limits
Variables to adjust:
    query_cache_size (> 50M)
    join_buffer_size (> 1.0M, or always use indexes with joins)
    table_cache (> 5000)
    innodb_buffer_pool_size (>= 1G)

There is config of my.cnf

Code:

[mysqld]
default-storage-engine=MyISAM
innodb_file_per_table=1
local-infile=0

port=3306
socket=/var/lib/mysql/mysql.sock

max_connections=300
max_user_connections=30
max_connect_errors=15
key_buffer=512M
myisam_sort_buffer_size=64M
join_buffer_size=1M
read_buffer_size=1M
sort_buffer_size=1M
table_cache=10000
thread_cache_size=30M
wait_timeout=300
connect_timeout=10
max_allowed_packet=16M
query_cache_limit=1M
query_cache_size=50M
query_cache_type=1
tmp_table_size=128M
max_heap_table_size=128M
innodb_buffer_pool_size=256M
slow_query_log=1
slow_query_log_file=mysql-slow.log
long_query_time=0.1
log-queries-not-using-indexes
delayed_insert_timeout=1

[mysql.server]
user=mysql

[safe_mysqld]
err-log=/var/log/mysqld.log
pid-file=/var/lib/mysql/mysql.pid

[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash

Can you help me optimize mysql use low resource (ram, cpu) but performance still remains? Thank you.

cPanelMichael · Oct 25, 2013

Hello

I just want to point out that memory usage is not always a bad thing with Linux. There is a thread on this at:

Memory Usage Higher Than Expected

That being said, there are likely optimizations that can be made to your MySQL configuration. I will leave this thread open to allow for user feedback to your MySQL tuner results.

Thank you.

jazz1611 · Oct 26, 2013

Hi,

I have problem with mysql. When i'm trying Optimize with 2 database, got same error. Please look at below with database have 3Mb and 400Mb

/http://i.imgur.com/2C4ohPH.png
/http://i.imgur.com/2BYS7je.png

thinkbot · Oct 26, 2013

Based on that
table_cache (> 5000)
table_cache=10000

[!!] InnoDB data size / buffer pool: 1.0G/256.0M
innodb_buffer_pool_size (>= 1G)

It seems like you got different my.cnf, and didn't restart mysql with that config

Anyways correct those:

read_buffer_size=128K
sort_buffer_size=256K
thread_cache_size=50
query_cache_size=100M
innodb_buffer_pool_size=1300M

This will increase RAM usage, since you need bigger innodb buffer, but will increase a speed a bit
You got
[OK] Maximum possible memory usage: 2.0G (25% of installed RAM)

So still, not much

jazz1611 · Oct 26, 2013

I am still getting 500 error. I dont know what that? i got it when server load avarage: 0.5+ not more.

thinkbot · Oct 26, 2013

its error from apache/php
which handler you use ? mod_php, fastcgi ?
you should check apache/php error logs

jazz1611 · Oct 27, 2013

thinkbot said:
its error from apache/php
which handler you use ? mod_php, fastcgi ?
you should check apache/php error logs

It from phpmyadmin when i trying optimize database. I use Litespeed with handler suPHP.

thinkbot · Oct 27, 2013

so check litespeed error log

jazz1611 · Oct 27, 2013

Error in Processing Request
Error code: 500
Error text: Internal Error

I dont found anything error on error log. Why show that on phpmyadmin when i trying optimize database?

---------- Updated ----------

I checked and see optimize database with MyISAM is normal and fine. If with InnoDB is show error like top. Although small database InnoDB still error. Not need larger

thinkbot · Oct 27, 2013

enable php display_errors and display_startup_errors,
if you got 500 error you should have entry in logs anyways

jazz1611 · Nov 4, 2013

I'm facing I/O high load. Look at picture

/http://up9x.net/X

Code:

[email protected] [~]# ./mysqltuner.pl

 >>  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.5.32-cll
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 2G (Tables: 2483)
[--] Data in InnoDB tables: 1G (Tables: 2116)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
[--] Data in MEMORY tables: 1M (Tables: 349)
[!!] Total fragmented tables: 285

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

-------- Performance Metrics -------------------------------------------------
[--] Up for: 54m 31s (1M q [364.504 qps], 20K conn, TX: 6B, RX: 166M)
[--] Reads / Writes: 72% / 28%
[--] Total buffers: 2.0G global + 1.9M per thread (300 max threads)
[OK] Maximum possible memory usage: 2.6G (33% of installed RAM)
[OK] Slow queries: 0% (7/1M)
[OK] Highest usage of available connections: 13% (40/300)
[OK] Key buffer size / total MyISAM indexes: 512.0M/520.3M
[OK] Key buffer hit rate: 99.2% (5M cached / 43K reads)
[OK] Query cache efficiency: 86.2% (908K cached / 1M selects)
[!!] Query cache prunes per day: 852747
[OK] Sorts requiring temporary tables: 0% (72 temp sorts / 24K sorts)
[!!] Joins performed without indexes: 12
[OK] Temporary tables created on disk: 19% (5K on disk / 27K total)
[OK] Thread cache hit rate: 99% (40 created / 20K connections)
[OK] Table cache hit rate: 98% (1K open / 1K opened)
[OK] Open file limit used: 4% (1K/24K)
[OK] Table locks acquired immediately: 99% (268K immediate / 268K locks)
[OK] InnoDB data size / buffer pool: 1.1G/1.3G

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    MySQL started within last 24 hours - recommendations may be inaccurate
    Adjust your join queries to always utilize indexes
Variables to adjust:
    query_cache_size (> 100M)
    join_buffer_size (> 1.0M, or always use indexes with joins)

there is current my.cnf

Code:

[mysqld]
default-storage-engine=MyISAM
innodb_file_per_table=1
local-infile=0

port=3306
socket=/var/lib/mysql/mysql.sock

max_connections=300
max_user_connections=30
max_connect_errors=15
key_buffer=512M
myisam_sort_buffer_size=64M
join_buffer_size=1M
read_buffer_size=128K
sort_buffer_size=256K
table_cache=10000
thread_cache_size=50
wait_timeout=300
connect_timeout=30
max_allowed_packet=16M
query_cache_limit=1M
query_cache_size=100M
query_cache_type=1
tmp_table_size=128M
max_heap_table_size=128M
innodb_buffer_pool_size=1300M
slow_query_log=1
delayed_insert_timeout=1

open_files_limit=24440
[mysql.server]
user=mysql

[safe_mysqld]
err-log=mysqld.log
pid-file=/var/lib/mysql/mysql.pid

[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash

Thread starter	Similar threads	Forum	Replies	Date
S	How to optimize mysql - help	Workarounds and Optimization	2	Mar 11, 2020
S	mysql cpu usage optimize	Workarounds and Optimization	3	Dec 11, 2016
T	Nginx , Apache , Mysql , Memcache with server 4G ram. How optimize ?	Workarounds and Optimization	0	Jun 11, 2012
D	MySQL optimize help for Server	Workarounds and Optimization	0	Apr 12, 2012
S	I need to know how to Optimize Mysql.ini with my server	Workarounds and Optimization	5	Jan 27, 2012

Search

Search

MySQL Optimize For Server

jazz1611

Well-Known Member

cPanelMichael

Administrator

jazz1611

Well-Known Member

thinkbot

Well-Known Member

jazz1611

Well-Known Member

thinkbot

Well-Known Member

jazz1611

Well-Known Member

thinkbot

Well-Known Member

jazz1611

Well-Known Member

thinkbot

Well-Known Member

jazz1611

Well-Known Member