This article explains how MySQL settings can be optimized following a set of predetermined steps. The results are pretty reliable and we keep using this with a very high degree of success. This is something regularly done at Leanservers.
Category Archives: linux
Quick and dirty fix for VMware Linux guests loosing clock accuracy
I covered on a previous post how to keep the clock synchronized for VMware Linux guest(s). Well this seems to not work at least for recent versions VMware Server 2 (i.e. the one with web based management console). For now the quick& dirty solution I am using is putting a cron job that executes ntpdate pretty often…
My cron job looks like this:
# # Temporary fix for the time getting lost # 0-59/10 * * * * /usr/sbin/ntpdate north-america.pool.ntp.org > /dev/null 2>1
Yes, this fix requires to have NTPDATE installed (apt-get install ntpdate under Debian).
http_load man page
http_load(1) http_load(1) NAME http_load - multiprocessing http test client SYNOPSIS http_load [-checksum] [-throttle] [-proxy host:port] [-verbose] [-timeout secs] [-sip sip_file] [-cipher str] ( -paral- lel N | -rate N [-jitter] ) ( -fetches N | -seconds N ) url_file DESCRIPTION http_load runs multiple http fetches in parallel, to test the throughput of a web server. However unlike most such test clients, it runs in a single process, so it doesn't bog down the client machine. It can be configured to do https fetches as well. The -checksum flag tells http_load to do checksums on the files fetched, to make sure they came across ok. The check- sums are computed the first time each URL gets fetched, and then recomputed and compared on each subsequent fetch. Without the -checksum flag only the byte count is checked. The -throttle flag tells http_load to throttle its consumption of data to 33.6Kbps, to simulate access by modem users. The -proxy flag lets you run http_load through a web proxy. The -verbose flag tells http_load to put out progress reports every minute on stderr. The -timeout flag specifies how long to wait on idle connections before giving up. The default is 60 seconds. The -sip flag lets you specify a file containing numeric IP addresses (not hostnames), one per line. These get used randomly as the *source* address of connections. They must be real routable addresses on your machine, created with ifconfig, in order for this to work. The advantage of using this option is you can make one client machine look like a whole bank of machines, as far as the server knows. The -cipher flag is only available if you have SSL support compiled in. It specifies a cipher set to use. By default, http_load will negotiate the highest security that the server has available, which is often higher (and slower) than typical browsers will negotiate. An example of a cipher set might be "RC4-MD5" - this will run considerably faster than the default. In addition to specifying a raw cipher string, there are three built-in cipher sets accessible by keywords: * fastsec - fast security - RC4-MD5 * highsec - high security - DES-CBC3-SHA * paranoid - ultra high security - AES256-SHA Of course, not all servers are guaranteed to implement these combinations. One start specifier, either -parallel or -rate, is required. -parallel tells http_load to keep that many parallel fetches going simultaneously. -rate tells http_load to start that many new connections each second. If you use the -rate start specifier, you can also give the -jitter flag, telling http_load to vary the rate randomly by about 10%. One end specifier, either -fetches or -seconds, is required. -fetches tells http_load to quit when that many fetches have been completed. -seconds tells http_load to quit after that many seconds have elapsed. The url_file is just a list of URLs, one per line. The URLs that get fetched are chosen randomly from this file. All flags may be abbreviated to a single letter. Note that while the end specifier is obeyed precisely, the start specifier is only approximate. If you use the -rate flag, http_load will make its best effort to start connections at that rate, but may not succeed. And if you use the -parallel flag, http_load will attempt to keep that many simultaneous connections going, but may fail to keep up if the server is very fast. Sample run: % http_load -rate 2 -seconds 300 urls 591 fetches, 8 max parallel, 5.33606e+06 bytes, in 300 seconds 9028.87 mean bytes/connection 1.97 fetches/sec, 17786.9 bytes/sec msecs/connect: 28.8932 mean, 44.243 max, 24.488 min msecs/first-response: 63.5362 mean, 81.624 max, 57.803 min HTTP response codes: code 200 -- 591 SEE ALSO http_ping(1) AUTHOR Copyright (C) 1998,1999,2001 by Jef Poskanzer . All rights reserved. 15 November 2001 http_load(1)
How to install MySQL Server on Debian Linux
While installing MySQL Server it is always good to keep in mind that the logs and data folders will potentially have a big size. By default MySQL keeps them in the root mount point (i.e. ‘/’). That may cause your database server system disk to get full, which is never a good idea.
This article describes how to move these two folders to ‘/home’ which is ideally mounted into another disk and has enough space to keep your database data and logs.
First, I install the required apt-get packages as follows:
apt-get update apt-get install mysql-server
To check the status:
/etc/init.d/mysql status /usr/bin/mysqladmin Ver 8.41 Distrib 5.0.51a, for debian-linux-gnu on i486 Copyright (C) 2000-2006 MySQL AB This software comes with ABSOLUTELY NO WARRANTY. This is free software, and you are welcome to modify and redistribute it under the GPL license Server version 5.0.51a-24 Protocol version 10 Connection Localhost via UNIX socket UNIX socket /var/run/mysqld/mysqld.sock Uptime: 3 sec Threads: 1 Questions: 78 Slow queries: 0 Opens: 23 Flush tables: 1 Open tables: 17 Queries per second avg: 26.000.
Now, stop MySQL, move the folders to the right location, reconfigure MySQL and start again:
# Stop MySQL /etc/init.d/mysql stop # Move and reconfigure data mkdir /home/mysql mv /var/lib/mysql /home/mysql/mysql-data ln -s /home/mysql/mysql-data/ /var/lib/mysql # Move and reconfigure logs mv /var/log/mysql/ /home/mysql/mysql-logs ln -s /home/mysql/mysql-logs/ /var/log/mysql # Start MySQL and check that everything is OK /etc/init.d/mysql start /etc/init.d/mysql status /usr/bin/mysqladmin Ver 8.41 Distrib 5.0.51a, for debian-linux-gnu on i486 Copyright (C) 2000-2006 MySQL AB This software comes with ABSOLUTELY NO WARRANTY. This is free software, and you are welcome to modify and redistribute it under the GPL license Server version 5.0.51a-24 Protocol version 10 Connection Localhost via UNIX socket UNIX socket /var/run/mysqld/mysqld.sock Uptime: 14 sec Threads: 1 Questions: 78 Slow queries: 0 Opens: 23 Flush tables: 1 Open tables: 17 Queries per second avg: 5.571.
These are some settings that I usually put on the /etc/mysql/my.cnf configuration file:
# Here you can see queries with especially long duration log_slow_queries = /var/log/mysql/mysql-slow.log long_query_time = 1 log-queries-not-using-indexes # A server-id unique server-id = 177 log-bin = /var/log/mysql/mysql-bin.log log-bin-index = /var/log/mysql/mysql-bin.log innodb_file_per_table # Unique log names (this prevents replication breaking upon hostname change :-) relay-log = iamalsounique98127-relay-bin relay-log-index = iamalsounique98127-relay-bin # Taking care of the auto-increment values (for multi-master replication) auto_increment_increment = 10 auto_increment_offset = 1
For these changes to take effect, you would need to restart MySQL:
/etc/init.d/mysql restart
If you want to ignore databases or tables you may use the following options:
binlog_ignore_db = information_schema replicate_ignore_db = information_schema binlog_ignore_db = mysql replicate_ignore_db = mysql # Ignore all the cache* tables which have caused DUPLICATE # ENTRY issues. Unai. replicate_wild_ignore_table = exampledb.cache%
Having ‘binlog_ignore_db’ is enough to exclude databases from replication BUT having ‘replicate_ignore_db’ as well will make things clearer since the databases that are being ignored will appear in both the ‘SHOW SLAVE STATUSG’ and ‘SHOW MASTER STATUSG’.
How to test CAS’ SAML using soapUI
Overview
Recent versions (I believe 3.2 or older) of Central Authentication System (a.k.a. CAS) include Security Assertion Markup Language (a.k.a. SAML) support, out of the box. The beauty if it is that it is already “there” accessible through the URL ‘/cas/samlValidate’ instead of the usual ‘/cas/serviceValidate’.
One thing to be noted is that it is not so easy to communicate with your CAS instance using SAML protocol since the requests need to be HTTP POST (which put browsers out of the picture) using a properly formed SAML payload.
Here is when soapUI comes in, which is an excellent tool for web services testing using SOAP requests (there should not be any problem/limitation by using the open source version of the tool) since it can be used to complete the SAML communication and see what the CAS server is actually returning back.
Steps
So, in order to complete that, you would need to connect to your CAS server, login by providing valid credentials and then get a CAS ticket. This can be done by opening the following URL on a browser:
https://CAS_DOMAIN:PORT/cas/login?service=http://localhost/fooThe browser should be now displaying an error because it should have been redirected back to the URL http://localhost/foo which probably does not exist. No problem. What is important though is that you would be able to retrieve the ticket from the URL. Example:
# URL http://localhost/foo?ticket=ST-3-j6RIZfeaNTxilsFYr3xe-cas # TICKET ST-3-j6RIZfeaNTxilsFYr3xe-casNow using SoapUI you need to send CAS a proper SAML request. You may do that using the “submit a request to a specified end point” action. The URL where to send the request should be:
https://CAS_DOMAIN:PORT/cas/samlValidate? -> TARGET=http://localhost/foo&ticket=ST-3-j6RIZfeaNTxilsFYr3xe-casthe request body should be:
ST-3-j6RIZfeaNTxilsFYr3xe-casCAS’ response should be similar to this:
http://localhost/foo juan.huerta urn:oasis:names:tc:SAML:1.0:cm:artifactThe returned username can be found in the ‘NameIdentifier’ tag.
See Also
Note.- special thanks to Juan Huerta, Julien Gribonvald and Marvin Addison for their tips which inspired me to write this post.
How to define shorewall rules to allow VRRP traffic
It is essential for routers that implement the Virtual Router Redundancy Protocol to be able to communicate with each other.
As the protocol defines, the master router needs to send multicast packets to the whole subnet and of course, the rest of the backup routers need to receive this announcements otherwise they will think that the master router is dead and will initiate an election of a master router.
If no router is able to receive this “multicasted” announcements they all will eventually think that they are the only ones alive and thus become master. All of them master. That brings networking issues.
This page covers how to define the rule(s) under shorewall firewall in order to allow this VRRP announcements pass-through.
Rule definition
VRRP’s announcement multicast packets have the following characteristics:
- They are sent to the following multicast IP address: 224.0.0.18
- They use the protocol vrrp
- They source IP address is a virtual router
Thus, a rule that allows all the incoming VRRP traffic would look like this:
ACCEPT net fw:224.0.0.18 vrrp
A rule that allows VRRP packets from a specific router would look like this:
ACCEPT net:OTHER_VIRTUAL_ROUTER_IP fw:224.0.0.18 vrrp
Example
Let’s imagine we have two routers implementing VRRP (using keepalived, for example). Their IPs are: 10.20.30.40 and 10.20.30.41. Their shorewall rules should include the following:
on 10.20.30.40:
ACCEPT net:10.20.30.41 fw:224.0.0.18 vrrp
on 10.20.30.41:
ACCEPT net:10.20.30.40 fw:224.0.0.18 vrrp
References
KeepAlived Installation under Debian Etch
Briefly, KeepAlived is a daemon that is able to provide failover capabilities to servers/services by binding virtual IP addresses to machines. In the event of failure, KeepAlived would reassign this virtual IP to another machine. This action is executed fast (less than 2 seconds) and automatically.
This is a very interesting daemon to be used in combination with HAProxy, for example. It would be possible to have a failovered load balancer. In the event of this load balancer failing, keepalived would switch to another that is up and running in such a clean and fast way that the clients would not notice.
Installation steps under Debian Etch
apt-get update apt-get install keepalived
The system will ask a couple of questions. I usually reply using the default values, then configure myself manually the daemon, by editing /etc/keepalived/keepalived.conf.
To make the virtual IP address bindable, you should add this line /etc/sysctl.conf:
net.ipv4.ip_nonlocal_bind=1
Check binding:
sysctl -p net.ipv4.ip_nonlocal_bind = 1
It is convenient to alter the order when keepalived is being started upon restarts. We probably want to have it started at the end so all the services are already running by the time keepalive runs. To do that:
update-rc.d -f keepalived remove Removing any system startup links for /etc/init.d/keepalived ... /etc/rc0.d/K20keepalived /etc/rc1.d/K20keepalived /etc/rc2.d/S20keepalived /etc/rc3.d/S20keepalived /etc/rc4.d/S20keepalived /etc/rc5.d/S20keepalived /etc/rc6.d/K20keepalived update-rc.d keepalived defaults 90 Adding system startup for /etc/init.d/keepalived ... /etc/rc0.d/K90keepalived -> ../init.d/keepalived /etc/rc1.d/K90keepalived -> ../init.d/keepalived /etc/rc6.d/K90keepalived -> ../init.d/keepalived /etc/rc2.d/S90keepalived -> ../init.d/keepalived /etc/rc3.d/S90keepalived -> ../init.d/keepalived /etc/rc4.d/S90keepalived -> ../init.d/keepalived /etc/rc5.d/S90keepalived -> ../init.d/keepalived
See Also
Having HAProxy check mysql status through a xinetd script
HAProxy is able to load balance MySQL wonderfully. The main issue is how to make sure that the backend MySQL server to forward the request to is up and running (I mean not just to establish a connection to port 3306, I mean something more “complete”, that performs a little operation against the MySQL server).
It is possible to make haproxy check the status of a mysql server using a small shell script managed through the xinetd daemon.
What this script basically does is performs a basic operation against the mysql database then returns http status 200 if the operation was successful or http status 500 if it there was any error (i.e. mysql was not available).
Script
The script looks like this:
#!/bin/bash # # This script checks if a mysql server is healthy running on localhost. It will # return: # # "HTTP/1.x 200 OKr" (if mysql is running smoothly) # # - OR - # # "HTTP/1.x 500 Internal Server Errorr" (else) # # The purpose of this script is make haproxy capable of monitoring mysql properly # # Author: Unai Rodriguez # # It is recommended that a low-privileged-mysql user is created to be used by # this script. Something like this: # # mysql> GRANT SELECT on mysql.* TO 'mysqlchkusr'@'localhost' # -> IDENTIFIED BY '257retfg2uysg218' WITH GRANT OPTION; # mysql> flush privileges; MYSQL_HOST="localhost" MYSQL_PORT="3306" MYSQL_USERNAME="mysqlchkusr" MYSQL_PASSWORD="secret" TMP_FILE="/tmp/mysqlchk.out" ERR_FILE="/tmp/mysqlchk.err" # # We perform a simple query that should return a few results :-p # /usr/bin/mysql --host=$MYSQL_HOST --port=$MYSQL_PORT --user=$MYSQL_USERNAME --password=$MYSQL_PASSWORD -e"show databases;" > $TMP_FILE 2> $ERR_FILE # # Check the output. If it is not empty then everything is fine and we return # something. Else, we just do not return anything. # if [ "$(/bin/cat $TMP_FILE)" != "" ] then # mysql is fine, return http 200 /bin/echo -e "HTTP/1.1 200 OKrn" /bin/echo -e "Content-Type: Content-Type: text/plainrn" /bin/echo -e "rn" /bin/echo -e "MySQL is running.rn" /bin/echo -e "rn" else # mysql is fine, return http 503 /bin/echo -e "HTTP/1.1 503 Service Unavailablern" /bin/echo -e "Content-Type: Content-Type: text/plainrn" /bin/echo -e "rn" /bin/echo -e "MySQL is *down*.rn" /bin/echo -e "rn" fi
Steps on the MySQL server
First, you should create the script somewhere, and assign proper permissions:
chown nobody /opt//mysqlchk chmod 744 /opt//mysqlchk
Then, set permissions into the mysql server:
mysql> GRANT SELECT on mysql.* TO 'mysqlchkusr'@'localhost' -> IDENTIFIED BY 'secret' WITH GRANT OPTION; mysql> flush privileges; mysql> exit
Test:
/opt/mysqlchk HTTP/1.x 200 OK
Now, configure xinetd by adding this line at the bottom of /etc/services:
mysqlchk 9200/tcp # mysqlchk
Then add this file /etc/xinetd.d/mysqlchk:
# default: on # description: mysqlchk service mysqlchk { flags = REUSE socket_type = stream port = 9200 wait = no user = nobody server = /opt/mysqlchk log_on_failure += USERID disable = no only_from = 0.0.0.0/0 # recommended to put the IPs that need # to connect exclusively (security purposes) per_source = UNLIMITED # Recently added (May 20, 2010) # Prevents the system from complaining # about having too many connections open from # the same IP. More info: # http://www.linuxfocus.org/English/November2000/article175.shtml }
Restart xinetd (you can watch for issues on /var/log/syslog):
/etc/init.d/xinetd stop /etc/init.d/xinetd start
Test:
telnet localhost 9200 Trying 127.0.0.1... Connected to localhost.localdomain. Escape character is '^]'. HTTP/1.1 200 OK Content-Type: Content-Type: text/plain MySQL is running. Connection closed by foreign host.
Steps on the HAProxy server
Now, in order to make haproxy check the status of the mysql service through the xinetd-managed-script, we should add something similar to this on the haproxy.cfg file:
listen MySQL 10.135.2.67:3306 mode tcp option httpchk server 10.135.2.69:3306 10.135.2.69:3306 check port 9200 inter 12000 rise 3 fall 3 source 10.135.2.67
What is important?
- option httpchk.- tells haproxy to check for full http response (i.e. http headers: 2xx OK or 5xx ERROR)
- check port XXXX.- tells haproxy to check the status of the service by sending an http request on that port
How to install NAGIOS NRPE plugin under Debian Linux
NRPE allows you to remotely execute Nagios plugins on other Linux/Unix machines. This allows you to monitor remote machine metrics (disk usage, CPU load, etc.). NRPE can also communicate with some of the Windows agent addons, so you can execute scripts and check metrics on remote Windows machines as well. Citation.
You may follow the steps to install NRPE in any of the following ways:
1) Steps (compiling from sources)
First, you should download the latest NRPE version from HERE.
Then, install some required packages:
apt-get update apt-get install build-essential libssl-dev
Unpack the NRPE addons, configure and install:
cd /opt tar xvfz nrpe-2.12.tar.gz cd nrpe-2.12 ./configure --enable-command-args make all make install-plugin
2) Steps (using apt binaries)
apt-get update apt-get install nagios-nrpe-plugin
Invocation
NRPE can now be invoked using the following:
/usr/lib/nagios/plugins/check_nrpe
Another option would be to create a symlink to make the invocation easier:
ln -s /usr/lib/nagios/plugins/check_nrpe /usr/bin/check_nrpe
Thus:
check_nrpe
HAProxy hot-reconfiguration
As of version 1.2.8, a new soft-reconfiguration mechanism has been introduced.
It is now possible to “pause” all the proxies by sending a SIGTTOU signal to
the processes. This will disable the listening socket without breaking existing
connections. After that, sending a SIGTTIN signal to those processes enables
the listening sockets again. This is very useful to try to load a new
configuration or even a new version of haproxy without breaking existing
connections. If the load succeeds, then simply send a SIGUSR1 which will make
the previous proxies exit immediately once their sessions are closed ; and if
the load fails, then simply send a SIGTTIN to restore the service immediately.
Please note that the ‘grace’ parameter is ignored for SIGTTOU, as well as for
SIGUSR1 when the process was in the pause mode. Please also note that it would
be useful to save the pidfile before starting a new instance.The ‘-st’ and ‘-sf’ command line options are used to inform previously running
processes that a configuration is being reloaded. They will receive the SIGTTOU
signal to ask them to temporarily stop listening to the ports so that the new
process can grab them. If anything wrong happens, the new process will send
them a SIGTTIN to tell them to re-listen to the ports and continue their normal
work. Otherwise, it will either ask them to finish (-sf) their work then softly
exit, or immediately terminate (-st), breaking existing sessions. Citation.
Procedure
The command to be issued to restart HAProxy gracefully would be:
haproxy -f configfile -sf
Example (added the PID location):
haproxy -f /etc/haproxy/haproxy.cfg -p /var/run/haproxy.pid -sf $(cat /var/run/haproxy.pid)
References