You are here: Home / 2007 / Archives for May 2007

Google Maps adds real-time images

by

Yesterday I was amazed to see yet another new feature in Google Maps.

A new button [Street View], gives you are real view of the street in question, you can rotate the image around 360 degrees, and move up and down the street. Now not all areas have been mapped yet, but you will see highlighted in blue what is.

It’s rather amazing what they can do. Presently they have images of San Francisco, Las Vegas, Denver, Miami and New York.

BTW: On the news tonight (Wed May 30) there is already controversy over the feature. People have been able to zoom in and see great detail of people, sometimes in places were it may be inappropriate.

My ‘hourly’ MySQL monitor script Version 0.03

by

I realized when I released my very crappy version of My ‘hourly’ MySQL monitor script I really should have included my standard logging.

So I did that the night I wrote my original blog, but never published it. I’ve had need to use it again today, so a few more usability tweaks for parameterization and we are good to go.

Now Version 0.03 includes three files:

  • hourly.sh
  • common.sh
  • mysql.conf

Simple use is:

$ cd /directory
$ vi mysql.conf
# correctly specify MYSQL_AUTHENTICATION
$ chmod +x ./hourly.sh
$ nohup hourly.sh &

This gives you the following files

-rw-r--r-- 1 rbradford rbradford  2643 2007-05-29 15:47 mysql.innodbstatus.070529.154757.log
-rw-r--r-- 1 rbradford rbradford   414 2007-05-29 15:47 mysql.processlist.070529.154757.log
-rw-r--r-- 1 rbradford rbradford 12597 2007-05-29 15:47 mysql.status.070529.154757.log
-rw-r--r-- 1 rbradford rbradford 22229 2007-05-29 15:47 mysql.variables.070529.154757.log
-rw-r--r-- 1 rbradford rbradford 13146 2007-05-29 15:47 os.ps.070529.154757.log
-rw-r--r-- 1 rbradford rbradford   390 2007-05-29 15:48 os.vmstat.5.070529.154757.log

By default, written in /tmp, you can override by setting LOG_DIR.

It gives you a pile of output you can easily grep, I’m working on some very simple graphing. One thing I have done is pass the status into Mark Leith’s Aggregating SHOW STATUS Output as well as passed on some feedback that I hope will get integrated into later solutions.

For now, it’s a tool I can implement in a few seconds, run while somebody is showing or demonstrating a system, and I’ve got some meaningful information to look at. Combined with my more in-depth ‘minute’ script, a general-log and taking notes of individual steps in a system walk though, I have all the information I need to analyze a working system very quickly from a purely database level. Still there is lots to do manually, but I’ve got a consistent view of information to review.

My 'hourly' MySQL monitor script

by

Following my article Everything fails, Monitor Everything, and some inquiries, I’ve made some small modifications to my initially hourly script. This script is still a quick and dirty trial of what I’m wanting to develop, but in true Guy Kawasaki terms “5. Don’t worry, be crappy”. It works for now, and enables me to determine what works and what doesn’t.

My goals are Data Collection, Data Analysis and Data Presentation. This is the start of Data Collection. So now I get the following files:

  • os.vmstat.070524.122054.log
  • os.ps.070524.122054.log
  • mysql.innodbstatus.070524.122054.log
  • mysql.processlist.070524.122054.log
  • mysql.status.070524.122054.log
  • mysql.tablestatus.070524.122054.log
  • mysql.tablestatus.vertical.070524.122054.log
  • mysql.variables.070524.122054.log


#!/bin/sh
#  Name:    hourly
#  Purpose: Script to 'cron' hourly to run for monitoring
#  Author:  Ronald Bradford

error() {
  echo "ERROR: $1"
  exit 1
}

MYSQL_AUTHENTICATION=".mysql.authentication"
[ ! -f $MYSQL_AUTHENTICATION ] && error "You must specific MySQL Authentication in $MYSQL_AUTHENTICATION"
[ -z `which mysqladmin` ] && error "mysqladmin must be in the PATH"

DATETIME_FORMAT="+%y%m%d.%H%M%S"
DATETIME=`date $DATETIME_FORMAT`
DATABASE="test"

AUTHENTICATION=`cat $MYSQL_AUTHENTICATION`
# run vmstat every second for 1 hour
# normally this is an overkill 5 seconds is acceptable
# but need to monitoring any spike

VMSTAT_OPTIONS="1 3600"
LOG_FILE="os.vmstat.$DATETIME.log"
echo "INFO:  Logging vmstat $VMSTAT_OPTIONS to $LOG_FILE"
vmstat $VMSTAT_OPTIONS > $LOG_FILE 2>&1 &

