Home |  MySQL Buzz |  FAQ |  Feeds |  Submit your blog feed |  Feedback |  Archive |  Aggregate feed RSS 2.0 English Deutsch Español Français Italiano 日本語 Русский Português 中文
Previous 30 Newer Entries Showing entries 91 to 120 of 439 Next 30 Older Entries

Displaying posts with tag: Professional (reset)

Optimizing UPDATE and DELETE statements
+3 Vote Up -0Vote Down

While most people look at performance optimizations for SELECT statements, UPDATE and DELETE statements are often overlooked. These can benefit from the principles of analyzing the Query Execution Plan (QEP). You can only run an EXPLAIN on a SELECT statement, however it’s possible to rewrite an UPDATE or DELETE statement to perform like a SELECT statement.

To optimize an UPDATE, look at the WHERE clause. If you are using the PRIMARY KEY, no further analysis is necessary. If you are not, it is of benefit to rewrite your UPDATE statement as a SELECT statement and obtain a QEP as previously detailed to ensure optimal indexes are used. For example:

UPDATE t
SET	c1 = ‘x’, c2 = ‘y’, c3 = 100
WHERE c1 = ‘x’
AND	d = CURDATE()

You can rewrite this UPDATE statement as a SELECT statement for using EXPLAIN:

EXPLAIN SELECT
  [Read more...]
Simple lessons in improving scalability
+2 Vote Up -0Vote Down

It can be very easy to improve scalability with a MySQL server by a few simple rules. Here is one of them.

“The most efficient way to improve an SQL statement is to eliminate it”

There are numerous ways to eliminate SQL statements, however before I give a classic example that I’ve observed again with a client, let me explain the basic premise of why this improves scalability?

The MySQL kernel can only physically process a certain number of SQL statements for a given time period (e.g. per second). Regardless of the type of machine you have, there is a physical limit. If you eliminate SQL statements that are unwarranted and unnecessary, you automatically enable more important SQL statements to run. There are numerous other downstream affects, however this is the simple math. To run more SQL, reduce the number of

  [Read more...]
MySQL 5.5.9
+5 Vote Up -0Vote Down

You blink and there is a new version. I have not seen an Planet MySQL release as yet about this new version. Release Notes.

I’d like to say I installed it, but I downloaded the Linux – Generic 2.6 (x86, 64-bit), TAR file, only to find it contains 6 rpm files. #fail, I’m using Ubuntu.

You have to scroll to the bottom of the list (another stupid thing for a generic binary choice) to get Linux – Generic 2.6 (x86, 64-bit), Compressed TAR Archive. Double #fail

Leveraging the InnoDB Plugin
+3 Vote Up -0Vote Down

Beginning with MySQL 5.1 as an additional plugin and included by default in MySQL 5.5 the InnoDB plugin includes many performance improvements. To leverage the support of new file formats however a very important setting is necessary.

#my.cnf
[mysqld]
innodb_file_per_table

The use of innodb_file_per_table with an existing system or during an upgrade to 5.1 or 5.5 requires a complete reload of your database to use effectively. In summary.

  • Backup all InnoDB tables via mysqldump
  • Drop InnoDB tables
  • Verify InnoDB not used
  • Stop MySQL
  • Enable innodb_file_per_table & simplified innodb_data_file_path (if applicable)
  • Remove ibdata? files
  • Start MySQL
  • Create Tables
  • Reload Data
  • Verify InnoDB Operation
    • The primary

  [Read more...]
Interesting MySQL 5.5 upgrade gotcha
+10 Vote Up -1Vote Down

Today I discovered an interesting upgrade problem with a client migrating from MySQL 5.0 to 5.5. The client who is undertaking the upgrade reported that MySQL 5.5 did not support the DECIMAL(18,5) data type. I easily confirmed this not to be the case:

mysql> drop table if exists x;
mysql> create table x (col1 DECIMAL(18,5));
Query OK, 0 rows affected (0.01 sec)

Delving more into the issue in question, I looked at the complete CREATE TABLE statement, recreating the syntax.

mysql> drop table if exists x;
mysql> create table x ( MinValue DECIMAL(18,5));
Query OK, 0 rows affected (0.00 sec)

No problem there.

mysql> drop table if exists x;
mysql> create table x (Department INT NOT NULL, MinValue DECIMAL(18,5) NULL, MaxValue DECIMAL(18,5) NULL);
ERROR 1064 (42000): You have an error in your SQL syntax; check
  [Read more...]
Do you use PHPMyAdmin?
+5 Vote Up -0Vote Down

