You are here: Home / Archives for 2007

MySQL Camp II – Introductions

by

We have started MySQL Camp II. The first session is Introductions.

I didn’t catch all the employers, but here is part of the list of attendees at the Introduction section. Great to see multiple people from many places including ESPN, priceline.com, Proven Scaling, Solid Tech – sponsors of Dorsal Source, ForSaleByOwner. fontshop.com, 9Mmedia, CafeMom, JP Morgan, Upoc, ClubMom, Stock Photo Finder, AmieStreet,LogicWorks, Skoll – Distributed Continuous QA , AOL, Minggl, Minggl New Test Site, Visibone. Others include OnlineBuddies.com, NT Snort User Group, DreamweaverNY User Group, A law firm. As well as a few people from MySQL.

Many people mentioned having an Oracle background, or working with Oracle now, at least 6 people that heard me speak at “MySQL DBABootcamp for the Oracle DBA” last week.

There were a lot of MySQL Beginners here which was really great.

And now we are onto the discussion of the sessions.

MySQL Camp II – It begins

by


Well readers, your either here or your not. MySQL Camp II starts today in Brooklyn, New York, at Polytechnic University. Last night’s pre drinks meetup in NYC went well, but today it’s brass tacks time. View Larger Map

For those of you not able to make it, IRC@Freenode #mysql-camp will be the place to hang out to hear what’s happening. If your not at the camp, please identify yourself. Be sure to also check out the Camp Web Site MySQL Camp II for the plans for today and tomorrow.

For those of you not here, MySQL Camp III is already in planning.

MySQL Backup & Recovery – Part 1

by

I realized recently from observation that some smaller websites which use MySQL do not have a working backup and recovery strategy. Some may have a backup approach but it will not work in a recovery. As part of a number of discussions on Backup & Recovery here is part 1.

Using straight ‘cp’ for Backup

Using ‘cp’ to simply copy your running database *DOES NOT WORK*. You may be lucky, but in a world of guarantees, this is no guarantee that your can recovery your system. Why is this so.

  • The data is not consistent during the backup. If it takes say 5 minutes to copy your files, they are copied probably in alphabetical order, what if data is written during the backup to a table starting with ‘A’ and a table starting with ‘Z’, the A file has already been copied.
  • When using MyISAM, Indexes are not flushed to disk unlike Table data. This means that while MyISAM has the facility of recover and rebuild indexes using myisamchk, you need to know if you have corruption, you need to rebuild your tables offline, and there is not assurance your data can be corrected.
  • Likewise with using Innodb, Data and Indexes are not flushed to disk. While Innodb does flush the redo logs to enable crash recovery, I have seen on a production website, granted 4.1 that Innodb failed to recover and caused major downtime and serious business ramifications.

Using ‘cp’ correctly

In order to copy your database, there are two different ways. The first is to shutdown your database first then copy your data files. Be sure to also copy your my.cnf file, this is important in a recovery process. The second is to use FLUSH command. For example.

mysql> FLUSH TABLES WITH READ LOCK;
-- In second session copy all data
mysql> UNLOCK TABLES;

The advantage is you don’t have to shutdown your instance, the disadvantage is that FLUSH TABLES will halt any WRITE statements to your database, so depending on the time to copy your data files that could have significant effect on your system. In a high write environment the FLUSH command could also take a significant time to complete.

I’ll leave with one simple tip. You should always have sufficient diskspace on your machine for performing the backup. This is important for two parts. First, if you say compress your backup during the process, you are taking a longer time then a straight copy and your database is unavailable longer. Second, over 90% of any recovery occurs with last nights backup, and the time take to uncompress the backup affects your recovery time, particularly in a large system. Your ‘cp’ should indeed be a two phase process. First ‘cp’ on the local server for a local backup, then have an off server compressed backup.

In my next Part, I’ll discuss more alternatives to improving your backup strategy.

References

FLUSH TABLES

Today's interesting MySQL Error Message

by

You have to love error messages some times. Today is was “ERROR 1289 (HY000): The ‘UNKNOWN’ feature is disabled;”

For those interested.

mysql> select version();
+-------------+
| version()   |
+-------------+
| 6.0.0-alpha |
+-------------+
1 row in set (0.00 sec)
mysql> set session sql_mode=NO_ENGINE_SUBSTITUTION;
Query OK, 0 rows affected (0.00 sec)

mysql> create table T2 (id INT UNSIGNED NOT NULL) ENGINE=InnoDB;
ERROR 1289 (HY000): The 'UNKNOWN' feature is disabled; you need MySQL built with 'UNKNOWN'
to have it working

In this case it’s obviously something that’s not quite right, so being a good community contributor I raised a bug. You can read more at Bug #29373.

MySQL values the contributions from the community, even as trivial as documentation or error messages. If you have an inquiry raise it at the forums or the lists, or if it’s obviously an issue raise it in the Bugs System

SHOW STATUS WHERE

by

When you use SHOW STATUS can can restrict with the LIKE syntax, allowing for a subset of values. For example:

mysql> SHOW GLOBAL STATUS LIKE 'Com%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| Com_admin_commands       | 0     |
| Com_alter_db             | 0     |
| Com_alter_table          | 0     |
| Com_analyze              | 0     |
| Com_backup_table         | 0     |
| Com_begin                | 0     |
| Com_change_db            | 0     |
| Com_change_master        | 0     |
...

That’s great, but sometimes you want specific values. Using WHERE can achieve this. For Example.

mysql> SHOW GLOBAL STATUS WHERE VARIABLE_NAME IN (’Com_insert’,'Innodb_buffer_pool_pages_latched’,'threads_running’);
+----------------------------------+-------+
| Variable_name                    | Value |
+----------------------------------+-------+
| Com_insert                       | 0     |
| Innodb_buffer_pool_pages_latched | 0     |
| Threads_running                  | 1     |
+----------------------------------+-------+
3 rows in set (0.00 sec)

Cool, the downside is you loose the wildcard capability, however you can string commands together with LIKE

mysql> SHOW GLOBAL STATUS WHERE VARIABLE_NAME LIKE 'innodb%' OR VARIABLE_NAME LIKE  'com%';

MySQL NY Meetup – Part 2 in our series

by

