MySQL's Mystery Files

MySQL is a very powerful and easy to use - and administer RDBMS. For light use it mostly just works out of the box - but if you intend to use it for something serious you need to tweak it some.

For one thing, MySQL makes use of temporary space - that is, temporary space outside of the designated areas where it stores the database files themselves. It appears to be configured as follows:

On Unix, MySQL uses the value of the TMPDIR environment variable as the path name of the directory in which to store temporary files. If TMPDIR is not set, MySQL uses the system default, which is usually /tmp, /var/tmp, or /usr/tmp.

On Windows, Netware and OS2, MySQL checks in order the values of the TMPDIR, TEMP, and TMP environment variables. For the first one found to be set, MySQL uses it and does not check those remaining. If none of TMPDIR, TEMP, or TMP are set, MySQL uses the Windows system default, which is usually C:\windows\temp\.

Source: Where MySQL Stores Temporary Files

One potential caveat is that it uses "hidden" files that can not be detected by normal file system level tools. Here's a discussion on how that is possible. In short - MySQL creates files which are not linked in the file system and hence can not be found and simply vanish if the process making use of them - i.e., the MySQL daemon process - disappears. In other words, you are guaranteed that no left-over space will be used. From the IT standpoint it is a nuisance though as you get file system space consumed without being able to find the files responsible for such consumption.

As to the size of such consumption - I was actively using a database amounting to no more than 300 GB and due to the fact that I only had about 900 GB to spare in the filesystem I was forced to do "repairing with keycache" which is way slower than regular reindexing - but regular reindexing requires lots of temporary space which I apparently lacked.

Some pracitcal uses of VPN

VPN is a term one hears often these days. However, while many people have some ideas about what it is used for - secure access, for instance - many probably lack vision of how they could benefit from its use. So for starters - what is VPN? You can use the Wiki link for a formal definition but in a less formal way one can define it as a network one can build to their own design provided one controls the server and the other machines one intends to network together have the capability to access that server via the internet.

So let us say you control a machine on the Internet with a public IP address. On it you can install a VPN server process. Then you can issue authorization to those you want to allow to join your VPN network.

Let us consider a practical example. I configure a server to serve a VPN with a private network defined as 192.168.20.0/255.255.255.0. Let us say the server gets the virtual IP address of 192.168.20.1, with the other addresses (192.168.20.2-254) available for grabs. So let us say my laptop gets an address of 192.168.20.2, my home machine gets an address of 192.168.20.5 and my office machine gets an address of 192.168.20.10.

Thus - using the same network protocols - I can collect the video feed off of my home computer to see what is going on at home, print to my office computer's printer - and do all of it from a WiFi point half the world away using my laptop.

Or - let us say - in addition to my office in Boston I decide to get one in Buenos Aires. No problem. I get another machine there - let's say, with a virtual IP address of 192.168.20.15 - and use it and the one at my office in Boston - 192.168.20.10 - to link the two networks. Now they are linked - via the internet but at the same time utilizing the VPN's security which is normally considered an unrbeakably secure way to communicate.

Those are just a couple of possible usage scenarios. I will try to cover this topic in more detail later on. For now just think of the VPN as a network you can define the way you like no matter where the computers who will join it happen to be geographically and topologically. So long as they have access to the internet and you allow them to join your VPN they can do so.

Sparse Files

OK, I heard the term before but never had to really delve into them. That's an interesting concept, though. First delved into it needing a large (multi-terrabyte) file to house a file system. See here.

Then came across this article:

Sparse files – what, why, and how

I like the concept - though it does come with a few pitfalls, it seems. More on that later.

SSHFS and AutoFS

Mounting an SSH-accessible remote directory automatically is a nifty capability. Here's a nice description of how one can do that - specifically under Ubuntu but it will work the same just fine under most other Linux distributions:

Automatically mounting a remote directory in Ubuntu using autofs + sshfs

One thing not mentioned there - and something I keep forgetting about between the instances I need to recall it - is that the passwordless SSH login will fail unless the user directory on the SSH server is writable by the owner only! So, using the same terms as in the example above one should do the following:

1) Log into example.com as remoteusername

2) Execute the following command:
chmod g-w,o-w ~

The different flavors of 568

Yes, I admit this one was stupid. But cut me a little slack - last time I had to make network cables was many moons ago. And yes, I even got to make coax cables for 10 Mbit Ethernet. But no matter.

OK, so we got this spool of cable from a generic vendor with the cable labeled "ANSI/TIA-568-C.2". For some reason - looking at some pictures online, most likely - I decided that was to be wired according to the T568A layout. And wire them I did - a few cables, actually. Which worked fine in multiple jack with an exception of a Cisco Catalyst 2000 100 Mbit switch where they did not work at all.

Then I wired one cable to the T568B layout and it seems to work everywhere including that Catalyst 2900 switch. Given the T568C is said to have superseded T658B which in turn superseded T568A which is by now obsolete all of this seems to make sense. Hence it appears that the solution has been found.

Let me also take the time here to thank all those mailing list respondents whose guidance helped me figure this mess out. It always helps to have other minds to collect thoughts off of!

Reference:

TIA/EIA-568

Ethernet over twisted pair

How to wire Ethernet Cables

ClipGrab

Oh, how fast things change!

Just a little while ago it looked like DamnVid was all the rage - but now YouTube has changed something and it is dead in the water. However, it looks like ClipGrab is a viable alternative... for now.

Resume

Boris Epstein

Malden (Metro Boston), Massachusetts, US (map)

Contact

E-Mail: borepstein@gmail.com
Phone: 617.816.9654

Summary

Strong analytical abilities derived from both study and hands-on experience. Enjoy challenge and uncertainty. Seeking a position where I could contribute and grow, both professionally and personally.
15 years of full-time experience in the IT industry. That includes programming, systems administration (primarily various versions of UNIX and Linux) as well as network administration.
Participated in development, installation and maintenance of large-scale IT solutions in fields as diverse as telecommunications, financial, internet hosting, e-commerce, etc. Have experience developing standalone applications from scratch as well as working in a team. Diverse client interaction experience both domestically and internationally.
Operating Systems: UNIX, Linux, Mac OS X, MS Windows (XP, NT), MS DOS, Apollo Aegis, VMS
Programming Languages: C/C++, PERL, Ruby, JAVA, UNIX (C-Shell, Bourne, Korn), Tcl/Tk, PHP, Lisp/Scheme
Tools: Netbeans, Eclipse, Emacs, MS Office, OpenOffice, LibreOffice, FrameMaker, VI

Technologies: MySQL, PostgreSQL, SYBASE, Xen, VirtualBox, OpenVPN, Oracle RDBMS
Hardware: IBM-compatible PC, HP, SUN, Mac

Timeline
May 2007 - present

National Resource for Imaging Mass Spectrometry
Cambridge, MA

Work as a jack of all trades though IT management is the focus of activities. Main responsibilities include maintaining the laboratories' computer systems, providing the data storage solution, maintaining the users' desktops and participating in the development of the lab's unique OpenMIMS analysis software module. Other tasks include general office management tasks such as placing and processing purchase orders, arranging various meetings and conferences, arranging shipments, etc.

Beyond officially delineated responsibilities maintain my own privately-run VPN solution to provide connectivity to the lab's employees and collaborators outside the lab. Due to the distributed nature of the lab's activities this is a function critical to the lab's success.

April 2004 - May 2007

Travel. Educational activities. Occasional freelance projects.

Provided technical assistance to several online projects including Cooperative Research and New England JAVA Users Group.

In 2005, in the wake of Hurricane Katrina helped coordinate volunteer relief activities in the Gulf and personally participated in those activities.

July 2001 - April 2004

ARCON Corporation
Waltham, MA

