Archive

Archive for February, 2009

MySQL Query Profiler; Better late than never

February 3rd, 2009 No comments

One of the great things about MySQL is the superior innovation model that’s used to deliver database server software. Rather than relying solely on internal engineers who create and maintain a piece of software (as in a traditional software company), MySQL partners with the millions of active users across the world who take advantage of the open source model and daily extend the MySQL server to do new and pioneering things. These innovations can then be submitted to MySQL AB, tested, validated, and rolled into the database server so everyone can benefit from the creativity of the very active MySQL community.

An example of this model in action is the recent release of a new SQL diagnostic facility that assists in the debugging and analysis of running SQL – the SQL Profiler. The new profiler became available in the 5.0.37 version of the MySQL Community Server and was created and submitted by Jeremy Cole of Proven Scaling (http://www.provenscaling.com/).

Let’s take a look at this new diagnostic utility Jeremy developed and see how it can help you create high-performing SQL code as well as assist in troubleshooting existing queries that aren’t providing the response times you’d like.

The Best Way to Diagnose Performance Problems

When it comes to overall performance, it’s important to remember that the #1 contributor is always a good database design. The second highest contributor to performance is well-tuned SQL code. Some try and switch the two in priority, but this is a mistake because a bad design has the potential to simply drown even the best-tuned SQL code (e.g. you can’t get index access in your SQL queries if the physical index design is poorly done). But make no mistake – bad SQL code that’s introduced into an otherwise well-running database can make a real train wreck of things.

So how do you go about analyzing database performance? There are three forms of performance analysis that are used to troubleshoot and tune database systems:

  1. Bottleneck analysis – focuses on answering the questions: What is my database server waiting on; what is a user connection waiting on; what is a piece of SQL code waiting on?
  2. Workload analysis – examines the server and who is logged on to determine the resource usage and activity of each.
  3. Ratio-based analysis – utilizes a number of rule-of-thumb ratios to gauge performance of a database, user connection, or piece of code.

Of the three, bottleneck analysis is the most efficient approach in terms of fast problem resolution. By determining where the server, a user connection, or set of SQL code is spending its time, you can then work to eliminate the discovered blockages, increase throughput, and reduce overall response times. Unfortunately, this hasn’t always been easy in MySQL because the server hasn’t traditionally exposed the type of wait-based and resource statistics to quickly uncover bottlenecks.

But a better day has come along in MySQL 5.0.37 with the inclusion of the SQL Profiling utility. This interactive diagnostic aid allows a developer or DBA to gain insight into where a set of SQL code spends its time and the resources it is using. The best way to see the power of this new utility is to walk through some examples of how it’s used and see the clear benefits it supplies, so let’s do just that.

rschumacherRobin Schumacher is MySQL’s Director of Product Management and has over 13 years of database experience in DB2, MySQL, Oracle, SQL Server and other database engines. Before joining MySQL, Robin was Vice President of Product Management at Embarcadero Technologies.

Read the full article at Mysql Developer Articles

Open PHP MyProfiler is just a trial to run query profiling on a php-mysql application, without changing the architecture too much. Any one who needs to do the analysis could make use of the same by downloading the profiler, and implementing with their code. The profiler would create logs depending on hostname and date. upload the profile logs to our Profile Sampler and you should be able to see the full profile of your application.

The Kerala’s favourite portal Keralaonline was built on top of the popular blogging and content managment system WordPress. With about 30K hits per day, and 8000 entries across 100+ categories and video clips, the site started to slow down by about the end of March. This gave a spark, and the Open PHP MyProfiler, was built originally to run sql profiling on keralaonline.com.

Categories: General Articles, mysql Tags:

A Googly MySQL Cluster Talk

February 3rd, 2009 No comments

Google TechTalks April 28, 2006 Stewart Smith Stewart Smith works for MySQL AB as a software engineer working on MySQL Cluster. He is an active member of the free and open source software community, especially in Australia. ABSTRACT Part 1 – Introduction to MySQL Cluster The NDB storage engine (MySQL Cluster) is a high-availability storage engine for MySQL. It provides synchronous replication between storage nodes and many mysql servers having a consistent view of the database. In 4.1 and 5.0 it’s a main memory database, but in 5.1 non-indexed attributes can be stored on disk. NDB also provides a lot of determinism in system resource usage. I’ll talk a bit about that. Part 2 – New features in 5.1 including cluster to cluster replication, disk based data and a bunch of other things. anybody that is attending the mysql users conference may find this eerily familiar.
Read more…

Categories: Videos Tags:

Performance Tuning Best Practices for MySQL

February 3rd, 2009 No comments

Google TechTalks April 28, 2006 Jay Pipes Jay Pipes is a co-author of the recently published Pro MySQL (Apress, 2005), which covers all of the newest MySQL 5 features, as well as in-depth discussion and analysis of the MySQL server architecture, storage engines, transaction procesing, benchmarking, and advanced SQL scenarios. You can also see his name on articles appearing in Linux Magazine and can read more articles about MySQL at his website. ABSTRACT Learn where to best focus your attention when tuning the performance of your applications and database servers, and how to effectively find the “low hanging fruit” on the tree of bottlenecks. It’s not rocket science, but with a bit of acquired skill and experience, and of course good habits, you too can do this magic! Jay Pipes is MySQL’s Community Relations Manager for North America.
Read more…

Categories: Videos Tags:

PHP TestFest 2009

February 2nd, 2009 No comments

It’s time to start getting the 2009 PHP TestFest underway. The TestFest is a worldwide event in which PHP user groups and individuals contribute to PHP by writing tests for PHP. It’s a great way to contribute to one of the most successful open source projects there has ever been, it’s also pretty cool to see your name in the the source distribution for code that’s running on over 20 million web domains.

So how does it work?

User groups can register by sending a mail to php-qa@lists.php.net. In the mail we’d like you to pick an date for your event (somewhere between April 1st and June 30th), it would be great if you could let us have the name and e-mail for the primary contact too. As these arrive on the QA list, someone (probably me) will transfer them to the TestFest wiki. We will help you to work out what sort of event to run and how to organise it. Individuals can register for the TestFest too, just send a mail to the php-qa@lists.php.net.

User groups (or individuals) can pick areas in which they’d like to write tests, this might be something you know quite well already, or maybe something new you’d like to know about. For example, the London PHP group picked the dom extension last year. I didn’t know much about that when we started but I did when we finished!

What do we need?

A bit of infrastructure! Scott MacVicar is putting together an SVN repository for tests so that contributors will be able to commit tests directly. He’s still working on how access control will work….more on this later.

Sponsors! We have one offer of ElePHPants. We’ll do the same as we did last year and hold a draw at the end of the TestFest. Anyone who contributes a test will have a chance to win one. Of course, the more you contribute the higher your chances will be!

Mentors, mentors and more mentors! We need internals developers who know how to write tests, have karma in cvs.php.net and who can review tests and commit them. Again – if you can help with this send a note to the php-qa@lists.php.net or let me know on IRC (efnet #php.pecl or freenode #phptestfest, #phpwomen).

For more information on PHP’s TestFest have a look here and here

Categories: Conferences & Roadshows Tags:

Scott’s Blog: Stupid Bug Reports

February 2nd, 2009 No comments

When you make a bug report or feature request to any sort of project please check you have all of the relevant information and if you can get someone else to check it through. Try searching Google first or ask in a mailing list.

For a performance issue check against an older version of PHP to see if you can work out when it was introduced, if you’re not currently testing the CVS version then check that as well. It may have been fixed already. Reports that language X is faster than PHP will also most likely be ignored, these really aren’t constructive and unless you can identify the issue within PHP it’s pointless to create a report.

Scott in his blog continues to write “Finally, don’t get aggressive or be an asshole when your bug reports get closed. PHP is an open source project and most contributors are volunteers. If you don’t like something then you are more than welcome to submit a patch.”

Categories: General Articles, PHP Tags: