Find out the creation date of a MySQL table

Sometimes it’s useful to know when a certain database table was created. For those cases the following statement can be used:

SELECT create_time
FROM INFORMATION_SCHEMA.TABLES WHERE table_schema = 'DB_NAME'

In case you are interested in the creation time of a particular table, just add the appropiate WHERE-clause:

SELECT create_time
FROM INFORMATION_SCHEMA.TABLES WHERE table_schema = 'DB_NAME'
AND table_name = 'TABLE_NAME'

+---------------------+
| create_time         |
+---------------------+
| 2011-12-22 12:32:34 |
+---------------------+
1 row in set (0.00 sec)

Update: As pointed out below in the comments, it is not possible to know the creation date of a database without access to the directory structure of MySQL.

Teach yourself how to program

I’m more into system administration than I am into programming, this is mainly because at my job I”m not involved in programming tasks (left aside scripting stuff). I do however have a very strong desire to refresh those programming classes from university and get to know awesome new programming languages such as Android, Ruby, NodeJS, .., and do cool stuff with them, who knows even join an OpenSource project.

For that purpose I’m collecting links to nice tutorials and books that I come across while browsing the net. Hopefully one day I’m able to read all those (after finishing the Oracle 11g Administration book I’m currently reading, and the RedHat Linux Certification that I wish to prepare afterwards). So little time and that much to discover yet!

Anyway, the links (more links will be added along the way):

http://www.codecademy.com

The idea behind this website is to learn to program by actually doing it instead of reading a book. They offer a nice interactive shell to learn JavaScript (more languages will be added). Users are encouraged by the receiving badges as they accomplish targets (sort of Stack Overflow style). The courses being offered are FREE.

http://www.codeschool.com

Same principle as codecademy, learn by doing. They offer several courses as pay-per-view ($55) or monthly subscription ($25/mo). Some of the courses they offer include:

  • jQuery
  • Functional HTML5 & CSS
  • Ruby on  Rails
  • Ruby (this one is FREE)

http://learncodethehardway.org/

Emphasizes precision, attention to detail, and persistence by requiring you to type each exercise (no copy-paste) and make it run, as well as to read up on outside topics and to return to exercises and ideas that you don’t understand, and understand them. Don’t take “the hard way” to literal, it just means you learn by actually doing the excercices instead of just copying them. They offer various books for FREE in HTML format (PDF and other formats are to be paid, but cost little):

  • Python
  • Ruby
  • SQL (Currently in Alpha version)
  • RegEx (Currently in Alpha version)
  • C (Currently in Alpha version)

http://academicearth.org/subjects/computer-science

Offers FREE academic videos on computer science related topics as taught at leading universities. Just to name a few:

  • Programming Methodology (Stanford)
  • Introduction to Computer Science and Programming (MIT)
  • Introduction to Computer Science (Harvard)
Excellent resource to learn Ruby on Rails. It doesn’t matter whether you already know Ruby or web development in general, all get touched in this book. Available for FREE in HTML and in PDF for $26. They also offer +15 hours of screencasts (to be paid) which allow you to look over the shoulder of an experienced Ruby on Rails developer and see how they do it.
Yet another resource to learn Ruby. Available for FREE in HTML format, to be paid in others.

Please feel free to drop a comment if you know other good resources

How to change password expiration in Oracle 11g

Another quick hint, this time for Oracle database administration.

Starting Oracle 11g the default profile that users belong to, if no other profile is specified, forces the password to be changed within 180 days. Previous versions didn’t have this “feature”, so this might result in a nice surprise when you find out that your application isn’t working anymore after 6 months.

Although security-wise it’s a good idea to change passwords now and then, sometimes it’s necessary for the password to remain the same. To change the expiration time to unlimited for the default profile, execute the following statement:

alter profile default limit password_life_time unlimited;

How to log Apache requests conditionally

Today a small Apache configuration trick.

I’m using a fancy Amazon ELB (http://aws.amazon.com/elasticloadbalancing/) to loadbalance (duh) two Apache nodes. In order to check whether the nodes are alive, the load balancer requests a static html page every few seconds. In my case this is elb-ping.html. Soon you will notice that this results in a your Apache acces.log being flooded with those ping requests.  Don’t worry, just tell Apache to log the requests conditionally.

To do so add the following lines to your virtualhost or apache configuration in general:

SetEnvIf Request_Uri “elb-ping.html” dontlog
CustomLog logs/access_log common env=!dontlog

Restart Apache and voila!

Boosting I/O throughput of a process

Almost 6 years ago, when Windows Vista was released, I decided to switch to Linux on a day-to-day basis. I had been using Linux before, but was still using Windows on my desktop machine (I know…pretty foolish). The other day I was glad I made that switch a few years ago.

I was creating a HEAP dump, that is a snapshot of all objects in memory, of a JBoss server. A HEAP dump really dumps everything, so if your JBoss (or Tomcat or whatever) process is occupying 4GB of memory, it will dump the whole 4GB in a nice file for you to analyze later on (which is the whole idea behind dumping the HEAP).

Really cool stuff, but not so cool if you see that the dump file only grows 2MB each 10 seconds. What the hell?! I’ll be old and gray before the dump has finished!!! Linux power to the rescue!

The Linux operating system has an I/O scheduler (http://en.wikipedia.org/wiki/I/O_scheduling) that tries to prioritize process I/O requests in the best possible way. It’s clear that it isn’t giving my dump process the highest priority, so let’s tell Linux to do so: Say hi to ionice.

ionice -c class -n classlevel -p PROCESS_ID

class

1 (real time): First priority is given to the process, regardless of other I/O requests.

2 (best-effort): The default priority class for a process

3 (idle): The process will only get I/O time when no other program has requested I/O time.

classlevel

A number from 0 to 7 indicating the time slice the process will receive on each scheduling window, where 0 is the highest priority (process gets more I/O time) and 7 is the lowest (process gets less I/O time).

Depending the class and level, the process is given a priority. Beware using the real time priority class as it might choke other processes needing some I/O time.

In my case I used the best-effort class with the highest priority (0). Magic happened, instead of seeing the dump file increased by 2MB per 10 seconds, it was now bumped up to a rate of 10MB/s, AWOOT!!

The importance of disabling auto deploy in JBoss

Recently I was investigating the memory usage of a JBoss application server (I have a post about this pending btw), and while I was preparing everything to launch a stress test, I was surprised to see how the Eden Space kept growing around 80MB each 5 seconds while JBoss was totally idle.

Garbage collection Eden Space

In the above image you can observe how the Eden Space fills up continuously when the autodeployer is activated. Changing the autodeployer’s check period to 60 seconds, we can observe that the Eden Space is filling at a very different rate. Disabling it completely results in almost no growth…

Conclusion: Disable the autodeployer on a production server!

To disable it (at least in JBoss 5), simply delete the following file:

JBOSS_HOME/server/<CONF>/deploy/hdscanner-jboss-beans.xml