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
Showing entries 1 to 30 of 26694 Next 30 Older Entries
Joins
+0 Vote Up -0Vote Down

In SQL, join is a means for combining records from two tables into a single set which can be either returned as is or used in another join. In order to perform the operation a join has to define the relationship between records in either table, as well as the way it will evaluate the relationship. The relationship itself is created through a set of conditions that are part of the join and usually are put inside ON clause. The rest is determined through a join type, which can either be an inner join or an outer join.

The SQL clauses that set the respective join type in a query are [INNER] JOIN and {LEFT | RIGHT} [OUTER] JOIN. As you can see the actual keywords INNER and OUTER are optional and can be omitted, however outer joins require specifying the direction – either left or right.

  [Read more...]
MySQL Workbench 5.2.40 GA Released
Employee +3 Vote Up -0Vote Down

The MySQL Developer Tools team is announcing the next maintenance release of it’s flagship product, MySQL Workbench, version 5.2.40. This version contains more than 28 bug fixes applied over version 5.2.39.

MySQL Workbench 5.2 GA

• Data Modeling

• Query

• Administration

Please get your copy from our Download site.

Sources and binary packages are available for several platforms, including Windows, Mac OS X and Linux. http://dev.mysql.com/downloads/workbench/

Workbench Documentation can be found here.

http://dev.mysql.com/doc/workbench/en/index.html

Utilities Documentation can be found here.

  [Read more...]
MySQL Backup & Recovery Essentials
+2 Vote Up -0Vote Down

Download PDF Presentation

A hardware, software or human failure can occur at any time. Are you prepared?
Many organizations take a risk of serious data loss and system downtime with inadequate procedures in place to support a disaster recovery. This presentation covers the essentials of MySQL backup and recovery options, identifying the necessary tools for an effective strategy to support data resilience and business continuity for your organization. MySQL has no one single unbreakable backup solution, so it is important to understand the impact of MySQL replication, storage engines, configuration options for durability, hardware


  [Read more...]
Benchmarking single-row insert performance on Amazon EC2
+0 Vote Up -0Vote Down

I have been working for a customer benchmarking insert performance on Amazon EC2, and I have some interesting results that I wanted to share. I used a nice and effective tool iiBench which has been developed by Tokutek. Though the “1 billion row insert challenge” for which this tool was originally built is long over, but still the tool serves well for benchmark purposes.

OK, let’s start off with the configuration details.

Configuration

First of all let me describe the EC2 instance type that I used.

EC2 Configuration

I chose m2.4xlarge instance as that’s the instance type with highest memory available, and memory is what really really matters.

High-Memory Quadruple Extra Large
  [Read more...]
Register to our live webinar and learn how to protect your sensitive information using Real-Time Dynamic Data Masking
+0 Vote Up -0Vote Down

Dynamic Data Masking is an emerging technology that provides real-time data masking in changing environments, typically in production databases.

GreenSQL Dynamic Data Masking enables you to mask or randomize any sensitive information stored on MS SQL Server, MySQL and PostgreSQL databases.

Register Now!

When? Wednesday, May 23, 2012 (9:00 am PDT; 12:00 pm EST; 16:00 pm GMT; 19:00 pm GMT+3:00)

In this webinar, David Maman, GreenSQL Founder and CTO, will explain:

  • What Real-Time Dynamic Data Masking is?
  • How to
  [Read more...]
SwRI Chooses TokuDB to Tackle Machine Data for an 800M+ Record Database
+0 Vote Up -1Vote Down

Tackling machine data on the ground to ensure successful operations for NASA in space

Issues addressed:

  • Scaling MySQL to multi-terabytes
  • Insertion rates as InnoDB hit a performance wall
  • Schema flexibility to handle an evolving data model

The Company:  Southwest Research Institute (SwRI) is an independent, nonprofit applied research and development organization. The staff of more than 3,000 specializes in the creation and transfer of technology in engineering and the physical sciences. Currently, SwRI is part of an international team working on the NASA

  [Read more...]