Last night at the MySQL NY Meetup we continued on from a very successful July presentation on “Practical Performance Tips & Tricks”. I must admit after speaking and standing all day for the MySQL DBA Bootcamp for the Oracle DBA it was a stretch, and we didn’t cover all material as expected, but the evening was still very productive to everybody. Links are here for my August Presentation and July Presentation.

Thanks to Marc and the team from LogicWorks for again sponsoring our NY Meetup Event. We don’t get the beer and food any other way.

As a consultant working on client sites even in the time from the previous meeting, I see a number of simple steps that can be applied to every single MySQL environment and last nights talk placed some focus on this. The following is the homework that has been set for attendees, so next month we can have a panel discussion and then continue more on the analysis of MySQL.

This homework focuses on three areas. It seems a lot, but it will be worth it.

  1. Collection of SQL statements (in various forms)
  2. Monitoring of raw MySQL Status information
  3. Review of Schema Data Types for more optimal storage

Collection

This information is to be collected.

  • Production is to have slow query log enabled and long_query_time =1
  • Daily review of Production slow query log to view the longest running and most frequent (Slide Aug-13)
  • All development and test environments have general log and slow query log enabled.
  • All application paths to be tested in development or testing, to capture all SQL. Use self documenting approach (Slide: Aug-21)

Outputs
Your outputs will include offending SQL statements via the Slow Query log that require investigation, extra credit for doing an EXPLAIN and a SHOW CREATE TABLE for each table in the query for analysis of improving the queries.
You will have a review of every SQL statement. A simple desk check will determine are there any queries which are exact duplicates, are there any queries that could be combined (as they use the same where condition). Are there any queries that are repeated a lot, but with only changing values. These are a clear candidate for combining into a single query.

MySQL Status Information

The MySQL Status can provide help into where to look for problems. This is the raw fundamentals that either home grown scripts or MySQL Network Monitoring and Advisory Service use to provide monitoring of important information (e.g. Buffer Hit Ratio) and can provide alerts of present or potential pending problems. We will not be reviewing all these status (~250) or developing any detailed analysis, however this is designed to show you the building blocks.

You should collect figures for 1 minute, 1 hour and 1 day that represent appropriate load on your system.

1 Minute

$ mysqladmin -uroot -p -r -i 1 -c 60 extended-status | grep -v " | 0 " > minute.log

1 Hour

$ mysqladmin -uroot -p -i 60 -c 60 extended-status > hourly.log

1 Day

$ mysqladmin -uroot -p -i 3600 -c 24 extended-status > daily.log

Extra credit for downloading Statpack and doing an analysis of the statistics. The trick is to take the first and last status output from each of the Hourly and Daily logs to get a 1 hour picture and a 1 day picture.

Review Schema

For reviewing the schema, try to use a static production sized copy (e.g. a copy of production in a test environment). This ensures you don’t make changes that will cause your production system to fail, and before and after figures are for the same data set as it’s not changing during production use.

  • Calculate size of present data
  • Review schema for immediate size improvements (Slide Jul-59)
  • Apply size improvements and re-run to see size improvements.

SQL

Calculate Size of all schemas

select table_schema, count(*) as tables,
           sum(data_length+index_length)/1024/1024 as total_mb,
           sum(data_length)/1024/1024 as data_mb,
           sum(index_length)/1024/1024 as index_mb
from information_schema.tables
group by table_schema
order by 3 desc;

Calculate Size of all tables for given schema

use test;
select table_name,engine,row_format,avg_row_length,table_rows,
          (data_length+index_length)/1024/1024 as total_mb,
          (data_length)/1024/1024 as data_mb,
          (index_length)/1024/1024 as index_mb
from information_schema.tables
where table_schema=database()
order by 6 desc;

Learning MySQL as an Oracle DBA

by

Updated

I have an entire section now devoted to various MySQL for the Oracle DBA Resources. You will find additional information here.

This week I presented two one day free seminars, “MySQL DBA Bootcamp for the Oracle DBA” in New York and San Francisco. Both were very successful days providing an opportunity to speak to seasoned enterprise professionals.

One question I was asked was “As an Oracle DBA, how can I become a MySQL DBA, what do I do, where do I start?”

Here are my references and recommendations that have zero cost to get started.

  • Read the MySQL Documentation Reference Manual.
  • Download MySQL install and use it.
  • The MySQL Developer Zone is a great sources for articles, information and references.
  • Planet MySQL is our official consolidated Blog Aggregator. Read it daily.
  • The MySQL Forge is a detailed reference of MySQL related projects, code snippets, wiki and MySQL WorkLog. A look at what MySQL is doing in future versions, and what others like yourself would like to see in future versions.
  • The MySQL Conference website has many papers from the recent 2007 Conference. You can also review the 2006 Conference Papers.
  • MySQL has various Forums and Email Lists. We have a specific Oracle Forum to assist Oracle DBA’s and Developers with MySQL questions.
  • MySQL also provides a large number of White Papers and Case Studies in it’s Why MySQL section. These are helpful to see how MySQL is being used today.
  • Register at mysql.com, if you join a list, fill in download form or respond to a forum, your already registered, but if not you will get a regular newletter that provides helpful information, including events, webinars and training
  • Sheeri Kritzer, the She-BA of MySQL and this years community award winner has an extensive list of resources on her website including podcasts and a long list of Audio & Video from the recent MySQL Conference.

For additional Oracle to MySQL specific references from recent conferences include 2006 – MySQL For Oracle DBA’s,
MySQL For Oracle Developers and 2007 – MySQL For Oracle DBA’s and Developers.

For your reference material, I would initially recommend the following books.

  • “MySQL 5.0 Certification Guide” – which all attendees received for free.
  • “MySQL Cookbook” by Paul DuBois, “MySQL” by the same Paul DuBois or “Pro MySQL” by Michael Kruckenberg and Jay Pipes.
  • “MySQL Administrator’s Guide and Language Reference”. – This is just a printed copy of the MySQL Manual, however some people may appreciate this.

Following that, additional resources depending on your level of interest in development or internal workings etc would include.

  • “MySQL Stored Procedure Programming” by Guy Harrison.
  • “Understanding MySQL Internals” by Sasha Pachev.

