There have been plenty of blog entries and writings about the MySQL Query Analyzer, for what I think are good reasons. Labeling it a potential Killer App, causing many MySQL users to become paying Sun customers, may be a daring thing. However, the Query Analyer might very well have what it takes. The key benefit of it is that it identifies the source of performance bottlenecks. In that sense, one could perhaps instead call it a profiler, as it analyses the set of all queries going on, as opposed to an individual one. One person to whom I described it said “ah, so it’s sort of a DTrace for MySQL queries?” — and for those who know what DTrace is, it’s not a bad analogy.

Some of the key documents and blogs to read includes the overall product description, the Sun PR text, and the interview with Mark Matthews. Do read Mark Callaghan’s review “Query Analyzer Rocks“. Do read Zack Urlocker’s Query Analyzer related business model discussion, where he asks “Can you develop a business model that works for the community and for paying customers?“.

Given the non-FOSS nature of the Query Analyzer, there has also been some community discussion on this, such as Baron Schwartz mentioning an alternative to the MySQL Query Analyzer, followed by Mark Leith’s response to it.

And the non-FOSS nature of the Query Analyzer is something I need to underline, in order not to set false expectations. It is part of the MySQL Enterprise subscription offering. It is not GPL software. So what this means is that you will be confronted with registrations, with trial periods and similar things familiar from other non-FOSS software, should you decide to try it out. You may agree or disagree as to whether that is right or wrong, but that is the business model we’ve chosen at Sun Microsystems (and read Zack’s blog post for some of the underlying reasoning). And while my role as VP MySQL Community is primarily focused at growing and serving the MySQL user base, I am of course also interested in Sun having a business model for MySQL that enables growth in economic terms. I’m excited to see where the Query Analyzer will take us!

So, to sum it up: You may recall various discussions on Sun wanting to serve both the group of people who “have more time than money” and those who “have more money than time“. The value of MySQL Query Analyzer just might be compelling enough for many to count themselves into the latter group.

Links:

• http://www.mysql.com/products/enterprise/query.html
• http://www.sun.com/aboutsun/pr/2008-11/sunflash.20081119.1.xml
• http://dev.mysql.com/tech-resources/interviews/interview_mark_matthews.htm
• http://mysqlha.blogspot.com/2008/11/query-analyzer-rocks.html
• http://weblog.infoworld.com/openresource/archives/2008/11/mysql_query_ana.html
• http://www.xaprb.com/blog/2008/11/20/an-alternative-to-the-mysql-query-analyzer/
• http://www.markleith.co.uk/?p=22
• http://www.mysql.com/why-mysql/white-papers/mysql_wp_queryanalyzer.php

[MySQL Business Model Dtrace Killer App MySQL Enterprise Profiler Query Analyzer ]
View original post|Add to del.icio.us | Share

This is the 124th edition of Log Buffer, the weekly review of database blogs. Welcome.

Here’s what the Oracle blogosphere had to offer. On Oracle and more, Amardeep Sidhu announces the birth of the North India Oracle Users Group. Not too far away, on the AskDba.org Weblog gets the word out about a seminar by Tom Kyte on 18th December in Bangalore, arranged by the All India Oracle User Group.

Vivek Sharma examined Library Cache Latch Contention and Scalability to answer the question, “Why is Library Cache Latch Contention referred to as a ‘Severe Scalability Issue’?”

Marco Gralike discusses the value of setting an “impossible” password for SYS — even when you can use LOCK.

Greg Rahn of Structured Data explained the use of a preprocessor for external tables. He writes, “Before External Tables existed in the Oracle database, loading from flat files was done via SQL*Loader. One option that some used was to have a compressed text file and load it with SQL*Loader via a named pipe. This allowed one not to have to extract the file, which could be several times the size of the compressed file. As of 11.1.0.7, a similar feature is now available for External Tables . . . ”

On Oracle Corp’s Oracle Certification blog, Paul Sorensen debuts a new booklet called “Performing an Oracle DBA 1.0 to DBA 2.0 Upgrade”: “The knowledge required by the typical Oracle DBA has increased, requiring additional skills and in some cases job responsibilities. Oracle Certified Masters Joel Goodman and Harald van Breederode discuss this shift in DBA skills in this 10-page PDF booklet . . . ”

(more…)

[Log Buffer MySQL Non-Tech Articles Oracle SQL Server ]
View original post|Add to del.icio.us | Share

Brian, Monty, Stewart, Lee and myself sat down yesterday and fleshed out the blueprint tasks that we are targeting for the Cirrus milestone. This marks the first time we've made a concerted effort to really determine the tasks which are of essential importance in moving towards Drizzle's first release. Before I detail some of the major tasks, I'd like to make a quick recognition of our contributor community.

Although the MySQL server does have community contributions in some of the releases, the Cirrus milestone marks something of a new day in MySQL-related development. Cirrus contains tasks which are actively being developed by external contributors. This may not sound like a huge deal, but it is.

In the past, contributions have been included in the MySQL server, however these contributions have always been included after the code has been contributed. For instance, Jeremy Cole's SHOW PROFILES patch, although heavily modified from its original submitted form, was included in MySQL Community Server after a long period of code review and modification. However, to my knowledge, the code contributor community has never been actively involved in either ongoing feature development for a release, nor actively involved in the direction in which the server is developed.

Cirrus marks a new day. Not only are tasks for Cirrus assigned to external contributors, but the decision-making and strategic power of the release is in the community's hands. The only reason a community member would not have a say in the direction of the server is if they don't speak up and share an opinion. As of this morning, there are 299 members of the drizzle-discuss mailing list. All of these members have a say in what gets done in Drizzle. This makes me a happy boy.

