Amazon’s Web Services are very handy, although sometimes the command line tool syntax is a little awkward to remember and the documentation, while extensive, is not quite as simple to navigate as I’d like. I’m providing these help files as a reference for anyone who might need them. As you are no doubt aware, you can also get this content directly by issuing <aws_command_name> --help in the shell. For me it’s much easier to have these up in a browser window so I can quickly toggle between it and the command line without losing my place. I’ve added references for Auto Scaling, CloudWatch, Elastic Compute Cloud and Elastic Load Balancing.

While I’m a fan of Gearman so far, I thought it prudent to look at alternative solutions. This is a survey of alternative solutions I’ve located so far. Most of my clients are LAMP(hp) and so I’ll probably be ignoring the language specific packages that don’t support PHP. After a cursory overview from the list below I know I’ll be checking in on Amazon SQS and Beanstalkd before I make my final selection.

• 0MQ
• Amazon Simple Queue System (SQS)
• Apache MQ
• Apache Qpid
• Beanstalkd
• Dropr
• Gearman (Perl system by Danga)
• Gearman (C)
• Microsoft Message Queuing (MSMQ)
• Minimalist Queue Services (MQS) abandoned project
• Q4M MySQL Pluggable storage engine
• OpenAMQ
• Open Source Message Queue (OSMQ)
• Peafowl
• RabbitMQ
• Red Hat Enterprise MRG
• Starling
• Sun Java System Message Queue
• Z Message Queue abandoned project

Linden Labs (publishers of Second Life) posted their evaluation of Messaging Queue Software. Of course they’re an edge case in terms of scale so some of these may work just fine for your uses despite being eliminated by them.

Gearman is a job scheduling service and I’m very excited about it. I’m using it in a development capacity so your mileage may vary in production but I wanted to share my experience thus far. As I said, I’m very bullish on this project and I see it as hugely helpful in eliminating latency in applications that often get bogged down during unnecessary synchronous communications.

Compiling gearman required installing a package that wasn’t part of my default Fedora Core install and for me wasn’t intuitive to locate. The UUID header file was located in the package e2fsprogs-devel which I found using yum provides "*/uuid.h". After that it was rather smooth to get it up and running. gearmand -d -u nobody got it up and running as a damon and I was able to connect to it using telnet over port 4730. Next I compiled the source for the PHP client and got that hooked into PHP by adding an extension file include in /etc/php.d to load the module and restarted Apache so it would be loaded there too.

Process to install and get running:

// First the server
tar -xzvf gearmand-0.5.tar.gz.tar
cd gearmand-0.5
yum install e2fsprogs-devel
./configure; make && make install
gearmand -d -u nobody
 
// Next the PHP client
tar -xzvf gearman-php-ext-0.2.tar.gz.tar
cd gearman-php-ext-0.2
phpize
./configure; make && make install
echo "extension=gearman.so" > /etc/php.d/gearman.ini
service httpd restart

So now to do some work, even if it’s useless, that takes a long time. It just so happens that creating a file with 1,000,000 sequential numbers takes a few seconds on a small EC2 instance, perfect for my test. I realize this is a highly insecure process, NEVER pass filenames as parameters in production code. Here’s the worker that creates a file (passed as the parameter) on the current system’s /tmp directory.

$worker = new gearman_worker();
$worker->add_server('127.0.0.1', 4730);
$worker->add_function('fill_file', 'fill_file_fn');
 
while(1) $worker->work();
 
function fill_file_fn($job){
	$data = $job->workload();
	$fh = fopen("/tmp/" . $data, "w");
	for($i=1;$i<1000000;$i++){
		fwrite($fh, $i . "\n");
	}
	fclose($fh);
	return;
}

The calling client just invokes this 20 times in the background.

$client = new gearman_client();
$client->add_server('127.0.0.1', 4730);
for($i=0; $i<20; $i++){
	$client->do_background('fill_file', 'file' . $i . '.txt');
}

Workers are started from the command line with something like this, “php worker.php &” and if you want more, just run more of them. You can also kill off some if they’re no longer needed.

The client completes it’s run in about 5 seconds while 5 worker threads toil away in the background until they get their work done about 3 minutes later. The use cases from the gearman team show the utility of this as a spider and for image manipulation. I see uses for sending mass emails to distribution lists using a template and substitute parameters to create a unique email for each person on the worker instead of the client – thus reducing the processing time to get the mail ready and speeding the delivery using multiple worker threads for sending (that can even be on remote machines). This product is definitely worth checking out.

Hopefully this helps you get up and running with Gearman!

This is the second part of of an article looking at how to effectively host a small subversion based project that is no longer going through rapid development. The first part looked at using EC2 to run Subversion and S3 for persistent storage. While an intruiging solution, it raised some concerns.

The alternative solution is to look at outsourcing the hosting of Subversion and ticket management to another provider. The size of our repository is less than 1GB and so I’m using that as the price point. Additionally, there are 2-3 developers who’ll require access to the repository. There are many great “free” services including Google, but this is not an open sourced project so it’s out. In the hosted subversion realm, there are a number of providers with basic accounts to handle this size repository. The following table is a price comparison at the 1GB storage level. Many providers offer a free service for smaller projects with different limitations for bandwidth, tickets and so on so YMMV.

• ¹ Pricing dependent on storage and developers
• ² Offers a cheaper or free plan with less than 1GB of storage.

The real benefit of a hosted solution is the addition of services such as Trac, user management, automated backups and more. If you are looking at building a project with multiple developers who are not in the same physical location, hosting your project with a service is definitely the way to go. It’s cheaper and the overhead of configuring and maintaining your own EC2 instance (or even a dedicated server) increases the costs significantly.

Over the last few weeks I’ve been considering some options for cutting development costs for myself and a few clients. One of the continuing questions is how to manage the code base. Keeping a development server on hand is great during periods of active development and work, but when the site reaches maturity and only bug fixed are required, development servers sit idle for weeks on end without use. This got me thinking about how to best manage the source in a persistent way. This first post looks at how this might be accomplished using EC2.

I’ve been thinking about moving a development environment to Amazon EC2 from a dedicated server. The problem is, at least for this project, development only occurs a few hours per day and may go entire weeks without anyone working on it. Obviously a small instance at $0.10/hour is sufficient for load. That would cost roughly $72/month. But, even if I’m working on the application 40 hours per week, I should be able to reduce that charge to $16/month to cover the time the server is actually on. Additionally, after being burned with an instance failure last weekend, I want to be sure the data is securely backed up as well. I thought about using EBS but, as readers have pointed out, even they can fail. Furthermore, I don’t want to create a drive sized in GB if I only need a few MB of storage. Lastly, but not least, if I need to scale the drive – I don’t want to re-create the AMI each time to reflect new drive ids.

My initial thought was to start with a public Fedora Core instance and install PersistentFS, automating all of the startup and shutdown process to ensure data integrity. Next, configure subversion to use that mount point for file storage. Last but not least, I’ll create a script I can run from my local machine (or a remote server) that starts and stops an instance and binds a known elastic IP to that instance at boot time. I think my overall costs will be greatly reduced.

Estimate of costs on EC2:

The bare minimum – if no development work was done at all would be the storage costs of $1.65 – certainly cheap enough! However, the time to build the initial environment, create the scripts and the time lost during the startup and shutdown of the server each time made me think there may be a better alternative. Read more on subversion hosting in the second part.

Today I learned a few painful lessons: First, EC2 instances die and a simple reboot will not recover them. Second, unlike many web hosts – amazon doesn’t offer any level of monitoring. Third, backups are only useful if they’re current.

Lesson 1:

When I originally built my EC2 instance to host this site (and a few other applications) I was learning about Amazon’s EC2 and so spent a good amount of time trying to be as thorough in my documentation of my server setup. The result was an install script, “install_server” that effectively did all the steps I did when I turned on the server. The script goes something like this:

#!/bin/sh
yum -y upgrade
yum -y install sendmail
yum -y install httpd
yum -y install php
yum -y install php-mysql
yum -y install php-pecl-memcache
yum -y install memcached
yum -y install subversion
pear install HTTP_Request
cp configs/freetds.conf /etc/freetds.conf
cp configs/httpd.conf /etc/httpd/conf/httpd.conf
cp configs/memcached /etc/sysconfig/memcached
cp configs/php.ini /etc/php.ini
cp configs/fstab /etc/fstab
tar -xzf webroot.01.tar.gz 
/etc/init.d/memcached restart
/etc/init.d/httpd restart
/etc/init.d/sendmail restart
echo configs/crontab.txt
echo NOW SETUP CRONTAB!!!

This effectively copies into place all of the settings I need for a server. This is nice, because I am able to bring a new server online within 10 minutes or so of it going down. I should probably just automate the creation of a server specific AMI once or twice a day, but I’m just not there yet. Also – I know there are some weak points in the startup script… I’ll be working those out soon now that I see it’s really a useful tool.

Lesson 2:

Monitoring of EC2 instances needs to be done by an external service. Fortunately, not too many people care what’s going on with this site over a Saturday night to Sunday morning. I’m looking at the following options in two realms, first – a basic alert that there’s a problem, and secondly a more proactive approach that can do some instance killing and restarting on it’s own.

Right now I’m looking at these services for quick and dirty SMS alerts about the status of my instances:

• Pingdom
• JustUptime
• UptimeArchive

And I’m looking at these for a more holistic approach to monitoring but am gun shy on relying on these to manage the instances until I learn more about them:

• RightScale
• Scalr

Any experiences anyone has had with any of these products is always appreciated.

Lesson 3:

Last but not least are backups. I have another script, aptly named “backup_server” that makes a snapshot of the settings and configurations every 24 hours during off peak times storing the data on an elastic block storage device that I have mounted to the application server. That goes something like this:

crontab -l > configs/crontab.txt
cp /etc/php.ini configs/php.ini
cp /etc/httpd/conf/httpd.conf configs/httpd.conf
cp /etc/sysconfig/memcached configs/memcached
cp /etc/fstab configs/fstab
tar -czvf backups/configs.01.tar.gz configs
tar -czvf backups/webroot.01.tar.gz /mnt

Where I was burned here is that my cron job only backed up my data every 24 hours at 4am. However, the application server crashed and burned at 2am EDT. Clearly I need to consider something like rsync to prevent this type of data loss. Rsync can grab the incremental changes hourly, thus reducing work losses during between the full backups every 24 hours. As a stop gap, I’ve increased the frequency of the backups until I can get back to the system and setup rsync.

Over the last few weeks I fought with getting my Mac OS X machine to mount a NFS share on Linux remotely. I’ve tried different port numbers, different security models, even SSH tunneling to no avail. I’ve read mailing lists that say it can’t be done, and others that say certain kernel versions work and others don’t. I’ve been able to actually edit files for 2-3 minutes before the connection would drop for no apparent reason. In other tests, I was able to view files, but due to some permissions errors, I was unable to make changes. After parsing through the miriad of freely given advice online, I began seeking alternatives.

The key requirements for me were simple:

1. Be able to browse the directory structure using Mac OS X finder.
2. Be able to save/copy/move/delete files without being prompted for my password.
3. Be able to edit files using any editor of my choosing.

ExpanDrive

I have found the tool I’ve been looking for! ExpanDrive allows me to use sftp, basically ssh to manage my files remotely but leverage my super handy Mac tools to do the editing. This is a huge win for productivity. The publisher provides a 30 day free trial, but I knew instantly this was what I’ve been looking for. The package runs $39.95. I expect it will pay for itself in less than a week of development. There’s lots of great support through getsatisfaction.com for common questions like, “how do I access ec2?” which didn’t directly answer my question, but got me sufficient information to troubleshoot my own SSH connection issues.

I’m still a little disappointed I was unable to get NFS working as I had hoped, but this solution definitely makes my life easier.

Meerkat

Another great tool that I discovered today is Meerkat. As you’ll recall I said I was trying to use SSH tunnels to access the NFS service. After trying 8 different tools (that were all junk) I found Meerkat. The software is dead simple and provides rich access to pretty much everything I could want. Using Meerkat you can leverage an SSH login into one machine to provide access to a port bound service somewhere else. I see myself using this to grab remote access to a MySQL server parked behind a firewall via an SSH tunnel to a server that can see it. Other tools may have more “options”, but Meerkat actually works. I haven’t bitten the bullet to buy this one yet, I want to see how much I actually use it first, but at $19.95 – it’s a bargain as well and may quickly pay for itself.

I also wanted to give credit to Alan Watson who’s blog post about using ExpanDrive even put them on the radar for me. He also blogged on Meerkat which is how I found him in the first place. Thank you Alan, you made my day!

