NTFS support on Centos 5.x

If you have a Removable memory device (USB Memory stick, USB Hard drive) that is formatted in NTFS, it may not be able to mount on Centos by default.

Just a couple pacakges are needed to automagically detect and mount NTFS-formatted drives.

0. Login as root


1. Update then Install packages
yum -y update
yum install fuse fuse-ntfs-3g


2. Plugin your drive and it will auto-mount and display in a little window.


-n

Installing Wine on CentOS linux

Wine is a linux application that allows installation and execution of some native Windows apps. It is not included in the standard CentOS distro, so this is a simple installation tutorial.

0. Either Sudo or login as root on the machine

1. Get the rpmforge rpm (if not already installed)

cd /tmp
wget http://apt.sw.be/redhat/el5/en/i386/rpmforge/RPMS/rpmforge-release-0.3.6-1.el5.rf.i386.rpm

2. Install the rpmforge rpm
rpm -Uvh rpmforge-release-0.3.6-1.el5.rf.i386.rpm

3. Install wine
yum install wine -y enablerepo=rpmforge
 
4. Launch Wine from the Centos Applications menu


-n

Build your own updated Moodle Schema

This particular post is to assist in getting a Moodle DB schema specific to your database. It is especially handy if there are non-standard plugins installed on Moodle.

As mentioned in an earlier post, getting a relevant ERD on Moodle.org is almost herculian. This tool will reverse engineer the databse ERD.

The application is Fabforce4 located at http://www.fabforce.net/dbdesigner4/

Installation on Windows is straightforward, simply download, install and configure to point to your database.

Installation on CentOS linux was not as good. The application refused to launch. I decided to run it on Wine (which I had to install here).

I'll update with a sample schema of my test database as soon as the operation completes.

-n

Moodle Issues after an upgrade! [Resolved!]

Part of my project in migrating to a load-balanced virtualized infrastructure involved running a minor version upgrade from 1.9.7 to 1.9.11 which addressed several bugs in our core Moodle usage. The project plan included checking the database before and after for corruption as well as re-indexing using mysqlcheck and myisamchk respectively, and no errors were detected, both in the Test environment as well as during the upgrade in the production environment. Basic checks were done post upgrade to ensure core moodle functionality. To my surprise (read: shock, horror, pain), two weeks after the upgrade, two major issues were reported by several lecturers:
    1. Quiz attempts were not being saved, where the quiz spanned multiple pages.
    2. Assignments (Advanced Uploading of Files) could not be submitted.

Tracking down this issue took some time, and since so many variables changed from the original environment and so much time had elapsed since the upgrade, reverting was not an option.
The order of troubleshooting included:

•     Determining if the course/activity settings were at fault.
•     Turning on Debugging to check for errors upon avtivity submits.
•     Determining if my (tested) load balancer environment was causing the issue.
•     Checking the new webserver configuration
•     Checking the database configuration
•     Checking and rechecking  and attempting to repair the database for corruption using variations of switches on the respective tools (mysqlcheck and myisamchk)
•     Checking if caching was causing the issue (disabling memcached)

I then decided to try two different tests:

•     Parallel installation of moodle on the same new environment/hardware.
•     Try the current database on different hardware

The first test required using a copy of the existing moodle code, but on a fresh database. To my surprise (read: temporary relief), the environment was fine. The empty database with a test course worked perfectly. The next logical step assumed that the database may be the issue, as the previous test used the same moodle code, effectively eliminating it as the root cause.
The second test confirmed this. I migrated the same Moodle code plus a copy of the database to a clean server and the problem was replicated. At this point the epic horror dawned upon me on the possibilities ahead as visions of rebuilding from scratch haunted me.

At this point, only two variables needed investigating:

•     Database Indexes
•     Database Data/Structure

In my test environment (where the problem was replicated), I attempted to perform some last ditch attempts to recover the environment. Firstly, I attempted to strip all the indexes from the database and use the mysiamchk tool to rebuild.
I took a backup of the database (without indexes) using the following code:
mysqlhotcopy -u root -p yourpassword --addtodest --noindices db_name /path/to/backup/destination/
I then used the MYSQL restore method in my earlier post to restore the database to the test environment.

Since the indexes had to be rebuilt using myisamchk, it required the mysql service to be stopped. The indexes were rebuilt using:
myisamchk -rq /var/lib/mysql/db_name/*.MYI

I then ran the mysqlcheck command as in step 7 of my earlier post.

Again, no errors/issues were reported. I held my breath as I started the test quiz and assignment upload....and it failed again. That meant the data was royally screwed. (...and that's putting it nicely).
I then decided I was going to take a look at the database structure and perhaps even delve into the data itself and try to see where the issues were.

I took mysqldumps of the structure of the working database and the non-working database and compared them line by line in Notepad++ (a tedious task, but some real team effort came into play here), but came to the conclusion that the structure was fundamentally the same. Therefore, by the process of elimination, the root cause was the actual tables/data.
The structure was analyzed using the following:
mysqldump --no-data --host localhost --user=root -p db_name > /path/to/report.sql

On to the analysis of data. Analysis of the tables from the phpmyadmin interface showed no anomalies. In Moodle, there's a database report that is included with the base code which contains a bit more useful data and can be accessed via:
http://yourmoodleurl.com/admin/dbperformance.php

I ran the script and used the view tables link and started scouring the tables one by one, searching for anomalies. Interesting enough, I stumbled upon two very strange issues.
The auto_increment fields for two tables were HUGE, compared to the number of rows the tables contained:

1. mdl_question_sessions
2. mdl_assignment_submissions

The figures were large enough to be beyond the range of the ID field (BIGINT-10) and the names of the questionable tables coincided with the issues we were having. A faint light began to appear at the end of this proverbial tunnel.
The problem lay with the Primay Keys of these two tables. After searching for the Moodle Database schema on Moodle.org (almost a herculian task), and careful analysis of the stable structure I learned that the Primary keys were not foreign keys in other tables. The proverbial light grew a little bigger.

The only way forward at this point would be to delete the entire primary key fields on the two affected tables, recreate the field (and associated options) and reset the auto_increment to 1.

This operation was done from the phpmyadmin interface and needed to be tested significantly to ensure that other functionality would not be negatively affected. Success! It worked in the test environment. The light grew stronger...

I was especially wary about implementation in production, having a very narrow maintenance window to attempt the fix. After the database fix, I re-ran the myisamchk with repair as well as the mysqlcheck to err on the safe side. The operation was a success, and I finally saw the end of the tunnel...Problem solved.

Thoughts: I've yet to determine WHY the moodle upgrade script failed and caused this databse anomaly. From the front-end, the upgrade was successful and NO errors were reported. I've also to understand how the mysqlcheck and myisamchk could not detect that a table field contained data beyond it's configured range. Even turning on Debugging on the Moodle site failed to show anything useful about a failed insert to at least provide a tip in the general direction of the issue.

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)