A Note on What a "Release" Is

Before the emails start firing off about what's in the first release of Drizzle and when it will come, I'd like to note that we are not going for a "big bang" approach to releasing software. The tasks I outline below are targets for a milestone. These tasks do not mean that the first release of Drizzle will contain all of the listed items. In fact, to be sure, some of them likely won't make it into the first release, and other tasks not listed currently for the milestone will "make it in". Although the community will eventually decide the release model, most (all?) of the developers sitting at Brian's table agree that an Ubuntu-like release model leads to more stable and consistent releases.

By "Ubuntu-like", I mean that it is the release date which is important to be kept stable, and not the list of features contained in the release. People want consistency in when to expect the next release; it makes it easy to look forward to a certain date. What is less important is what is included in the release. What counts is that each release is stable and demonstrates incremental improvements at a consistent rate. I'll be blogging more about this concept shortly and will start a discussion on the mailing list regarding possible release dates and a schedule for locking down commits before that date. Whatever is feature-complete at the time of lock-down goes into the release. Nothing more. Why? Because stability is more important. With a set release cycle, the feature that "missed the deadline" will eventually make it into the code base in a shorter amount of time, in a consistent and stable manner.

Targets for Cirrus

There are a number of major areas that Cirrus is targeting:

1. Code cleanup and standardization
2. An updated plugin architecture
3. An overhauled table/DB discovery design
4. An updated replication protocol
5. A completely rewritten client API and library
6. An overhauled testing system
7. An entirely redesigned INFORMATION_SCHEMA

Many tasks in the "cleanup, reuse and refactor" category have already been completed, by Monty, Brian, myself, and community contributors such as Toru Maesaka, Patrick Galbraith, Eric Day, C.J. Collier, and Yoshinori Sano. These tasks are listed on the blueprints page starting with "code-cleanup". They are also not as dependent on each other as some of the other task areas.

Feel free to click through on the various links to the milestone and blueprint tasks in this blog post, comment on the mailing lists, and be an active contributor. Nothing is off limits.

[MySQL Drizzle Launchpad ]
View original post|Add to del.icio.us | Share

Sun updates MySQL Enterprise. The Microsoft/Novell deal is two years old. Nuxeo and Boxee get funding. Red Hat’s CEO on open source in a downturn. Steve Ballmer as a glove puppet. And more.

Press releases
Sun Enhances MySQL Enterprise With New Query Analyzer Tool to Boost Database Application Performance Sun Microsystems

Microsoft and Novell Mark Two Years of Interoperability Progress Microsoft

Nuxeo secures 2 million Euros and strengthens its board of directors and corporate governance Nuxeo

Red Hat Expands JBoss Certified ISV Program With 250 Partners Red Hat

Live from Beijing China: Community to Launch NetBeans 6.5 IDE at Sun Tech Days Conference Sun Microsystems

rPath Expands Multi Operating System Support with Ubuntu, CentOS rPath

Open Source Leaders Andy Astor and Anthony Gold Join Bluenog’s Board of Directors Bluenog

Univa UD Announces Software Stack to Simplify Cluster Deployment Univa UD

Sun’s Lustre File System Powers More Than 50 Percent Of The Top 50 Supercomputers Sun Microsystems

New Open Storage Solutions Establish Sun As HPC Storage Leader Sun Microsystems

Open Source Data Protection from Zmanda Soars to the Cloud(s) in 2008 Zmanda

CorraTech Launches Outsourcing Solutions For Open Source Independent Software Vendors CorraTech

New, Faster Zend Framework 1.7 Features Adobe High-Speed Format for RIAs plus IBM i DB2 Adapter, JQuery, & Dojo 1.2.1 Zend

Ulteo unveils the first Open Source virtual desktop, providing businesses with quicker, cheaper deployment and easier applications management Ulteo

OpenLiberty.org Releases First Open Source Identity Governance Framework Software OpenLiberty.org

News articles
Red Hat CEO Whitehurst Talks About Slowdown, Virtualization, Linux Charles Babcock, InformationWeek

Boxee raises funding as it goes after content, your living room Mg Siegler, VentureBeat

Struggling Sun faces difficult choices about future Jon Brodkin, NetworkWorld

The Microsoft-Novell Linux deal two years later Paul Krill, Computerworld

Open Source 101: An Executive Guide to Open Source Christopher Lindquist, CIO.com.au

An Open Source Vendor’s Biggest Competitor: Its Customers? Serdar Yegulalp, InformationWeek

Blogs
Sustainability in Uncertain Times Mitchell Baker, Mozilla

Two Years and Counting…. Peter Galli, Port25

Microsoft and Novell at two: Was the patent pact worth it? Mary-Jo Foley, ZDNet

Final Judgment in SCO v. Novell: SCO Loses Again Pamela Jones, Groklaw

MySQL Query Analyzer and open source business models Zack Urlocker, InfoWorld

Is Red Hat’s Whitehurst Right? Open Source Thrives In Downturn? Charles Babcock, InformationWeek

Open-source traffic is way up in 2008 Matt Asay, Cnet

How to Maintain a Successful Community Dawn Foster, Community 2.0

Ubuntu Server Edition: Canonical’s Big Challenge The VAR Guy

OpenX: the Unknown Variable Glyn Moody, ComputerWorld

Audio/visual
Steve Ballmer on Yahoo Acquisition 1938enterprise
The clip linked to above features a glove puppet of Steve Ballmer and some very strong language. Do not click it if you are easily offended. Do click it if you are easily amused.

[Links Software 451 group 451caostheory 451group amanda Andy Astor anthony gold bluenog boxee canonical caostheory CentOS corratech jboss Linux lustre matt aslett mattaslett matthew aslett matthewaslett Microsoft mysql netbeans Novell nuxeo open-source ope]
View original post|Add to del.icio.us | Share

Progress on the Open Source enterprise grade MySQL monitoring system;  the schema for Monolith version 2 has been designed. Due to the many suggestions for features and the interest it has received I’ve put this on the front burner. That said, here is some more info on the next steps I’ll be taking.

• Monitored servers will use a command line agent (called remotely) to pull information from both MySQL and the OS.
• Historical information will include all values from global status and global variables, as well as CPU/Memory/IO/Disk usage.
• Standard graphing functions - the ones in the list from the previous post - will gather information from various view tables that contain historical data that is collected from the agent script. User defined graphing will allow you to look at historical values over time for any of the various global status or global variable settings (integer based ones anyway).
• The cnf file for each server will be stored in the monitoring database and available for viewing for historical purposes. 

[Monolith Uncategorized mysql server analytics dba graphing monitoring mysql community MySQL Enterprise ]
View original post|Add to del.icio.us | Share

It almost never crashes for me now, but you might not be able to make the same claim. Well, if you use MyISAM rather than InnoDB you might be able to make the claim, but in that case you really need it to not crash as MyISAM might not recover to a transaction consistent state. The fix is not in the official release. This is almost a repeat of a recent post, but one thing has changed. We deployed the fix for bug 32149 and eliminated the cause of the majority of crashes from software bugs. We still get crashes on a daily basis, but hardware is the cause.

What can you do?

• update bug 32149 and ask for a fix
• file a support ticket and ask for a fix
• include a request for a fix of this bug in the holiday card you send to Marten
  

But whatever you do, don't hold up the release of GA for 5.1.

This is a very simple fix and is available here and here.

View original post|Add to del.icio.us | Share

MySQL 6.0.8 has new interfaces for semisynchronous replication.

The interfaces make it possible to load components to ensure that all transactions are replicated to the slave before the users of the master gets acceptance of the commits.

The original code was developed by Mark Callaghan (Google) and Wei Li (Google). Zhenxing He (MySQL) ported the Google code to MySQL 6.0, extracted the functionality into pluggable components, and changed the code so that all storage engines are supported. Paul DuBois (MySQL) wrote the MySQL manual documentation.

The extraction of this patch into components is a first step in our strategy to make MySQL Replication modularized with separate loadable components for special replication functionality. With more interfaces to the server, it becomes easier to make the server behave in the way you want.

Please let us know what you think. Does it work as you expect? Do you want the synchronization to be different? Want to publish your own replication components for MySQL?

References

• MySQL manual documentation
• Plugin executables
• Plugin source code
• Original Google patches

[mysql replication 6.0 ]
View original post|Add to del.icio.us | Share

Peter wrote a post a while ago about choosing a good InnoDB log file size.  Not to pick on Peter, but the post actually kind of talks about a lot of things and then doesn't tell you how to choose a good log file size!  So I thought I'd clarify it a little.

The basic point is that your log file needs to be big enough to let InnoDB optimize its I/O, but not so big that recovery takes a long time.  That much Peter covered really well.  But how do you choose that size? I'll show you a rule of thumb that works pretty well.

In most cases, when people give you a formula for choosing a configuration setting, you should look at it with skepticism.  But in this case you can calculate a reasonable value, believe it or not.  Run these queries at your server's peak usage time:

PLAIN TEXT SQL:

1. mysql> pager grep sequence
2. PAGER SET TO 'grep sequence'
3. mysql> SHOW engine innodb STATUS\G SELECT sleep(60); SHOW engine innodb STATUS\G
4. Log sequence number 84 3836410803
5. 1 row IN SET (0.06 sec)
6.  
7. 1 row IN SET (1 min 0.00 sec)
8.  
9. Log sequence number 84 3838334638
10. 1 row IN SET (0.05 sec)

Notice the log sequence number. That's the total number of bytes written to the transaction log. So, now you can see how many MB have been written to the log in one minute. (The technique I showed here works on all versions of MySQL. In 5.0 and newer, you can just watch Innodb_os_log_written from SHOW GLOBAL STATUS, too.)

PLAIN TEXT SQL:

1. mysql> SELECT (3838334638 - 3836410803) / 1024 / 1024 AS MB_per_min;
2. +------------+
3. | MB_per_min |
4. +------------+
5. | 1.83471203 |
6. +------------+

As a rough rule of thumb, you can make the log big enough that it can hold at most an hour or so of logs. That's generally plenty of data for InnoDB to work with; an hour's worth is more than enough so that it can reorder the writes to use sequential I/O during the flushing and checkpointing process. At this rate, this server could use about 110 MB of logs, total. Round it up to 128 for good measure. Since there are two log files by default, divide that in half, and now you can set

PLAIN TEXT CODE:

1. innodb_log_file_size=64M

Does that look surprisingly small? It might. I commonly see log file sizes in the gigabyte ranges. But that's generally a mistake. The server I used for the measurements above is a big one doing a lot of work, not a toy. Log file sizes can't be left at the default 5MB for any real workload, but they often don't need to be as big as you might think, either.

If this rule-of-thumb calculation ends up showing you that your log file size ought to be many gigabytes, well, you have a more active write workload. Perhaps you're inserting a lot of big rows or something. In this case you might want to make the log smaller so you don't end up with GB of logs. But also realize this: the recovery time depends not only on the total log file size, but the number of entries in it. If you're writing huge entries to the log, fewer log entries will fit into a given log file size, which will generally make recovery faster than you might expect with a big log.

However, most of the time when I run this calculation, I end up finding that the log file size needs to be a lot smaller than it's configured to be. In part that's because InnoDB's log entries are very compact. The other reason is that the common advice to size the logs as a fraction of the buffer pool size is just wrong.

One final note: huge buffer pools or really unusual workloads may require bigger (or smaller!) log sizes. This is where formulas break down and judgment and experience are needed. But this "rule of thumb" is generally a good sane place to start.

Entry posted by Baron Schwartz | No comment

Add to: | | | |

[Innodb tuning ]
View original post|Add to del.icio.us | Share

...has just been released:

You can download the GPL source distribution here. The config tool has been updated to use the new version.

Important things in 6.3.19:

• MySQL Server 5.1.29
  
• Faster --initial
• Undo logs and data files (disk data) is created faster. Now 512K is written at a time instead of 32K.
  
• New config parameter: InitFragmentLogFiles=sparse|full (you can choose if you want sparse redo log files or zero filled (full)).

For a full change log please see the reference manual.

To upgrade using severalnines.com/config:

• copy cluster/config/config.ini to a safe place (if you have made changes to it)
  
• copy cluster/config/my.cnf to a safe place (if you have made changes to
  comment out #skip_thread_priority in my.cnf if you have it set (it is deprecated).
  
• generate a new config that mimics your current config so that you get exactly the same scripts (but a new version of them).
• Unpack the mysqlcluster-63.tar.gz
• copy the config.ini and the my.cnf from the safe place to mysqlcluster-63cluster/config/
• cd mysqlcluster-63/cluster/scripts
• sh download-and-compile.sh
• sh dist.sh
• sh rolling-restart.sh (will copy out the config files from cluster/config hence it was important to copy them back there)

CMON
If you are using cmon-0.10 (you should upgrade to cmon-0.11) or cmon-0.11:

• stop cmon
  
• start a mysql client:
  
• use cmon
  
• drop mysql_statistics;
• drop mysql_variables;
• start cmon
  

cmon will now recreate the mysql_statistics and mysql_variables tables. This is because the MySQL server in MySQL Cluster 6.3.19 (and 6.3.18) has added more status variables. If you don't do the above, then cmon cannot aggregate mysql statistics.


View original post|Add to del.icio.us | Share

I have the pleasure to invite the Ukrainian and Russian MySQL communities to three events in the upcoming weeks, all of which I am attending myself.

Firstly, there is the Sun Microsystems ISV partnership event in Kiev on Wed 26.11.2008 from 10:00-18:00. The program for this event is published on my Russian language blog. Please register for this event through an email to Svetlana.Kovtun@Sun.COM.

Second, right after the ISV partnership event, we have a MySQL community get-together in the Sun offices within a 15 minutes walk. We’ll talk about MySQL and Sun, the integration status and plans, at 18:30-20:00. I hope to get several of the former MySQL employees, current Sun employees, attending. As a community member, you can attend either one or both of these events, but always register with Svetlana.Kovtun@Sun.COM.

Third, we have a community event for the Moscow MySQL users. It happens on Monday 1 December 2008 from 19:00 onwards in the High School of Economics at Myasnitskaya 20, room 124.

Please register for this event by leaving a comment on Kostja Osipov’s blog.

• http://blogs.arno.fi/fandorin/2008/11/21/kiev-26-nov-2008-conference-sun-for-isvs/
• http://blogs.arno.fi/fandorin/2008/11/13/visit-in-moscow-1-3-dec-2008/
• http://blogs.arno.fi/fandorin/2008/11/13/visiting-kiev-26-27-nov-2008/
• http://kostja-osipov.livejournal.com/27466.html

[MySQL ]
View original post|Add to del.icio.us | Share

I have written about MySQL Query Browser and MySQL Administrator in my article: A graphical way to MySQL mastery on Linux.com

PS: All my writings are archived here at http://amitsaha.in.googlepages.com/writingsandtalks

[MySQL ]
View original post|Add to del.icio.us | Share

I have measured the time it takes to do some operations with Cluster (6.3.19, which is about to be released very soon - you should upgrade) with respect to the disk, like starting it, creating table spaces etc.

Below all of this you can find the setup I used.

Initial start of Cluster (40GB of redo log files):
6.3.19: 3min 27sec

Create a 20GB undo file:
6.3.19: 6min 17sec

Create a 128MB data file for the tablespace:
6.3.19: ~3 sec

Insert 1M records a' 4096B (1 thread, batches of five):
6.3.19: 286 sec (3721.12 QPS)
(we can probably provision faster with bigger batches or more threads)

I then provisioned another 4M records (total of 5M records in DB).

Evil test: 100K Random reads (read 4096B) (5M records in DB):
6.3.19: 1290.42QPS (20 threads, io util is ~90% so we are almost completely io bound). So this is result is inline what we would expect when being io bound, especially since i have used four data nodes, each having one disk.

Setup:

• Total of 4 data nodes on 4 computers.
• Another computer was used to generate the inserts/reads
  
• 8GB of RAM
• Gig-E
  
• 1 * 146GB SAS, 10KRPM
  
• 128MB IndexMemory
  
• 1024MB DataMemory
• ODirect=1
  
• SharedGlobalMemory=384M
• NoOfFragmentLogFiles=40
  
• FragmentLogFileSize=256M
  
• DiskPageBufferMemory=3072M
• Table space (one ts) with 100 datafiles a' 128MB (best practice is to use many but small data files instead of one big. This will be changed in 6.4 so that you can use one big data file).
  
• Extent size=1MB (which is quite ok)
  
• Logfile group: One 20GB undofile and 128MB Undobuffer
  The undo file was a bit too big (not that it matter, i had the disk space) but I used 5366054928 extents out of 21474836480 (so ~25% was only used).
• There is also a new configuration option in 6.3.19 which lets you create the data files
• The disk data table looks like (data column will be stored on disk):
  create table dd (
  id integer primary key,
  ts integer,
  data varbinary(4096),
  index(ts)) engine=ndb TABLESPACE ts_1 storage disk;
• Fedora core 9 ( uname -r --> 2.6.26.6-79.fc9.x86_64 )
  

(the config files were generated by severalnines.com/config )

Now, especially for inserts/writes there are quite a few things competing for the single disk:

• REDO log
  
• LCP
• and the disk data itself (UNDO LOG + DATA FILES)
  

However, the best setup is to (if you have two disks)

• Disk 1: REDO + LCP
• Disk 2: UNDO LOG + DATA FILES

Three disks:

• Disk 1: REDO + LCP
• Disk 2: UNDO LOG
  
• Disk 3: DATA FILES

If you have more than that then you can put DATA FILES and UNDO LOG on a RAID.

IMPORTANT: When you have done an --initial start, the files for the UNDO LOG and the DATA FILES are NOT removed. You have to remove them by hand. Otherwise you will get an error if you try to CREATE LOGFILE GROUP..

View original post|Add to del.icio.us | Share

So it’s been a while since I’ve hit my blog, but I feel compelled to respond to Baron’s post, and many of the other (perhaps short sighted) criticisms out there against this new functionality that we’ve been working on for so long.

Everybody seems to be saying that this functionality should be implemented in the server, or that the better way to do this is to use these patches which add functionality to the logging that MySQL already provides. Well guess what people - what does that give you, other than some more details on you queries?

More I/O.

What’s bad on a database server?

More I/O.

Query Analyzer, whilst it does currently use a proxy to collect the statistics, doesn’t hit your disk at all. Everything is collected and aggregated in memory, it doesn’t require you to turn on logging at all to collect this information. Everybody already knows that turning on the logging on MySQL can give some “serious” performance decreases - which is why we initially looked at using the proxy, rather than log monitoring and aggregation.

Yes, we are still looking at feeding information in to the Query Analysis page from different sources, such as the logs, or even DTrace, for people that don’t want to use the proxy, but we think we’ve actually come up with the better solution for overall database performance here in the short term.

And guess what:

It works on 4.1, 5.0, 5.1, 6.0

It gets the EXPLAIN plan at the time the query was at it’s worst, not at some random time afterwards when you decide to run a log analysis

You can redirect away from the proxy when you don’t need it - no extra overhead at all (whilst server instrumentation is more code in the server, more overhead, whether you want it or not…)

Now, we do have some limitations, which are perfectly valid - we can’t monitor the queries that a slave SQL thread executes for example, and we can’t measure the IO, lock waits, etc. for each statement - but we have some plans on server instrumentation without I/O in the works for that, and Query Analysis is sure to snap that up when available.

I’ll also note that the user and table statistics that were created by Google “get this”, and I’d very much like to see those integrated in to the server. We could really make some good use of those within MySQL Enterprise Monitor as well, and I’m hopeful that we will get these integrated at some point.

So Percona - here’s a challenge - how about creating your extra query logging patches in a way that doesn’t give the I/O overhead of using logging, in the way that Google’s User/Table/Index monitoring does? That would make them compelling, for me. The statistics that have been implemented are fantastic, no doubt about that, but don’t add I/O or lock overhead (such as the current mysql.general_log and mysql.slow_log in 5.1).

[MySQL ]
View original post|Add to del.icio.us | Share

More good news on Ivan Nikitin!

Yesterday, I got an email and some fresh pictures from a happy Andrii Nikitin, Ivan’s father. He met with a Danish doctor last week, and it seems as if everything is ready for Ivan and the rest of the family to move from the German clinic to Denmark at the end of November. The Nikitin family is settling down in Denmark and Andrii is already employed by Sun Microsystems Denmark.

Andrii, the proud father, says Ivan looks like any boy now. Sure, Ivan still gets tired a bit faster than other boys his age, and sometimes, he has a small nose bleed. However, the Nikitin family is now able to live as most families do.

The generous donations by the MySQL community and Sun employees were enough to cover the expert treatment Ivan got in Germany. It wouldn’t have covered a transplantation, but it looks increasingly unlikely that one will be needed. The Nikitins and all MySQL guys at Sun are very, very grateful for the donations.

So to sum it up: Ivan Nikitin’s future is painted in much brighter colours than ever before!

[Sun Andrii Nikitin Ivan Nikitin ]
View original post|Add to del.icio.us | Share

I know the architect of the new Sun/MySQL query analyser, Mark Matthews, very well. Even though the tool is not OSS (and thus does not benefit from an OSS development model with early feedback in the design/development cycle, from many eyes) I know that Mark organises his designs and team in such a way that the resulting quality (barring interference) will always be very good. Anyone who uses Connector/J will surely attest to that. So I'd personally trust the result, based on my knowledge of the person who led the team. That's still a form of semi-blind trust though, OSS development makes that unnecessary which is why I'd say an OSS dev model is preferred for most things. And the new tool is, of course, bundled with the MySQL Enterprise subscription model. You have to subscribe to that service to have access to the tool.

But back to the query analyser itself. I'm really fairly pleased with Sun/MySQL launching this, because it does appear to provide actual value for clients, without weird arrangements that make people cringe. Well done, Mark & MySQL!


Now we can make an interesting observation... as Baron already noted, there are some things that only the server knows. No proxy, packet sniffing, processlist or even show status can reveal all details in the required context. And running things like SHOW SESSION STATUS just adds extra overhead (an extra round trip to the server) after each query, so while that would provide many of the otherwise missing insights, it's not really a desirable approach.
This is why I too regard extra instrumentation inside the server as vital. Yet:

- Sun/MySQL, owner of the MySQL codebase (which is GPL), makes tools that are independent from the server.
- Others (Percona, OurDelta) add extra instrumentation into the server, to enable better monitoring and tuning.

Most intriguing!

View original post|Add to del.icio.us | Share

MySQL Query Analyzer is Sun's latest addition to the MySQL Enterprise tools suite. Here is the press release.

A simple architectural overview of the product is at http://www.mysql.com/common/images/enterprise/queryanalyzeragent_350.jpg

[MySQL ]
View original post|Add to del.icio.us | Share

MySQL just released their new MySQL Query Analyzer (link to a trial), and recently wrote up an interview with Mark Matthews about it. If you haven’t read that article, go ahead and do it. I have not used this software, but I fully believe its functionality is quite nice. But there is at least [...]
[SQL Tools microslow logging mysql MySQL Query Analyzer Percona ]
View original post|Add to del.icio.us | Share

Currently I'm working hard to find and remove scalability
bottlenecks in the MySQL Server. MySQL was acquired by Sun
10 months ago by now. Many people have in blogs wondered what
the impact has been from this acquisition. My personal
experience is that I now have a chance to work with Sun
experts in DBMS performance. As usual it takes time when
working on new challenges before the flow of inspiration
starts flowing. However I've seen this flow of inspiration
starting to come now, so the fruit of our joint work is
starting to bear fruit. I now have a much better understanding
of MySQL Server performance than I used to have. I know fairly
well where the bottlenecks are and I've started looking
into how they can be resolved.

Another interesting thing with Sun is the innovations they have
done in a number of areas. One such area is DTrace. This is a
really interesting tool which I already used to analyse some
behaviour of MySQL Cluster internals with some success. However
to analyse other storage engines inside MySQL requires a bit more
work on inserting DTrace probes at appropriate places.

To work with DTrace obviously means that you need to work with
an OS that supports DTrace. Solaris is such a one, I actually
developed NDB Cluster (the storage engine for MySQL Cluster) on
Solaris the first 5-6 years. So one would expect Solaris to be
familiar to me, but working with Linux mainly for 6-7 years means
that most of the Solaris memory is gone.

So how go about developing on Solaris. I decided to install a virtual
machine on my desktop. As a well-behaved Sun citizen I decided to
opt for VirtualBox in my choice of VM. This was an interesting
challenge, very similar to my previous experiences on installing
a virtual machine. It's easy to get the VM up and running, but how
do you communicate with it. I found some instructions on how to
set-up IP links to a virtual machine but to make life harder I
have a fixed IP address on my desktop so this complicated life
quite a bit. Finally I learned a lot about how to set-up virtual
IP links which I already have managed to forget about :)

