Some news from the trenches.

I have a crapload of docs to prepare and upload.

• Building a loadbalanced web cluster for Moodle
• Basic Apache tuning 
• VNC server/client
• Additional Moodle tweaks
• Adding a new virtual disk to a Linux VM on ESXi

After my project goes live (mid-May 2011), I'll start working on these docs.
Til then...

-n

Some MySQL database tuning - from a non-DBA perspective.

A DBA I'm not, but that does not mean I have to accept the default configuration options for Mysql.

There are a couple scripts which can assist with tuning an existing database server, such as
a) Tuning-Primer: http://www.day32.com/MySQL/tuning-primer.sh
which handles recomendations for the following:

Slow Query Log

Max Connections

Worker Threads

Key Buffer

Query Cache

Sort Buffer

Joins

Temp Tables

Table (Open & Definition) Cache

Table Locking

Table Scans (read_buffer)

Innodb Status

b) Then, there is also the mysqltuner.pl [perl script] which essentially does the same thing. I used both with some pretty decent results in tweaking an old server.

The above mentioned scripts only work properly on a currently running setup. It does not really help in tuning a new database server, which I'm in the process of building.
Even the sample mysql config files located in the distro is limited, with the largest (my-huge.cnf) only catering to a database server with 1-2GB of memory.

My intention is to tune a dedicated database server with the following base specifications:
CentOS 5.5 64 bit installed on
HP ProLiant DL380 G7 Server
2 Intel Xeon E5620 Quad-core processors
32GB memory



There is a builtin benchmark tool with MySQL, I'd recommend running the benchmark tests before any changes are made, then run and document the results after each configuration change. Normal elevated privlieges apply - login as root.


0. Run the initial benchmark test - then copy paste (or pipe) the result into a textfile for further analysis.
cd /usr/share/sql-bench
perl run-all-tests --user='yourdbusername' --password='yourdbpassword'
Sample output - not mine, sample taken from here:
alter-table: Total time: 8 wallclock secs ( 0.02 usr 0.01 sys + 0.00 cusr 0.00 csys = 0.03 CPU)
ATIS: Total time: 2 wallclock secs ( 1.20 usr 0.09 sys + 0.00 cusr 0.00 csys = 1.29 CPU)
big-tables: Total time: 5 wallclock secs ( 2.45 usr 0.08 sys + 0.00 cusr 0.00 csys = 2.53 CPU)
connect: Total time: 50 wallclock secs (12.74 usr 4.50 sys + 0.00 cusr 0.00 csys = 17.24 CPU)
create: Total time: 31 wallclock secs ( 1.20 usr 0.44 sys + 0.00 cusr 0.00 csys = 1.64 CPU)
insert: Total time: 397 wallclock secs (97.95 usr 13.61 sys + 0.00 cusr 0.00 csys = 111.56 CPU)
select: Total time: 44 wallclock secs ( 8.71 usr 0.88 sys + 0.00 cusr 0.00 csys = 9.59 CPU)
transactions: Test skipped because the database doesn’t support transactions
wisconsin: Total time: 3 wallclock secs ( 0.91 usr 0.23 sys + 0.00 cusr 0.00 csys = 1.14 CPU)
TOTALS 562.00 123.77 19.82 143.59 3425950  

I first started off by keeping a backup of the existing /etc/my.cnf ann then using the distro's my-huge.cnf, making that my baseline config.


1. backup existing config, set new baseline and restart mysql service
mv /etc/my.cnf /etc/my.cnf.bkp
cp /usr/share/doc/mysql-server-5.0.77/my-huge.cnf /etc/my.cnf
service mysqld restart

The MySQL documentation suggests the following server disk parameters:
hdparm - which allows configuration of the disk interface.
MySQL suggests the following configuration:

2. Tuning disk parameters (reboot not necessary, but I did anyway)
hdparm -m 16 -d 1


It should be noted that I mounted the /var partition on it's own disk array to avoid disk contention.
Mysql also suggests the following mount options on the DB disks:
noatime and async

nano /etc/fstab

find your /var partition or whatever partition your db resides on, and append noatime and async after the default option:

LABEL=/var            /var                  ext3            defaults,noatime,async 1 2

Note: differnet filesystems have their own pros and cons for use of a database server. Ext3 is the middle ground in terms of performance and stability.

3. Tune additional my.cnf options (requires a restart of mysql service before changes are applied)


MySQL documentation suggests that the two most important parameters to begin tuning are:
key_buffer_size (or key_buffer on newer versions of MySQL)
table_open_cache

The key_buffer_size allows you to store the MyISAM table indexes in memory. The generally accepted rule of thumb is setting this to 25-50% of server memory on a dedicated database server.
nano /etc/my.cnf
find key_buffer_size (or key_buffer)
adjust to suggested value above with the following syntax
key_buffer_size = 16384M

The table_open_cache (or table_cache) is related to the max_connections configuration. Tuning this parameter relies on obtaining information from the running setup and tweaking accordingly.
Tips on tuning this and other parameters can be found here: http://www.databasejournal.com/features/mysql/article.php/3367871/Optimizing-the-mysqld-variables.htm


Remember to run the benchmark after each change - analysis of the results should be fun.

-n

Mysql - checking read/write ratio

At some point during database tuning, it becomes necessary to check the amount of reads and write transactions to determine the best way forward.

In order to check these stats, you need to login as the Mysql admin. Then execute the following query:

show global status like 'Com%'; 

* These stats are only gathered for the duration the mysql service is running. All data will be reset if the service is restarted.

Expected output:
In this case, the read:write ratio is quite high, selects are 28 million; other write based operations add up to approx 11 million.
                         
+--------------------------+----------+
| Variable_name            | Value    |
+--------------------------+----------+
| Com_admin_commands       | 1        |
| Com_alter_db             | 0        |
| Com_alter_table          | 164      |
| Com_analyze              | 4        |
| Com_backup_table         | 0        |
| Com_begin                | 0        |
| Com_call_procedure       | 0        |
| Com_change_db            | 6903942  |
| Com_change_master        | 0        |
| Com_check                | 0        |
| Com_checksum             | 0        |
| Com_commit               | 0        |
| Com_create_db            | 0        |
| Com_create_function      | 0        |
| Com_create_index         | 0        |
| Com_create_table         | 246      |
| Com_create_user          | 0        |
| Com_dealloc_sql          | 0        |
| Com_delete               | 68561    |
| Com_delete_multi         | 0        |
| Com_do                   | 0        |
| Com_drop_db              | 0        |
| Com_drop_function        | 0        |
| Com_drop_index           | 0        |
| Com_drop_table           | 246      |
| Com_drop_user            | 0        |
| Com_execute_sql          | 0        |
| Com_flush                | 26       |
| Com_grant                | 0        |
| Com_ha_close             | 0        |
| Com_ha_open              | 0        |
| Com_ha_read              | 0        |
| Com_help                 | 0        |
| Com_insert               | 1859347  |
| Com_insert_select        | 3288     |
| Com_kill                 | 0        |
| Com_load                 | 328      |
| Com_load_master_data     | 0        |
| Com_load_master_table    | 0        |
| Com_lock_tables          | 26       |
| Com_optimize             | 4        |
| Com_preload_keys         | 0        |
| Com_prepare_sql          | 0        |
| Com_purge                | 0        |
| Com_purge_before_date    | 0        |
| Com_rename_table         | 0        |
| Com_repair               | 6804     |
| Com_replace              | 0        |
| Com_replace_select       | 0        |
| Com_reset                | 0        |
| Com_restore_table        | 0        |
| Com_revoke               | 0        |
| Com_revoke_all           | 0        |
| Com_rollback             | 0        |
| Com_savepoint            | 0        |
| Com_select               | 29848728 |
| Com_set_option           | 6755821  |
| Com_show_binlog_events   | 0        |
| Com_show_binlogs         | 0        |
| Com_show_charsets        | 0        |
| Com_show_collations      | 0        |
| Com_show_column_types    | 0        |
| Com_show_create_db       | 0        |
| Com_show_create_table    | 0        |
| Com_show_databases       | 0        |
| Com_show_errors          | 0        |
| Com_show_fields          | 760708   |
| Com_show_grants          | 0        |
| Com_show_innodb_status   | 0        |
| Com_show_keys            | 0        |
| Com_show_logs            | 0        |
| Com_show_master_status   | 0        |
| Com_show_ndb_status      | 0        |
| Com_show_new_master      | 0        |
| Com_show_open_tables     | 0        |
| Com_show_privileges      | 0        |
| Com_show_processlist     | 0        |
| Com_show_slave_hosts     | 0        |
| Com_show_slave_status    | 0        |
| Com_show_status          | 3        |
| Com_show_storage_engines | 0        |
| Com_show_tables          | 65       |
| Com_show_triggers        | 0        |
| Com_show_variables       | 26       |
| Com_show_warnings        | 0        |
| Com_slave_start          | 0        |
| Com_slave_stop           | 0        |
| Com_stmt_close           | 0        |
| Com_stmt_execute         | 0        |
| Com_stmt_fetch           | 0        |
| Com_stmt_prepare         | 0        |
| Com_stmt_reset           | 0        |
| Com_stmt_send_long_data  | 0        |
| Com_truncate             | 3048     |
| Com_unlock_tables        | 26       |
| Com_update               | 2871820  |
| Com_update_multi         | 2540     |
| Com_xa_commit            | 0        |
| Com_xa_end               | 0        |
| Com_xa_prepare           | 0        |
| Com_xa_recover           | 0        |
| Com_xa_rollback          | 0        |
| Com_xa_start             | 0        |
| Compression              | OFF      |
+--------------------------+----------+
104 rows in set (0.00 sec)