If so then were is it installed on your publicly accessible website. If the location is where the documentation states not to put it, or in other popular locations then you can easily become open to an attack. The following are apache logs of a simple hacker test to find a potential security hole on a new IP public address for a client.

My advice is never have PHPMyAdmin accessible by default. You should use HTTP authentication, firewall rules and additional security practices all to protect any level of access to your data.

78.111.81.180 - - [02/Feb/2011:05:29:24 -0500] "GET //phpmyadmin/ HTTP/1.1" 401 290 "-" "Made by ZmEu @ WhiteHat Team - www.whitehat.ro"
78.111.81.180 - - [02/Feb/2011:05:29:24 -0500] "GET //phpMyAdmin/ HTTP/1.1" 401 290 "-" "Made by ZmEu @ WhiteHat Team - www.whitehat.ro"
78.111.81.180 - - [02/Feb/2011:05:29:24 -0500] "GET //pma/ HTTP/1.1" 401
  [Read more...]
Microsoft’s position on MySQL
+5 Vote Up -1Vote Down

While Oracle provides no official information they are planning on improving MySQL and using as a product to compete with Microsoft SQL Server, it is rather obvious from what little information you can glean from public announcements this is a clear business goal.

Microsoft however are publicly seeking a Senior Product Manager, MySQL Compete in the Marketing department. Your goal is nothing technical, it’s all PR to dispel MySQL as a viable product. I quote “you will equip field and partners to win in competitive engagements against MySQL, and you will influence market perception in favor of Microsoft technologies.” Here is the Full job description for those that want an amusing read.

This information came from an Oracle colleague of mine based in Asia.

Welcome new Oracle ACE’s
+7 Vote Up -0Vote Down

I am pleased to announce that the Oracle ACE program has two new MySQL inductees. These people actively contribute to the MySQL community via a varied means in an unbiased and non commercial way.

Giuseppe Maxia (aka DataCharmer) is no stranger to the MySQL community having filled a position with MySQL Inc/Sun Microsystems/Oracle Corporation in the Community team for many years. Giuseppe was an active member in the community before this position, and continues to provide great input with tools such as the MySQL sandbox and many project and code snippets on the MySQL Forge as well as many writings for the Developer zone.

  [Read more...]
Changes in using Profiling in MySQL 5.5
+3 Vote Up -0Vote Down

In the past I’ve used the profiling features (e.g. SHOW PROFILES) in MySQL to help with timing SQL statements, especially those in the

Out of habit I did use this to time all SQL statements however in MySQL 5.5.8 GA I've found this no longer to be representative.

As you can see, the query takes some 50+ms longer with profiling enabled, not to mention they have broken the Source_file column which I've actually used to troll the source code with.

mysql> set profiling=1;

4 rows in set (1.14 sec)
4 rows in set (1.15 sec)
4 rows in set (1.17 sec)

mysql> set profiling=0;

4 rows in set (0.37 sec)
4 rows in set (0.37 sec)
4 rows in set (0.37 sec)

Investigating further showed the cause. There appears to be some new overhead that causes profiling to log excessive amount of information.

mysql> show profile source for

  [Read more...]
MySQL 5.5.8 GA and PHP 5.3.4 don’t get along with libmysql
+0 Vote Up -0Vote Down

Today I discovered that you are unable to compile the current stable PHP version 5.3.4 with yesterday’s MySQL 5.5.8 GA release. I was able to download the current MySQL 5.1.54 and compile without issue.

You can find all the gory details in Bug #58987 however I was able to edit a number of MySQL include file to get a build. Does this mean it’s a MySQL packaging problem or a PHP problem I don’t know, but I would hope that Oracle in the testing phase of a GA release test this against popular programming languages starting with the LAMP stack to ensure compatibility such as what I uncovered.

Five reasons to upgrade to MySQL 5.5
+3 Vote Up -0Vote Down

I have been looking forward to the general availability (GA) release of MySQL 5.5 since is was publically announced in September that we would see this in 2010. While I already have a production client with 5.5.7rc, the badge of general availability is a great way to promote why environments should consider moving to using MySQL 5.5. Here is my quick short list of why I’d promote moving to MySQL 5.5.

1. Improved integration

The first significant improvement is that InnoDB is now again firmly a default included storage engine. The InnoDB plugin 1.1.x is now the builtin version of the engine, not a plugin version. Also the 1.1.x version has continued improvements over the 1.0.x version available as an included but not enabled plugin in current MySQL 5.1.x versions. Removing the complexity for end users over the choice of InnoDB and the necessary

  [Read more...]
Wish list for MySQL thread polling events
+2 Vote Up -1Vote Down

It is great to draw inspiration from other Open Source communities. Brad Fitzpatrick recently wrote about Android Strict Mode. His twitter tag line for this post was “I see you were doing 120 ms in a 16 ms zone” which is all I needed to hear from somebody who also worries unreasonably about responsiveness (Web site quote).

How would I apply this to a MySQL context? This is what happens in Android. “Strict Mode lets you set a policy on a thread declaring what you’re not allowed to do on that thread, and what the penalty is if you violate the policy. Implementation-wise, this policy is simply a thread-local integer bitmask. By default everything is allowed and it

  [Read more...]
Unexpected mysqld crashing in 5.5
+3 Vote Up -2Vote Down

An update of MySQL from 5.0 to 5.5 on CentOS 5.5 64bit has not resulted in a good experience. The mysqld process would then crash every few minutes with the following message.

101120 8:29:27 InnoDB: Operating system error number 22 in a file operation.
InnoDB: Error number 22 means ‘Invalid argument’.
InnoDB: Some operating system error numbers are described at
InnoDB: http://dev.mysql.com/doc/refman/5.1/en/operating-system-error-codes.html
InnoDB: File name /tmpfs/#sql6cf3_5c_0.ibd
InnoDB: File operation call: ‘aio write’.
InnoDB: Cannot continue operation.

The work around was to change the tmpdir=/tmpfs (which was a 16G tmpfs filesystem) to a physical disk.

The referenced URL didn’t provide any more information of help. Unlike Bug #26662 O_DIRECT is not specified as the flush method.

Damm you Peformance Schema
+0 Vote Up -2Vote Down

One significant new feature of MySQL 5.5 is the Performance Schema. I recently performed an upgrade from 5.0 to 5.5, however my check of differences in the MySQL variables via mysqladmin variables failed because we now have a new record long variable name “performance_schema_events_waits_history_long_size”.

The full list of new performance_schema variables are:

> | performance_schema_events_waits_history_long_size | 10000                                                                                                                  |
> | performance_schema_events_waits_history_size      | 10                                                                                                                     |
> | performance_schema_max_cond_classes               | 80
  [Read more...]
The Casual MySQL DBA – Operational Basics
+2 Vote Up -0Vote Down