MySQL Professional Services also provides training and certification for MySQL. With 9 different training courses held world wide, and 5 different certification courses there are various programs to suit DBA’s and Developers at different skill levels. For more information see MySQL Training and Certification.

Let's improve MySQL security

by

We have all done it in the past, and probably most people that read this (will admit| or lie) to still doing it, but everybody must start making an effort to improving MySQL security in the usage on your MySQL Installation, including just on your laptop, and in presentations that people read.

I spotted a reference article on Planet MySQL this evening and without looking at the details the syntax presented typifies two basic and fundamental 101 MySQL security issues.

1. Always, always, always have a password for a MySQL account, especially for the ‘root’ user.
2. Don’t use the ‘root’ user unless you really have to. The SUPER privilege is just that SUPER, there are many things you really don’t want every person accessing to have. In a larger environment you want to ensure good principles, but also in single developer environment.

I was asked on Thursday at a seminar I was giving. “Our developers login as ‘root’ for development and use ‘root’ for MySQL access, is this bad?”. To which my simple response was “Yes”.

I think MySQL should improve the product and not allow a root user without a password. You can of course use mysql_secure_installation to set this, but it’s not a mandatory step. You can also use SQL_MODE to ensure users can’t be created without a password, in both instances the horse has already bolted.

My iPhone Bill

by

Today I got back home to my iPhone bill. Lucky for me it was only 15 pages long. Rather stupid, but this news has been circulating for a few days. You can read More Google News stories including creative titles like “How many trees did your iPhone bill kill?”, “A 300-page iPhone Bill?” and “iPhone: The 800-Pound Gorilla Spawns a 300-Page Bill”. Not only did they waste trees, but I read one article stating it cost AT&T $7 in postage for one bill.

The present news is that the only way AT&T will address this is if you accept to get a paperless bill. How stupid, how about they just not print every data transfer, it’s not like you can actually get on a data plan that’s not unlimited anyway.

5.1.20 Gotcha – The MySQL Error Log

by

While using the latest MySQL 5.1.20 yesterday I came across another situation that was not expected as with previous editions of MySQL. The background is experimenting with DRBD. When I configured MySQL to startup with a /etc/my.cnf file with data on a DRBD partition I got a failed startup error message with mysqld_safe.

$ bin/mysqld_safe &
[1] 12615
070720 10:10:42 mysqld_safe Starting mysqld daemon with databases from /drbd/data
070720 10:10:42 mysqld_safe mysqld from pid file /drbd/data/newyork.localdomain.pid ended

Ok. Well this happens so I went to the data directory to look for `hostname`.err.

$ cd /drbd/data
$ ls -l

What the! There is no error log. Then the discussion started about this. Apparently mysqld_safe now uses syslog (e.g. /var/log/messages) for logging messages. Ok, but where is the line between mysqld_safe and mysqld. There was some confusion here about this, however investigation of /var/log/messages showed what was necessary to at least find the error.