Worked as a programmer involved in development and support of the ETMS air traffic management software for the FAA at the Volpe National Transportation Systems Center in Cambridge, MA. Specific area of concentration was the CDM component of the ETMS. The tasks included design and development of new functionality, as well as supporting legacy code. A large codebase together with the need for system reliability provide for a challenging task. The code was originally written in Pascal and later migrated to C.
The system was in essence a large transportation management system receiving and reflecting frequent (mostly once-a-minute) updates regarding the status of flights operating over the US airspace. Near realtime requirements were in place for processing and analyzing data which made for an exciting and challenging task.

December 1999 - April 2001

Boston Internet Group
Boston, MA

Performed multiple roles on a daily basis, including those of a senior system designer and developer, Windows NT and UNIX system administrator and web hosting support engineer. Key player in the technology planning and implementation area. As a sole expertise in a number of areas, including object-oriented design and development, networking, network security and systems management, advised other team members on various technology issues.
Main tasks included web site backend implementation for clients, in-house product design and implementation as well as day-to-day activities mentioned above. Most of the coding was done using object-oriented technology, with JAVA as a programming language of choice.
Provided critical insight which allowed to greatly improve stability and efficiency of the internal systems.
Clients included Davox Corporation (now part of Aspect Software), Pilates Store, Lobsters-Online, Inc. and others.

September 1999 - January 2000

Thomson Financial
Medford, MA

Worked as a UNIX administrator in a large-scale web and dataserver hosting facility. Was responsible for maintenance and troubleshooting of multiple industrial-scale UNIX servers in SUN, HP and IBM platforms. Performed database maintenance of SYBASE and ORACLE databases as well as data recovery and general server troubleshooting.

May 1999 - July 1999

GTE International (currently part of Level3 Communications)
Cambridge, MA

As part of the Y2K team worked on the remediation of custom SUN Solaris machines hosting clients' mission-critical WWW sites and applications. Complex upgrades and modifications had to be accomplished requiring an absolute minimum in customers' downtime. Tasks included upgrades of the OS, DB servers and various other third-party software. Custom scripting and coding was often required to facilitate the necessary transition.

November 1995 - May 1999

WorldCare, Inc.
Cambridge, MA

Originally hired as an outside contractor. Accepted a permanent staff position in a three months' time. Throughout my whole tenure was a critical part of a small and continually overtasked team.
Responsibilities included day-to-day maintenance and support of a network of UNIX hosts. Maintained code control systems. Designed and implemented a data backup/archival system for in-house use. Created web pages using HTML, PERL and JAVA. With the emphasis on publicly available software restructured the environment to optimize and economize the development and production process. Modified publicly available software for local needs using C, Tcl/Tk, PERL, JAVA, etc. Was also involved in equipment and software installation and support at client sites. Worked with medical applications and protocols including DICOM, ISG's VRS graphical application and AWARE wavelet compression. Wrote system installation and maintenance scripts. Modified and integrated various third party software packages. Worked with multimedia devices in medical data capture/processing systems. Set up and supported LANs and WANs.

July 1992 - November 1995

Kenan Systems Corporation (currently part of Alcatel-Lucent)
Denver, CO - Cambridge, MA

Participated in database design of SYBASE databases.As part of a product team supported development effort in a heterogeneous UNIX environment. Oversaw the operation of a distributed development environment which included multiple geographically disjoint locations. Wrote a suite of PERL scripts that encompassed the local customizations to ClearCase as required by the project. Other responsibilities included release/code management using ClearCase code control system, SYBASE database administration, system design activities.

Summers of 1990 and 1991

Unisys Corporation
Cambridge, MA

Participated in the development of ATMS (Automated Traffic Management System), an air traffic management and control system for the FAA. Coding was done in Pascal against a proprietary database. The network consisted of a multitude of Aegis hosts on the Apollo platform. The development was done under DSEE as an integrated code control and management environment.

Personal

Born in St. Petersburg, Russia in 1969. Have lived in the US since 18 years of age. Attended Tver University (website in Russian) in Tver, Russia; Boston University and University of Massachusetts at Amherst graduating in 1992 with a Bachelor's Degree in Mathematics/Computer Science in 1992.

References

Available upon request.