Posts Tagged ‘responce times’

How to tune MySQL Server to increase MySQL performance using mysqltuner.pl and Tuning-primer.sh

Tuesday, August 9th, 2011

MySQL Easy performance tuning with mysqltuner.pl and Tuning-primer.sh scripts

Improving MySQL performance is crucial for improving a website responce times, reduce server load and improve overall work efficiency of a mysql database server.

I’ve seen however many Linux System administrators who does belittle or completely miss the significance of tuning a newly installed MySQL server installation.
The reason behind that is probably caused by fact that many people think MySQL config variables, would not significantly improve performance and does not pay back for optimization efforts. Moreover there are a bunch of system admins who has to take care for numerous services so they don’t have time to get good knowledge to optimize MySQL servers.
Thus many admins and webmasters nowdays, think optimizations depend mostly on the side of the website programmers.
It’s also sometimes falsely believed that optimizing a MySQL server could reduce the overall server stability.

With the boom of Internet website building and internet marketing, many webmasters emerged and almost anybody with almost no knowledge on GNU/Linux or minimal or no knowledge on PHP can start his Online store, open a blog or create a website powered by some CMS like joomla.
Thus nowdays many servers even doesn’t have a hired system administrators but are managed by people whose knowledge on *Nix is almost next to zero, this is another reason why dozens of MySQL installations online are a default ones and are not taking a good advantage of the server hardware.

The incrase of website visitors leads people servers expectations for hardware also to grow, thus many companies simply buy a new hardware instead of taking the few time to investigate on how current server hardware can be utilized better.
In that manner of thought I though it will be a good idea to write this small article on Tuning mysql servers with two scripts Tuning-primer.sh and mysqltuner.pl.
The scripts are ultra easy to use and does not require only a minimal knowledge on MySQL, Linux or (*BSD *nix if sql is running on BSD).
Tuning-primer.sh and mysqltuner.pl are therefore suitable for a quick MySQL server optimizations to even people who are no computer experts.

I use this two scripts for MySQL server optimizations on almost every new configured GNU/Linux with a MySQL backend.
Use of the script comes to simply download with wget, lynx, curl or some other web client and execute it on the server host which is already running the MySQL server.

Here is an example of how simple it is to run the scripts to Optimize MySQL:

debian:~# perl mysqltuner.pl
>> MySQLTuner 1.2.0 - 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.1.49-3
[OK] Operating on 64-bit architecture

——– Storage Engine Statistics ——————————————-
[–] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[–] Data in MyISAM tables: 6G (Tables: 952)
[!!] InnoDB is enabled but isn’t being used
[!!] Total fragmented tables: 12

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

