« March 2004 | Main | May 2004 »

April 29, 2004

Working in Africa (remotely)

The University of Kwazulu-Natal, in Durban, South Africa, has been running a snapshot of our code for a few months now. Although we had a consultant do the initial machine build, package install and software configuration they needed someone to help with an Apache issue today. The consultant isn't available, so I got set up on the machine and found myself tinkering with a Linux box halfway around the world.

Had no idea what to expect in terms of connection speed or latency, wasn't terrible. Seemed somewhere slightly faster than a 56K modem (if my memory serves me right).

Easy enough to fix the issue, machine had been power cycled but Apache and MySQL weren't in the startup scripts. Wasn't a startup script in /etc/init.d/ for apache so stuck one there and spent a bit of time looking around, comparing directories to determine where the latest version of the code had been stashed (for doc root).

It's interesting looking at someone else's installation of the code and how it's been tweaked for their purpose. Quickly generates a list of things to do differently in our software development (like moving more things into config variables).

Posted by mike at 12:00 AM

April 21, 2004

Using Extra Caution When Filing Bugs Against Open Source Software

I hate finding bugs in open source software. Actually, it's not the finding as much as the reporting. I dislike having to point out a problem to someone who may be volunteering time to maintain the code, and I'm afraid of making a fool of myself by missing something obvious. That being said, software won't get better unless someone finds and reports issues (even if foolishly).

Fear of bug submission can be good. It means that rather than saying "it doesn't work" and filing an undescriptive bug I'm spending the extra time to dig into the source, find the offending piece code and a create a fix recommendation. Then I walk someone else through the issue to verify I haven't missed some obvious (or subtle) answer.

Having identified the line(s) and fix suggestions one might consider fixing the issue and posting a patch. In today's issue with XML::Twig, I can think of several ways to fix the problem (more than the two I suggested), but can't be sure if the author would agree that there's a bug, and what method he'd prefer.

To be honest, I have an extremely limited history in reporting bugs to the larger community (outside of applications at Tufts), maybe with time I'll gain confidence and not be as fearful.

Posted by mike at 5:53 PM

April 19, 2004

Nagging Thoughts about MySQL

MySQL 2004 was incredible, packed with much-needed information and interaction. However, since leaving MySQL 2004 I've had one thought that keeps coming back into my mind, is MySQL still the open source project I used to know?

For years, my perception of MySQL was that of a small, community-driven database that was making huge leaps toward the high-end RDBMS market, largely by grass-roots efforts.

The conference had an air of having graduated from the grass-roots and being ready for the enterprise with a full-fledged company of marketing, sales, internal development and 24x7 support folks. It was clear that MySQL AB is trying to convince and prove to people that MySQL is ready for the big time, at a significantly lower cost. While that is wonderful for MySQL and it's users I wonder about the relationship with the open source contributors. I heard at least two references to the fact that code is rarely contributed from non-employees, and that the community value is in submission of bugs. I think I'm blurring boundaries, but in my mind the true open source project is not just debugging.

Don't get me wrong, I think MySQL is still my database (since 1999), and it makes a lot of sense to graduate to offering support contracts etc. I just don't feel as strongly about advocating it as I did back when.

I'm curious to see how MySQL will balance it's relationship with the open source community along with it's interest in becoming a respected player in the enterprise database market.

Posted by mike at 11:31 PM

April 16, 2004

MySQL and PHP: Best Practices

Jim Winstead speaks about good practice for MySQL and PHP at MySQL 2004. Looking down this it appears that I've just duplicated Jim's slides, not sure how much value that adds if you can just go an get the slides yourself.

Security:
- do not run as root
- do not run as same user as apache
- set a root password
- all the usual password and account security practices (random password, know accounts)
- restrict connections to localhost or ip addresses (unless you know you can trust your DNS)
- skip-networking on mysqld if not needed
- bind-address = 10.1.10.5 (if you have an internal network)
- look at SSL, SSH tunneling and IP level restrictions (firewall, kernel packet filtering)

Don't Trust User Data - don't trust user data
- sql injection - use prepared statements
- XSS (cross-site scripting) - use htmlspecialchars() to get rid of html in form fields
- CSRF (cross-site request forgeries) - turn off register_globals, use $_POST and form keys

Optimization - Things to Benchmark
- overall page load time
- individual functions (use a profiler like dbg)
- specific queries (use slow query)
- benchmark as often as possible - know how each change has made a difference

Optimization - Compiler Cache
- APC in PEAR
- Turck MMCache
- IonCube Accelerator
- BWare Afterbuerner
- Zend Performance Suite

Optimization - Database
- don't use * in MySQL selects
- use mysql_unbuffered_query() - stream the results as soon as something is available
- use mysqli
- use the right storage engine
- help the optimizer
- use EXPLAIN
- tune buffers
- consider replication
- use query_cache - SHOW STATUS LIKE 'Qcache%'

Important to consider needs of the application when choosing a storage engines
- MyISAM - inserts that append to table do not lock
- InnoDB - ACID, row-level locking
- DBD - page level locking
- HEAP - (memory)
- NDB (cluster)

Non-Tips (not performance isues)
- mysql_pconnect doesn't work much faster and might introduce other issues
- echo vs print
- moving in and out of php

Use CSS templates to send less data and allow future flexibility

Posted by mike at 3:14 PM

Stored Procedures in MySQL

In the lab watching Peter Gulutzan demonstrate stored procedures and functions at MySQL 2004. Won't write everything (like the other sessions), just interesting notes not from the docs.

The nice thing about using a standard is the book is already written, even before MySQL has the functionality. Jim Melton's Understanding SQL Stored Procedures is recommended.