So your not a MySQL DBA, but you have to perform like one. If you have a production environment that’s running now, what are the first things you do when it’s not running or reported as not running?

  • Are the MySQL processes running? (i.e. mysqld and mysqld_safe)
  • Can you connect locally via cli?
  • What’s in the MySQL error log?
  • What are current MySQL threads doing? Locked? long running? how many? idle sources?
  • Can you connect remotely via cli?
  • Verify free diskspace?
  • Verify system physical resources?
  • If this is a slave, is MySQL replication running? Is it up to date?
  • What is the current MySQL load, e.g. reads/writes/throughput/network/disk etc?
  • What is the current InnoDB state and load? (based on if your using InnoDB)
  • After you do this manually more then

      [Read more...]
    MySQL HandlerSocket under Ubuntu
    +3 Vote Up -0Vote Down

    Starting with the great work of Yoshinori-san Using MySQL as a NoSQL – A story for exceeding 750,000 qps on a commodity server and Golan Zakai who posted Installing Dena’s HandlerSocket NoSQL plugin for MySQL on Centos I configured and tested HandlerSocket under Ubuntu 10.04 64bit.

    NOTE: This machine already compiles MySQL and Drizzle. You should refer to appropriate source compile instructions for necessary dependencies.

    # Get Software
    cd /some/path
    export DIR=`pwd`
    wget http://download.github.com/ahiguti-HandlerSocket-Plugin-for-MySQL-1.0.6-10-gd032ec0.tar.gz
    wget http://mysql.mirror.iweb.ca/Downloads/MySQL-5.1/mysql-5.1.52.tar.gz
    wget
      [Read more...]
    Improving MySQL Insert thoughput
    +1 Vote Up -0Vote Down

    There are three simple practices that can improve general INSERT throughput. Each requires consideration on how the data is collected and what is acceptable data loss in a disaster.

    General inserting of rows can be performed as single INSERT’s for example.

    INSERT INTO table (col1, col2, col3) VALUES (?, ?, ?);
    INSERT INTO table (col1, col2, col3) VALUES (?, ?, ?);
    INSERT INTO table (col1, col2, col3) VALUES (?, ?, ?);
    

    While this works, there are two scalability limitations. First is the network overhead of the back and forth of each SQL statement, the second is the synchronous nature, that is your code can not continue until your INSERT is successfully completed.

    The first improvement is to use MySQL’s multi values capability with INSERT. That is you can insert multiple rows with a single INSERT statement. For example:

      [Read more...]
    OTN MySQL conference slides
    +2 Vote Up -1Vote Down

    2010 has been the first year I have re-presented any of my developed MySQL presentations. Historically I have always created new presentations, however Paul Vallee gave me some valuable advice at UC 2010. In the past two weeks I’ve traveled to seven countries in South America on the OTN LA tour where I have been speaking about and promoting MySQL.

    My three current presentations have been improved and even simplified, more future improvements are planned. There is definitely a benefit in repeating a good presentation multiple times.

    My SQL Idiosyncrasies That Bite OTN View more   [Read more...]
    MySQL Support Options
    +4 Vote Up -7Vote Down

    Oracle has released news about changing policies of MySQL Enterprise Support (http://mysql.com/products/enterprise/features.html) effectively dropping annual support for Basic and Silver. The entry level support is now $3000 per server per year. The MySQL support team now part of Oracle has great resources however Oracle is in the business of making money. When a general company question for OOW is company income, and the first option is

    The success of MySQL as an open source company has lead to other leading providers that now can provide enterprise level support. More importantly many organizations over per-incident support which is more cost effect. News in the past week has included Percona - New Support Option for MySQL, and today

      [Read more...]
    MySQL Best Practices for DBAs and Developers
    +3 Vote Up -2Vote Down

    This is one of the MySQL presentations I’m doing on the OTN LAD Tour in South America, starting today in Lima, Peru.

    MySQL Best Practices for DBAs and Developers

    Learn the right techniques to maximize your investment in MySQL by knowing the best practices for DBAs and Developers. Understand what subtle differences between MySQL and other RDBMS products are essential to understand in order to maximize the benefits and strengths of MySQL. We will be covering areas including the minimum MySQL configuration, ideal SQL, MySQL security and schema optimizations.

    • MySQL Configuration default settings including SQL_MODE
    • Documenting, formatting and future proofing your SQL
    • Developing and reviewing all SQL paths
    • MySQL physical and user security
    • The best schema optimizations
      [Read more...]
    MySQL 5.5 and transaction management
    +1 Vote Up -4Vote Down

    Announced at MySQL Sunday was the Release Candidate edition of MySQL 5.5.6. Also noted by Geert where he points out the default storage engine is now InnoDB.

    However, for those from a background other then MySQL there is still a gotcha.

    mysql> show global variables like 'autocommit';
    +---------------+-------+
    | Variable_name | Value |
    +---------------+-------+
    | autocommit    | ON    |
    +---------------+-------+
    

    Unlike Oracle for example, the default autocommit is on.

    Doing some other boundary conditions, it is no longer possible to disable InnoDB on startup which you would of course expect.

    $ bin/mysqld_safe --skip-innodb &
    $ tail error.log
    
    101003 15:33:32 [Note] Plugin 'InnoDB' is disabled.
    101003 15:33:32 [ERROR] Unknown/unsupported storage engine: InnoDB
    101003
      [Read more...]
    Common MySQL Scalability Mistakes
    +3 Vote Up -3Vote Down

    This week I was one of the presenters at the first Surge Scalability Conference in Baltimore. An event that focused not just on one technology but on what essential tools, technologies and practices system architects need to know about for successfully scaling web applications.

    While MySQL is an important product in many environments, it is only one component for a successful technology stack and for many organizations is one of several products that manage your data.

    My presentation was on the common MySQL scalability mistakes and how to avoid them. This is a problem/solution approach and is a companion talk with

      [Read more...]
    OTN Interview about MySQL
    +3 Vote Up -1Vote Down

    I was interviewed by Justin Kestelyn the OTN Senior Director about MySQL at Oracle Open World this week.

    Some highlights of the questions asked:

    • 0:55 Since the close of acquisition has there been any change in direction?
    • 2:23 How have your clients and customers responded to the acquisition?
    • 3:53 You mentioned that Oracle will bring added advantages and could infuse invocation.
    • 5:15 InnoDB and MySQL are now both owned by Oracle. What do you see as the development advantages?
    • 6:47 What were your thoughts on the first MySQL Sunday?
    • 8:58 Forks?
    • 11:04 Contact Details

    You can view the Video online or play below.

    Successful MySQL Scalability Presentation
    +3 Vote Up -1Vote Down

    Last night I was the invited guest at the SF MySQL Meetup. In my presentation “Successful MySQL Scalability” I talked about a set of principles to ensure appropriate system architecture, data availability and best practices to build an ideal solution for your business. The presentation was also live streamed and is available online.

    Successful MySQL Scalability
    MySQL South America tour
    +4 Vote Up -0Vote Down

    DISCLAIMER: This post contains no technical MySQL content however it is good news for the MySQL Community.

    MySQL content will be included for the first time with the LAOUC (Latin American Oracle Usergroups Council) Oracle tour that is being organized in conjunction with OTN (Oracle Technology Network).

    I have no idea what MySQL user communities are in South America however if you live in any of the following cities, please feel free to contact me. I am happy to have additional discussion regarding MySQL or help in some way if there is interest in any cities.

    This seven country tour includes:

    • Oct 12 – Lima, Peru
    • Oct 14 – Santiago, Chile
    • Oct 16 – Montevideo, Uruguay
    • Oct 18 – São Paulo,
      [Read more...]
    2011 MySQL Conferences
    +8 Vote Up -2Vote Down

    Next year will mark a significant change for the MySQL community. At least three major conferences will have dedicated MySQL content that is great for attendees getting the best information on how to use MySQL from the experts in the field.

    O’Reilly MySQL Conference & Expo

    The 9th Annual MySQL conference will be held at is usual home of recent years. Colin will again be back as committee chair for a 3rd year and this will be my 6th straight MySQL conference.

    Date: April 11 – 14, 2011
    Location: Hyatt Regency, Santa Clara, California
    Website: There is no website at this time
    Call for Papers: There are no details for call for papers
    Program Chairs: Colin Charles from Monty Program AB and Brian



      [Read more...]
    The case against using rpm packaging for MySQL
    +3 Vote Up -7Vote Down

    In some environments using a distro package management system may* provide benefits including handling dependencies and providing a simpler approach when there are no dedicated DBA or SA resources.

    However, the incorrect use can result in pain and in this instance production downtime. Even with dedicated resources at an unnamed premium managed hosting provider, the simple mistake of assumption resulted in over 30 minutes of unplanned downtime during peak time.

    One of the disadvantages of using a system such as rpm is the lack of control in managing the starting and stopping of your MySQL instance, and the second is unanticipated package dependency upgrades.

    So what happened with this client. When attempting to use the MySQL client on the production server, I got the following error.

    $ mysql -uxxx -p
    error while loading shared
      [Read more...]
    Upcoming MySQL Conferences
    +5 Vote Up -1Vote Down

    Unlike previous years when the number of conferences with MySQL content diminishes after the O’Reilly MySQL and OSCON conferences (Open SQL Camp excluded), this year has a lot on offer.

    This month:

    Upcoming next month in September:

    • MySQL Sunday at Oracle Open World on September 18 in San Francisco includes 4 tracks and around 15 quality speakers. (Big numbers of attendees also rumored but yet unconfirmed).
    • The inaugural Surge Scalability conference in Baltimore will include presentations by myself and
      [Read more...]
    Why GRANT ALL is bad
    +15 Vote Up -3Vote Down

    A common observation for many LAMP stack products is the use of poor MySQL security practices. Even for more established products such as Wordpress don’t always assume that the provided documentation does what it best for you. As per my earlier posts where I detailed installation instructions and optimal permissions for both WordPress and Mediawiki, and not just directed readers to online documentation.

    In this post I will detail why GRANT ALL is bad.

    Let’s start with what GRANT ALL [PRIVILEGES] provides. As per the MySQL 5.1 Reference

      [Read more...]
    You have to love the Planet MySQL voting system
    +13 Vote Up -19Vote Down

    Within a few hours my post Installing Mediawiki on Oracle Enterprise Linux LAMP stack got 5 negative votes.

    Wow, I’d be glad if these people could felt so passionately about all the other CRUD on Planet MySQL that has ZERO to do actually do with MySQL.

    Using a LAMP product, and providing instructions for operation can’t be a negative voting offense. So it can only be the words “Oracle Enterprise Linux”.

    For those negative people out there that care enough to physically mark blogs let me share some facts with you. RedHat Enterprise Linux (RHEL) is the most widely used and support platform for production MySQL environments. CentOS and Oracle Enterprise Linux (OEL) provide via the

      [Read more...]
    Previous 30 Newer Entries Showing entries 91 to 120 of 439 Next 30 Older Entries

    Planet MySQL © 1995, 2014, Oracle Corporation and/or its affiliates   Legal Policies | Your Privacy Rights | Terms of Use

    Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.