MySQL Cluster 7.2 -- Unlimited Possibilities
Employee +7 Vote Up -0Vote Down
We've recently seen some great announcements of MySQL Cluster delivering amazing results for both selects and updates. The posts (see related articles below) are full of juicy technical details and proofs, but today I'd like to change the perspective a bit. Let's compare those figures with real-world data and imagine what could be done. Please note that I'm not using any scientific method here, just dreaming about the unlimited opportunities offered by MySQL Cluster today.

MySQL Cluster 7.2.7 -- 1B+ Writes per Minute Cluster can deliver 

  [Read more...]
Percona Server 5.5.23-25.3 released!
+0 Vote Up -0Vote Down

Percona is glad to announce the release of Percona Server 5.5.23-25.3 on May 16, 2012 (Downloads are available here and from the Percona Software Repositories).

Based on MySQL 5.5.23, including all the bug fixes in it, Percona Server 5.5.23-25.3 is now the current stable release in the 5.5 series. All of Percona‘s software is open-source and free, all the details of the release can be found in the 5.5.23-25.3 milestone at Launchpad.

Bugs Fixed:

  • Percona Server would crash on a DDL statement if an
  [Read more...]
MySQL Cluster 7.2.7 achieves 1BN update transactions per minute
Employee +7 Vote Up -0Vote Down
In MySQL Cluster there is a limiting factor in the receive threads that limits our update performance to about 0.5M update transactions per node group per second (usually 2 nodes per node group). In MySQL Cluster 7.2.7 we have removed most of this bottleneck and can now achieve 3x as many update transactions. We're reaching about 1.5M updates per node group per second. On a 30-node configuration we achieved 19.5M update transactions per second which corresponds to 1.17BN updates per minute. This means we achieve almost linear increase of update performance all the way to 30 data nodes.

The benchmarks were executed using the benchmark scripts dbt2-0.37.50 available at dev.mysql.com, the benchmark program is the flexAsynch program mentioned in some of my earlier blogs. We used 8 LQH threads per data node.
Stalls during DDL
+3 Vote Up -0Vote Down

InnoDB locks the buffer pool mutex and scans the LRU to remove pages when DROP TABLE is done for a table that uses innodb_file_per_table. If you read the source you might notice that it scans the LRU twice, once in buf_LRU_drop_page_hash_for_tablespace and then again in buf_LRU_invalidate_tablespace. Locking the buffer pool mutex and scanning the LRU isn't cheap when the buffer pool is large. It takes ~1 second on servers I use and that is too much as nothing gets done during that time by other threads.

 

I changed InnoDB to remove one of the LRU scans and reduced the stall in half. However that was not good enough. The next step was to avoid any LRU scan during DROP TABLE. Several bugs have been filed for this but the primary ones are

  [Read more...]
Announcing the Explain Analyzer
+3 Vote Up -0Vote Down

The explain statement can be an important tool for understanding how a query is being executed and what you can do to make it run better.  Although the output of EXPLAIN is relatively straightforward it can be confusing to inexperienced users or can be mangled by terminal wrapping.

