My Favorite Short Cut: ALT-TAB

You can tell how long someone has been using computers by paying attention to the short cuts he/she uses to navigate on the computer. I usually have quite a few application running on my computer and I need to be able to quickly switch between them. Back when dinosaurs roamed the earth and Windows 3.0 first came on the market, I used to only have one or two apps running and could easily just click between them. It didn't take long to quickly exhaust that functionality, especially with the small screens on laptops we had back then.

I remember leading a training session on a new database tool and I kept having to switch between a PowerPoint presentation and the tool. I would escape out of presentation mode, minimize PowerPoint, and show my demo. Once I completed the demo, I would expand PowerPoint and go back into presentation mode. Then someone showed me the power of ALT-TAB that allowed me to instantly switch between all running applications. I thought it was great and still use that key combination today even though there are more advanced ways of switching between running applications. For instance, you can see the running applications in Windows by just looking at the toolbar on the bottom of your screen. I still prefer ALT-TAB.

There are a number of other computer short cuts that will give away the length of time someone has been using computers. My work laptop run Microsoft Windows but I am an old Unix guy. When I open the Windows Power Shell it is a hard to remember not to use the "ls" command to get a directory listing of files instead of the preferred "dir" command familiar to those who learned "DOS." Fortunately Power Shell understands both commands and so I don't get the old "Syntax Error" I used to.

Another trick that really shows how old I am is from when I started using Oracle version 4. When you wanted to get a list of all the tables in the database, you would run the following command:

SELECT * FROM tab;

The result was a very simple listing of tables and some other basic information. Oracle later added more complete table definitions but I still use this simple command. Why? Because it is so simple and easy to remember. Are there better ways to find out what tables are in your database or schema? That depends upon how you define better. If you have to go to a manual and look it up, nope.

I used to work for a company that took PostgreSQL and made it look like Oracle for a lot less money. The first thing I tried when I sat down to play with the product was the command listed above. When it worked, I knew there were others at the company that appreciated quick and simple. I also knew they had people on the development staff that had used Oracle for a very long time, an important fact to me at the time.

Use It or Lose It

Today I started getting ready for a new project at work and needed to set up a PostgreSQL database. I have a database administrator that I works for me and normally I would ask him to set it up. However this is sort of a research project that I am doing in part to keep my technical skills sharp and so I wanted to set it up on my own. It has been a few years since I last administered a database and so it took some Internet searches to figure out everything again.

I felt like such a beginner as I got things configured. Fortunately I sort of remembered most of the steps and so I didn't have to comb through too much documentation. However it underscored the importance of keeping up on my technical skills.

After becoming a manager several years ago, I tried to do something technical on a daily basis. When that proved difficult, I tried to do something technical on a weekly basis. After today I can say that I failed at that goal. This new project will be good for me as I work to regain some of the skills lost and also dive into some coding that I have not done since back in school. It should be fun and the skills that I have lost seem to be returning quickly.

No Instant Experts

What do playing the guitar and skiing have in common? Both require many hours of practice to become proficient. This is something I didn't understand growing up. Fortunately being a beginner skier can still be a lot of fun. When I would take guitar lessons as a child or teenager, I would practice for about a month or two and then get frustrated that I didn't know very much. This time I am making the process of learning the guitar a lot of fun and I look forward to practice every day.

So what does any of that have to do with computers and technology? The same rule of practice applies. If you want to get good, then you need to spend some time with the technology. The trick is how to make it fun. Each of us is different and so one solution might not work for everyone.

I once read about someone that wanted to learn how to program computers with a new language. He loaded his computer into his camper and headed into the woods for a week. He then spent one uninterrupted week of running through Donald Knuth's Fundamental Algorithms and turning out computer programs. My wife would never let me get away with that but I always thought that would be the perfect way to learn a new programming language.

I find that the right motivation helps people learn new things as well. I often try to marry learning tasks with something something I really want to do. Back in 2004 I had wanted to learn more about the PostgreSQL database system. I was also part owner in a restaurant and heard a great presentation about customer loyalty systems. I thought it would be fun to use PostgreSQL to help me write a web-based customer loyalty program. So I sat down and designed a system to keep track of customers and reward them with points every time they made a purchase. My program never got used but I did learn a lot about PostgreSQL and made a career of it for quite a while.

If you find yourself frustrated with your computer, just remember that there are no instant experts. It takes time to learn a lot of this stuff. Search engines are your friend and there may be someone who has already solved the problem you are having. If that doesn't work, post your question on a forum and a number of experts may be able to help you out. Who knows. One of them may have learned how to solve your problem by spending a week camping in the woods.