Migrating to EC2? Here’s a gotcha that’s documented but not something I even thought to look up. You can see it in the System Output Log from bootup “INIT: Entering runlevel: 4″ The default runlevel for EC2 instances (at least if you’re using the Amazon provided Fedora AMI’s) is 4. The process for editing the scripts is of course the same, it’s just all in /etc/rc.d/rd4.d instead. Alternately, for less command line oriented editing, you can always use the /usr/sbin/setup tool to manage the runlevel – of course you’ll still need to create your own scripts for your non-default packages.

While considering different options for a database server, I decided to do some digging into Amazon Web Services (AWS) as an alternative to dedicated servers from an ISP. I was most curious about the I/O of the Elastic Block Storage (EBS) on the Elastic Compute Cloud (EC2). What I tested was a number of different file systems EXT3, JFS, XFS, ReiserFS as single block devices and then some different software RAID configurations leveraging JFS. The tests were run using Bonnie++.

The configuration was vanilla, no special tuning was done, just the default values that are assigned by the tools. I used Fedora Core 9 as the OS from the default Amazon AMI and used “yum install” to aquire the necessary utilities (more on that below). I expect with further tuning, some increases in performance can still be obtained. I used the small instance for cost reasons, which includes “moderate” I/O performance. Running on a large or extra-large standard instance should perform even better with “high” I/O performance. You can get all the instance specifications from Amazon.

First I wanted to determine what the EBS devices would compare to in the physical world. I ran Bonnie against a few entry level boxes provided by a number of ISP’s and found the performance roughly matched a locally attached SATA or SCSI drive when formatted with EXT3. I also found that JFS, XFS and ReiserFS performed slightly better than EXT3 in most tests except block writes.

The Numbers

Again, let me re-iterate that some numbers may not be accurately reflected in your production environment. Amazon states, small instances have “moderate” I/O availability. Presumably if your running this for a production DB, you’ll want to consider a large or extra-large instance for the memory and so you should see slightly better performance from your configuration. Also note, that the drives I allocated were rather small (to keep testing costs low) so you may experience different results with larger capacities.

Note: The graph below is in KB, not bytes as titled.

As expected, RAID 0 does best with read/write speed and RAID 5 does very well on reads (input block) as well. For InnoDB, the re-write and block read (input)/write (output) operations are the most critical values. Longer bars mean better values. To better understand what the test is doing, be sure to read the original Bonnie description of each field.

Making Devices

The process for making a device is simple. There are many tutorials on how to make this persistent and you can certainly build this into your own AMI when you’re done – this is not a tutorial on how to do that. To get a volume up and running you’ll follow these basic steps:

1. Determine what you want to create – capacity, filesystem type etc.
2. Allocate EBS storage
3. Attache the EBS storage to your EC2 instance
4. If using RAID, create the volume.
5. Format the filesystem
6. Create the mount point on the instance filesystem
7. Mount the storage
8. Add any necessary entries to mount storage at boot time.

Single Disk Images

Remember, the speed and efficiency of the single EBS device is roughly comparable to a modern SATA or SCSI drive. Use of a different filesystem (other than EXT3) can increase different aspects of drive performance, just as it would with a physical hard drive. This isn’t a comparison of the pros and cons of different engines, but simply providing my findings during testing.

I didn’t test any other filesystems such as ZFS, because I’ve read some other filesystems are unstable on Linux and I’ll be running production on Linux so the extra time for the tests seemed unnecessary. I am interested in other alternatives that could increase performance if you have any to share I’d love to hear about them.

You can quickly get a volume setup with the following:

mkfs -t ext3 /dev/sdf
mkdir /vol1
mount /dev/sdf /vol1

Next time you mount the volume, you won’t need to use “mkfs” because the drive is already formatted.

RAID

The default AMI already includes support for RAID, but if you needed to add them to your yum enabled system, it’s “yum install mdadm”. On the Fedora Core 9 test rig I was using, RAID 0, 1, 5, 6 were supported, YMMV.

To create a 4 disk RAID 0 volume, it’s simply:

mdadm --create --verbose /dev/md0 --level=0 --raid-devices=4 /dev/sdf /dev/sdg /dev/sdh /dev/sdi
mkfs -t ext3 /dev/md0
mkdir /raid
mount /dev/md0 /raid

To create a 4 disk RAID 5 volume instead, it’s simply:

mdadm --create --verbose /dev/md0 --level=5 --raid-devices=4 /dev/sdf /dev/sdg /dev/sdh /dev/sdi
mkfs -t ext3 /dev/md0
mkdir /raid
mount /dev/md0 /raid

This example assumes you have 4 EBS volumes attached to the system. AWS shows 7 possible mount points /dev/sdf – /dev/sdl in the web console, however, the documentation states you can use through /dev/sdp, which is 11 EBS volumes in addition to the non-persistent storage. This would be a theoretical maximum of 10TB of RAID 5 or 11TB of RAID 0 storage!

Checking in on things…

• cat /proc/mdstat
  is a great way to check in on the RAID volume. If you run it directly after creating a mirroring or striping array, you’ll also be able to see the scrubbing process and how far along it is.
• mount -l
  shows the currently mounted devices and any options specified.
• df
  disk free provides a nice list of device mounts and their total, available and used space.

Conclusion

It’s clear from the numbers that software RAID offer a clear performance advantage over a ESB volume. Since with EBS you pay per Gb not per disk, it’s certainly cost effective to create a robust RAID volume. The question that remains is how careful do you need to be with your data? RAID 0 offered blistering fast performance but like a traditional array, without redundancy. You can always set it up as RAID 5, RAID 6 or RAID 10 but this of course requires more unusable space to handle the redundancy.

Since the volumes on EBS are theoretically invincible, it may be okay to run unprotected by a mirror or parity drive, however, I haven’t found anyone who would recommend this in production. If anyone knows of a good reason to ignore the saftey of RAID 10 or RAID 6 or RAID 5, I’d love to hear the reasoning.

I am also curious if these drives maintain a consistent throughput over the full capacity of the disk or will they slow down as the drive fills like a traditional drive? I did not test this. It remains open for another test (and subsequent blog post). Should anyone run ZCAV against a 100Gb+ drive and figure that out, please let me know.

Fine Print – The Costs

Storage starts at a reasonable $0.10/GB-Month which is reasonable and is prorated for only the time you use it. A 1Tb RAID 0 volume made of 10×100Gb volumes would only cost $1,200 per year. Good luck getting performance/dollar costs for 1Tb like that from any SAN solution at a typical ISP. There are however some hidden costs in the I/O that you’ll need to pay attention to. Each time you read or write a block to disk, there’s an incremental cost. The pricing is $0.10 per million I/O requests – which seems cheap, but just running the simple tests I ran with Bonnie++ I consumed almost 2 million requests in less than 3 hours of instance time. If you have a high number of reads or writes, which you likely do if you’re considering reading this, you’ll need to factor these costs in.

The total AWS cost for running these tests was $0.71 of which $0.19 were storage related. The balance was the machine instances and bandwidth.

Resources

• Linux Software RAID (Kevin Korb)
• mdadm man page (Man-Wiki)
• Quick HOWTO Linux Software RAID (Linux Home Networking)
• Software-RAID (Linux Documentation Project)

You can probably save a considerable amount of storage by right sizing your columns. It’s been documented that right sizing data types for columns can return data faster and of course making the data smaller results in less wasted storage space on disk. So I decided to find out just how much.

Inserts

I created two user tables and attempted to insert 1,200,000 rows in each, getting some performance information along the way. The method was to insert 100,000 records in a table, check out how disk space had been used and how the performance of the inserts was impacted. The code to run a similar test on your system is available at the end of this post.

As you can see on the graph, when the on-disk file reached ~45Mb, the performance of the inserts fell very quickly. This is likely due to the low innodb_buffer_pool_size set on this machine (32M) and the forced swapping for the table. Analysis of the table after the inserts showed a average row length of 80 bytes for User1 and 67 bytes for User2. Roughly 15% smaller, which lends credibility to the roughly 15% increase in number of rows inserted before suffering similar performance bottlenecks.

Interestingly, I expected a higher throughput for the total queries per second for the user2 table because of the smaller data segments. While a small 1% increase was observed, this could eassily have been due to fragmented disk or any other number of potential elements impacting the test machine. However, since MySQL uses pages and not individual file writes for each record, it is unlikely to make a large impact unless the difference in record size (currently a difference of 13 bytes per record) is significant.

Selects

Curious how significant the impact would be on select speed, I turned my attention to seeing how fast I could get the data back. So I modified my script below to use a select statement instead and hammered the DB again. As you can see below, the performance of the select is greatly impacted by the number of rows. I ran the test with 300K, 600K and 1.2M rows and saw a performance decrease with each increment, however, the smaller record size was consistently faster than the larger format.