To help with these problems as well as provide a pastebin for MariaDB developers to share explains during development we created The MariaDB/MySQL Explain Analyzer. This tool:

  • Helps unmangle explains (both vertical and tabular format)
  • Displays explains in an easy-to-read format.
  • Highlights and provides explanations for some terms.
  • Links to KB articles for different optimization techniques.
  • (Optionally) Allows you to save the explain for sharing.
  • This is the

      [Read more...]
    Some throttling for PECL/mysqlnd_ms 1.4
    Employee +3 Vote Up -0Vote Down

    Users of MySQL Replication sometimes throttle client requests to give slaves time to catch up to the master. PECL/mysqlnd_ms 1.4, the current development version, features some throttling through the quality-of-service filter and global transaction identifier (GTID). Both the plugins client-side GTID emulation and the MySQL 5.6 built-in GTID feature can be used to slow down PHP MySQL requests, if wanted.

    How its done

    The replication plugin has a neat feature called quality-of-service filter. If, for example, the quality of service you need from a MySQL Replication cluster is "read your writes",

      [Read more...]
    Finally we have a MySQL User Group in Sweden!
    Employee +6 Vote Up -0Vote Down
    I have always found it strange that we do not have a MySQL user group in Sweden - this is the country where the MySQL saga started.
    Therefore I am delighted to announce that since today we have a user group in Sweden and I hope it will be a active one!
    If you want to join our user group in Sweden, join the group here!
    (My)SQL mistakes. Do you use GROUP BY correctly?
    +2 Vote Up -0Vote Down

    Often I see a SQL problem solved incorrectly and I do not mean inefficiently. Simply incorrectly. In many cases the developer remains unaware that they aren’t getting the results they were expecting or even if a result is correct, it is only by chance, for example because the database engine was smart enough to figure out some non-sense in a query. In a few posts I will try to disclose some of the more common problems.

    Aggregate with GROUP BY

    Unlike many other database systems, MySQL actually permits that an aggregate query returns columns not used in the aggregation (i.e. not listed in GROUP BY clause). It could be considered as flexibility, but in practice this can easily lead to mistakes if a person that designs queries does not understand how they will be executed. For example, what values an aggregate query returns for a

      [Read more...]
    Scale differences between OLTP and Analytics
    +0 Vote Up -0Vote Down

    In my previous post,http://database-scalability.blogspot.com/2012/05/oltp-vs-analytics.html, I reviewed the differences between OLTP and Analytics databases.

    Scale challenges are different between those 2 worlds of databases.



    Scale challenges in the Analytics world are with the growing amounts of data. Most solutions have been leveraging those 3 main aspects: Columnar storage, RAM and parallelism.
    Columnar storage makes scans and data filtering more precise and focused. After that – it all goes down to







      [Read more...]
    Why do threads sometimes stay in ‘killed’ state in MySQL?
    +4 Vote Up -0Vote Down

    Have you ever tried to kill a query, but rather than just go away, it remained among the running ones for an extended period of time? Or perhaps you have noticed some threads makred with killed showing up from time to time and not actually dying. What are these zombies? Why does MySQL sometimes seem to fail to terminate queries quickly? Is there any way to force the kill command to actually work instantaneously? This article sheds some light on it.

    Threads and connections

    MySQL uses a separate thread for each client connection. A query sent to MySQL is handled by a thread that was previously associated with the connection over which the query arrived. Anyone with sufficient privileges can see the list of currently active threads, along with some additional details, by running SHOW PROCESSLIST command, which returns a

      [Read more...]
    Under the Hood with Connector/J's "rewriteBatchStatements=true"
    Employee +2 Vote Up -0Vote Down

    My old post (http://www.jroller.com/mmatthews/entry/speeding_up_batch_inserts_for) on the performance gains from batch rewritten statements gets regurgitated in various forums, and conference sessions, and often people miss the nuances of it.

    Under the hood, what is happening with this feature is the following:

    (1) The driver attempts to detect that the SQL being prepared is an INSERT. We (on purpose) don‘t ship a full-fledged parser in the driver, so this works 95% of the time. For the other 5%, you‘re out of luck unless you can simplify your query text.
    (2) If the statement is an INSERT, the driver attempts to determine if it can be rewritten as a multi-value INSERT. From the code itself, the conditions are:

    // Needs to be INSERT,


      [Read more...]
    Speeding up EC2 work by using AWS tools and scripts to bypass the AWS management console
    +0 Vote Up -0Vote Down
    Believe me managing EC2 instances is not as simple or magical as marketers would like for you to believe. The cloud gets complicated when it gets bigger. EC2 really only enables a person to ignore power, network layout (which is bad), and getting more servers that end up costing more then actual servers fast.

    Things that EC2 is missing to make life easier for the developer:

    Ability to update all servers with packages and code. Natively they do not support the ability to push files or install new software packages to server groups. Thus install cluster-it and puppet and write your own deploy program.

    Server names and the EC2 AWS management console do not match. Everything is referenced by instance ids. The interface does not allow one to launch many instances





      [Read more...]
    WordPress on S3: the beauty of simplicity
    +0 Vote Up -1Vote Down

    My first computer program was written almost quarter a century ago on a BK-0010 computer.  It was very simple: the program asked the user to enter their name and then greeted the user using the entered name, like “Hello, Artem!”.  I was fascinated.  A couple of lines written in Vilnius BASIC transformed a piece of metal and silicon into a considerate thing that cared about a person’s name enough to remember it :-).  Of course, the first experience doesn’t represent the day-to-day routine of software development, but the moments when I see a couple of lines making an amazing transformation still enchant me, and remind me why I’ve been writing code all this time.

    I’ve just experienced this very same first-time feeling as we’ve released

      [Read more...]
    Announcement of Percona XtraDB Cluster 5.5.23
    +4 Vote Up -0Vote Down

    Our previous GA release of Percona XtraDB Cluster caused a lot of interest and feedback. I am happy to announce next version Percona XtraDB Cluster 5.5.23, which comes with bug fixes and improvements.

    List of changes:

    • Fixes merged from upstream (Codership-mysql)
    • Support for MyISAM, now changes to MyISAM tables are replicated to other nodes
    • Improvements to XtraBackup SST methods, better error handling
    • New SST wsrep_sst_method=skip, useful when you start all nodes from the same sources (i.e. backup)
    • Ability to pass list of IP addresses for a new node, it will connect to the first available

    Binaries are available from downloads area or from

      [Read more...]
    Tokutek Welcomes Gerry Narvaja!
    +7 Vote Up -0Vote Down

    We are excited to have Gerry Narvaja start today at Tokutek! Gerry has spent more than 25 years in the software industry, most of them working with databases for different kinds of applications, from embedded to large-scale web products. Gerry worked first at MySQL, and then Sun Microsystems supporting the Sales teams. In 2008 he transitioned into being a Senior MySQL DBA. Gerry graduated as an Electronic Engineer from I.T.B.A (Instituto Tecnológico de Buenos Aires) and has an M.B.A. from Universidad del Salvador in collaboration with S.U.N.Y.A (State University of NY at Albany).

    Gerry enjoys helping users to solve complex database production issues. For almost a year he has been co-hosting the popular MySQL Community podcast, OurSQL, which was given the

      [Read more...]
    Hopper for InterBase, 1.0 released
    +0 Vote Up -0Vote Down
    Hopper for InterBase, 1.0 released
    [2012-05-09]

    Upscene Productions is proud to announce the first release of our new product "Hopper", a Stored Code Debugger for InterBase.

    Thanks to the feedback of people who downloaded the betas, we were able to improve Hopper.

    Hopper is currently available for InterBase and Firebird, the MySQL Edition will follow shortly.

    More information available at the Hopper page, download your copy today via our downloads page, pricing information is available.
    Challenges in reaching 1BN reads and updates per minute for MySQL Cluster 7.2
    Employee +4 Vote Up -0Vote Down
    In an earlier blog we've described the general high-level idea of how to achieve 10X better performance for MySQL Cluster 7.2 compared to MySQL Cluster 7.1.

    Naturally the development is never as straightforward as the high-level view looks like. In this blog I'll mention a few of the most important roadblocks on the path to improved performance of MySQL Cluster 7.2 that we met and resolved.

    Initially when we increased the number of LQH threads from 4 to 16 we only saw scaling to 8 LQH threads and we saw no scaling in going to 16 LQH threads. This was very puzzling since we don't really have any mutexes that should be an issue. However we looked into the mutexes that we had and managed to decrease the number of conflicts on the send mutexes by a factor of 15. This did however not improve performance at all.

    Next we noted using oprofile





      [Read more...]
    Impact of foreign keys absence on replicating slaves
    +2 Vote Up -0Vote Down

    In this post I describe what happens when a slave's Foreign Key setup is different from that of the master. I'm in particular interested in a setup where the slave has a subset of the master's foreign keys, or no foreign keys at all. I wish to observe whether integrity holds.

    Making the changes

    Which foreign keys do we have and how do we drop them? If you want to do this by hand, well, good luck! Fortunately, common_schema provides with quite a few handy views and routines to assist us. Consider viewing the existing foreign keys on sakila:

    master> SELECT create_statement FROM common_schema.sql_foreign_keys WHERE TABLE_SCHEMA='sakila';
      [Read more...]
    How to run a flawless technical demo
    +6 Vote Up -0Vote Down

    Why demos?

    For as long as I can remember in my public speaking activities, I have always planned my presentations with some sort of live demo in it. I am always surprised when a conference venue asks me to provide my slides in advance, to be loaded in an anonymous computer with no chance of demos. I always turn down such offers, as I always want to provide a demo.

    There have been times when technical or time constraints prevented me from demoing something, and in these cases I felt that the presentation was lacking a vital part. But I always try. I have even given demos during lightning talks, and those were the ones that made me feel really good.

    I have given hundreds of presentations, and hundreds of demos, and as in every human activity, I have made plenty of mistakes. I believe I have learned some valuable lesson from my mistakes, and this

      [Read more...]
    SQL Injection at Reddit
    +2 Vote Up -0Vote Down

    Reddit takes SQL injection very seriously.

    How seriously?

    Check their headers:

    scabral-07890:~ scabral$ curl --head www.reddit.com
    HTTP/1.1 200 OK
    Content-Type: text/html; charset=UTF-8
    Set-Cookie: reddit_first=%7B%22organic_pos%22%3A%201%2C%20%22firsttime%22%3A%20%22first%22%7D; Domain=reddit.com; expires=Thu, 31 Dec 2037 23:59:59 GMT; Path=/
    Server: '; DROP TABLE servertypes; --
    Date: Sat, 12 May 2012 13:54:20 GMT
    Connection: keep-alive

    scabral-07890:~ scabral$

    A colleague at PICC showed me this when he learned of my talk on MySQL security!

    OurSQL Episode 90: Handle With Care
    +1 Vote Up -0Vote Down

    This week we present how to use pt-archiver and pt-find, two Percona Toolkit tools. We focus on the common usage of the tools and the gotchas we ran into using them.

    News/Events/Feedback
    Conferences:
    MySQL Innovation Day Schedule Tuesday June 5th, Redwood Shores, CA. Register here (free). Content will be available via live stream, so save the date!

    read more

    Portuguese Planet
    Employee +3 Vote Up -0Vote Down
    A big welcome to the Portuguese MySQL Community.  The MySQL team has recognized your support of MySQL, so we hope you can take advantage of the new  Portuguese MySQL PlanetWagner has started us off with his blogs so please feel free to submit your Portuguese Feeds.
    Workaround for ‘Authentication failed’ Issue When Connecting through HTTP Tunnel
    +0 Vote Up -0Vote Down

    Some of our users have encountered problems with establishing a connection over the tunnel.php script despite the fact that the tunnel.php script is installed correctly. The following error message occurs:

    ‘Can’t connect to MySQL server on ‘your.sitename.com’ (10061): Authentication failed.’

    The problem appears to be with cached proxy servers and will be fixed in one of the next builds of our product.

    Currently it can be fixed by replacing the dbforgemysql.exe.config file, that can be found in dbForge Studio for MySQL installation folder, with the attached one.

    History of MySQL Cluster architecture development
    Employee +5 Vote Up -0Vote Down
    With the release of MySQL Cluster 7.2.5 we've released a version of MySQL Cluster where each data node is capable of using up to 51 threads in total. This is a remarkable feat for a product that only a few years ago was purely single-threaded. This article tries to explain what it is in the MySQL Cluster architecture that makes it so scalable to new HW demands.

    The MySQL Cluster architecture is based on a design that is used in the world's most sold telecom switch, the AXE switch. This switch is used in all mobile networks delivered by Ericsson. MySQL Cluster also originates from Ericsson. The architecture of the AXE switch was developed in reaction to an older switch that had quality issues since there were too many global variables and too little modularity. The architecture was inspired by how HW was designed. In HW design each module can only

      [Read more...]
    Showing entries 1 to 30 of 26694 Next 30 Older Entries

    Planet MySQL © 1995-2008 MySQL AB, 2008-2010 Sun Microsystems, Inc.,
    2012, Oracle Corporation and/or its affiliates.
    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.