The next step is to get going on having a development environment
for opensolaris. I soon discovered that there was a package
manager in opensolaris which could be used to get all the needed
packages. However after downloading a number of packages I
stumbled into some serious issues. I learned from this experience
that usage of Developer Previews for OS's is even worse than newly
released OS's which I already know by experience isn't for the
fainthearted.

So I decided to install a released opensolaris version instead
(the OpenSolaris2008.05 version). After some googling I discovered
a very helpful presentation at opensolaris developer how-to
which explained a lot about how to install a development
environment for opensolaris.

After installing opensolaris 2008.05, after following the
instructions on how to install a development environment
I am now equipped to develop DTrace probes and scripts and
try them out on my desktop.

I definitely like the idea that opensolaris is looking more
like yet another Linux distribution since it makes it a
lot simpler to work with it. I would prefer GNU developer
tools to be there from scratch but I have the same issue
with Ubuntu.

That the system calls are different don't bother me as a
programmer since different API's to similar things is
something every programmer encounters if he's developing
for a multi-platform environment. I even look forward to
trying out a lot of Solaris system calls since there are
lots of cool features on locking to CPU's, controlling
CPU's for interrupts, resource groups, scheduling
algorithms and so forth. I recently noted that most of
these things are available on Linux as well. However
I am still missing the programming API's to these
features.

View original post|Add to del.icio.us | Share

Lately planetmysql.com has shown an increasing amount of commercial promotion for Sun products. Many recent articles included in this supposedly MySQL-related feed aggregator have nothing to do with MySQL. Now I see an article included in the feed from ComputerWorld, about database researcher David Dewitt getting a job at Microsoft. Not only is this non-MySQL-related content, but more than
[mysql ]
View original post|Add to del.icio.us | Share

Imagine eating meze in Ankara with a Turkish Member of the Parliament, and member of the Turkish EU Harmonisation Committee. That’s not the basic reason I joined MySQL AB back in 2001, but it’s one of the most interesting outcomes of my decision. Bottom line: The honourable Mr Osman Co

[MySQL Advocacy Ankara Aslam Raffee Bili Turkey ]
View original post|Add to del.icio.us | Share

I am hoping I can get some feedback from you all.  I have been excited about the support for completion in the NetBeans PHP editor (and other editors to follow).  The response to my quick little blog about this has been pretty positive.

To refresh, here's an example of what it would look like:



But I have also gotten some feedback from more than one source that this may not actually be as valuable as it may first appear.  The reason is that, and this makes sense, the actual work flow when building SQL for your application is as follows:

• Open SQL tool and write your query
• Execute, evaluate results, modify, repeat
• Copy and paste final SQL into your editor

With this flow in mind, it doesn't seem that important to provide completion and other SQL editing features embedded in your application editor, because you need to test to validate that the SQL is correct.

What are your thoughts?  Can you explain to me why SQL editing features in the code editor is valuable, if generally SQL is composed in a SQL tool/editor?

One thing we've talked about is enabling testing from within the code editor.  But it's not clear what this would look like.  We would need to be able to build up an executable SQL statement from SQL that contains substitution variables and may even be a concatenation of multiple strings.  I suppose it's possible, but it seems complicated and error-prone.

I suppose one simplistic approach is the following flow:

• In your editor, write a full SQL statement as a string
• right-click and choose "Test SQL" (or run a hot key) to let you test the SQL string right there in the application editor 
• refine, retest
• modify the SQL to use substitution variables

At least with this approach you wouldn't have to cut-and-paste, but I'm not sure it buys you much.

So, what are your thoughts?  Your feedback much appreciated.
[mysql netbeans ]
View original post|Add to del.icio.us | Share

The MySQL database server can display a lot of performance statistics. However, most of them relate to general server parameters, such as buffer sizes and query cache settings. Of course, such settings are important to get right, and can make a huge difference in performance. Once you've tuned them however, you need to start looking at the tables and queries. When a single bad query can cost 90 - 99% of the total performance, server tuning doesn't fix the problem. But obtaining query profiling information from MySQL can be tricky. Here are some of the options:Slow query logYou can use the built-in slow_query_log from within the server. It will show you queries that take more than a second. Such queries typically don't use indices properly and are important to address. But only slow queries end up there, and many performance problems aren't caused by slow queries, but by queries that are run very often (for example, for-loops in the code instead of JOINs). You could use complete server query logging. This will log every statement to file, but it's not recommended, as it will take too much disk I/O.SHOW PROFILESSHOW PROFILES command by Jeremy Cole (available from MySQL 5.0.37) can help you a great deal. Once enabled, it will gather cpu execution times and other important information on up to 100 queries. You can compare the results and see wich query is most expensive. This article explains more. SHOW USER / INDEX / TABLE STATISTICSThe SHOW USER / INDEX / TABLE STATISTICS patches from Google add functionality to trace cpu time and much more per user, index and table. No query information is shown though, you will have to figure that out based on the tables. The patches are not part of the main MySQL distribution, but Percona provides prebuilt MySQL versions.MySQL Enterprise Monitor's Query AnalyzerMySQL Enterprise Monitor's Query Analyzer is great for finding the heaviest queries. Using MySQL Proxy (man-in-the-middle software), it collects all queries sent to the server and ranks them by execution time. You can also see number of times the query was run, and number of rows typically returned. Being the best tool of them all, it also costs, and it requires additional software/hardware setup for the MySQL Proxy.SHOW PROCESSLISTAnother option is the SHOW PROCESSLIST command. It shows a list of currently running processes (queries), slightly similar to the Unix ps command or Windows Task Manager. For every query, you can see how long the query has run, its user, ip and state. If you run this command just once, you will get a snapshot of what queries the server is busy doing right now. Aggregating multiple snapshots over time can give you a good view of what queries the server typically is busy performing. An advantage of SHOW PROCESSLIST is that it works on all MySQL versions without modifications and requires no additional software / hardware. On the downside, you don't necessarily catch all queries, just the queries that happen to run during every snapshot. If you collect many snapshots over long time, this is less of a problem.IssuesYou might not have the option to go for MySQL Enterprise, or install MySQL Proxy in front of your database servers. Or you might not be able to swith to the MySQL server versions containing Google's patches or SHOW PROFILES. Another problem is, that even if you do get profiling information, it is typically presented in raw text form, and might not be easy to browse through. Good tools to visualize the profiling information and navigate through the data is just as important as getting the information in the first place.A new profiling tool We're currently in the process of developing a tool based on the SHOW PROCESSLIST above. The tool, called Jet Profiler for MySQL, collects process list information and stores it in an internal database where it is analyzed, ranked and presented. After normalizing the queries, top lists are created of:

• most frequent queries
• most frequent users
• most frequent tables
• most frequent schemas

By looking at the state of the processes, additional information can be extracted, such as lock-prone queries (if you're using MyISAM tables), or queries which create a lot of temp tables on disk. The tool is a client desktop app so no server changes are required. It works on all MySQL versions (3.23 - 6.0).Better visualization and usabilityTo provide good visualization, the information is presented in a line chart over time. You can zoom in on spikes and other interesting time intervals, and see the top queries / users / etc for that particular time frame. Every top list is accompanied by a pie chart to make it easier to compare the impact of different items. An experimental EXPLAIN visualization feature is underway, helping you understand the queries better.Beta testers are welcomeWe still have lots of adjustments and features before a public release, but we are looking beta testers. If you are interested, click here.

More information


Comments
[mysql news query profiling processlist ]
View original post|Add to del.icio.us | Share

Scaling MySQL on a 256-way T5440 server using Solaris ZFS and Java 1.7

*cough*

(and then wipe coffee off the computer)

of course the real aim should be to scale with one instance on the machine as scaling with multiple instances on the one machine isn’t scaling at all - it’s scale out, but with more problems (now when one machine goes down, so do 1110202434 database instances).

[mysql ]
View original post|Add to del.icio.us | Share

Can you develop a business model that works for the community and for paying customers? READ MORE

[Open Source ]
View original post|Add to del.icio.us | Share

Kaj Arnö is coming to Moscow on 1-3 of December.

There will be a Moscow MySQL User Group meeting with Kaj in High School of Economics:

Date: Monday, 1st of December
Time: 19-00
Topic: Sun and MySQL: what's happening
Location: Myasnitskaya 20, room 124

Everybody is welcome to join. To come, you need to register (there is security at the entrance
to the building, and it needs a list of names, usual stuff), there is no url for a registration
form yet ;), you could just leave a comment on this entry, I'll add you to the list.