LOG_FILE="os.ps.$DATETIME.log"
echo "INFO:  Logging ps to $LOG_FILE"
ps -ef > $LOG_FILE 2>&1 &

LOG_FILE="mysql.variables.$DATETIME.log"
echo "INFO:  Logging mysqladmin variables to $LOG_FILE"
echo "| date_time                        | $DATETIME |" > $LOG_FILE
mysqladmin $AUTHENTICATION variables >> $LOG_FILE 2>&1 &

LOG_FILE="mysql.tablestatus.vertical.$DATETIME.log"
mysql $AUTHENTICATION $DATABASE -e "SHOW TABLE STATUSG" > $LOG_FILE 2>&1 &
LOG_FILE="mysql.tablestatus.$DATETIME.log"
mysql $AUTHENTICATION $DATABASE -e "SHOW TABLE STATUS" > $LOG_FILE 2>&1 &

COUNT=0
MAX_COUNT=60
SLEEP_TIME=60
LOG_FILE1="mysql.status.$DATETIME.log"
LOG_FILE2="mysql.processlist.$DATETIME.log"
LOG_FILE3="mysql.innodbstatus.$DATETIME.log"
> $LOG_FILE1
> $LOG_FILE2
> $LOG_FILE3
echo "INFO:  Logging mysqladmin extended-status per $SLEEP_TIME secs for $MAX_COUNT times to $LOG_FILE1"
echo "INFO:  Logging mysqladmin processlist per $SLEEP_TIME secs for $MAX_COUNT times to $LOG_FILE2"
echo "INFO:  Logging mysql show innodb status per $SLEEP_TIME secs for $MAX_COUNT times to $LOG_FILE3"
while [ $COUNT -lt $MAX_COUNT ]
do
  NOW=`date $DATETIME_FORMAT`
  echo "| date_time                        | $NOW |" >> $LOG_FILE1
  echo $NOW >> $LOG_FILE2
  echo $NOW >> $LOG_FILE3
  mysqladmin $AUTHENTICATION extended-status >> $LOG_FILE1
  mysqladmin --verbose $AUTHENTICATION processlist >> $LOG_FILE2
  mysql $AUTHENTICATION $DATABASE -e "SHOW INNODB STATUS\G" >> $LOG_FILE3 2>&1 &
  COUNT=`expr $COUNT + 1`
  sleep $SLEEP_TIME
done
exit 0

So from here, I need to:

  • Put into my standard sh script framework which provides correct logging, message management and true parameterization.
  • Additional pre-checks for the correct security requirements
  • Revised Parameterised settings including database
  • Host and Instance logging
  • Additional file parsing for later Data Analysis and Data Presentation.

Got the next great web thing!

by

I joined a new meetup group New York Dot Com Hatchery on advice from my friend Marc. Now only if my schedule keeps in NY for the event I’ll be very much looking forward to going.

I think this statement on the www.hatchedby.us website sums up the opportunity for expert advice.

The payoff – Your friends won’t tell you the truth, we will. And we can make your billion-dollar dreams come true. Your odds for success? Better than buying a lottery ticket.

Cool Photo Printing Site

by

I just came across Moo as a link from Fotolog.

This site gives you the option to get a photo like card on the front, and text on the back. The interesting part is that you can do 100 different photos in the batch of 100, and it’s the size that grabs me. I’ve been looking for something different with my various sites including Admiring Creation and Heavy Horse Day and GeekErr. Just now trying to get all my photos ready for printing!

Using Perl with MySQL

by

NOTE: Problems presently exist, I’m seeking the expert help of the community and Perl Gurus

I have the need to do some quick benchmarking, I use MyBench as it’s effective in being able to plug in a query, some randomness and 2 minutes later (with a correctly configured Perl/MySQL environment) you have multi-threaded load testing.

However, when the environment you are on is not configured, and you only know the basics for Perl Operation and Installation, (code is just code, that’s the easy part) and the box is not accessible to the outside world say for cpan, it gets more complicated. I’ve attempted to install and configure DBI, DBD::mysql and Time::HiRes but without success.

DBI
DBI was straightforward, a download, make, install worked without issue. a make install was performed.

DBD:mysql
DBD::mysql didn’t need an compile, mysql.pm already existed and make said it was all up to date. However then running mybench gave the first error.


failed: Can't locate DBD/mysql.pm in @INC

Ok, so it wasn’t installed as ‘root’. Some small Perl Pathing.


PERL5LIB=~/DBD-mysql-4.004/lib;export PERL5LIB

Let to the next error:


failed: Can't locate loadable object for module DBD::mysql in @INC (@INC

Hmmm, a little more complicated. So going back to the compiling part, I realized I could force compile it, and this also confirmed one possible issue, the libmysqlclient library.


perl Makefile.PL
I will use the following settings for compiling and testing:
cflags (mysql_config) = -I/path/to/mysql/include
embedded (mysql_config) =
libs (mysql_config) = -L/path/to/mysql/lib -lmysqlclient -lz -lcrypt -lnsl -lm
..

Both the mysql.h and libmysqlclient software correctly located and valid, but still no luck.

Moving in parallel I then managed a SA that could install the rpm’s (being RHEL). Problem is, MySQL is not installed via RPM, so the only possible means of installing DBD::mysql is to force no dependencies. This did not prove successful be added clues to the problem.


failed: Can't load '/usr/lib64/perl5/vendor_perl/5.8.5/x86_64-linux-thread-multi/auto/DBD/mysql/mysql.so' for module DBD::mysql: libmysqlclient.so.14: cannot open shared object file: No such file or directory at /usr/lib64/perl5/5.8.5/x86_64-linux-thread-multi/DynaLoader.pm line 230.

So it used the installed DBD::mysql, and it couldn’t find the expected library path. This gives some confident that perhaps the earlier version is right, and that some other dependancy is missing.

I’ve not found any good resource to work though this online in my environmental situation, but surely this is pain that somebody else has experienced.

Thanks for those friends that have already contributed small parts to getting someway here, however it just ain’t working, and I need it to just work.

Any input appreciated.

New Category – Clever Design

by

I am sometimes really impressed by Clever Design in print media or TV media. Over the years I’ve even collected piles of ideas, who knows where they all are now. Time to consolidate into one area, a new category on my Blog aptly named “Clever Design”. A place where I can put ideas, images, marketing, TV ads etc that have had a positive effect on me anyway. Stay tuned!

Website of the Day – Slideshare

by


I came across an interesting site while reading World’s Best Presentation Contest Winners Announced by Guy Kawasaki called SlideShare.

It’s a happy medium between the bulk of image sites like Flickr and Yahoo Photos and video sites like Revver and YouTube where you can easily add Text to what you are wanting to say in a Slide Show. Interestingly enough, like most Web 2.0 Communities people will come up with ideas you never considered, for example check out Evangeline Lilly where this is effectively a portfolio photo shoot of an actress. Clever.

Everything fails, Monitor Everything

by


From the recent MySQL Conference a number of things resonate strongly almost daily with me. These included:

  1. Guy Kawasaki – Don’t let the bozos grind you down.
    • Boy, the bozos have ground me down this week. I slept for 16 hrs today, the first day of solid rest in 3 weeks.
  2. Paul Tuckfield – YouTube and his various caching tip insights.
    • I’ve seen the promising results of Paul Tuckfield’s comment of pre-fetching for Replication written recently by Farhan.
  3. Ramus – SSL is not secure — This still really scares me.
    • How do I tell rather computer illiterate friends about running multiple browsers, clearing caches, never visiting SSL sites after other sites that are insecure etc.
  4. Everything fails, Monitor Everything – Google

What I’ve been working on most briefly lately, and really want to be far more prepared everywhere I go is Monitor Everything.

It’s so easy on site to just do a vmstat 1 in one session and a mysqladmin -r -i extended-status | grep -v ” | 0 “ in another, and you may observe a trend, make some notes, say 25% CPU, 3000 Selects, 4000 Insert/Updates per second etc, but the problem is, the next day you don’t have actual figures to compare. What was the table_lock_waits yesterday, they seem high today.

I also only found a problem on a site when I graphed the results. I’ll give you a specific example. The average CPU for the system was 55%, the target was 50%. When graphing the CPU, it was plainly obvious something was not right. I could see with extremely regularity (and count 12 in one hour) a huge CPU spike for a second or two. It was so regular in the graph it was not possible it was random. So, after further investigation and testing, a 5 minute job on this production server (and not on previous testing servers) took 25% CPU for a second or two, and a huge amount of Page Faults. Did it effect the overall impact of the performance of the system. I don’t know, but it was a significant anonomoly that required investigation.

So, quite simply, always monitor and record so you can later reference, even if you don’t process the raw figures at the time. The question is then, “What do I monitor”. Answer, monitor everything.

The problem is with most monitoring, e.g. vmstat and mysqladmin is the lack of a timestamp for easy comparison. It’s really, really annoying that you can add this to the line output. The simple solution is to segment your data into both manageable chunks and consistent chunks.

Manageable chucks can be as easy as creating log files hourly, ensuring the start exactly at the top of the hour. Use a YYYYMMDD.HHMMSS naming standard for all files and you can never go wrong.
Consistent chunks is to ensure you start all manageable monitor (e.g. hourly) at the exact same time, so you can compare.