Choosing the right scheduling algorithm for a Linux Based Load Balancer - Part II

This is a continuation of an earlier post:
Choosing the right scheduling algorithm for a Linux Based Load Balancer

This is the current scenario:

• Two identical webservers, mirrored hardware and software specifications.
• The main variable is physical location of the second webserver, but it is located on the same physical network but a different building.
• Network traffic may or may not be an issue.

Based on some initial tests, (scale testing is almost irrelevant for these algorithms ), I have decided to implement the Weighted Least Connection. It seems the most appropriate at this point.

Weighted Least-Connections (default)

Distributes more requests to servers with fewer active connections relative to their capacities.
Capacity is indicated by a user-assigned weight, which is then adjusted upward or
downward by dynamic load information. The addition of weighting makes this algorithm
ideal when the real server pool contains hardware of varying capacity.

-n

Choosing the right scheduling algorithm for a Linux Based Load Balancer

I'm currently doing some research into choosing the right scheduling algorithm for a Linux based Load balancer for a dual front end moodle installation.
The actual loadbalancer implementation will be detailed later.

This particular post will be to outline the scheduling algorithms possible on ipvs and which would be the best option for my particular scenario. The assumption in my case is that both webservers are identical, from hardware specifications to OS to php code.

From the CentOS Documentation on IPVS Scheduling Algorithms:

Round-Robin Scheduling
Distributes each request sequentially around the pool of real servers. Using this algorithm,
all the real servers are treated as equals without regard to capacity or load. This scheduling
model resembles round-robin DNS but is more granular due to the fact that it is networkconnection
based and not host-based. LVS round-robin scheduling also does not suffer the
imbalances caused by cached DNS queries.

Weighted Round-Robin Scheduling
Distributes each request sequentially around the pool of real servers but gives more jobs to
servers with greater capacity. Capacity is indicated by a user-assigned weight factor, which
is then adjusted upward or downward by dynamic load information.
Weighted round-robin scheduling is a preferred choice if there are significant differences in
the capacity of real servers in the pool. However, if the request load varies dramatically, the
more heavily weighted server may answer more than its share of requests.

Least-Connection
Distributes more requests to real servers with fewer active connections. Because it keeps
track of live connections to the real servers through the IPVS table, least-connection is a
type of dynamic scheduling algorithm, making it a better choice if there is a high degree of
variation in the request load. It is best suited for a real server pool where each member
node has roughly the same capacity. If a group of servers have different capabilities,
weighted least-connection scheduling is a better choice.