$ tail -30 /var/log/messages
Jul 19 16:59:51 newyork mysqld_safe[6156]: Starting mysqld daemon with databases from /drbd/data
Jul 19 16:59:54 newyork mysqld[6160]: /usr/local/mysql/bin/mysqld: Can't find file: './mysql/plugin.frm' (errno: 13)
Jul 19 16:59:54 newyork mysqld[6160]: [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it.
Jul 19 16:59:54 newyork mysqld[6160]: InnoDB: The first specified data file ./ibdata1 did not exist:
Jul 19 16:59:54 newyork mysqld[6160]: InnoDB: a new database to be created!
Jul 19 16:59:54 newyork mysqld[6160]: InnoDB: Setting file ./ibdata1 size to 10 MB
Jul 19 16:59:54 newyork mysqld[6160]: InnoDB: Database physically writes the file full: wait...
Jul 19 16:59:54 newyork mysqld[6160]: InnoDB: Log file ./ib_logfile0 did not exist: new to be created
Jul 19 16:59:54 newyork mysqld[6160]: InnoDB: Setting log file ./ib_logfile0 size to 5 MB
Jul 19 16:59:54 newyork mysqld[6160]: InnoDB: Database physically writes the file full: wait...
Jul 19 16:59:54 newyork mysqld[6160]: InnoDB: Log file ./ib_logfile1 did not exist: new to be created
Jul 19 16:59:54 newyork mysqld[6160]: InnoDB: Setting log file ./ib_logfile1 size to 5 MB
Jul 19 16:59:54 newyork mysqld[6160]: InnoDB: Database physically writes the file full: wait...
Jul 19 16:59:54 newyork mysqld[6160]: InnoDB: Doublewrite buffer not found: creating new
Jul 19 16:59:54 newyork mysqld[6160]: InnoDB: Doublewrite buffer created
Jul 19 16:59:54 newyork mysqld[6160]: InnoDB: Creating foreign key constraint system tables
Jul 19 16:59:54 newyork mysqld[6160]: InnoDB: Foreign key constraint system tables created
Jul 19 16:59:54 newyork mysqld[6160]: InnoDB: Started; log sequence number 0 0
Jul 19 16:59:54 newyork mysqld[6160]: [ERROR] /usr/local/mysql/bin/mysqld: Can't find file: './mysql/host.frm' (errno: 13)
Jul 19 16:59:54 newyork mysqld[6160]: [ERROR] Fatal error: Can't open and lock privilege tables: Can't find file: './mysql/host.frm' (errno: 13)

To ensure backward compatibility now with previous versions of MySQL, it is necessary to specific in my.cnf the log-error parameter.

#my.cnf
[mysqld]
log-error = /drbd/data/mysql.err

Starting gives you expected error logging.

$ more /drbd/data/mysql.err
070720 10:16:30 mysqld_safe Starting mysqld daemon with databases from /drbd/data
/usr/local/mysql/bin/mysqld: Can't find file: './mysql/plugin.frm' (errno: 13)
070720 10:16:30 [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it.
070720 10:16:30  InnoDB: Operating system error number 13 in a file operation.
InnoDB: The error means mysqld does not have the access rights to
InnoDB: the directory.
InnoDB: File name ./ibdata1
InnoDB: File operation call: 'open'.
InnoDB: Cannot continue operation.
070720 10:16:30 mysqld_safe mysqld from pid file /drbd/data/newyork.localdomain.pid ended

So, you need to read the fine print in the Changes in release 5.1.20 (25 June 2007) 5.1 Manual you get.


Incompatible change: mysqld_safe now supports error logging to syslog on systems that support the logger command. The new –syslog and –skip-syslog options can be used in conjunction with the –log-error option to control logging behavior, as described in Section 5.3.1, “mysqld_safe — MySQL Server Startup Script”. The default is to use syslog, which differs from the previous default behavior of writing an error log file. To maintain the older behavior, use the –log-error option. (Bug#4858)

If you read mysqld_safe — MySQL Server Startup Script you also see the following note.


Note

As of MySQL 5.1.20, the default error logging behavior is to write errors to syslog on systems that support the logger program. This differs from the default behavior of writing an error log file for versions prior to 5.1.20. To maintain the older behavior, use the –log-error option.

RTFM those release notes!

MySQL 5.1.20 Installation

by

MySQL recently released 5.1.20 beta. I just ran into another gotcha, which prompted me to post this draft I’ve had for a while.

rbradford@newyork:/opt/mysql51$ scripts/mysql_install_db
Installing MySQL system tables...
070709 23:24:08 [Note] Plugin 'InnoDB' disabled by command line option
OK
Filling help tables...
070709 23:24:08 [Note] Plugin 'InnoDB' disabled by command line option
OK

To start mysqld at boot time you have to copy
support-files/mysql.server to the right place for your system

PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !
To do so, start the server, then issue the following commands:
./bin/mysqladmin -u root password 'new-password'
./bin/mysqladmin -u root -h newyork password 'new-password'
See the manual for more instructions.
You can start the MySQL daemon with:
cd . ; ./bin/mysqld_safe &

You can test the MySQL daemon with mysql-test-run.pl
cd mysql-test ; perl mysql-test-run.pl

Please report any problems with the ./bin/mysqlbug script!

The latest information about MySQL is available on the web at

http://www.mysql.com

Support MySQL by buying support/licenses at http://shop.mysql.com

Wait, there is an Innodb error message I’ve not seen . I’ll have to look into that.

mysql> show engines;
+------------+---------+----------------------------------------------------------------+--------------+-----+------------+
| Engine     | Support | Comment                                                        | Transactions | XA  | Savepoints |
+------------+---------+----------------------------------------------------------------+--------------+-----+------------+
| InnoDB     | YES     | Supports transactions, row-level locking, and foreign keys     | YES          | YES | YES        |
| MRG_MYISAM | YES     | Collection of identical MyISAM tables                          | NO           | NO  | NO         |
| BLACKHOLE  | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO  | NO         |
| CSV        | YES     | CSV storage engine                                             | NO           | NO  | NO         |
| MEMORY     | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO  | NO         |
| FEDERATED  | YES     | Federated MySQL storage engine                                 | YES          | NO  | NO         |
| ARCHIVE    | YES     | Archive storage engine                                         | NO           | NO  | NO         |
| MyISAM     | DEFAULT | Default engine as of MySQL 3.23 with great performance         | NO           | NO  | NO         |
+------------+---------+----------------------------------------------------------------+--------------+-----+------------+
8 rows in set (0.00 sec)

mysql> show plugins;
+------------+--------+----------------+---------+---------+
| Name       | Status | Type           | Library | License |
+------------+--------+----------------+---------+---------+
| binlog     | ACTIVE | STORAGE ENGINE | NULL    | GPL     |
| partition  | ACTIVE | STORAGE ENGINE | NULL    | GPL     |
| ARCHIVE    | ACTIVE | STORAGE ENGINE | NULL    | GPL     |
| BLACKHOLE  | ACTIVE | STORAGE ENGINE | NULL    | GPL     |
| CSV        | ACTIVE | STORAGE ENGINE | NULL    | GPL     |
| FEDERATED  | ACTIVE | STORAGE ENGINE | NULL    | GPL     |
| MEMORY     | ACTIVE | STORAGE ENGINE | NULL    | GPL     |
| InnoDB     | ACTIVE | STORAGE ENGINE | NULL    | GPL     |
| MyISAM     | ACTIVE | STORAGE ENGINE | NULL    | GPL     |
| MRG_MYISAM | ACTIVE | STORAGE ENGINE | NULL    | GPL     |
+------------+--------+----------------+---------+---------+
10 rows in set (0.00 sec)

Yet, it works fine! So what does message “[Note] Plugin ‘InnoDB’ disabled by command line option” really mean. On closer inspection it doesn’t say Error, however it’s a different message from previous installs, so it requires more investigate to know what’s happening.

OS Freedom

by

I’m at some internal MySQL training today at our US HQ. In a room of 11 people we are all using our laptops for access to additional machines. The beauty of this is we have Linux (Ubuntu 5.10, 6.10, 7.04, Red Hat Fedora), Mac OS/X and Windows Operating Systems on various staff machines. It’s great to realize we are not locked into just one infrastructure, and we have the ability to all do our work effectively and MySQL works just as well across these technologies. I can’t however SSH from my iPhone yet, unlike my previous Nokia E62 PDA.

The Hatchery July Event

by


Wednesday night I attended my second Hatchery event. The Hatchery is an opportunity for organizations and clever individuals seeking venture capital to make a proposal in a formal panel process with venture capitalists. This month’s event included 3 presenters, all 3 different from last month, each presenting in a better style (thanks to being prepped) in a revised format that included a longer presentation time, and an opportunity of questions from the floor. So the presenters.

safeTspace.com


On the Internet there is “no way to prove who people say they are”. safeTspace.com is an attempt to address this problem starting with the vertical of creating a safer Internet for children. This is a noble pursuit, it’s clearly needed, I applaud the attempt but it’s a battle that I believe can’t be solved via traditional means. I compared this pursuit with a two very common problems. 1: SPAM. This can’t be solved without eliminating the underlying email protocol that is flawed. 2: VIRUSES. This can’t be solved without the underlying Microsoft Operating System that is a virus incubator.

With these two examples there will always be spam filters and spam programs competing to eliminate spam, and the issue for example of false positives. There will be always be anti-virus software attempting to fix as quickly as possible problems or identification *after* a new virus is found and released to the world. Until Microsoft re-defines the way it secures it’s underlying operating system, and application suit from the ground up, those users that use this OS will continue to live with daily concern of viruses. Enough ranting.

It would appear from the presentation there are two steps of the process, authentication and verification. The authentication process involves a physical person verifying the child in question is a physical real person. The verification process is either a fingerprint scanner, or a camera. Fingerprint scanners for example are not secure, and there are plenty of non-standard entry level models already available, so a fingerprint could be faked. Photo recognition, what’s stopping somebody putting a photo of the child in front of the camera.

The problem is the system is only half of two necessary parts. Authenticating a real person to access the Internet doesn’t stop this person from then doing anything like pretending to be somebody else. While the purpose of the pursuit is to ensure the reverse, this first point can also occur. The co-operation of all websites is needed but how can you get a buy in from sites such as MySpace, FaceBook, YouTube etc. I work in this industry, I know people that work for these organizations, I work with large organizations such as these, I know this type of implementation will not happen easily without significant incentive, and there are millions of community web sites, millions.

It was interesting that this project started “from something that came out of an argument”. A comment that was re-iterated by one of the panel saying this was a good thing. I can’t say I agree here, active discussion promotes collaboration, and opinionated views. Arguments rarely achieve anything other then resulting in personal attacks and usually is over something less then very important.

While all three key people were all from the Department of Homeland Security (good to see them all there for the presentation), the comments regarding the proposal included the lack of a technology security expert, and resources with significant Sales & Marketing background. This re-iterates what I have learned from my experiences in working with startup companies and venture capital using the Bell Mason Diagnostic (BMD). This approach tackles 4 different stages of development, 4 quadrants with 12 axis of analysis that covers Technology, CEO, Product, Team, Software Development, Board, Business Plan , Cash, Marketing, Financeability, Sales and Controls. What I know and don’t have in any of my own ventures is contacts and involvement of people covering these areas of required expertise. It’s an important checklist with anybody that has an idea.

ParkWhiz.com


The second presenter was for ParkEhiz.com, a quick and easy way to search for and pay in advance for parking. With one click access to your city, Google Maps mashup integration, slider refresh of distance and price (but not rating) the website provides quick access to information and quick filtering, something necessary for a website success, and something that can kill you in performance with a successful site. Still, good points scored from me here for the practical site.

The presenter did a good job. While clearly a technical person, his enthusiasm towards the idea was evident and this is one side to promoting you idea. There were clearly areas of greater analysis in the business plan necessary such as securing major clients, and considering ideal marketing and pricing plans. Panel Investor Hugh Cullman post presentations comments stated in his discussion that the zeal of the presenter and not just the business of the presentation contributed to evaluating proposals early in the funding process. At the ring of the 7 minute timer, when asked to complete the sentence the reply was “it’s going to be a 7 slide sentence”. The presenters know the terms before the presentation, they were also prepared prior. It’s very important that your professionalism includes following the rules.

The ParkWhiz Guarantee is an interesting offer. A 100% guarantee is parking is not honored. This will become an issue if the “phone for an alternative” floods the most likely single phone operator for now. With a dependency on more traditional communication means additional resourcing will always be necessary.

Convenience is a strong selling point. However, having worked for a failed Internet startup from 1999-2002 that had 3 rounds of funding and one significant project that worked with bringing *buyers* and *sellers* together I had a number of points of input. The most significant is meeting the technology capabilities of the “buyer” and “seller”. I saw huge problems here, and this was clearly raised by one of the panel members saying “I know my parking garage just got an answering machine”. In this instance, the reliance on Parking Garages to have Internet access, and to use this in a timely manner will simply never work. Likewise for those looking for last minute parking, people may not both have readily available Internet access not the desire to pay in advance. Combine the management fee for this service of 25%, and I’m sure the desire of less reputable garages for a cash business, as well as the handling of money twice is also and overhead. That is taking money from the buyers, and then passing onto the sellers.

To overcome this I would suggest two things. First, for this to work you need to meet the sellers with the technology they can support, and the most I could see here is cell phone text messaging. No more. The second is providing a monthly fee service for buyers, that again via Cell phone, Text Message, email or PDA version provide a buyer with a list of parking garages with prices and times in the area provided. Would a service of $5-$10 a month work here. Well I guess only trial and error can tell. I did find out that a PDA version of the website existed, however on my iPhone (which is a full web browser), I was stuck with the PDA version.

Newstin


The final presentation was Newstin, a Global News Aggregator. On first inspection this site showed nothing more then what I get with Google News. The presenter who didn’t stand still (making it interesting for the videographer) was quick to indicate the key differences, the first was the number of feeds being significantly more, and the second being translation capabilities via a machine translation technologies.

This presentation described the direct competition with Googke, Topix.net and Factiva, and similar services as Bloomberg. This information is always necessary, you have to set your apart from your competitors. You have make yourself unique. That uniqueness was the schematic keyword search and integrated translation. This analysis included a patent. The presentation included description of technology partners and it seemed while not clear to indicate that translation was performed by a third party.

The service however is a niche product at $2500 per year. It was also immediately clear that Americans and America can never be considered a primary source for this service. I’ve spoken previously regarding my opinions on the clear lack of World News by the US media, and a clear false view presented to the viewing public in this country. My thoughts on CNN International was also echoed by one of the panel. The key target market is clearly Europe, a point raised many times by the panel. What was not mentioned and I consider an emerging market is Asia. Korea, Japan are powerhouse large Internet communities and I’m sure China and other Asian countries will become likewise.

And to include a quote from the presenter, when asked how much money do you have left from initial funding the response was “Just about enough to get me here to ask for more”. They were clearly asking for a lot and with 30 staff in Prague, Czech Republic resources were also working for peanuts.

Post Presentation


One great thing about this meetup is the opportunity to talk more with the presenters afterwards, to also network and this time (as well as before) and the opportunity for a drink with some people following completion. I had a chance when discussing an idea to mention The Purple Cow by Seth Godin. A quick and easy read, but an inspiration in thinking outside the square. I had an opportunity to show my Moo Cards which last month were on order, but used by one of the presenters. What was surreal was as a showed my cards, one was randomly selected just as the NewsTin presenter approached. The card selected was of the Praque Castle (the only Praque photo of the collection) and Praque being the home city of operations for NewsTin. Coincidence!

The fobar of a Web 2.0 website

by

Web 2.0 is all about community driven content. Recently eBay purchased Stumble Upon for $75 million. There is a problem here. When I first heard of the site, I looked at. I remember going back the next few days, and I was sure it hadn’t changed. Then I started taking screen shots. Having forgotten about it now for over a month under a discussion today, I took another screenshot. In over a month, from June 3rd to July 15th there has been no content change to the website. There has been a change to the counter of number of stumblers, and a change of image for a Recent Stumblers, but the “content”, the recent popular web site on the first page of the website remains unchanged. Check out my screenshots.

-rw-r--r-- 1 rbradford rbradford 568715 2007-07-15 19:13 stumbleapon.15072007.png
-rw-r--r-- 1 rbradford rbradford 570622 2007-06-03 21:32 stumbleupon.03062007.png
-rw-r--r-- 1 rbradford rbradford 580385 2007-06-04 18:25 stumbleupon.04062007.png
-rw-r--r-- 1 rbradford rbradford 586082 2007-06-07 00:22 stumbleupon.07062007.png
-rw-r--r-- 1 rbradford rbradford 570792 2007-06-15 02:03 stumbleupon.15062007.png

Cool Product – OQO

by

My flatmate just go an OQO and he hasn’t stopped raving about it.

A small PDA type device, however it runs a full version of Windows XP, has a full keyboard, mouse and keypad, a screen of 800 x 480 and build-in Wi-Fi and wireless broadband that can be connected to a telephone provider. Apparently with Skype installed you could then use it as a phone. He just tested it as we were talking to see if it had a built-in mic. I must admit even I’m impressed. Something I’ll need to play with a little more. Doesn’t beat my iPhone however for the cool alpha geek, in your pocket sized being able to show my photos & view the web device.

MySQL NY Meetup Presentation

by

Tonight I spoke at the NY MySQL Meetup. The topic “Practical Performance Tips & Tricks” was a full packed 1 hour session, with 4 x 15 minute sections on Beginner, Intermediate, Advanced and MySQL Proxy. The goal to hopefully cover content for different level of attendees. This meeting followed up the large turnout from last month’s meeting with at least 35 people. Thanks again to Logicworks for sponsoring the night and providing the beer and food, especially to adjust for the late arriving presenter.

My Slides are download able in PDF format here.

Some additional links that were discussed during the meeting for reference included.

Details of my previous MySQL presentations can be found in my Articles section.

Other iPhone experiences for corporate users

by

I am not alone in my experience as a corporate AT&T/Cingular user wanting an iPhone. See my last post iPhone activation – Finally after 3 days. In the past 30 minutes I’ve read the same pain from many people. How can Apple and AT&T get this so wrong?

Some posts of interest:

No iPhones for corporate accounts?!
Anyone else here on an AT&T corporate account?
Apple, er AT&T, tells corporate types to pound sand
No iPhone for business account customers at launch?

iPhone activation – Finally after 3 days

by

If you have been following my iPhone saga, last time was iPhone for corporate users. What a debacle

So after 3 days, my phone is finally activated, however it was no as simple even with the required hoops mentioned last time.

I started by getting my company to add iPhone as an acceptable device for *our own* company account. The response was “My rep has stated that he can not add the iphone to our corporate account. This is currently not allowed to do it by AT&T corporate. Our only option is #2 which is to release your number and port it back to a personal account. “

What the! So my number is released, and I start the process of getting my iPhone on a personal account. I take the option of calling 1.866.895.1099 rather then trying with iTunes. I explain the situation, and then I’m asked for the account number. I don’t have that, it’s a company account, then I’m asked to type in *#06# on the phone, but of course I’m calling on my phone and that doesn’t work. Then I had to prove I was the owner of the phone by providing 3 recent numbers called. Of course this is in the phone I’m presently using.

Ok we overcome this, then I’m told I may have to pay a security deposit on my phone. What the! More questions including what is your Drivers License Number. Well I don’t have a US drivers license. Anyway, a few more minutes and I’m told I would not have to pay a security deposit.

Continuing on, I was told my account was created, and then I could use iTunes to activate my phone. Wanting to ensure this all worked smoothly I was still online with the operator. I had to agree to the “Apple and Third Party Terms and Conditions” and “AT&T – Terms of Service”. I made a copy of these pages, but didn’t keep the links that they reference unfortunately.

At the end of the process, I click the final button in iTunes and the phone goes dead. Doh!. My Apple iPhone is immediately activated and my present phone is de-activated. A few minutes later I actually had the operator I was talking with call me back, so that counts as one of the few positive experiences in this entire process.

Given this has been a forgettable experience I state I specifically want to make a complaint and I want a number for complaints. I’m told there isn’t one, I have to register online at the AT&T website and then find the email for contacting them. Well, that’s just not customer service.

So finally at like 1am Tue Jul 3rd 3 days after my purchase, my useless paper weight is usable.

iPhone for corporate users. What a debacle

by

What a debacle. Here is how poorly AT&T have handled the management of the iPhone for the millions of “individual” users that have their bill paided for under a corporate account.

First, prior to release I inquired with my company’s technical people who contacted our local rep. Here is what they had to say.

“I’ve spoken to our Cingular rep and he not going to be able to sell the iPhone to business customers for 1-3 months. I suppose this is to keep stock high in the retail channel. Your best bet, according to him, is to go directly to an Apple store and buy it there. He thinks it’s a better bet then going to a Cingular store.”

So on the night of the release I goto the New York Apple Store, which is huge and open 24 hours, inquire about getting an iPhone given I’m on a corporate account. I have to ask a sales person who asked another more senior who asked another more senior again (it all was rather smoothly handled) until I get the response, “Yes, I can get an iPhone given I’m on an existing corporate account, I simply have to a call AT&T for the activation process and get a new 2 year contract”. That’s it. None of the detail I’m about to discuss. So, as I’ve recently written I did indeed buy the phone. (Includes Pictures & Video)

The first time I call Friday Night (now actually 1am Saturday morning), no answer other then a very boring on hold message for 30 minutes.

I call a second time Saturday midday and again, no answer other then a very boring on hold message for 30 minutes.

On Sunday, call a third time at midday, and surprisingly after 20-25 mins I get an operator. Before that story, it was interesting to observe a change in the on hold message from the previous two times. This one I recorded, I wish I’d had the earlier ones. This time the message clearly states. “Your iPhone is not eligible for any corporate discount.” Wow, just some little detail nobody has at hand before.

Of course, I can’t get my phone activated. What a surprise. As the operator stated, paraphrasing “The iPhone is intended for individuals. We didn’t expect this need for corporate account uses”. I’m sorry, I’m an “individual”, I just have somebody paying the bill on my behalf. AT&T/Apple you blew it here.

So in order to get my iPhone activated I have to contact the appropriate person at my company with the right authority (fat chance that will happen on a weekend, Friday 6pm realize of a product, and nobody considered this, either the did and decided to let the pain happen or the didn’t, either way it’s rather poor product release consideration. So the right person has to contact AT&T and either allow to list the iPhone as a regular device or Release my number (without penalty I’m told). Given there is no corporate discount either option works for me, I just want to play and show off my $600 useless phone. I should also add, my phone account already runs at about $120 per month, I have unlimited data already, so it’s not like switching to an iPhone I would actually be paying more from my account. The operator could see that, so where is the problem. If I had a phone on a corporate account without unlimited data already I could see this being an issue.

I also find out on this call another piece of information I didn’t know. The iPhone is an non insurable device. I’m not sure exactly what that means, but I take it AT&T offer phone insurance, except for the iPhone. Does this mean it won’t last the distance of say 12 months.

Overall the operator was as helpful as she could be, she was courteous and provided all the information I needed, but not with the result I wanted.

AT&T and Apple have done a piss poor job of the release for “individual users” that simply have there phone account paided differently. It’s been a rather unacceptable experience. I’m disappointed in the process and now a full 2 days later I still have a useless iPhone that I can’t activate.

And for the record, I went to the iPhone website to make a complaint. You think after looking at all the contact details I could find a link to do this. Seems, they may not be happy with people making complaints.

Update You can read the final conclusion at iPhone activation – Finally after 3 days

The iPhone

by

Apple today released the iPhone. It was released at 6pm on Friday June 29. Who releases a product at 6pm on a Friday? While walking up 5th Avenue before dinner I noticed a line of people outside a Cingular (now the new AT&T) store (the only phone network you can get an iPhone for).

Apparently people had been lining up at the New York Apple store for two days. Fools. So after dinner tonight at 11pm I headed up to the NY Apple store with Andrew and Jo, given they had never been there before. Lucky the store here is open 24 hours.

No lines, but inside it was the normal mayhem with some extra fanfare around the obvious new iPhones. Check out photo.

A few minutes later I got to play with one, really cool. Smaller then I expected, I had some difficulty understanding how to zoom in on web pages, I needed to learn the pinch and expand step. The keyboard is smaller then my Nokia E62 PDA so that was a little annoying.

You could even get a free box bag with a picture of an iPhone on it. Check it out.

Of course the box is free when you buy an iPhone. I got the 8GB model at $599 US, plus case plus tax set me back $681.82

Ok, so that’s nice, just had to wait the 1hr 15min to get home.

It’s now 1am, and the first requirement is to download the latest iTunes. Reboot my laptop into Windoze, and of course it’s like a 56MB file. While downloading I grabbed the image from my camera, upload the image to my Fotolog account, and started this blog. Download and install complete, and guess what, Windows has to restart your computer. There is just one more reason why I hate Windoze and simply don’t use it. Reboot later, and finally I get to start.

Activation of phone is via iTunes. Of course this doesn’t work. Please Call AT&T. This was to be expected as my phone is under an a corporate account. I did specifically check with the store before purchasing that I could use this account for the required 2 year contract commitment.

Of course the number presented on screen, and that I rang (1.877.419.4500) could not help me because I’m a business account. I have to call the Business Center on 1.866.907.3484. Ring that, and guess what I’m on hold, 20+ mins later still nothing. It’s now 2:14am, I hang up, I was on the phone waiting for 27 minutes. Rather disappointing, I guess that’s one reason why you start a sales promotion at 6pm on a Friday. All the phone support the Business Center have gone home for the weekend.

This had better turn out for the better in the morning. I’ll be rather peeved if I find out, I need some account information I simply don’t have then have to wait until Monday to call my office (of course wait until midday as that is 9AM Pacific time).

Right now failure, after such a great evening with friends, food and just excellent service it’s just one of those bummers in life.

Update 1. I’ve added video of the NY Apple Store iPhone activity.
Update 2. My phone is still not activated. Read about it at iPhone for corporate users. What a debacle.
Update 3 It’s alive iPhone activation – Finally after 3 days

MySQL Proxy. Playing with the tutorials

by

I was playing with the 5 sample tutorial Lua scripts available here with the MySQL Proxy, but I was doing something a little inefficiently.

I started mysqld, then I started the MySQL Proxy with the lua script, then connected to MySQL via the proxy. To test a different script I was actually killing the MySQL Proxy and restarting with appropriate script, but this is unnecessary. MySQL Proxy will re-read the lua script, as specified with –proxy-lua-script on new connection. All I need to do is copy in the file in question and get a new mysql client connection.

The tech version of the right way:

$ cp tutorial-basic.lua running.lua
$ ./mysql-proxy --proxy-lua-script=running.lua &
$ mysql -uusr -p -P4040 -h127.0.0.1
mysql> # do my stuff
mysql> exit;
$ cp tutorial-inject.lua running.lua
$ mysql -uusr -p -P4040 -h127.0.0.1

In the tutorial-resultset.lua example, we see the creation of a new command, show querycounter which gives you the number of queries executed for the connection. Immediately I can see that people will be creating pseudo Com_insert,Com_select,… variables per connection so you can get a better granularity of information then what default status variables can provide.

It was interesting however to see the counter be a value of 20 when you open a connection. What you may ask, but we forget the little things sometimes when running the mysql client. On invocation it actually executed 20 commands to the server, that is 20 round trips.

There are indeed: SHOW DATABASES; SHOW TABLES; then 16 Field Lists for each mysql table (e.g. host,db,func…), then a SELECT @@version_comment. Most of these as a result of the rehash or tab-completion capabilities in mysql client.

Wow, I also learned something new, there is a @@version_comment, I just knew about @@version. You learn some new trivial point every day!

 mysql> select @@version,@@version_comment;
+------------+------------------------------+
| @@version  | @@version_comment            |
+------------+------------------------------+
| 5.0.37-log | MySQL Community Server (GPL) |
+------------+------------------------------+
1 row in set (0.00 sec)

MySQL Proxy. Get it here.

The algorithm is banned in China

by

This is an image I took yesterday of a billboard “The algorithm is banned in China.”. I don’t get it. This one has “Ask” in the bottom of the image.

I remember a few months ago seeing “The algorithm killed Jeeves”, and it had no reference to “Ask” on it, so I assumed it was some reference to Google Killing askjeeves.com. Need to find that photo.

Found it. I took this photo below on Apr 20, 2007. Note: there is no Ask logo like the one above.

What's your disk I/O thoughtput?

by

MySQL uses disk. Ok, so everybody knew that. MySQL uses disk in two primary ways.

  • Random I/O (Reading & Writing Data/Index blocks)
  • Sequential I/O (Binary Log, InnoDB Redo Log)

Historically it’s been best practice to separate these onto different spindles, and also separating the OS and tmp space onto a third spindle. With commodity H/W that can be easily done, but today a lot of people use SAN. Is this a good thing for a MySQL Database?
That’s a topic of much discussion at a later time, however I’ll add two points. A lot of SAN configurations are RAID 5, and RAID 10 is a better choice due to removing the requirement to calculate the parity. Second, last week I observed a RAID disk failure and it took an incredible long time for the disk to be re-built. Just how many SAN uses our there have actually timed a disk rebuild on a loaded system and seen the impact on the system in general.

Back on topic, I don’t have access to any variety of hardware, so community here is where you can help. Those that can spare 5 mins, and have some free disk space (< 5GB), here is an exercise.

Commands

$ time dd if=/dev/zero count=100000 of=testfile.32k bs=32k
$ ls -lh testfile.32k
$ rm testfile.32k

You should see something like (FYI: from a 5400rpm laptop drive)


$ time dd if=/dev/zero count=100000 of=testfile.32k bs=32k
100000+0 records in
100000+0 records out
3276800000 bytes (3.3 GB) copied, 160.172 seconds, 20.5 MB/s
real 2m40.342s
user 0m0.120s
sys 0m15.277s
$ ls -lh testfile.32k
-rw-r--r-- 1 usr grp 3.1G 2007-06-28 10:02 testfile.32k

If your output doesn’t provide the dd M/B output (like Solaris for example) if you could also add:

$bc
3.2*1024/160
20
^D

NOTE: Replace 160 with the number of seconds from the real time (e.g. 2*60+40)

Of course I’m not collecting a lot of stuff, like comparing different block sizes, or looking at iostat for existing load and introduced load. I thought I’d ask an easy question to see what type of response and output I’d find.

If you want to add any additional information such as Drive Types & Speeds (e.g. SATA 5400rpm), RAID configuration or SAN configuration (e.g. RAID and connection type), OS and File System type that would be great, but completely optional.

If you would like add your results anonymously, please email me directly.

Thanks in advance.

Top 5 Best Practices for MySQL

by

We had the Top 5 wishes for MySQL started by Jay recently. So in true chain letter fashion I’m starting a new one this week. “The top 5 Best Practices for MySQL”. This like it’s predecessor is generally vague, so it can include points on development, design, administration etc.

My list:

1. Write your application to support Transactions (and therefore use a Transactional Storage Engine).

2. Always use SQL_MODE. e.g. at least TRADITIONAL and ANSI to ensure better data integrity and errors as errors.

3. Use the most optimal data types (particularly for number (e.g. TINY/SMALL/BIG INT and nullability) and especially in relation to columns in indexes.

4. When using InnoDB use the shortest primary key possible (e.g. INT UNSIGNED. BIGINT unless you have more then 4 billion rows in your potential data set is laziness).

5. VARCHAR(255) is just plain dumb and lazy. This is not database design and for the record, yes there is an impact when your queries use certain buffers (e.g. sort_buffer). Last year I wrote on this topic in If you don’t know your data, you don’t know your application.. Combined with SELECT * FROM TABLE in queries is not a well designed application.

Obviously I need to clarify that this is a baseline for Best Practice and many considerations can lead to a more optimized means depending on circumstances, for example using MyISAM or other MySQL non-transactional storage engines etc, when not to use sql_mode etc and when the shortest Innodb is not the best when you are being killed by I/O. For points 3 and 5, there are no exceptions.

To all Planet MySQL bloggers and readers, the challenge is on.

Got MySQL Proxy yet!

by

If you haven’t got MySQL Proxy yet, then stop and get it now. Jan announced the release a few days ago of this new product offering from MySQL.

I first heard about MySQL Proxy at the recent MySQL Conference 2007 and actually used it a few weeks later to help address slow running queries during benchmarking with a granularity of milliseconds — Wow. The product has grown immensely since then and I’ve watched in true amazement at the speed of development by Jan, who I only found out recently was the creator of Lighthttpd.

Now it’s past midnight and I think of those things I’ve wanted, can the proxy now do them. Here are a few ideas for me to play with.

  • Find Slow Queries < 1 second
  • Add a time (HH:MM:SS) column to SHOW GLOBAL STATUS output, and really hope it comes out in mysqladmin extended-status. Cross fingers here
  • Be able to log slow queries to a Memory table, a little like mysql.slow_log but more intelligent, and in 5.0.
  • True SQL access to things like PROCESSLIST, SHOW GLOBAL VARIABLES and SHOW GLOBAL STATUS, again like the 5.1 I_S tables, but I want them now in 5.0

The mind boggles. As Giuseppe describes it’s also an excuse to learn a new language called Lua. Those familiar with the MySQL GUI Products and have played with the GRT would have already seen Lua in action, as early as the 2006 User Conference.

Woot! Time to hit post and check out the real www.woot.com