Engineering Notebooks

I was instructed to keep notebooks by my engineering professors when I was getting my degree in Electrical Engineering. I was told that all good engineers kept notebooks and so I dutifully obliged. When I went to work for Oracle Corporation right out of college, I continued to keep notebooks and have kept every single one. My professors told me that my employers would want to retain my notebooks when I left but not a single company has ever asked for them.

Naturally I started a new notebook when I joined my current company and have filled two volumes. My coworkers see my notebooks and have been known to make copies of various pages. I have even had several people mention that I should write a book. I tell them I have and that it was a lot of work for very little pay. Lately I have been thinking about writing another book though. The only problem is that people don't really read technical books any more. It is much easier find information on the Internet. Then my boss suggested I write a technical blog, string the entries together, and create an electronic book of sorts. I suppose I could do that with this blog, but it is far too diverse for a single book. Besides I try to keep my entries short and simple so that everyone can understand them.

The book I want to write is very technical and so this evening I will be starting a second blog. Don't worry, I will still contribute to this one so that I get my 71 entries per year. My second blog will be a deep dive into PostgreSQL. PostgreSQL has some of the best documentation for any computer software, whether commercial or open source. However it lacks a cohesive set of examples and certain solutions to real-world problems. My hope is that my new blog will work hand-in-hand with the existing documentation and add clarity to some rather difficult concepts. Hopefully it solves a hole that I feel exists currently.

Recovering a PostgreSQL Database

A couple of weeks ago I got to pull an all-nighter at the office. This was a first for me at this job but it was necessary. It was also a first in that I lost a PostgreSQL database. Considering I have been working with PostgreSQL for over a decade, that speaks to its reliability as I was always losing Oracle databases when I worked for them.

The culprit turned out to be some maintenance we were doing on our network that caused us to loose connection with our database machines. We had to do a hard reboot on them. Normally that would not cause a problem with PostgreSQL but we were using the ext4 filesystem and that did. We lost our global/pg_filenode.map file which is a pretty bad problem. Searching through the Internet we discovered how bad. Everyone pretty much agreed that we needed to find a backup of that file or all our data was lost.

We searched high and low for a backup of the file without any luck. Our next step was to restore from our nightly backups. Unfortunately our backup had not been taking place and so that was not an option. Our last-ditch effort was to go through the data files and try to recover the information one byte at a time. At least we still had those.

There is a utility for PostgreSQL called pg_filedump that allows you to go through your data files and browse their contents. I located a series of scripts that helps you find the data files that contain table data. We really only needed to rebuild 2 tables and they were not very large.  The pg_filedump program helped us locate the data we needed. It took a while but we were able to get everything back.

There were a number of problems that all cascaded together and caused me and my coworker to stay up all night. While it shouldn't have happened, it did. The trick to getting our data back was never giving up.

Document Store Databases

Today I am doing some testing on MongoDB. We are getting ready to launch a new project and so I have spent the first part of the month getting things configured. I now have our staging area completely set up and ready as the rest of the software comes online.

Most database software like Oracle or PostgreSQL is relational. That means data is stored in tables and those tables can be related to other tables. If you have a PERSON table with five attributes (e.g., last name, first name, gender, etc.), those same attributes are stored for every person in the table. MongoDB is a document store. This means that you can keep track of different attributes for each person. You may store that Jimmy has green eyes and not care about that for any other person in the database.

The reason we are using MongoDB is because of its speed. While we can get 1,000 transactions per second out of Oracle or PostgreSQL, we are getting 25,000 from MongoDB using equivalent hardware. That is a huge difference.

MongoDB also has the ability to automatically partition the data to run on separate servers. If your database starts getting too big, simply add another server and the data is automatically partitioned onto it. Then when you go to look for Jimmy's data, both machines look through half as much data so you get even more speed.

Of course MongoDB does have a drawback. For starters, there are not really any reporting tools. In our system we will be doing nightly pulls of the data and inserting it into a PostgreSQL database. Then we can run reports on a daily basis using tools like BIRT or JasperReports. There is also a bit of a learning curve for database administration. However these issues are a small price to pay for such speed.

Teamwork

This morning I began documenting a project I just started. I am using a new product called Eucalyptus and want to write down the steps I went through to get everything running. Then if I have to go through the same steps in the future, I won't have to figure it out again.

