|
MySQL Newsletter: January 2004
Author: MySql AB | Monday February 02, 2004
This is a big newsletter filled with many updates regarding Mysql and direction. If you are a Mysql fan, check it out.
ySQL Newsletter
January 2004
newsletter@mysql.com
--------------------------------------------------
Welcome to MySQL Newsletter
MySQL Newsletter is a convenient channel in keeping you
informed of the latest MySQL news. MySQL newsletter
is sent only to those, who have opted-in to receive
it either by registering at mysql.com or when using
the online forms at mysql.com. If you don't want to
receive it -or- think that you've received it in
error, please let us know.
Personalized Newsletter
The newsletter you are reading is prepared specially
for you based on the categories you have chosen to
receive in your profile. This means that the full
newsletter actually might contain additional articles.
You can read the full newsletter on our website at
http://www.mysql.com/newsletter/2004-01/
Articles in this newsletter:
HIGHLIGHTS
* MySQL Administration Best Practices
* MySQL Users Conference Sneak Peak
* Early-bird Registration for the MySQL Users Conference
PRODUCTS
* MySQL 5.0.0. ALPHA Has Been Released
* MySQL Connector/J 3.0.10 STABLE Has Been Released
* The XQuark Project: Open Source Information Integration Components Based on XML and XQuery
MYSQL TIPS & HINTS
* How to Monitor MySQL's performance
* Stored Procedures in MySQL
TRAINING
* Courses and Upcoming Dates of MySQL Training Being Held Worldwide
PARTNERS' CORNER
* 2003/2004 MySQL Partner of the Year Awards
* MySQL and Zend Join Forces to Strengthen Open Source Web Development
* Emic Application Cluster Adds High Availability And Load Balancing To Xserve G5
* Arkeia Server Backup Solution Offers Flexible Backup Solutions for Online MySQL™ Database Servers
LATEST NEWS
* Latest MySQL News
--------------------------------------------------
HIGHLIGHTS
MySQL Administration Best Practices
Database administration serves a number of
important functions from improving the performance
of your database to avoiding a disaster that would
result in lost data or costly system downtime.
This article will describe some of the fundamental
tools and practices programmers, as well as DBAs
and SysAdmins, can use with the powerful new MySQL
Administrator tool to ensure a healthy database
environment.
To read the entire article visit:
http://www.mysql.com/articles/mysql-administrator-best-practices.html
To read more about MySQL Administrator, please
visit
http://www.mysql.com/products/administrator/
MySQL Users Conference Sneak Peak
The 2004 MySQL Users Conference and Expo will be
host to many talented speakers delivering
interesting and valuable tutorials, technical
presentations and business presentations. Here is
a sample of some of the presentations you will be
able to attend:
* Best Practices: Using MySQL at Sabre (Alan
Walker)
* Using MySQL in a Japanese environment (Nils
Valentin)
* Data Warehousing with MySQL (John Paul
Ashenfelter)
* Inside LiveJournal's Backend (Brad
Fitzpatrick)
* MySQL and Tools for Determining Effective
Return-on-Investment (Stacey Quandt)
You will also be able to hear and learn from MySQL
AB founders and staff, including:
* Monty's Guided Tour of the MySQL Source Code
* Monty's & David's The History of MySQL
Plus, there will be keynote presentations for
industry leaders including Apple, Emic Networks,
Intel & HP.
Please note that the schedule is not yet final.
There is the possibility that some of these
sessions might change, but this should give you an
overview of the types of presentations you will
hear in April in Orlando.
The latest version of the schedule is always
available at
http://www.mysql.com/events/uc2004/sessions.html
Early-bird Registration for the MySQL Users
Conference
April 14-16, 2004
Orlando, Florida
Mark your calendars! Designed for both the MySQL
developer and the corporate decision maker, this
is the place to learn about the latest in MySQL
technology, discover new business opportunities,
take a pulse on industry direction and commune
with like minds.
Take advantage of the early bird registration and
get 15% discount on the registration fee, when you
register before the 15th of February 2004. The
package includes all sessions, tutorials, exhibit
hall, food, and special evening events.
You can register online at
https://order.mysql.com/?sub=pg&pg_no=14
--------------------------------------------------
PRODUCTS
MySQL 5.0.0. ALPHA Has Been Released
MySQL 5.0.0, a new version of the popular Open
Source/Free Software Database Management System,
has been released. It is now available in source
and binary form for a number of platforms from our
download pages at http://www.mysql.com/downloads/
and mirror sites.
Due to an unfortunate build error at the last
minute we can't provide -max binaries for 5.0.0 at
this time. Sorry about that.
Note that not all mirror sites may be up to date
at this point in time - if you can't find this
version on some mirror, please try again later or
choose another download site.
This is the first Alpha development release of the
5.0 tree, adding many new features (see below). As
this code is currently labeled "Alpha", we do not
recommend that this version be used in production
environments yet! It does however pass our test
suite on all our build platforms and all old
features should be reasonably stable.
However, we encourage you to test and evaluate it
and, more importantly, report any bugs or
observations to our bug tracking database at
http://bugs.mysql.com/. Please note, that for us
to resolve a bug report, a reproducible test is
required. See "How to report a bug" at
http://bugs.mysql.com/how-to-report.php for more
details before filing a bug report. We appreciate
your support!
The most prominent new feature of MySQL 5.0 is
probably Basic support for stored procedures
(SQL:2003 style). However, there are several
additional enhancements, which are planned to be
implemented before MySQL 5.0 reaches beta
status.
Our development plan is to continue to add new
features to 5.0 at least until 4.1 reaches 'gamma'
status after which we will move new development to
5.1. Our man goal is to have more major releases
with shorter time intervals to get out new stable
features faster.
The MySQL 4.1 branch seems to be relatively stable
and we will, if we don't find any new unexpected
hard bugs that will require a new design
decisions, make a beta release of 4.1 in January
followed by a gamma release ASAP.
MySQL Connector/J 3.0.10 STABLE Has Been Released
MySQL Connector/J 3.0.10, a new version of the
Type-IV all-Java JDBC driver for MySQL has been
released.
Version 3.0.10 is a bugfix/performance release for
the STABLE tree.
It is now available in source and binary form from
the Connector/J download pages at
http://www.mysql.com/downloads/api-jdbc-stable.html
and mirror sites.
Note that not all mirror sites may be up to date
at this point of time - if you can't find this
version on some mirror, please try again later or
choose another download site.
Changes in this version can be found from
http://www.mysql.com/downloads/api-jdbc-stable.html#CHANGES
The XQuark Project: Open Source Information
Integration Components Based on XML and XQuery
XQuark Fusion is a powerful information
integration engine, based on XQuery, for querying
in real-time multiple, heterogeneous and
distributed data sources.
XQuark Fusion currently supports the following
data sources:
* Relational databases wrapped through XQuark
Bridge.
* XML documents.
* Data sources wrapped through XQuark Fusion.
XQuark Bridge expands existing relational database
functionalities with advanced XML import/export
capabilities. It supports flexible extraction and
publishing of relational data into any target XML
format, using the XQuery language. Using a
powerful mapping language, it can also perform
efficient insertion of structured XML data into
existing relational tables, while taking into
account the database integrity constraints and
transforming the implicit relations appearing in
the XML document into explicit ones in the
database.
Read more at http://xquark.objectweb.org/
--------------------------------------------------
MYSQL TIPS & HINTS
How to Monitor MySQL's performance
Here are some ideas, how you can monitor the
database performance of your MySQL installation.
Monitoring is always an iterative and continuous
process. You need to learn what patterns are OK
for your database and what are the signs of slight
problems or even dangerous situations.
Below are the main items you can use to monitor
your system:
- mysqladmin extended (absolute values)
- mysqladmin extended -i10 -r (relative values)
- mysqladmin processlist
- mysql -e "show innodb status"
- OS data. vmstat/iostat
- MySQL error log
- InnoDB tablespace info.
1) mysqladmin extended (absolute values)
The values making most sense to monitor are:
* Slave_running: If the system is a slave
replication server, this is an indication of the
slave's health.
* Threads_connected: The number of clients
currrently connected. This should be less than
some preset value (like 200), but you can also
monitor that it is larger than some value to
ensure that clients are active.
* Threads_running: If the database is overloaded
you'll get an increased number of queries running.
That also should be less than some preset value
(20?). It is OK to have values over the limit for
very short times. Then you can monitor some other
values, when the Threads_running was more than the
preset value and when it did not fall back in 5
seconds.
2) mysqladmin extended (counters)
The idea is that you store the performance counter
value and compute the difference with the new
values. The interval between the recordings should
be more than 10 seconds. The following values are
good candidates for checking:
* Aborted_clients: The number of clients that were
aborted (because they did not properly close the
connection to the MySQL server). For some
applications this can be OK, but for some other
applications you might want to track the value, as
aborted connects may indicate some sort of
application failure.
* Questions: Number of queries you get per second.
Also, it's total queries, not number per second.
To get number per second, you must divide
Questions by Uptime.
* Handler_*: If you want to monitor low-level
database load, these are good values to track. If
the value of Handler_read_rnd_next is abnormal
relative to the value that you normally would
expect, it may indicate some optimization or index
problems. Handler_rollback will show the number of
queries that have been rolled back. You might want
to wish to investigate them.
* Opened_tables: Number of table cache misses. If
the value is large, you probably need to increase
table_cache. Typically you would want this to be
less than 1 or 2 opened tables per second.
* Select_full_join: Joins performed without keys.
This should be zero. This is a good way to catch
development errors, as just a few such queries can
degrease the system's performance.
* Select_scan: Number of queries that performed a
full table scan. In some cases these are OK but
their ratio to all queries should be constant. if
you have the value growing it can be a problem
with the optimizer, lack of indexes or some other
problem
* Slow_queries: Number of queries longer than
--long-query-time or that are not using indexes.
These should be a small fraction of all queries.
If it grows, the system will have performance
problems.
* Threads_created: This should be low. Higher
values may mean that you need to increase the
value of thread_cache or you have the amount of
connections increasing, which also indicates a
potential problem.
3) mysqladmin processlist or "SHOW FULL
PROCESSLIST" command
You can get the number of threads connected and
running by using other statistics, but this is a
good way to check how long queries that are
running take. If there are some very long-running
queries (e.g. due to being badly formulated) the
admin should be informed. You might also want to
check how many queries are in "Locked" state -
these are not counted as running but are inactive,
i.e. a user is waiting on the database to
respond.
4) "SHOW INNODB STATUS"
This statement produces a great deal of
information, from which you should extract the
parts in which you are interested. The first thing
you need to check is: "Per second averages
calculated from the last xx seconds". InnoDB
rounds stats each minute.
* Pending normal aio reads: These are InnoDB IO
request queue sizes. If they are bigger than 10-20
you might have some bottleneck.
* reads/s, avg bytes/read, writes/s, fsyncs/s:
These are IO statistics. Large values for
reads/writes means the IO subsystem is being
loaded. Proper values for these depend on your
system configuration.
* Buffer pool hit rate: The hit rate also depends
a lot on your application. Check your hit rate,
when there are problems.
* inserts/s, updates/s, deletes/s, reads/s: These
are low level row operations that InnoDB does. You
might use these to check your load if it is in
expected range.
4) OS Data. Good tools to see the system status
are vmstat/iostat/mpstat.
To see what kind of information these tools can
provide for you,
read their man pages.
5) MySQL error log - Nothing should written to the
error log, after the server has completed its
initialization sequence, so everything appearing
in the log should be brought to admin's attention
immediately.
6) InnoDB tablespace info.
With InnoDB the only danger is that the tablespace
gets full - the logs can't get full. Best way to
check this is to do: show table status;
You can use any InnoDB table for monitoring the
InnoDB table space free space.
Stored Procedures in MySQL
Peter Gulutzan
Did you notice that we added a new chapter in the
online MySQL Reference Manual last month: "Chapter
18 Stored Procedures and Functions"
(http://www.mysql.com/doc/en/Stored_Procedures.html)?
The new chapter has all the syntax descriptions
and examples that you need to make stored
procedures go once you have version 5.0. Rather
than repeating what the manual says, I'll describe
why we did it the way that we did it.
Look at this procedure definition and
invocation:
DELIMITER // [1]
CREATE PROCEDURE payment [2]
(payment_amount DECIMAL(6,2),
payment_seller_id INT)
BEGIN
DECLARE n DECIMAL(6,2);
SET n = payment_amount - 1.00;
INSERT INTO Moneys VALUES (n, CURRENT_DATE);
IF payment_amount > 1.00 THEN
UPDATE Sellers
SET commission = commission + 1.00
WHERE seller_id = payment_seller_id;
END IF;
END;
//
DELIMITER ;
CALL payment (5.00, 13); [3]
[1] We had to invent the DELIMITER statement so
that we could use semicolons inside the CREATE
PROCEDURE. The MySQL client ordinarily thinks that
";" means end of statement. By saying "we'll use
something else for end-of-statement marker" we
make multi-statement procedures possible.
Incidentally, the MySQL Reference Manual suggests
| as a delimiter, but I prefer // because I
sometimes need to use || as an operator within an
SQL expression.
[2] We had no choice about the syntax of the
CREATE PROCEDURE statement because the ANSI/ISO
standards committee defines how we can use
parameters, declare variables, assign values, or
include regular SQL statements. We determined from
the start that we'd follow "SQL:2003" period.
Comparing with other DBMSs:
-- With IBM's DB2 (tm), the CREATE PROCEDURE would
be exactly the same because DB2 also follows the
standard, as do a few of the smaller vendors
-- With Oracle (tm), there would be a keyword AS
and no DECLARE, and the assignment would start
with "commission := " rather than "SET commission
= "
-- With Microsoft's SQL Server (tm), the
parameters would not be in parentheses, names
would start with at-signs (e.g. @payment_amount),
there would be a keyword 'AS' before the DECLARE,
the IF statement wouldn't contain THEN or END IF,
and the BEGIN / END would be absent.
[3] The benefit of the stored procedure is
apparent when you consider how simple it is to say
"CALL ... (parameter-list)". This involves only
one message from the client to the server (think
how much faster it might go than if you had to do
the INSERT and the UPDATE separately). Plus,
whenever you change the business rules for what to
do with a payment, you just have to replace the
procedure. Until now you had to change scripts or
programs in host languages, and it's so easy to
miss something when not everything is inside the
database.
MySQL 5 is a young alpha, and I have to say there
are "issues".
One kind of "issue" is the lack of nice features.
You cannot refer to a table inside a function, so
"CREATE FUNCTION f () RETURN (SELECT MAX(column1)
FROM Table1);" is still a no-no. The error
messages are vague and don't explain the context.
You can't get firm security until we have GRANT
EXECUTE, and have nailed down "invokers rights"
and "definers rights" (we'll support both kinds).
My personal peeve is that the PATH statement --
which is something like PATH on your Windows or
Linux command line -- isn't there yet. Doubtless
other people will have different yearnings.
Another kind of "issue" is, of course, bugs. This
month I've seen nine statements that crash the
server and several that don't work as advertised.
However, the reason I know that is: there's a bug
tracking system. So when the inevitable moment
comes that your stored-procedure attempt fouls up,
go to http://bugs.mysql.com, search the bugs
database with a keyword like "stored" to see if
anyone has already reported the problem, and if
not ... click "Report a bug". MySQL 5.0.0 is a
preview so be gentle and ignore small things.
But please do report significant problems, because
we cannot fix bugs that we do not know about.
We should now clap hands for the team that got
stored procedures going under the direction of Mr
Per-Erik Martin. We're changing the version number
from "4.x" to "5.x" which emphasizes that the
change is big. Unfortunately that makes it
difficult to say exactly when Version 5 will
change from "alpha" to "beta" and then to "gamma"
and then to "production". Sorry folks, the only
thing we know right now is that we must fix every
reported problem before we release.
--------------------------------------------------
TRAINING
Courses and Upcoming Dates of MySQL Training Being
Held Worldwide
MySQL AB is holding numerous training courses in
the following countries: USA, Germany, Canada,
France, UK, Australia, India and Sweden. Check out
dates and locations at
http://www.mysql.com/training/
You can choose your training from the following
selection of courses:
* Managing MySQL,
https://order.mysql.com/?sub=pg&pg_no=5#t4
* Using and Managing MySQL,
https://order.mysql.com/?sub=pg&pg_no=5#t1
* Developing Dynamic Web Applications with MySQL
and PHP,
https://order.mysql.com/?sub=pg&pg_no=5#t3
--------------------------------------------------
PARTNERS' CORNER
2003/2004 MySQL Partner of the Year Awards
MySQL understands the value that MySQL business
partners provide to the MySQL user community. At
this year’s conference MySQL will recognize its
most influential partners of 2003/2004. To
nominate a partner, or nominate your own
organization as a partner, visit:
http://www.mysql.com/events/uc2004/partner-awards.html
Nominations are open to technology partners, as
well as service organizations that provide
consulting or training services. Partners will be
judged by the value and influence their product or
service is delivering to MySQL users.
MySQL and Zend Join Forces to Strengthen Open
Source Web Development
MySQL AB, developer of the world's most popular
open source database, and Zend Technologies,
designers of the PHP Web scripting engine, today
announced a partnership to simplify and improve
productivity in developing and deploying Web
applications with open source technologies.
Through the alliance, the companies are improving
compatibility and integration between the MySQL®
database and Zend's PHP products to make it easier
for businesses to use complete open source
solutions, such as the popular LAMP (Linux,
Apache, MySQL and PHP) software stack.
In a continued effort to provide customers with an
overall solution, both Zend and MySQL are offering
disounted products until end of March. Zend Studio
and Zend Encoder can be purchased at a 15%
discount directly from the MySQL AB Web site
www.mysql.com and MySQL commercial licenses can be
purchased at a 15% discount directly from Zend at
www.zend.com.
Emic Application Cluster Adds High Availability
And Load Balancing To Xserve G5
Emic Networks today announced that it will be
adding support for Mac OS X to Emic's Application
Cluster (EAC) products for Apache and MySQL
servers. EAC application clustering products are
designed to provide best price/performance cluster
platform for horizontal scaling of web
applications. EAC scales economically from small
and medium size environments to high-end server
platform environments. EAC's benefits include
performance scalability and load balancing, fault
tolerance and continuous availability with fast
fail-over, and centralized cluster management.
"The network continues to be a mission critical
business system for organizations of all sizes.
XServe G5 is the ideal server for cross-platform
file and print, workgroup management, video
streaming, database applications, high-performance
computing, and web and mail serving, " said Eero
Teerikorpi, CEO of Emic Networks. "With Emic
Application Cluster, you now can add application
cluster to this list. It allows Xserve customers
to achieve greater scalability and reliability
with the industry's fastest synchronous
replication."
"The Xserve G5 is affordable, easy to manage and
delivers unparalleled performance with up to 30
gigaflops of processing power per system," said
Ron Okamoto, Apple's vice president of Worldwide
Developer Relations. "The addition of Emic
Application Cluster makes the Xserve G5 a perfect
solution for high availability Web sites."
If you are interested in testing the Emic
Application Cluster, when it is available, you can
register at http://www.emicnetworks.com/.
Arkeia Server Backup Solution Offers Flexible
Backup Solutions for Online MySQL™ Database
Servers
Arkeia, an international leader in enterprise
backup solutions, announced at LinuxWorld Expo
that its newly released Server Backup solution
features a MySQL® plug-in for hot backup. Arkeia
Server backup for Linux combined with the MySQL
online backup plug-in is a solid backup solution
that provides affordable protection for a MySQL
database server. Packages are available for less
than $500.
Backup solution features a MySQL® plug-in for hot
backup. Arkeia Server backup for Linux combined
with the MySQL online backup plug-in is a solid
backup solution that provides affordable
protection for a MySQL database server. Packages
are available for less than $500. Arkeia Server
Backup's easy-to-use and intuitive interface
allows administrators to develop a backup strategy
that protects information without interruption.
The Arkeia plug-in performs either incremental,
differential or full MySQL backup while data is
online and accessible. The MySQL plug-in is easy
to install and configure: databases and table
backup are selected via the convenient Arkeia GUI
navigator. Backup can be defined up to the table
level and the plug-in is compatible with all table
formats handled by MySQL.
The Arkeia MySQL plug-in is compatible with
versions of MySQL starting from 3.21 and including
versions 4.x. It is available for download, as a
30-day demo, as well as Arkeia Server Backup at
www.arkeia.com/download.html.
The Arkeia Server Backup solution with the MySQL
hot backup plug-in is priced at $480.
--------------------------------------------------
LATEST NEWS
MySQL Receives New Recognition as Leading
Database
http://www.mysql.com/press/awards.html
Announcing MySQL Administrator
http://www.mysql.com/products/administrator/index.html
MySQL, Zend Join Forces to Strengthen Open Source
Web Development
http://www.mysql.com/press/release_2004_05.html
JBoss Group and MySQL AB Form Strategic Alliance
to Deliver Professional Open Source Solutions to
the Enterprise
http://www.mysql.com/press/release_2004_04.html
MySQL AB to Demo New Technologies at LinuxWorld
Expo
http://www.mysql.com/news/article-494.html
MySQL AB Adds Enterprise Features in MySQL 5.0
http://www.mysql.com/press/release_2004_02.html
MySQL Available for HP-UX on Intel Itanium 2
Processor
http://www.mysql.com/press/release_2004_01.html
Evans Data Survey Shows MySQL Usage Up 30% in 6
Months
http://www.evansdata.com/n2/pr/releases/Database_Winter_04.shtml
--------------------------------------------------
UNSUBSCRIBING & FEEDBACK
You can send us queries and give us feedback at
http://www.mysql.com/feedback/
You can unsubscribe from this newsletter from your profile page:
http://www.mysql.com/login.php
or by replying to this e-mail with the subject line containing
'unsubscribe '
ABOUT THE NEWSLETTER
Please read http://www.mysql.com/newsletter/
________________________________________________
Copyright © 2004 MySQL AB. All rights reserved.
You are free to distribute this newsletter,
as long as you don't make any changes.
|