Moscow MySQL User Group

Visit this group

View original post|Add to del.icio.us | Share

cmon 0.11 which you can get from www.severalnines.com/cmon corrects the following bugs:

• Graphs wrap half ways - https://bugs.launchpad.net/cmon/+bug/300222
• Memory leaks (missed a couple of mysql_free_resultset) https://bugs.launchpad.net/cmon/+bug/300225
  

There are also some code cleanups.
To upgrade from earlier version:

• start a mysql client on the mysql server(s) having the cmon database
• use cmon
• drop mysql_statistics table
• drop mysql_variables table
• start cmon which will then recreate those tables

The above is particularly important if you move from MySQL Cluster 6.3.17 or earlier to 6.3.18 (or later) because the number of statistic counters and variables has increased between the versions. cmon will build up the mysql_statistics and mysql_variables tables dynamically instead (if you have dropped them earlier).

View original post|Add to del.icio.us | Share

Admins in the U.S got a chance to talk shop at EDUCAUSE and the LISA ‘08 summit, but what about those in Europe? Don’t despair, because we’re co-hosting a mini-conference with MySQL, SchoolForge, RedHat, Sun, Op5, and Fusis at our UK headquarters in London on November 27th.

While the presentations are specifically aimed at education as well as the non-profit sector, anyone is welcome to attend. Engage us in discussions about your thoughts on the latest technology, how it blends with your school’s ICT strategy, or tackle open source trends for the future. Details on the schedule are over at OpenSourceInSchools.org.uk (of course registration is free, and besides providing the specifics it also gets you a complimentary lunch).

[/etc Community Education Open Source ]
View original post|Add to del.icio.us | Share

MySQL Performance Tuning is always a popular topic for DBAs and Developers. Traditionally, database tuning has required manual effort and custom scripts. MySQL Query Analyzer aims to minimize the manual effort, with easy-to-use tools to help find-n-fix problem queries. In this interview, we talk with Mark Matthews, to get an architects view of the product.

View original post|Add to del.icio.us | Share

I wanted to call out one database tooling feature in NetBeans 6.5 that I think is pretty cool, and it's a little bit hidden.

If you execute a query and you get results, you can then do a number of interesting things with them.



Things like show what the CREATE TABLE statement would look like, or copy and paste the data you have selected (the paste format is hard-coded right now, but next release we plan to support CSV and XML output).

The one I like the best is that you can generate INSERT statements for the data.



Combine this with generating the CREATE TABLE statement, and it makes it really easy to hand your data set to someone else for testing or collaboration.
[mysql netbeans ]
View original post|Add to del.icio.us | Share

One of the problems with living in times as they are now is the lack of funds for what I like to call non-directed autodidactic exploration. Some of you would call this 'investigating new tools' but most would call it getting 'toys'. How do you know if D-trace, the MySQL Query Analyzer, or the next new announcement is that new tool that you can not live without if you do not get a chance to live with it? In the past I was able to rig an old laptop, my new laptop and an old development system to play, er, experiment with MySQL Cluster before taking my Cluster DBA exam.

Cisco CCIE candidates end up with an expensive pile of routers, hubs, switches, and cables in preperation for their lab exam. I wanted to get back up to speed on Solaris and went hunting used hardware for Sparc system so I could learn all the new items added since the 2.5.1/2.6 days (and boy did they add stuff). In the back of my mind is using some holiday time to work on a Solaris 10 Certification.

I also wanted hardware to let me investigate OpenSolaris, Glassfish, and the latest BSD-based releases. Plus there is that experiment I want to do with the Proxy Server and sharding. The list of wants was a lot longer than the funds on hand. And the idea of adding a pyramid of box past their prime did not appeal to me nor did the prospect of a higher electrical bill.

Virtual Box is a virtualization software that is freely available under a GPL license. Very quickly I was able to install Virtual box on my Mac and configure Ubuntu and OpenSolaris clients.

Nothing is really free. Each client takes a dozen or so GB of disk space. Running both clients, NeoOffice and Thunderbird at the same time makes my MacBook work hard. The good news is that is easy to remove the clients so that old opportunity cost is relatively low.

Now I have Glassfish and OpenSolaris on one virtual machine and can use my Mac and the virtual Ubuntu as clients. I can then query a MySQL 5.1 instance on the Ubuntu system and watch the Proxy Server re-route the request. Plus I was able to load the latest Zend Framework without having to disturb an older version on my main development system. Total cost out of pocket $0. Disk space cost about 25 GB. Time spent? Well that took about an hour for the first virtual server and twenty minutes for the second.

But now I have systems that I can use to explore all those new tools without having to crack open the wallet. And I have all these new toys, er, tools to learn. Times are good.

View original post|Add to del.icio.us | Share