——– Performance Metrics ————————————————-
[–] Up for: 1d 2h 3m 35s (68M q [732.193 qps], 610K conn, TX: 49B, RX: 11B)
[–] Reads / Writes: 76% / 24%
[–] Total buffers: 512.0M global + 2.8M per thread (2000 max threads)
[OK] Maximum possible memory usage: 6.0G (25% of installed RAM)
[OK] Slow queries: 0% (3K/68M)
[OK] Highest usage of available connections: 7% (159/2000)
[OK] Key buffer size / total MyISAM indexes: 230.0M/1.7G
[OK] Key buffer hit rate: 97.8% (11B cached / 257M reads)
[OK] Query cache efficiency: 76.6% (46M cached / 61M selects)
[!!] Query cache prunes per day: 1822075
[OK] Sorts requiring temporary tables: 0% (1K temp sorts / 2M sorts)
[!!] Joins performed without indexes: 63635
[OK] Temporary tables created on disk: 1% (26K on disk / 2M total)
[OK] Thread cache hit rate: 99% (159 created / 610K connections)
[!!] Table cache hit rate: 4% (1K open / 43K opened)
[OK] Open file limit used: 17% (2K/16K)
[OK] Table locks acquired immediately: 99% (36M immediate / 36M 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
Increasing the query_cache size over 128M may reduce performance
Adjust your join queries to always utilize indexes
Increase table_cache gradually to avoid file descriptor limits
Variables to adjust:
query_cache_size (> 256M) [see warning above] join_buffer_size (> 256.0K, or always use indexes with joins) table_cache (> 7200)

You see there are plenty of things, the script reports, for the unexperienced most of the information can be happily skipped without need to know the cryptic output, the section of importance here is Recommendations for some clarity, I’ve made this section to show up bold.

The most imporant things from the Recommendations script output is actually the lines who give suggestions for incrase of certain variables for MySQL.In this example case this are the last three variables:
query_cache_size,
join_buffer_size and
table_cache

All of these variables are tuned from /etc/mysql/my.cnf (on Debian) and derivatives distros and from /etc/my.cnf on RHEL, CentOS, Fedora and the other RPM based Linux distributions.

On some custom server installs my.cnf is also located in /usr/local/mysql/etc/ or some other a bit more unstandard location 😉

Anyways now having the Recommendation from the script, it’s necessery to edit my.cnf and try to double the values for the suggested variables.

First, I check if all the suggested variables are existent in my config with grep , if they’re not then I’ll simply add the variable with doubled size of the suggested values.
P.S: One note here is sometimes some values which are configured, are the default value for the MySQL server and does not have a record in my.cnf

debian:~# grep -E 'query_cache_size|join_buffer_size|table_cache' /etc/mysql/my.cnf table_cache = 7200
query_cache_size = 256M
join_buffer_size = 262144

All of my variables are in the config so, now edit my.cnf and set values to:
table_cache = 14400
query_cache_size = 512M
join_buffer_size = 524288

I always, however preserve the old variable’s value, because sometimes raising the value might create problem and the MySql server might be unable to restart properly.
Thus before going with adding the new values make sure the old ones are commented with # , e.g.:
#table_cache = 7200
#query_cache_size = 256M
#join_buffer_size = 262144

I would recommend vim as editor of choice while editing my.cnf as vim completely rox 😉 If you’re not acquainted to vim use nano or mcedit or your editor of choice 😉 :

debian:~# vim /etc/mysql/my.cnf
...

Assuming that the changes are made, it’s time to restart MySQL to make sure the new values are read by the SQL server.

debian:~# /etc/init.d/mysql restart
* Stopping MySQL database server mysqld [ OK ]
* Starting MySQL database server mysqld [ OK ]
Checking for tables which need an upgrade, are corrupt or were not closed cleanly.

If mysql server fails, however to restart, make sure immediately you reverse back the changed variables to the commented values and restart once again via mysql init script to make server load.

Afterwards start adding the values one by one until find out which one is causing the mysqld to fail.

Now the second script (Tuning-primer.sh) is also really nice for MySQL performance optimizations are necessery. However it’s less portable (as it’s written in bash scripting language).
Consider running this script among different GNU/Linux distributious (especially the newer ones) might produce errors.
Tuning-primer.sh requires some minor code changes to be able to run on FreeBSD, NetBSD and OpenBSD *nices.

The way Tuning-primer.sh works is precisely like mysqltuner.pl , one runs it it gives some info about current running MySQL server and based on certain factors gives suggestions on how increasing or decresing certain my.cnf variables could reduce sql query bottlenecks, solve table locking issues as well as generally improve INSERT, UPDATE query times.

Here is an example output from tuning-primer.sh run on another server:

server:~# wget http://www.pc-freak.net/files/Tuning-primer.sh
...
server:~# sh Tuning-primer.sh
-- MYSQL PERFORMANCE TUNING PRIMER --
- By: Matthew Montgomery -

MySQL Version 5.0.51a-24+lenny5 x86_64

Uptime = 8 days 10 hrs 19 min 8 sec
Avg. qps = 179
Total Questions = 130851322
Threads Connected = 1

Server has been running for over 48hrs.
It should be safe to follow these recommendations

To find out more information on how each of these
runtime variables effects performance visit:
http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html

SLOW QUERIES
Current long_query_time = 1 sec.
You have 16498 out of 130851322 that take longer than 1 sec. to complete
The slow query log is NOT enabled.
Your long_query_time seems to be fine

MAX CONNECTIONS
Current max_connections = 2000
Current threads_connected = 1
Historic max_used_connections = 85
The number of used connections is 4% of the configured maximum.
Your max_connections variable seems to be fine.

WORKER THREADS
Current thread_cache_size = 128
Current threads_cached = 84
Current threads_per_sec = 0
Historic threads_per_sec = 0
Your thread_cache_size is fine

MEMORY USAGE
Tuning-primer.sh: line 994: let: expression expected
Max Memory Ever Allocated : 741 M
Configured Max Memory Limit : 5049 M
Total System Memory : 23640 M

KEY BUFFER
Current MyISAM index space = 1646 M
Current key_buffer_size = 476 M
Key cache miss rate is 1 / 56
Key buffer fill ratio = 90.00 %
You could increase key_buffer_size
It is safe to raise this up to 1/4 of total system memory;
assuming this is a dedicated database server.

QUERY CACHE
Query cache is enabled
Current query_cache_size = 64 M
Current query_cache_used = 38 M
Current Query cache fill ratio = 59.90 %

SORT OPERATIONS
Current sort_buffer_size = 2 M
Current record/read_rnd_buffer_size = 256.00 K
Sort buffer seems to be fine

JOINS
Current join_buffer_size = 128.00 K
You have had 111560 queries where a join could not use an index properly
You have had 91 joins without keys that check for key usage after each row
You should enable “log-queries-not-using-indexes”
Then look for non indexed joins in the slow query log.
If you are unable to optimize your queries you may want to increase your
join_buffer_size to accommodate larger joins in one pass.

TABLE CACHE
Current table_cache value = 3600 tables
You have a total of 798 tables
You have 1904 open tables.
The table_cache value seems to be fine

TEMP TABLES
Current tmp_table_size = 128 M
1% of tmp tables created were disk based
Created disk tmp tables ratio seems fine

TABLE SCANS
Current read_buffer_size = 128.00 K
Current table scan ratio = 797 : 1
read_buffer_size seems to be fine

TABLE LOCKING
Current Lock Wait ratio = 1 : 1782
You may benefit from selective use of InnoDB.

As seen from script output, there are certain variables which might be increased a bit for better SQL performance, one such variable as suggested is key_buffer_size(You could increase key_buffer_size)

Now the steps to make the tunings to my.cnf are precisely the same as with mysqltuner.pl, e.g.:
1. Preserve old config variables which will be changed by commenting them
2. Double value of current variables in my.cnf suggested by script
3. Restart Mysql server via /etc/init.d/mysql restart cmd.
4. If mysql runs fine monitor mysql performance with mtop or mytop for at least 15 mins / half an hour.

if all is fine run once again the tuning scripts to see if there are no further improvement suggestions, if there are more follow the 4 steps described procedure once again.

It’s also a good idea that these scripts are periodically re-run on the server like once per few months as changes in SQL queries amounts and types will require changes in MySQL operational variables.
The authors of these nice scripts has done great job and have saved us a tons of nerves time, downtimes and money spend on meaningless hardware. So big thanks for the awesome scripts guys 😉
Finally after hopefully succesful deployment of changes, enjoy the incresed SQL server performance 😉

How to harden Linux Security and imprpove network efficiency on Kernel sysctl Level to Stop SYN flood

Friday, July 8th, 2011

Power up Linux and protect against DDoS with sysctl var optimization

Some long time ago I’ve written an article Optimizing Linux tcp/ip networking

In the article I’ve examined a number of Linux kernel sysctl variables, which significantly improve the way TCP/IP networking is handled by a non router Linux based servers.

As the time progresses I’ve been continuing to read materials on blogs and internet sites on various tips and anti Denial of Service rules which one could apply on newly installed hosting (Apache/MySql/Qmail/Proxy) server to improve webserver responce times and tighten the overall security level.

In my quest for sysctl 😉 I found a few more handy sysctl variables apart from the old ones I incorporate on every Linux server I adminstrate.
The sysctl variables improves the overall network handling efficiency and protects about common SYN/ACK Denial of service attacks.

Here are the extra sysctl variables I started incorporating just recently:

############ IPv4 Sysctl Settings ################
#Enable ExecShield protection (randomize virtual assigned space to protect against many exploits)
kernel.randomize_va_space = 1
#Increase the number of PIDs processes could assign this is very needed especially on more powerful servers
kernel.pid_max = 65536
# Prevent against the common 'syn flood attack'
net.ipv4.tcp_syncookies = 1
# Controls the use of TCP syncookies two is generally a better idea, though you might experiment
#net.ipv4.tcp_syncookies = 1
net.ipv4.tcp_synack_retries = 2
##################################################
#
############## IPv6 Sysctl Settings ################
# Number of Router Solicitations to send until assuming no routers are present.
net.ipv6.conf.default.router_solicitations = 0
# Accept Router Preference in RA? Again not necessery if the server is not a router
net.ipv6.conf.default.accept_ra_rtr_pref = 0
# Learn Prefix Information in Router Advertisement (Unnecessery) for non-routers
net.ipv6.conf.default.accept_ra_pinfo = 0
# disable accept of hop limit settings from other routers (could be used for DoS)
net.ipv6.conf.default.accept_ra_defrtr = 0
# disable ipv6 global unicasts server assignments
net.ipv6.conf.default.autoconf = 0
# neighbor solicitations to send out per address (better if disabled)
net.ipv6.conf.default.dad_transmits = 0
# disable assigning more than 1 address per network interface
net.ipv6.conf.default.max_addresses = 1
#####################################################

 

To use this settings paste the above sysctl variables in /etc/sysctl.conf and ask sysctl command to read and apply the newly added conf settings:

server:~# sysctl -p
...

Hopefully you should not get errors while applying the sysctl settings, if you get some errors, it’s possible some of the variable is differently named (depending on the Linux kernel version) or the Linux distribution on which sysctl’s are implemented.

For some convenience I’ve created unified sysctl variables /etc/sysct.conf containing the newly variables I started implementing to servers with the ones I already exlpained in my previous post Optimizing Linux TCP/IP Networking

Here is the optimized / hardened sysctl.conf file for download

I use this exact sysctl.conf these days on both Linux hosting / VPS / Mail servers etc. as well as on my personal notebook 😉

Here is also the the complete content of above’s sysctl.conf file, just in case if somebody wants to directly copy/paste it in his /etc/sysctl.conf

# Sysctl kernel variables to improve network performance and protect against common Denial of Service attacks
# It's possible that not all of the variables are working on all Linux distributions, test to make sure
# Some of the variables might need a slight modification to match server hardware, however in most cases it should be fine
# variables list compiled by hip0
### http://www.pc-freak.net
#### date 08.07.2011
############ IPv4 Sysctl Kernel Settings ################
net.ipv4.ip_forward = 0
# ( Turn off IP Forwarding )
net.ipv4.conf.default.rp_filter = 1
# ( Control Source route verification )
net.ipv4.conf.default.accept_redirects = 0
# ( Disable ICMP redirects )
net.ipv4.conf.all.accept_redirects = 0
# ( same as above )
net.ipv4.conf.default.accept_source_route = 0
# ( Disable IP source routing )
net.ipv4.conf.all.accept_source_route = 0
# ( - || - )net.ipv4.tcp_fin_timeout = 40
# ( Decrease FIN timeout ) - Useful on busy/high load server
net.ipv4.tcp_keepalive_time = 4000
# ( keepalive tcp timeout )
net.core.rmem_default = 786426
# Receive memory stack size ( a good idea to increase it if your server receives big files )
##net.ipv4.tcp_rmem = "4096 87380 4194304"
net.core.wmem_default = 8388608
#( Reserved Memory per connection )
net.core.wmem_max = 8388608
net.core.optmem_max = 40960
# ( maximum amount of option memory buffers )
# tcp reordering, increase max buckets, increase the amount of backlost
net.ipv4.tcp_max_tw_buckets = 360000
net.ipv4.tcp_reordering = 5
##net.core.hot_list_length = 256
net.core.netdev_max_backlog = 1024
#Enable ExecShield protection (randomize virtual assigned space to protect against many exploits)
kernel.randomize_va_space = 1
#Increase the number of PIDs processes could assign this is very needed especially on more powerful servers
kernel.pid_max = 65536
# Prevent against the common 'syn flood attack'net.ipv4.tcp_syncookies = 1
# Controls the use of TCP syncookies two is generally a better idea, though you might experiment
#net.ipv4.tcp_syncookies = 1
net.ipv4.tcp_synack_retries = 2
###################################################
############## IPv6 Sysctl Settings ################
# Number of Router Solicitations to send until assuming no routers are present.
net.ipv6.conf.default.router_solicitations = 0
# Accept Router Preference in RA? Again not necessery if the server is not a router
net.ipv6.conf.default.accept_ra_rtr_pref = 0
# Learn Prefix Information in Router Advertisement (Unnecessery) for non-routersnet.
ipv6.conf.default.accept_ra_pinfo = 0
# disable accept of hop limit settings from other routers (could be used for DoS)
net.ipv6.conf.default.accept_ra_defrtr = 0
# disable ipv6 global unicasts server assignmentsnet.
ipv6.conf.default.autoconf = 0
# neighbor solicitations to send out per address (better if disabled)
net.ipv6.conf.default.dad_transmits = 0
# disable assigning more than 1 address per network interfacenet.
ipv6.conf.default.max_addresses = 1
#####################################################
# Reboot if kernel panic
kernel.panic = 20

These sysctl settings will tweaken the Linux kernel default network settings performance and you will notice the improvements in website responsiveness immediately in some cases implementing this kernel level goodies will make the server perform better and the system load might decrease even 😉

This optimizations on a kernel level are not only handy for servers, their implementation on Linux Desktop should also have a positive influence on the way the network behaves and could improve significantly the responce times of opening pages in Firefox/Opera/Epiphany Torrent downloads etc.

Hope this kernel tweakenings are helpful to someone.
Cheers 😉