As I was adding comments to my notebook, I turned past a number of entries for new products I have learned this year. While there are the usual notes about PostgreSQL, MySQL, and other databases, there are also notes for MongoDB, BIRT, Fabric, and other software. I wish I could say that I discovered all these products myself, but that would be a lie. The reason I am using them is because of various people I work with and their recommendation to help make my job easier.

I used to do consulting and had my own bag of tools. While I would work with others, rarely would they make recommendations of other products that might help me out. Now that I am in more of a team environment, there is an open atmosphere of sharing. I find this to be a refreshing change and am enjoying the chance to add to my toolbox. So if you find yourself struggling with a particularly nasty problem, seek out a trusted resource and see if there isn't another tool out there to make the problem go away.

MySQL vs. PostgreSQL

The PostgreSQL community recently had PgWest, which is a conference where users and developers gathered together to learn from each other. It was held in San Francisco, near where I work and so I submitted a paper to present. The paper was accepted and I spent a day at the conference. I would have liked to stay for all three days but had a project back at the office that required my full attention.

One of the sessions that I missed was on the differences between MySQL and PostgreSQL. Both are database management systems and are freely available. MySQL was controlled by a single company and then was purchased by Sun, which was then purchased by Oracle. PostgreSQL is a community project with developers all over the world. I would have liked to attend the presentation as I use both MySQL and PostgreSQL for my job.

Looking at all of the online traffic generated by the presentation, I really wish I had been there. I get the feeling that it was a bit like watching a cat thrown into a room full of hungry dogs (MySQL being the cat and all of the PostgreSQL fans being the hungry dogs). I have to sit back and laugh at all of the contention the one presentation has caused. It reminds me of the movie, "Monty Python's Life of Brian." The movie takes place in Jerusalem during the time of Christ. There are several Jewish groups opposing the Roman occupation. One is the "People's Front of Judea" and the other is the "Judean People's Front." Instead of working together to rid themselves of the Romans, they fight against each other.

PostgreSQL and MySQL are both open source databases and can be used without any licensing costs. They may have different architectures and methods of development, but they allow users to run complex database management systems without the burden of heavy fees required to run Oracle, Microsoft SQL Server, or IBM DB2. Maybe someday the two camps will stop arguing long enough to figure out they are on the same side and stop trying to steal each other's users.

Then again MySQL is now owned by Oracle . . . who charges large sums of money to use their "other" database product . . .

Learning MySQL

I originally started today's entry with the title "I Hate MySQL." However I realize that I don't like it mostly because I don't know it very well. In an effort to be more fair to MySQL, I toned down the title a bit and publicly acknowledge that I have some learning to do.

For those that don't know, I prefer PostgreSQL to MySQL. I use PostgreSQL daily and it is my database of choice on any new project. I also know the Oracle database well and have used it in various production systems. Unfortunately it is rather expensive and PostgreSQL will do 95% of the things Oracle can for a lot less money.

Today I have to administer a MySQL database because of performance issues. I have about 8,000 rows that I need to move from one table to another. This involves inserting them into the new table and then deleting them from the old. You would think that given the rantings of many MySQL followers that such a task would be simple and quick. Unfortunately the application that I am using references two other very large tables which complicates things a lot. I started this move yesterday at 10am and it still hasn't completed. There are tools that allow me to see what is happening in the database and the delete seems to be running longer than one would guess. Unfortunately there doesn't seem to be anything I can do to speed things up.

During my frustrating morning, a friend sent me a link to a very funny video where two stuffed bears are discussing MySQL vs. PostgreSQL. The language is very crude and so I won't post the link here. However it did put a smile on my face.

A New Job

After several years of consulting, I have finally taken a job with a new company which happens to be based in Los Angeles. This means I am spending most of my time in sunny California. I have to say the weather is a lot warmer than back in Utah. The downside is that it is really cutting into my skiing.

My new job is as the database expert and administrator for a development team. We are using PostgreSQL and so I was a natural fit for the position. With any new project, there is some low-hanging fruit that is easy to implement and this job is no different.

PostgreSQL is a great open-source database and there are a lot of tools to make it better. One thing you can do to help increase performance is connection pooling with the help of pgbouncer or pgpool. Connection pooling is like keeping a phone line open to your best friend and never hanging up. If you have something to say, just talk. There is no delay while you wait for your friend to answer the ringing phone.

We decided to try pgbouncer because it has slightly better performance than pgpool (but doesn't do nearly as much). It took only fifteen minutes to install and we are already seeing huge performance improvements with our application. I like it when you can spend a few minutes and do something that has such a huge impact. The only problem is keeping it up.