Before writing stored procs change the DELIMITER (Peter uses //) before starting because you'll be using ; in the procedures.

While having some trouble cutting and pasting Peter says, "It's time to confess I'm not the mac expert in the MySQL community, but I have [holding up the mouse] figured out how to use this nifty device."

"The computer is fairly predictable and does things in a robot-like fashion".

BEGIN and END create statement blocks, or compound statements. Each has a semicolon. DECLARE is a statement, not a procedure variable, can be overwritten in future statements, scoped by statement blocks.

Goes over syntax for CREATE PROCEDURE, CREATE FUNCTION, BEGIN, END, DECLARE, SET, IF, CASE, WHILE, REPEAT, loop_label, ALTER PROCEDURE, ALTER FUNCTION.

Peter shows declaring an EXIT HANDLER FOR 1216, which catches any error 1216 and inserts the test into a separate table. Pretty cool.

A few commands Peter uses:
select * from mysql.proc where name = '<procedure name>' //
select current_user() //
show warnings //

There are several things that are in the standard but not in MySQL, but there is quite a bit in MySQL.

Posted by mike at 2:02 PM

The Evolution of MySQL at Yahoo

Decided to hang out with Jeremy at MySQL 2004 to get the low-down (or down-low) on the non-technical view of MySQL at Yahoo! Over the years I've heard a bit about Yahoo from Jeremy's weblog and presentations at OSCON but it's never been a complete non-technical picture.

Why did Yahoo choose MySQL? The question implies that it was chosen by some decision-makers, but it didn't happen that way.

Can speak to why people in the company do use it. Cost, performance, cost, preformance (yes, mentioned twice for effect), stability, reliability, ease of use, open source, documentation, scales cheaply (use lots of small, cheap boxes), easy to find people who know it, access to source code to find and fix, APIs for every language, runs well on FreeBSD (didn't always).

Off the top of Jeremy's head, where does Yahoo! use MySQL? Sports, games, news, finance, mail, movies, abuse, classifieds, shopping, search, personals, blogs, message boards. It's probably easier now to ask where is Yahoo! not using MySQL.

How is it used? Internal applications (bugzilla, rt, intranet), log/data analysis, batch processing, self-service app,ications, front lines (content storage and search), transaction processing.

Yahoo's environment includes many user-facing properties developed by independant groups (not forced to use any specific standard or language). Yahoo provides centralized technology support (FreeBSD, Apache, PHP etc). FreeBSD 4.x is the standardized operating system, running on standard commodity hardware. There is an open source bias at Yahoo. Because Yahoo is geagraphiucally diverse there can be communications problems. Engineers worry about performance, scalability, points of failure and security.

Is MySQL replacing other products? Has replaced Oracle and BDB, but also replace homegrown databases.

Timeline
(???? - 2001):
- MySQL 3.20.xx - 2.32.xx
- small scale usage
- feed systems
- simple reporting tools
- rt
- single installs
- not user-facing
- not mission critical
- work done in isolation

2001 - 2002 (the Linux days)
- MySQL 2.23.xx ant testing MySQL 4.0.xx
- FreeBSD problems
- replication
- software RAID
- bigger applications (feed systems, partner tools, batch processing)
- multi-machine installations

2002-2003 (adoption phase)
- Jeremy spends more that 50% of his time on MySQL consulting
- custom built packages
- growing internal support (FreeBSD issues fixed, mailing list)
- hardware RAID
- front-line applications (finance, news, 9/11 memorial site)

2003-2004 (becomes a standard)
- everyone is using it (apps designed with MySQL using PHP)
- management suprise, a very short period of time to convert from "this is not good" to "good idea, keep doing it"
- Jeremy switches jobs, 100% advocacy and support for MySQL
- figured out load balancing, multi-master, InnoDB, hot backups
- mission critical applications (lose money if MySQL is down)

2004-???? (future)
- larger deployments
- performance tuning
- bigger hardware
- cross-country failover
- MySQL cluster
- using 4.1 and 5.0 features

Byproducts of Yahoo Using MySQL
- Bugs fixed (linux threads rather than pthreads, DNS, realpath, FreeBSD sockets)
- tools (mytop, mysqlsnapshot)
- documentation

Barriers to Adoption
- technical (failover, internationalization, MySQL does some stupid things (alter table rewrites entrire table), missing features)
- political/management (fear of change, db needs 10 years to mature, belief that relational databases should be on front-line)
- developers don grok SQL
- MySQL isn't a "real" RDBMS
- It's not Oracle

Q/A

What security issues, policies
- Yahoo has a security team and when MySQL came up on the radar the security team met with Jeremy and a few others to determine best practives

What is used for the search engine?
- Yahoo has purchased a lot of search companies. Yahoo doesn't use MySQL for search in the sense that it returns the results.

Do we run auctions on MySQL?
- May not, haven't spoken to the auctions

Is the multimaster the standard?
- No, get the question a lot but the application has to be written to accomodate for auto increment, which is a problem in multi-master. If it wasn't for the auto_increment/primary key issue we'd have a lot more multimaster machines.

What versions are you running?
- Most of the versions are 4.0.x, mainly 4.0.15.

Standard configurations for data and log files?
- Isn't really one, wide range from single cpu with one IDE disk to hardware RAID.

Is there a repository for the FreeBSD issues?
- a lot are in weblog, documentation

InnoDB vs MyISAM
- roughly 80% in MyISAM, 20% InnoDB but seem to be shifting because applications want the features of InnoDB

Posted by mike at 11:15 AM

One Reason to Attend Conference Keynotes

You end up sitting next to Intel's Kevin Smith on the Discovery Cove bus and can't ask good questions about Intel's work with MySQL, afraid that it was covered in his keynote.

Posted by mike at 10:03 AM

Why it is a great time to be an open source entrepreneur

Tony Perkins, founder of Red Herring and AlwaysOn network, delivers the second keynote of Friday morning at MySQL 2004. Tony also wrote The Internet Bubble: Inside the Overvalued World of High-Tech Stocks.

Bill Gates (Microsoft): "IT will provide over twice the productivity in the next 10 years as it did in the last 10 years."

Nobuyaki Idei (Sony): "The next wave of the Internet is going to be huge, and the companies that create products that work together will be the winners."

Tony suggests that the startup cost for an internet company was very high in the mid-90s and has dropped to a low in 2004 and will start to rise back in the next 6 years. Reason? We now understand the internet better and can do it right.

IM is a huge generational change, use IM for everything. 75% of teens use IM several times a week. Interesting list of things used; break up with someone, make new friends, do homework, discuss with teachers, cheat.

The current killer apps are broadband and wireless. Sun has 1.8 employees/desk, goal to be a 4-to-1 ratio by 2005. Accenture has 2 employees/desk, CEO has no office.

Who are the leaders of the revolution? Chiefs who set the strategy and budget, geeks who recommend and build, boosters who advise the chiefs and geeks and investors who supply the capitol.

Tony compares big media and open source media, suggesting that big media is old and that the open source media is where we're going:

OLD MEDIA -> NEW MEDIA
--------------------------------------------------------
one-way broadcast -> participatory (weblogs)
editors decide content -> readers create content
editors prioritize content -> merit driven content
anonymous readers -> socially networked readers
no cross-links bewtween brands -> blogsphere interdependent

Plug for the AO2004 Innovation Summit at Stanford. Global technology leaders, government and media leaders analyze and debate emerging technologies. Sun's new president and CEO, Jonathan Schwartz, will be moderating between several of Sun's competitors.

Posted by mike at 10:01 AM

How Far Can Open Source Go?

Marten Mickos, MySQL CEO, is delivering the first keynote of the final day at MySQL 2004.

Starts by asking: Is Ingvar Kamprad (founder of IKEA) the richest man in the world? Some calculation somewhere says yes.

MySQL is just one little planet in the open source galaxy, which is just one incarnation of the creative commons.

Marten describes a range of software offerings in three ways; toy, what customers want and overkill. MySQL has hit solidly into the "what customers want." 90% of the features at 10% of the cost. Marten believes the software industry is doomed if software continues to have bloated prices, that some adjustment is needed. In the long term and adjustment will be healthy for the software industry. MySQL is a part of the revival.

The airline analogy, how do you fly free of charge? Join the crew. If you contribute to the effort you can use it for free. If you are in 1st class or on the crew you arrive at the destination at the same time.

There are a lot of people making money from MySQL. The ISPs that use mysql make much more than MySQL AB did last year.

A plea from Marten for the audience to contribute more, help MySQL AB increase productivity by 6 times. The exchange is that the customer saves money but needs to contribute by advocating MySQL and contributing to the development. MySQL will not venture into services or hardware, but will remain in the software industry.

(diagrams not

Open source is a rising tide. IBM, Oracle, Google, Yahoo!, Amazon, SAP, Novell, venture functing, government, Microsoft (has released some code under open source).

4 stages of opens source adoption
1 - what's open source
2 - yes, we run Linux
3 - yes, we have an open source stack
4 - our architecture is built on open source

Marten says they haven't had to touch the venture funding they got awhile back, been able to cover operations with revenue from license and support.

Success of MySQL is dependant on the approval of the MySQL community.

Ownership of source code is good and should be protected, but patents isn't the right way to do that.

The commitment: "In the next few years MySQL AB will pass to its customers savings totalling more than $1 billion"

Posted by mike at 8:48 AM

April 15, 2004

MySQL Query Browser

Last MySQL 2004 presentation of the day . . . a demo of MySQL Query Browser.

Apparently (according to Jeremy) this tool has been in development for a matter of weeks, but is coming along so nicely they decided to demo it (the session was just announced today).

The GUI for both this tool and the administrator are clean and well designed.

Mike is showing the "build query" functionality where you choose the kind of query (select, insert, update etc). Really slick interface. It loads up the tables so you can easily click on columns to add to queries. Can split the windows into multiple and run a master query and then create a detail query using columns to build where clauses (unlimited levels). The gui allows you to run two queries and compare results.

Like the MySQL Administrator the help is inline, for functions, statement syntax.

Comes with the ability to bookmark queries, a script editor for writing sets of commands and stored procs (includes ability to create break points), use transactions, explain queries, export to csv and Excel and SSL support.

You can interrupt a slow query gracefully with the stop button.

After a slick demo, Mike opens it up for suggestions for features:
- ability to execute commands and have them "recorded" to a script in the editor
- look at issues with locking
- ability to connect through am SSH tunnel
- shortcut keys customizable
- share reports
- graphic representation of data

Posted by mike at 5:09 PM

Database Design Workshop

Sitting (on the floor) at MySQL 2004 in the Database Design Workshop with Lenz Grimmer, Jeremy Zawodny, Jeremy Cole and Sergei Golubchik). The session is a Q/A for database design (but really turned into a "ask anything about MYSQL").

A few introductions and then opened for Q/A.

Woman who is an Oracle DBA just started using MySQL two weeks ago is asking for a diagram of how MySQL works (memory map, key buffer, query cache etc).

Jeremy: MySQL is simple, and a lot is based on how many connections you need. Biggest thing to know about is global memory buffers. Two big ones are key_buffer (used for MyIsam), which is used to cache index blocks in memory so reduced disk interaction. The innodb_buffer_pool (used for InnoDB) is similar, used for caching data read from disk (both data and index). General recommendation is to have 50-80% of the buffer pool.

Jeremy Z (continues): Two others are the cache of table structures, if using a lot of tables or lot of users increase the table cache. The query cache (query_cache_limit and query_cache_size) can really boost performance, will cache results of query and won't go back to disk until the data underneath changes.

Does the query cache lock?

Someone on MySQL Team: When the cache finds a result it will lock that result, not everything else.

Jeremy Z: The global key_buffer has issues with dual processors before version 4.1

Can you preallocate tablespaces?

Jeremy C: Yes, in innodb you can specify.

Can you preallocate in multiple tablespaces in InnoDB?

Jeremy Z: No.

Will 4.1 prepared statements accept parameters?

Jeremy C: Yes

Jeremy Z: There is performance gain using perpared statements, but need to touch the C api to make use of it.

(too much back an forth to get . . . sorry)

Where are temp tables stored?

Jeremy C: Depends on size of table, can be in default temp location or in memory. Internally MySQL keeps track of number of tables to try to keep under control.

Downside for using all-memory heap table to speed up queries?

Jeremy Z: Heap tables don't have full-text indexing. Real downside is making sure the data is loaded up before the application needs it.

When you unpack ISAM tables it's limited in how many more rows can be inserted. Why?

Jeremy Z: Is it the 4G limit? (No.) Sounds like a bug, please submit.

Recommendations on fiber channel hardware?

Participant: There is a RAID card (Vortex?) which is good.

Merge table with 72 underlying tables, each with 600,000 rows each. The merge table is slower by 2 orders of magnitude. Why?

Sergei: Try a new version, the bug site has good details on bugs with merge tables which has been worked on in recent versions.

Talk a little bit about the c++ api vs. the c api.

Lenz: Go with the c, the c++ is esentially a wrapper for the c, and isn't maintained as actively as the c.

Talk more about merge tables.

Jeremy Z: You want to break it down by some sequence (ie a table per week) and then create merge tables to represent a set of the sequenced tables. It's a poor man's version of views.

Are merge tables in memory?

Jeremy Z: It's another .frm file which points to a group of tables.

With an app that can predict the growth, what do you do for preparation?

Jeremy Z: Don't worry about changes on the database size as much as the application change costs. Make sure the application can do things like get_read_handle() and get_write_handle().

Is a loop in PHP just as fast as using a merge table?

Jeremy Z: Depends on the round trip to get to the database. That might add some performance issues.

Abstracting MySQL?

Jeremy Z: Usually not worth it. Creating configuration is good.

Tips for performance tips in replication?

Jeremy Z: Use 4.0 or later. Bandwidth typically isn't the issue, latency is more of a concern. For most of the cases don't need to worry about disk i/o. A lot of time people think write time on disks is key, but seek time is important, because you're doing lots of small reads.

Jeremy C: It's hard to beat local disks.

Does MySQL support partitioning?

Jeremy Z: Not in the way Oracle does.

Multiprocessors, does MySQL work well on it?

Jeremy Z: Typically db isn't using CPU intensive methods (comparing, functions, stored procs).

Posted by mike at 4:25 PM

MySQL Administrator

Getting the afternoon sessions started with Alfredo Kojima talking about MySQL Administrator, a graphical tool for performing general administration in MySQL. For this demonstration the tool is pointed at MySQL 4.0, apparently is geared to work with releases greater than 3.23.x.

There is a slew of functionality that can be performed by the administrator. Alfredo is going over a list of all the functionality and I'm not going to take the time to list. Suffice it to say you can perform a ton of tasks from the administrator.

Features that I don't think we get using CLI:
- MySQL options grouped logically with inline explanations of options and guided value input
- select multiple of anything
- graphs for history of health (connections, traffic, query cache, key cache) that can be varied to show performance over time
- replication status of numerous hosts on one screen
- gui for sorting connections (SHOW PROCESSLIST)
- server logs parsed and presented in summary form (general, error and slow)

Currently the administrator is somewhat limited in that it can't execute certain commands remotely, only on localhost.

Posted by mike at 2:50 PM

MySQL Attendees: Thanks for the iTunes

Thanks to everyone who's at MySQL 2004, on a mac with a collection of iTunes. I kept telling myself that I would go to bed early last night, but found that I couldn't resist going through the playlists available from my room up on the 20th floor. Several different sources, each with hundreds of tunes, and suprisingly right along my taste. I knew it was getting too late when my judgement told me it was worth staying up another half-hour to listen to "Amish Paradise" and other Weird Al tunes. The memory of Weird Al tunes isn't doing much for me this morning operating on ~4 hours or sleep.

Posted by mike at 11:21 AM

InnoDB Multiple Tablespaces and Compressed Tables

Heikki Turri, creator of InnoDB, is delivering a presentation on the latest and greatest with InnoDB, multiple tablespaces and compressed tables.

Heikki got a PhD in Mathematics in Helsinki, Finland. Innobase Oy founded in 1995, goal was originally to make the worlds fastest disk-based relational database. In 1999 InnoDB complete with 110,000 lines of code. Did some marketing in 1999-2000 to find buyers, ran into Monty in August 2000. Wrote the interface to MySQL in 6 months and released InnoDB as a storage engine in March, 2001.

Main source or revenue (is profitable):
- license for hot backup tool
- royalty from MySQL pro license
- technical support contracts

Multiple tablespaces requested feature in 4.1.1, allows you to put each InnoDB into it's own .ibd file. Feature sponsored by RightNow Technologies, in Montana of all places.

Enabled by the innodb_file_per_table option in my.cnf. Will not move existing databases, but all newly created ones will go into a separate file. As before, the .ibd file contains both the data and index file (open to changing that in the future). If you remove the innodb_file_per_table option it will start creating new tables in one file, but will sill see the separate files.

Be extremely careful when downgrading from 4.1.1 to 4.0.18, read the downgrade instructions.

You can use symbolic links. Alter table used to recreate the table, removing the link and creating a new file in it's place. It sounds like that is not the case anymore and symbolic links can be used (not clear exactly).

The reason to have multiple tablespaces is to add flexibility. Cannot move .idb files between MySQL instances because they contain transaction ids specific for that instance.

It is possible to restore it to the database with DISCARD and IMPORT TABLESPACE. The .ibd file must be clean, meaning no uncommitted transactions, no unmerged insert buffer records, purged all delete-marked index records and .idb file flushed.

Table size on disk has been reduced by 20% in MySQL 5.0, also adding a zip-compression option to reduce disk space by another 50%.

Upcoming features
- Linux async disk i/o
- online index generation without locks

It's Heiki's birthday, Monty brought in a cake, and a large hourglass for "measuring database performance." Classy.

Posted by mike at 11:20 AM

MySQL and the ANSI SQL Standards

First MySQL 2004 session of the morning is Peter Gulutzan, going over MySQL and the compliance with ANSI SQL Standards.

Last year Peter preannounced some of the things that were going to be done, this year announcing some of them again, now they are completed.

(Peter has some mannerisms like Eugene Levy, think Waiting for Guffman).

Peter has been studying the SQL standards for years, was co-author of SQL-99 Complete, Really. Makes a good point, MySQL having a standards person on the staff demonstrates MySQL's commitment to getting standardized. ISO and ANSI publish the same spec. Microsoft and Oracle prefer to say seequel, but the standards docs always prounounce it Ess Queue Ell.

Are many (half-dozen) versions of the SQL standard, many of which claim can be "the" standard. SQL-92, SQL-99, SQL:2003 are all used, SQL:2003 became the official in December 2003. SQL:2003 is upward compatible with SQL-99, which is upward compatible with SQL-92. Each standard version makes clear distinction between mandatory and optional. Core compliance is doing all the mandatory. DB2 and Oracle claim core SQL-99, MS-SQL claims entry-level SQL-92. MySQL "continues to work toward" the ANSI SQL standard.

More info on db compliance.

How to check compliance of MySQL? Run NIST test, which is old and only looks at SQL-92. MySQL AB took SQL:2003 feature list and created a list of functions, added scripts for checking to the crashme script.

MySQL is compliant for data types specificed in standards except for a few instances. INTERVAL (partial), which is optional. Some difference in the date and time arithmatic. MySQL has a workaround for CLOB/NCLOB, NCHAR and NCHAR VARYING, converts them to CHAR and VARCHAR, but these aren't core items. MySQL, along with some other databases trim trailing spaces. Boolean is not supported, but not part of the core SQL. UDT is is non-core.

Character sets and collation features are well beyond the core spec, and far beyond most other DBMS.

SQL standard says that "table1" should not be equal to Table1 (ie "select column1 from Table1"). MySQL doesn't respect this

MySQL supports all operators in the core.

Subqueries are requred for core SQL compliance, important that it is available in 4.1.

All core joins are supported and some additional. Only join not supported is FULL JOIN, which is optional.

All constraints supported except CHECK, which is core (the only core feature that MySQL doesn't support). FOREIGN KEY has support, but have to be careful to create using index and specific column in primary key table. It works, but the syntax to create isn't exactly what the standard specifies.

Transaction control is supported, but want to keep MyISAM tables for speed in the cases where transactions arent necessary.

Set (or aggregate) functions work as specified. There is one case where a warning isn't delivered. MySQL is good at errors and OK, not so good at warnings. SQL standard requires warning very infrequently.

MySQL still needs to work on these issues to accomplish core compliance:
- schemas (container for tables) - very close
- select * from INFORMATION_SCHEMA.tables
- views
- grant and revoke - filled intent of standard, not necessarily exact - the security needs of MySQL demand some changes, but the core
- CHECK clause
- SQLSTATE - done in 4.1

The great MySQL Caveat - things we won't do
1. Change C-like syntax additiona
2. Add something that makes MySQL slower in default install
3. Change case sensitivity for identifiers
4. Add errors for illegal assignments

Features that are extensions of core SQL (all non-core SQL:2003):
- IDENTITY
- FLOOR, CEILING
- CASE
- CAST
- PROCEDURE

Magor missing non-core features:
- triggers
- UDTs
- XML

Final remarks:
- Much improved since 3.23
- Supports all but 4 Major non-Core Features
- Cathing up with upper classmates

Peter's script.

Posted by mike at 10:11 AM

Open Source's Role in Transforming Society

This morning's MySQL 2004 keynote is by Brian Behlendorf, founder of collab.net. Has been travelling and speaking quite a bit recently, visiting a wide range of organizations and individuals about the use of open source software etc.

According to the press, the open source movement started in 1998. Actuality is that it starts back in the origins of the internet.

Business isn't like it used to be, the spend-to-much-for-unmeasureable-roi and winner-takes-all mentality was getting old. With the crash of the dot-com people were more willing to consider alternatives. Software has continued to be more central to the operations of a company. Software has shifted from being a product to being a service.

There is also a political front. Met with Chinese representation two years back who indicated they love free software and that they'd been using Microsoft software for free for years. Joining the WTO means China must be better about piracy. In Vietnam it would take the average worker 18 months of salary to afford Windows and Office XP. Open source is a two-way street, organizations that use it should make efforts to contribute.

The right to fork is one of the most important parts of the open source license.

OSS is international, has been since the origins. Linus's first Linux post in 1991 got feedback from 10 countries in within one week.

Summary, open source software might be considered an uplift strategy, a set of patterns, techniques, or tools to solve Big Problems in a bottom-up way. The 90s was an era of dehumanization, engineers seen as cogs in a wheel. The open source movement sees the engineer as a creative person.

Examples of international movement:
- Lanka Software Foundation - started by IBM employee
- Rural wireless/VIOP in India
- E-government issues in EU
- China-Korea-Japan joint Linux distribution
- Thialand's laptop initiative - trying to get laptops for $600 - asked Microsoft for OS pricing, said $300 - got Linux distribution for free - went back to MS and showed them and MS said they'd reduce price to $10 - Thialand offers both and the Linux laptops outsell MS 2 to 1

Closer to home examples
- vendors surrounding MySQL
- the US governments core.gov
- groklaw
- deanspace, indyvoter.org
- MIT's user innovate lab - found patterns in development skateboards, windsurfing, batteries

Open source represents a templage to new ways to solving problems.

Posted by mike at 8:58 AM

April 14, 2004

Configuring Servers for Load Balancing

For load balancing our http and https traffic Tufts NOC uses a Foundry ServerIron switch.

I'm not an expert on load balancing but it seems like the method we're using is about as good as you can get. The DNS entry for the load balance URL (lb.tusk.tufts.edu) points at an VIP (virtual ip) address assigned to the load balancer. That VIP address is also assigned to a second loopback interface on each of the load balanced machines. Assigning the VIP to the loopback keeps it hidden, but allows the server to respond to traffic to that address.

When a request comes to the load balancer it rewrites the request packet to one of the real server ip addresses (based on a configuration options and existing connections to machines) and puts it back on the wire. The real server (not load balancer) will then respond to that request, leaving the remaining communication to happen directly between the client and the real server. Seems like an efficient way to balance traffic.

Assigning the second loopback ip address is simple, steps outlined in the Foundry docs.

The Foundry documentation has everything you'd want to know.

Posted by mike at 10:32 PM

High Performance MySQL, Finally

It's been a long time waiting. I first heard about this book at OSCON 2002, and wanted it then. Perfect timing to have it completed in time for (and here at) MySQL 2004.

Should I track down Jeremy and have him sign the book? Will it make me learn better with a signature on it?

Posted by mike at 7:52 PM

The Flood of MySQL Posts

I posted more than usual today, but realized that my postings weren't getting into the RSS feeds tonight when checking in with my aggregator. Apparently, some time ago, I played with MoveableType a little too much and prevented any new categories from being stuck into the feeds (MySQL being one of my new categories).

It feels pretty good to do such comprehensive write-ups of the presentations. I'm doing it primarily so I can refer back to them in the coming months, and will use them to pass along information to co-workers.

Just realized that I made 10 entries today . . . and some of them are long. That is just too much information.

Posted by mike at 7:50 PM

DBI Idioms

Giuseppe Maxia is speaking about DBI Idioms at MySQL 2004. The presentation is broken into three parts; connection, insert and fetch.

It seems hard to believe that 30 minutes will be enough to cover any subject in detail, but here we go. I numbered the idioms myself.

Idiom 1
Connecting. When putting the connection string, username and password into $dbh = DBI->connect you don't have to use the username and password. You can use mysql_read_default=$PATH/.my.cnf and it will pull the username and password from the conf file.

You can also use the .my.cnf group feature here by specifying mysql_read_default_group=groupname.

Idiom 2
Building SQL statements up as a string with a foreach is not recommended, should use ? and then pass the values in on the $sth->execute

Idiom 3
$sth->execute can take a hash slice, which eliminates the need to loop over sets of data to insert. Create the hash slice and pass that to execute and DBI will do the work.

Idiom 4
$sth->fetchrow_arrayref is the fastest method to get data, as much as 50% faster than fetchrow_hashref (depending on size of data).

Idiom 5
$sth->fetchall_arrayref({}) gets a hash instead of an array, which allows you to use the key to get values. Use $sth->fetchall_arrayref({id => 1}) to limit the results that come back (although I'm not sure why you wouldn't put that in your query).

And time is up . . . Giuseppe points to where's he's got a doc about the idioms to review the others.

Posted by mike at 7:23 PM

Protecting Private Information with MySQL

Listening to Peter Wayner presenting on Protecting Private Information with MySQL. This is a lab session, which means that there are computers in the room. Unfortunately there are only 5 computers and maybe 40 people in the room.

The slides for the presentation are on the screens of those 5 computers . . . wait, someone just handed me a thumb drive and I now have the slides.

Solutions for Securing Data:
- use basic permissions
- scramble data
- blur the data a little
- camoflauge the data
- misdirect attackers
- split the information across physical machines

Protecting the Box is Important
- physically protect the machine
- OS level
- good passwords
- firewalls
- encrypt the connection to and from

First Steps with MySQL
- set root password, grant permissions, and don't use root again

Looks like Peter used MySQL 3.23.x for creating the slides, the list of permission options in the slides is not what's in the 4.0.x branch has.

Peter discusses using transclucent databases, highlighting the importance of being informed about cryptography. The presentation gets into hands-on exercise in using MySQL encryption methods to encrypt certain, key pieces of information which will prevent an unauthorized user from putting the data into a complete picture and using it malicously.

Posted by mike at 4:38 PM

Introduction to MySQL Cluster

Listening to Mikael Ronstrom's presentation on MySQL Cluster at MySQL 2004.

Clustering softare originated in the telecom business. MySQL can use a new storage engine, which is the cluster with scalability and redundancy on three levels; number of applilcation server, mysql servers and number of nodes in the cluster.

Databae is distributed over many nodes, does updates in as many places as you specify in configuration. Database is automatically partitioned over the nodes. Failover capability if a node fails. Support for high update loads and high read loads.

MySQL cluster is affordable, easy to use and upgrade to.

Features:
- transactions
- synchronous replication
- auto-sync at restart of NDB node
- if complete cluster cache, can restpore from checkpoints in log
- online backups, no shutdown
- subscribe to row changes, see changes in gui
- two indexes (unique hash and T-tree ordered)
- online index build
- online software upgrade (rolling) - you change management server and go through each node and upgrade, upgrading the application last

Cluster is all available in 4.1

Research for the clustering was done from 1991 to 1996, drawn from Ericsson's experience inbuilding reliable soluctions for telecom. Development started in 1997, releases every year or so until April 14th (today), version 2.4.5 is released with MySQL integration.

Requirements
- availability - less that 5 minutes a year
- preformance - response time less than 5ms, throughput 10,000 transactions/sec
- scalability - many applications in distributed architecture

Performance
- 380,000 writes transactions/sec (128 Byte records)
- 1.5 million read transactions/sec (128 Byte records)

The MySQL server uses the NDB API interface to get data from the cluster. MySQL can use a variety of storage engines, so can have local MyISAM and InnoDB alongside cluster.

The cluster storage engine can only have 8000 bytes in a record, no blobs, but is on high priority list to fix that. You can easily get data from the cluster by using mysqldump and putting that into another table type, if you were wanting to have non-cluster environments with data from the cluster.

Mikael goes into the under-hood description of how the engine does reads and writes.

Development now is going into MySQL functationality, allowing you to do anything available in existing MySQL interface.

Because the storage is in memory, you need twice the amount of memory as data, which means that for 40G of data Mikael recommends 100G of memory (12G across 10 machines).

Posted by mike at 3:55 PM

MySQL User Panel: Why MySQL?

At MySQL 2004, observing a panel of MySQL users who are going to answer the question of why they chose MySQL. The panelist's are Michael Benzinger (Sabre Holdings), John Sudderth (Science Applications International Corp), Steve Stover (Quest Software) and Brent Nelson (iStockphoto).

In all comments, I paraphrased, did not take word-for-word.

How did MySQL end up at your organization?

Michael: The cost of databases they were considering were astronomical, CTO asked if they would look at open source. PostGres didn't preform, MySQL performed at an amazing level. Looked at creating their own database but after 4 weeks of work it was clearly reinventing the wheel.

John: Driven by cost, Oracle was restructuring fees and was going to cost them $250K, took 6 months to convert everything to MySQL. Were using perl (and since then some php). MySQL asked $15K for support. Been running for 3 years with no downtime.

Steve: Database is embedded in the application, needed to be invisible to the customer. MySQL was the best in ease of setup. Looked at sybase, postgres, sqlanywhere.

Brent: Started with ColdFusion on access, spent a brief time with ColdFusion on MSSQL and then moved to Apache/PHP/MySQL running on Slackware.

How important was it to have all the features and third-party tools?

Michael: Having all the features of Oracle wasn't what we needed. Was a lot of fluff. We needed a database, and asked how well it preformed.

John: ODBC was key, did some tests both pointed at their Oracle and MySQL and found 28% performance increase with MySQL.

What were some of the gotchas?

Michael: If the database doesn't work you have to put the symlink back to the old directory.

John: Can't find much . . . lived in a world for years there was a problem every day and vendors pointing at each other. It just works, everyday, and that's a really big deal.

Steve: Lack of views were a limitation. (Monty asks "What kind of views?"). "All views", says Steve.

Brent: Tried to use with Microsoft software with MySQL and had problems . . . felt that when they went to PHP things got better.

Open up for Q/A

Talk about support services, how does it compare to other vendors?

John: Were working with Oracle, only one DBA was allowed to talk to Oracle. We're asking questions that would stump Oracle support. MySQL tech support are their developers. Found a security issue and found it patched in the next release.

Michael: One time caused a crash on the database on thursday afternoon. Less that 24 hours later a patch was returned.

Steve: Have support, but haven't used it. Have worked with MySQL

Brent: Just doing things on own, getting some consulting services to analyze the database.

How big is the database? How often do the databases get reorganized?

Brent: Over 2G, a lot of bad data from the converstion. Are needing a redesign.

Steve: Fairly small when the application is released, using MyISAM. For larger installs can imagine being 50+G

John: 3G of data. 4000 hits/day. Have no need to reorganized the database.

Michael: Use InnoDB, about 50-60GB database on all machines.

How did the bringing of MySQL affect the existing in-house efforts?

Michael: Brought the DBA's over with training from MySQL.

John: DBA was an Oracle DBA and sysadmin, wasn't that tough.

Moving from Oracle to MySQL, did you find the datatypes limiting? What about XML?

John: Not familiar with all the datatypes, the dates required a functions. Started storing files as blobs.

Michael: From NonStop database format the timestamp was a microseconds and was used as the primary key. Wrote some functions to get to and from that.

Brent: Weren't doing much with access or MSSQL so it wasn't hard to do.

What were the components of the 250K cost for Oracle?

John: Was based on number of processors and type of hardware. Included maintenance and support. Always hear about the hidden costs of open source, but haven't seen any. Been easier to manage and less cost.

Has anyone had to sell this up to the CTO or CIO? How would you do that?

Brent: Our CEO has been receptive, moved with us. Cost was a huge reason.

Steve (who is the CTO): Make sure the product was well tested and that the savings could be passed on to the customer.

Are there types of applications that you might not use MySQL?

John: The list is shrinking. When started had to rewrite some subselect. If the application you are going to buy is very tightly tied to another database you might be forced to use that database.

Michael: It's a cost analysis, decide if it's worth redoing the application.

What features are you looking foward to:

Michael: Subselects, keeping MySQL out of considerations for a lot of applications.

John: Glad to be able to start checking off the features off as new releases come out.

Steve: Views. Stored proceedures.

Brent: Subselects, stored proceedures, clusters. Replication workflow is done manually, would be nice to have smoother replication. Failover.

Michael: Standards compliant is key, good that MySQL is sticking with standards.

How unbreakable, robust is the database?

John: We've been up three years. Have never had a day down or had any data corrupted. With MySQL, and what seems like a lack of competition, there seems to be a very simple.

Michael: On 45 machines over one year there has been one outage.

Brent: We've had some issues with having cheap hardware.

What about documentation?

Michael: Two years ago it was adequet, lately have found it much, much improved. The user comments allowed on the docs have been helpful.

John: Google groups has anything you're looking for.

Steve: It's not as robuest, but has really improved.

Do you find the amount of metadata in MySQL adequate?

Steve: No, there is not enough.

John: Oracle has good metadata, not in MySQL.

Posted by mike at 3:00 PM

Optimizing MySQL/InnoDB Performance

Peter Zaitsev is going over optimizing MySQL/InnoDB Performance at MySQL 2004. Not sure if I didn't get the organization of the talk, but having a hard time organizing it to be readable.

Peter uses DBT2 benchmark, with either a large or small workload against 200 warehouse databases with 30Gb data. Filesystem EXT3, swap disabled. A benchmark series is 4 runs, 2 "large" followed by 2 "small".

(talking fast, can't keep up)

After running the benchmark he uses mysqladmin (mysqladmin -i10 -r extended) for stats, then enables the log-slow-queries option (set to 2 seconds) to track down slow queries (changes updates to selects and uses EXPLAIN).

There will be a flood of common slow queries, won't see the bad queries until some of those are resolved. After wards use "show processlist" to catch typical queries.

Then goes to SHOW INNODB STATUS to review statistics.

A few thoughts on InnoDB options
When running ALTER TABLE for large InnoDB table:
- set innodb_buffer_pool to 80% of physical memory
- increase innodb_log_file_size to 512M

To increase innodb_log_file_size
- shut down mysql
- move old logs to backup
- increase innodb_log_file_size to 512M
- restart and wait for new logs
- be careful, larger logs means longer recovery time

When creating indexes on table:
- create indexes using alter table is fater than during create (and having subsequent inserts go through the process)

SHOW INNODB STATUS is a good command to see runtime stats
- look at "Buffer Poola and Memory" section, check reads/s and writes/s
- increase innodb_buffer_pool to as much as 1800M
- increate innodb_thread_concurrency= (num_disks * num_cpus)*2
- set innodb_log_buffer_size=8M to change i/o to the logs if have high log i/o

Schema Changes for Optimization (some of these appear to be innodb config options):
- shorten primary key, avoid using 2 or more keys
- updating primary key's is expensive, try to avoid
- rebuilding the table will help, removes fragmentation and reduces page splits
- in MySQL 4.1, using per-file tables increases performance, better data clustering and less concurrency
- in MySQL 4.1 UTF8 does not affect performance, unexpected
- tuning connections in the applications can improve performance, innodb_thread_concurrency (default 4) helps with many connections
- set the innodb_flush_method option, can avoid flushing to log at commit with innodb_flush_log_at_trx_commit=0 (set to 2 to flush to OS cache)
- may get benefit from tweaking query_cache

Peter concludes by saying that optimization is a never ending job.

Posted by mike at 11:06 AM

MySQL at Sabre

Michael Benzinger (Sabre Research Group) is speaking (at MySQL 2004) about the process of bringing MySQL into Sabre.

Sabre sas originally a portion of American airlines, was the reservations part. American still owns, also includes travelocity. Sabre has 2.06 billion/year revenue (only get revenue when a ticket is booked), 6500 employees. Sabre originated in 1960 with the world's first OLTP, written primarily in assembly language. Sabre has to be up 100% of the time, and wants to invest in the future. Open source software is the future, using Linux and MySQL (commercial license and 24x7 support).

Sabre evaluated several databases and couldn't come to a conclustion they liked so they decided to look at open source. Databases evaluated were Oracle and times10, MySQL was determined to be as fast or faster for the kind of transactions needed. Michael had used PosGres so started by porting the application there and found performance was unacceptable. Downloaded MySQL on a weekend, on Monday morning ran the benchmarks and had both himself and the CTO in disbelief.

Over 3 billion fare combinations for a single customer request (multiple airlines, flights, fares, dates, prices, taxes, surcharges). Schedules are not stored, but created dynamically. Up to a million fares can change in a day, 5 updates a day from the airlines.

For the shopping portion everything is done on a Linux farm (45 HP rx5670 4x1.5GHz 32 GB RAM). Pricing/booking is handled by a farm of HP NonStop servers, 17 nodes with 6 processors in each. Master database (not MySQL) is kept on NonStop and replicated to the Linux farms via GoldenGate Extractor/Replicator. The GoldenGate extractor replicates anywhere from 10 to 60 GB of data a day, typically gets data to all machines within 3-6 seconds. InnoDB is used, database on Linux are currently ~60GB in size.

Sabre needed precompiled SQL statements, MySQL doesn't have. Created a script using 1000 lines of awk to convert ESQL to MySQL C API calls to get precompiled sql functions. Currently sabre is doing testing on a table that contains 1.5 billion rows of data.

site59.com is a recent sabre-related site driven by open source, aquired in 2002 by Travelocity, uses MySQL and PHP.

The results of all the work? 50% decreas in development cost, decreased fare loading times, reduced runtime costs (less time to than to keep old mainfraim up), increase in functionality.

Posted by mike at 9:34 AM

MySQL State of the Dolphin

David and Monty are delivering the MySQL 2004 State of the Dolphin, also titled "From the Basement to the Enterprise"

A little bit of history of the project, it's interesting that it wasn't until 1995 that Monty and David decided to write a SQL engine, and here we are 9 years later at the MySQL conference. The first source release was available on Nov 19, 1996, 1000 downloads by Dec 9, 1996 (now have 35K downloads/month).

Covering the standard bases, MySQL is named from Monty's daighter My (actually pronounced Mee). Suprisingly, Monty's son is Max, thus MaxDB (renamed from SAPDB).

Monty says the main reason for a good manual was when questions came in they answered them by putting it in the docs.

The dual licensing model was with them from the start. The original business plan was to not focus on database theory, but practical usability. At the beginning they set a goal to make it possible to install and be using in 15 minutes, hated the process of installing.

Review of the big names using MySQL: Yahoo, Cox Communication, Sabre.

How they make money: "If you are free, we are free, if you are proprietary, we are commercial." Also do support, consulting, certification. Funded by Benchmark Capital, spending it pretty slowly. Using it for marketing and documentation.

Support for huge set of languages, and almost as many platforms. SCO Unixware support is being stopped. MySQL maintains ~20 test machines and before any release they compile it on all machines, wide variety of compilers. Performance

Today MySQL AB announces the release of it's cluster software, available in source only right now. You need a lot of memory, the cluster keeps the tables in memory.

Review of new/upcoming features, 4.1 tree should be to beta in the next month, production in Q4, 2004. The future is to implement all SAP R/3, which includes most of the SQL standard.

Look at the crash-me, mysql administrator (not yet available for OS X).

The State of the Dolphin concludes with a discussion of open source, a slide entitled "Free Databases get Better":
- repeatable bug reports as as worth as much as code
- a lot of testing for new features, because people are asking for it
- MySQL hires people who already know the code
- Documentation is not limited to what's been written, you can actually go to the code to see how it works (also helps to ensure security holes are absent)

David, about software patents, "We think they suck."

Posted by mike at 9:01 AM

MySQL 2004 Underway

Arrived in Orlando, settled and ready to be informed. This isn't the Florida I remember from a few weeks back. Tuesday night it's cloudy, windy and cold (relative to what I think it should be).

The conference welcome indicated 600 people have registered this year. It seems like I heard somewhere that last year was 150, which would mean quite a jump in attendance.

More to come . . .

Posted by mike at 8:33 AM

April 12, 2004

rsync to Keep Machines in Sync

Today I'm working on a process to keep directories on load-balanced servers in sync. I don't have the energy to document the ongoing debate about why wer're not using NFS for shared storage. Suffice it to say we're not using it (may hear more on that later).

The solution we've been using is rsync. In the past the webservers have pulled files from a central machine, where all files needed on the web were dumped. With the upcoming release of a web-based authoring tool we now enter a more complex arrangement where any one of the webservers will be getting new uploaded files, adding the necessity for updates going in each direction.

Thinking about each webserver being responsible for syncing to all the other webservers was more than anyone wanted to think about. Even having each webserver sync down to a central location was bringing up more questions than we wanted to answer. We've decided to try having one central machine handle all data synchronization. It will first rsync down from each webserver to a central directory, and after gathering all the new files will go back and push out to each machine. The current thinking is to have one script to do this which will be run every minute from cron, with an internal check to exit if the last process is still running.

Today I'm writing the script, and came across this issue. There are currently 18 directories with ~50,000 files. We've determined that only 4 of these directories need to be synced on the minute, the other ones can be done nightly. The 4 directories currently contain ~5,000 files.

The question: Is it faster to run 4 individual rsync processes, one for each sub-directory, or to use the --exclude option on one huge dir?

Running an rsync process for each sub-directory (with no files to sync) takes ~8 seconds. To run one process and --exclude the stuff I didn't want takes 2.7 seconds. That can be a pretty significant savings when trying to keep several machines up to date.

For the curious, the options I'm using for rsync are: -rltue ssh

Posted by mike at 6:08 PM

April 11, 2004

Switching from embperl to Mason

Several years ago embperl was embraced as the scripting language for the presentation layer of our application. We've had a few issues with embperl, and have talked several times over the last few years about moving to Mason.

The switch is finally happening. Two of our current projects are using Mason, which will run alongside embperl, allowing us to gradually migrate the templates. It feels good to take a system that's had several years worth of work by a half-dozen developers and build it from the ground up.

There may be technical reasons for the switch but the most compelling reason is the activity difference between the projects. Mason is being more actively developed by a larger group of people, and has a pretty impressive list of sites. We also feel like embperl has left Apache 1.3.x behind for development on Apache 2, but without a stable mod_perl for Apache 2 we're not willing to move up from 1.3.x. Mason shares our view of moving to mod_perl 2 (1.99_x), which is somewhat reassuring.

Posted by mike at 10:06 PM

April 9, 2004

Downloading Songs from Kazaa isn't Really Free

Harking back to my 10th grade economics professor saying that nothing is ever free. Wired is running a story (who isn't) about MP3Concept, a Mac OS X Trojan horse.

Nearly half of the executable files downloaded through Kazaa contain malicious code like viruses and Trojan horses, according to a recent study by security firm TruSecure. Out of 4,778 files downloaded in one month for the study, nearly half contained various types of nefarious code.
A previous article on Wired digs further into the Kazaa research.

It's been several years since I got tunes from a sharing service, not sure I would have noticed, but never would have guessed that it was more than music.

To say the music is free might be true in the sense that you didn't have to put up cash before the download, but it might end up costing in other ways.

Posted by mike at 12:48 PM

April 2, 2004

Redistribution of Investment Pays Significantly

Whan I started at Tufts a few years back I beefed up my retirement allocation, figuring that now's the time to really get serious. In the past had dabbled a bit and put some away here and there, but nothing serious. When I moved to Tufts I put the entire increase in salary towards retirement, split between two different investment firms, Tufts contributions into one, mine into the other.

I didn't look into how the accounts were configured by default, but for three years now I've been getting the statements telling me how poorly my retirement investments have performed. I have thought several times I should change the fund allocations but never got to it. When Pete and I got to talking about saving for retirement I finally got enough excitement/interest to do something.

Last week I spent some time looking through the available fund options and made some changes to diversify and be more aggressive (the default TIAA-CREF fund, money market, grew a measly .7% last year).

Without getting into specifics, I have watched with dropped jaw over the past week as the funds have rocketed up. I guess it's not hard to beat .7%, but watching the difference between how a money market performs vs a index fund is astounding and exciting, each night I check in on the accounts and am continually suprised at how much they grow in just one day.

If anything I have relized that with some attention I can make a significant difference in the growth of my retirement funds. Am definitely going to look into an investment book or two, and try to make a plan which will enable me to continually fine-tune the investments.

Posted by mike at 11:23 PM

April 1, 2004

New Office

When I was interviewing at Tufts there was a point made about us being in a temporary office for a bit until we got a permanent place. Today, just a little over 3 years later, we moved into our new office. There are many reasons why it took a long time, the most important is that we did everything right, not settling for less than what we thought would be right. For example, we turned down an offer for a space because it had no windows knowing it would push off getting space. We also took a good deal of time working with an office furniture consultant to have the space analyzed and furniture designed to meet workflow and staff needs.

I thought I'd better take a photo before it got messy (I'm pretty clean, but not this clean).

It's almost hard to believe the day finally came.

Posted by mike at 11:07 PM

Getting into the Social Networks thing with LinkedIn

I've watched the ongoing dialogue about social networks, uncertain if or how I'd get involved. About a month ago I got an invitation to join LinkedIn from Jarrett Goetz, a network guru I respected from back at the days of Jenzabar (although we were usually butting heads back then).

While vacationing with Pete, we discussed social networking, he recommend LinkedIn as one that hit things at a likeable angle.

The invitation to join Jarrett's network expires tomorrow, so I figured why not get set up. Was pleased to see a few other respected colleagues from Tufts had established networks and found some past co-workers.

Will be interesting to see where it leads. At this point I'm more interested in keeping in touch with folks than generating job leads.

Posted by mike at 6:02 PM