With user1 at 1.2Million rows, I was able to select 1M random uid values at an average rate of 3,458.3 qps. My smaller table, user2, was able to select 1M random uid values at an average rate of 3,590.6 qps, about 3.8% faster than the larger table. This translated to 132.3 queries per second more. Again, I suspect the performance increase has to do with the number of records stored in memory at any given time. Increasing the density of records by decreasing their size effectively makes the ram more efficient.

Running the tests again after purging roughly 1/2 of the records had similar results as you can see in the graph. The improvement was 4.3% which is clearly even faster but as the random pages were fetched from disk, the majority of the data now fit in RAM.

Investigating further, I opted to trim the file to just 300K records and run the tests one last time. The difference was only 1.5% faster, similar to the increased performance for the inserts. Perhaps a future test to measure the increased performance as a measure of the decrease in record length would show that a 15% decrease in record length will result in 1/10 increase in read/write performance? Alas, that’s for another blog post.

Conclusion

There are serious benefits to keeping your data footprint smaller. Although this is not the silver bullet of database tuning, it will certainly help your system run more efficiently. The best way to increase query throughput for reads is to have faster disk I/O. However, as this test shows, you can stretch your performance dollars just a little bit further by using smaller data values that make more sense for the values you are actually storing.

You can get more information on the space required by column type from MySQL’s website and do your own back of the napkin performance calculations on how much of a lift you can hope to see… good luck, and happy tuning.

The Scripts

<?php
	$records = 100000;
	$conn = mysqli_connect("127.0.0.1","root","","test");
	$start = microtime(true);
	for($i=0;$i<$records;$i++){
		$query = "INSERT IGNORE INTO user1 (uid, age, gender, lastvisit) VALUES (" . rand(1000000, 100000000) . ", " . rand(13, 50) . ", " . rand(0,2) . ", NOW())";
		// $query = "INSERT IGNORE INTO user2 (uid, age, gender, lastvisit) VALUES (" . rand(1000000, 100000000) . ", " . rand(13, 50) . ", " . rand(0,2) . ", NOW())";
		// $query = "SELECT * FROM user1 WHERE uid = " . rand(1000000, 100000000);
		// $query = "SELECT * FROM user2 WHERE uid = " . rand(1000000, 100000000);
		$conn->query($query);
	}
	$stop = microtime(true);
	mysqli_close($conn);
	print round(($stop - $start), 4) . " seconds elapsed filling the table (~" . number_format($records/round($stop-$start,4),1) . " qps).\n";
?>

mysql> CREATE TABLE `user1` (`uid` bigint(20) NOT NULL, `age` int(11) DEFAULT NULL, `gender` int(11) DEFAULT NULL, `lastvisit` datetime DEFAULT NULL, PRIMARY KEY (`uid`)) ENGINE=InnoDB;
mysql> CREATE TABLE `user2` (`uid` bigint(20) unsigned NOT NULL, `age` tinyint(3) unsigned NOT NULL, `gender` tinyint(3) unsigned NOT NULL, `lastvisit` timestamp NULL DEFAULT NULL, PRIMARY KEY (`uid`)) ENGINE=InnoDB
mysql> describe user1;
+-----------+------------+------+-----+---------+-------+
| Field     | Type       | Null | Key | Default | Extra |
+-----------+------------+------+-----+---------+-------+
| uid       | bigint(20) | NO   | PRI | NULL    |       | 
| age       | int(11)    | YES  |     | NULL    |       | 
| gender    | int(11)    | YES  |     | NULL    |       | 
| lastvisit | datetime   | YES  |     | NULL    |       | 
+-----------+------------+------+-----+---------+-------+
 
mysql> describe user2;
+-----------+---------------------+------+-----+---------+-------+
| Field     | Type                | Null | Key | Default | Extra |
+-----------+---------------------+------+-----+---------+-------+
| uid       | bigint(20) unsigned | NO   | PRI | NULL    |       | 
| age       | tinyint(3) unsigned | NO   |     | NULL    |       | 
| gender    | tinyint(3) unsigned | NO   |     | NULL    |       | 
| lastvisit | timestamp           | YES  |     | NULL    |       | 
+-----------+---------------------+------+-----+---------+-------+