Weighted Least-Connections (default)
Distributes more requests to servers with fewer active connections relative to their capacities.
Capacity is indicated by a user-assigned weight, which is then adjusted upward or
downward by dynamic load information. The addition of weighting makes this algorithm
ideal when the real server pool contains hardware of varying capacity.

Locality-Based Least-Connection Scheduling
Distributes more requests to servers with fewer active connections relative to their destination
IPs. This algorithm is designed for use in a proxy-cache server cluster. It routes the
packets for an IP address to the server for that address unless that server is above its capacity
and has a server in its half load, in which case it assigns the IP address to the least
loaded real server.

Locality-Based Least-Connection Scheduling with Replication Scheduling
Distributes more requests to servers with fewer active connections relative to their destination
IPs. This algorithm is also designed for use in a proxy-cache server cluster. It differs
from Locality-Based Least-Connection Scheduling by mapping the target IP address to a
subset of real server nodes. Requests are then routed to the server in this subset with the
lowest number of connections. If all the nodes for the destination IP are above capacity, it
replicates a new server for that destination IP address by adding the real server with the
least connections from the overall pool of real servers to the subset of real servers for that
destination IP. The most loaded node is then dropped from the real server subset to prevent
over-replication.

Destination Hash Scheduling
Distributes requests to the pool of real servers by looking up the destination IP in a static
hash table. This algorithm is designed for use in a proxy-cache server cluster.
Source Hash Scheduling
Distributes requests to the pool of real servers by looking up the source IP in a static hash
table. This algorithm is designed for LVS routers with multiple firewalls.

I'm currently doing some testing with one or two of the more viable options and will follow up with my choice (and why I chose it).

Part II here.
-n

Using the Linux diff command

I need to do a Moodle minor version upgrade(1.9.x to 1.9.y), but my basecode is highly customized. For a vanilla installation of moodle, an upgrade is very straightforward - but how much moodle installs are actually vanilla?

What I intend to do is a side-by-side comparison to check from a file standpoint the differences between the updated moodle core and my customized version.

0. copy production moodle code to a test machine (if possible)

1. on test machine download latest version of moodle from www.moodle.org and extract to a folder in tmp

2. run the following command:
diff -qry /path/to/current/code /path/to/downloaded/code > /pipe/to/textfile.txt

3. Have fun comparing files. I recommending opening the file in a spreadsheet editor.

Cheers,
-n

Find and Delete certain files in CentOS Linux

This command can be scripted (needs to be run as root), but this script is not covered here.

Basically what this command does is search a specified directory for a name/filetype and then delete. 
If you run it from the top level directory (/), chances are something important may get deleted. Be sure to specify your folder, it will traverse subfolders.

My particular reason for using this is I need to clean up over 3000 courses in Moodle and remove any old course backups that were used for creating course copies by the site admin. This involves sorting through tens of thousands of folders for course backups.

Bear in mind that this action cannot be undone, so if Lecturers/Teachers manage their own Moodle course backups, this script will not work for you. As always, you should have backups of all necessary files before attempting this sort of activity.


In order to ensure that no unnecessary files get deleted, run the command without the delete section.
find /path/to/your/folder -name \*backup\*.zip

*edit: to pipe the list including the size of the files to a textfile, please see below:
find /path/to/your/folder -size +5k -name \*searchstring\*.zip -exec ls -lh {} \; | awk '{ print $9 ": " $5 }' > /temp/report.txt

much thanks to http://www.cyberciti.biz/faq/find-large-files-linux/ for the size additionA list of files matching your search string will be displayed.






Breakdown:
find - Linux search command
/path/to/your/folder/ - self explanatory
-name - tells the command to search for specific filenames
\*backup\*.zip - search string, will return the following:

mybackup.zip
mybackup2001.zip
backup2002.zip

will not return:

backup.txt
mybackup.doc
backup1.log

 | xargs -   allows commands to be run from previous query/command
/bin/rm -f - delete command


Now for the cleanup:

find /path/to/your/folder -name \*backup\*.zip | xargs /bin/rm -f

Congrats! All your base are belong to us..err wait, I meant the files should have been deleted.
Rerun the original find command with your search string and it should no longer return any results.



Cheers,
-n