You need to monitor at least the following:

  • vmstat
  • mysqladmin extended-status
  • mysqladmin processlist
  • mysqladmin variables
  • mysqladmin -r -i [n] extended-status | grep -v ” | 0 “

I haven’t found an appropriate network monitoring, but you should also at that.

The issue here is frequency. Here are some guidelines. vmstat every 5 seconds. extended-status and processlist every 30 seconds, variables every hour, and extended-status differences is difficult, but it saves a lot of number crunching later for quick analysis. I do it every second, but not all the time, you need to work out a trigger to enable, or to say run it for 30 seconds every 15-30 minutes.

So in one hour I could have:

  • 20070519.160000.os.vmstat.log
  • 20070519.160000.mysql.variables.log
  • 20070519.160000.mysql.status.log
  • 20070519.160000.mysql.processlist.log
  • 20070519.160500.mysql.status.increment.log, then 1610, 1620, 1630 etc

I have my own scripts for monitoring under development, and I’ve been revising slowly, particularly to be able to load data into a MySQL database so I can easily use SQL for analysis. One thing I actually do is parse files into CSV for easy loading.

There are two tools out there that I’m reviewing and you should look at. Mark Leith has written a Aggregated SHOW STATUS stat pack, and there is also tool called mysqlreport. These both go some what to ultimately what I want.

I haven’t used it yet, but I’ve seen and been very impressed with the simplicity of Munin for graphing. I really need to get some free time to get this operational.

So Monitor Everything and Graph Everything. Plenty of work to do here.

Reading the right MySQL Manual

by

I learned an extremely valuable lesson today on a client site. It’s important that users of MySQL read the right version of the manual for the product they are using. It’s very easy to just goto http://dev.mysql.com/doc/ which is what I type in directly and browser the manual. While the MySQL Manual has separate sections for 4.x, 5.0, 5.1 etc, the 5.0 Manual for example reflects the most current version of MySQL 5.0. You may not be running the most current version, infact most production systems rarely run the current version.

My specific case was with Connector/J (JDBC) Reference of 5.0.4. The manual pages reflects the new 5.0.5 or todays’ 5.0.6 release and a particular default is now a different value. With Connector/J the docs are bundled with the version. The MySQL Community Server product does not bundle the manual, and I don’t know where to view instances of the MySQL manual for each specific dot release!

The MySQL Conference recap

by

I recently had the opportunity to return and speak at the Brisbane MySQL Users Group. I spent some time talking about MySQL User Conference 2007 Summary and Life as a Consultant. My summary of included:

  • Overview
  • Keynotes
  • Marten Mickos – MySQL
  • Guy Kawasaki
  • Michael Evans – OLPC
  • Rasmus Lerdorf – PHP
  • Paul Tuckfield – YouTube
  • Community Awards
  • Product Road Map
  • Google
  • Storage Engines
  • Dorsal Source
  • What’s Next

One question was posed to me. “What new did MySQL do this year?” being from the last User Conference. MySQL did seem to not make a great impact at the conference over the successes of the previous year. I had to think some time to come up with the following list.

And most recently:

  • Open Source Database Vendor Partners with LINBIT to Jointly Promote & Support DRBD for MySQL Enterprise Read More
  • IBM DB2 as a Certified Storage Engine for MySQL on System i Read More

It’s hard to say if these are big ticket items or not, but it is definitely disappointing that 5.1 GA is still MIA. We stay tuned.

I also managed a much better response then from my Conference Presentation opening Slide.


“How can you tell an Oracle DBA has touched your MySQL Installation?”

MYSQL_HOME=/home/oracle/products/mysql-version
mysqld_safe –user=oracle &

Reading the MySQL Manual

by

I was asked the question today, “How do I show the details of a Stored Procedure in MySQL. The SHOW PROCEDURE ‘name’ didn’t work.”.

The obvious answer was SELECT ROUTINE_NAME,ROUTINE_DEFINITION FROM INFORMATION_SCHEMA.ROUTINES, given I like to use the INFORMATION_SCHEMA whenever possible. This lead me to think was is the corresponding SHOW command. A quick manual search got me to SHOW CREATE PROCEDURE.

What was interesting was not this, but the list of other SHOW commands I didn’t know. I did not know about SHOW MUTEX STATUS, SHOW OPEN TABLES and SHOW PROCEDURE CODE for example.

It pays if you have free time (who ever has that), to re-read the manual, or at least a detailed index regularly. I’m sure there are plenty more helpful tips out there. Now just what does the output of these new commands really do, and does it really help. If only I could get commands to the stuff I really want.