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:
|
1 2 3 4 |
# # 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
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 |
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:
|
1 2 |
apt-get update apt-get install mysql-server |
To check the status:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
/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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 |
# 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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
# 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:
|
1 |
/etc/init.d/mysql restart |
If you want to ignore databases or tables you may use the following options:
|
1 2 3 4 5 6 7 8 |
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 STATUS\G’ and ‘SHOW MASTER STATUS\G’.
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:
|
1 |
https://CAS_DOMAIN:PORT/cas/login?service=http://localhost/foo |
The 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:
|
1 2 3 4 5 |
# URL http://localhost/foo?ticket=ST-3-j6RIZfeaNTxilsFYr3xe-cas # TICKET ST-3-j6RIZfeaNTxilsFYr3xe-cas |
Now 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:
|
1 2 |
https://CAS_DOMAIN:PORT/cas/samlValidate? -> TARGET=http://localhost/foo&ticket=ST-3-j6RIZfeaNTxilsFYr3xe-cas |
the request body should be:
|
1 |
ST-3-j6RIZfeaNTxilsFYr3xe-cas |
CAS’ response should be similar to this:
|
1 2 3 4 5 6 7 8 |
http://localhost/foo juan.huerta urn:oasis:names:tc:SAML:1.0:cm:artifact |
The returned username can be found in the ‘NameIdentifier’ tag. You may find here a soapUI real screenshot to make things clearer:
Click here to the the screenshot in FULL SIZE.
See Also
Note.- special thanks to Juan Huerta, Julien Gribonvald and Marvin Addison for their tips which inspired me to write this post.