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 838 Next 30 Older Entries

Displaying posts with tag: Uncategorized (reset)

Temporary Tables and Replication
+2 Vote Up -0Vote Down

I recently wrote about non-deterministic queries in the replication stream. That’s resolved by using either MIXED or ROW based replication rather than STATEMENT based.

Another thing that’s not fully handled by STATEMENT based replication is temporary tables. Imagine the following:

  • Master: CREATE TEMPORARY TABLE rpltmpbreak (i INT);
  • Wait for slave to replicate this statement, then stop and start mysqld (not just STOP/START SLAVE)
  • Master: INSERT INTO rpltmpbreak VALUES (1);
  • If for any reason a slave server shuts down and restarts after the temp table creation, replication will break because the temporary table will no longer exist on the restarted slave server. It’s obvious when you think about it, but

      [Read more...]
    Non-Deterministic Query in Replication Stream
    +0 Vote Up -0Vote Down

    You might find a warning like the below in your error log:

    130522 17:54:18 [Warning] Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statements writing to a table with an auto-increment column after selecting from another table are unsafe because the order in which rows are retrieved determines what (if any) rows will be written. This order cannot be predicted and may differ on master and the slave.
    Statement: INSERT INTO tbl2 SELECT * FROM tbl1 WHERE col IN (417,523)

    What do MariaDB and MySQL mean with this warning? The server can’t guarantee that this exact query, with STATEMENT based replication, will always yield identical results on the slave.

    Does that mean that you have to use ROW based (or MIXED) replication? Possibly, but not

      [Read more...]
    MyISAM’s "table lock" problem, and how InnoDB solves it
    +0 Vote Up -0Vote Down

    Most serious users of MySQL have moved their tables to InnoDB years ago.  For those who haven’t, let’s discuss why InnoDB is a more scalable solution than MyISAM.

    MyISAM was designed to be very fast for read queries.  It does not handle higher loads of writes very well.  It also suffers a more serious flaw: it isn’t crash-safe.  In other words, you better have frequent backups.

    MyISAM tables have a read queue and a write queue.  Queries are placed into one of those two queues to be processed.  The write queue has higher priority than the read queue, but the table can only process one write query at a time.  Multiple read queries can occur at once, so the read queue will often be empty.

      [Read more...]
    LEVENSHTEIN MySQL stored function
    +0 Vote Up -0Vote Down

    At Open Query we steer clear of code development for clients. We sometimes advise on code, but as a company we don’t want to be in the programmer role. Naturally we do write scripts and other necessities to do our job.

    Assisting with an Open Source project, I encountered three old UDFs. User Defined Functions are native functions that are compiled and then loaded by the server similar to a plugin. As with plugins, compiling can be a pest as it requires some of the server MySQL header files and matching build switches to the server it’s going to be loaded in. Consequentially, binaries cannot be considered safely portable and that means that you don’t really want to have a project rely on UDFs as it can hinder adoption quite severely.

    Since MySQL 5.0 we can

      [Read more...]
    MySQL Community Team at PHPTek, Drupalcon, Texas Linux Fest, SELF, Redhat Summit, and Lonestar PHP
    Employee +2 Vote Up -0Vote Down

    This is the start of the heavy travel season for the MySQL Community Team. So if you are attending PHPTek, Drupalcon, Texas LinuxFest, SELF, Redhat Summit, or Lonestar PHP please make sure to say ‘hi’ while you are in Chicago, Portland, Austin, Charlotte, Boston, or Addison. This group of trips is starting with a swag bag full of MySQL stickers, thumb drives, and ‘boogie bots’

    MySQL Boogie Bots


    • PHPTek – Ten Things to Make Your MySQL Servers Faster and Happier — May 16th, 2:45p – 3:45p
    • Texas Linuxfest – The Proper Care and Feeding of a MySQL Database for Linux Administrators — May 31st, 11:25a – 12:20p
    • SELF – Two full
      [Read more...]
    Keeping your Drupal from Drooping — part 1
    Employee +2 Vote Up -0Vote Down

    This is the first screen after a sucessful Drupal 7.22 install.

    Drupal is a content management system that runs at least 2.1% of all websites(1). It is easy to use, extensable with over 20,000 add-ons, and runs beautifully with a LAMP stack.

    At the heart of most Drupal sites is a MySQL database with, as of Version 7.22, 76 tables. Recently I was asked what needs to be done to a ‘generic’ Drupal to get it running on MySQL 5.6. It is a very easy update that provides better performance, security, and allows access to the newest MySQL 5.6 updates.

    For this example, the generic box is a two CPU Dell x86_64 box running Centos 6.4. This is fairly typical of what a

      [Read more...]
    Slides from Percona Live talks: optimizer tutorial and Cassandra Storage Engine
    +1 Vote Up -0Vote Down

    I’ve put online the slides for the two talks that I сo-presented at the Percona Live conference:

    The tutorial tries to cover most areas of the optimizer, with focus

      [Read more...]
    Fedora 19 – MariaDB Test Day 2013-04-30
    +1 Vote Up -1Vote Down

    From https://fedoraproject.org/wiki/Test_Day:2013-04-30_MariaDB, this installment of Fedora’s Test Day focuses on the replacement of MySQL with MariaDB. If you’re a Fedora (or RHEL or CentOS user), do take a peek at the page and see if you can pitch in – it might be a little bit of work for you, but with great benefits in terms of getting the MariaDB performance and features, and specifically on the day the Fedora crowd have extra people on the case to track and address issues you might find, so it’s an ideal opportunity to upgrade on a development or test-prod environment!

    MySQL Cluster gets a Community Award
    Employee +6 Vote Up -1Vote Down

    It was with pride I entered the stage at the Percona MySQL Conference yesterday to receive the “Storage Engine of the Year 2013 award” for ndb (the MySQL Cluster storage engine).  Thank you very much, on behalf of myself and the MySQL Cluster engineering team. The team has done a great work over the past 10 years to harden the product, and add new features. MySQL Cluster milestone 7.3.2 was just released. It will be my pleasure to present the “trophy” some members of the team when I get back to Stockholm. Please see a previous post for how the MySQL Cluster journey began for myself and the ndb team 10 years ago.

    The milestone release model revisited
    Employee +10 Vote Up -1Vote Down

    In my Percona live keynote “Driving MySQL Innovation” on Tuesday I talked about the quality problems we had with 5.0 and 5.1 and the switch to the new Milestone Release model. I explained how this new development process allowed us to deliver both 5.5 and 5.6, on time, including innovative features, and with very high quality.

    The description of the Milestone Release model has been available here, for almost two years. So, this is not new but it has now been in operation for 3.5 years and it can be worth looking back and summarize.

    Our first MySQL Server Milestone 5.4.3 was released on October 9th, 2009 and was then followed up by 5.5.2 on Feb. 26, 2010. Initially there was a time of trying out and fine

      [Read more...]
    Query in a loop?
    +0 Vote Up -0Vote Down

    I ran across this gem recently on StackOverflow:

    $queryxyzzy12=("SELECT * FROM visitorcookiesbrowsing ORDER by id ASC");
    $resultxyzzy23=mysql_query($queryxyzzy12) or die(mysql_error());

    while($ckxf = mysql_fetch_assoc($resultxyzzy23)){
    $querycrtx=("SELECT * FROM cart WHERE userkey='$ckxf[usercookie]' ORDER by datebegan DESC");
    $resultcrtx=mysql_query($querycrtx) or die(mysql_error());
    ------ snip ------

    Besides the interesting variable names used, it’s also doing a query inside a loop, which is very inefficient. This would be better written as a single JOIN query:

    visitorcookiesbrowsing v
    LEFT JOIN cart c ON c.userkey=v.usercookie
    v.id ASC,
    c.datebegan DESC

    The details of JOIN vs. LEFT JOIN depend on the actual

      [Read more...]
    Galera pre-deployment check
    +0 Vote Up -0Vote Down

    One of the first things we do when preparing a client’s infrastructure for Galera deployment is see whether their schema is suitable.

    • Avoiding quirks and edge cases, we can say that Galera simply requires all tables to be InnoDB and also have a PRIMARY KEY (obviously having a PK in InnoDB is important anyway, for InnoDB-internal reasons).
    • We want to know about FULLTEXT indexes. With recent InnoDB versions also supporting FULLTEXT we need to check not just whether a table has such an index, but actually which engine it is.
    • Spatial indexes. While both InnoDB and MyISAM can deal with spatial datatypes (POINT, GEOMETRY, etc), only MyISAM has the spatial indexes.

    Naturally, checking a schema in the server is more effective than going through other sources and possibly missing bits. On the downside, the only viable way to get this info out of MariaDB

      [Read more...]
    Speaking at the OpenWest Conference
    +0 Vote Up -0Vote Down

    I’m presenting two talks at the OpenWest Conference next month.  The first talk, on May 2nd, will be about PHP security.  The second talk, given on May 3rd, will be about database optimization, geared towards web developers.  I’ll discuss some of the same things that I have discussed on this blog.

    We’re going to have some great speakers at that conference.  On the PHP front, we’ll have Rasmus Lerdorf giving a keynote as well as another presentation.  From the MySQL community, Mark Callaghan from Facebook will be giving another keynote, and we’ll also have Colin Charles from MariaDB.

    It should be an exciting conference!

    Percona Server to ship with jemalloc
    +0 Vote Up -0Vote Down

    Joseph Scott pointed me to a little tidbit hidden in the latest Percona Server release notes: “Percona Server for MySQL will now be shipped with the libjemalloc library.”  Percona published the results of some testing of various malloc libraries on their MySQL High Performance Blog last year, and it looks like this will have a very positive impact on performance.

    I’m currently using MariaDB, so I’m hoping they pick up this change as well.

    How to identify an unnecessary index
    +0 Vote Up -0Vote Down

    Let’s look at the index from the wp_posts table in a standard WordPress installation.

    SHOW KEYS FROM wp_posts;
    | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
    | wp_posts | 0 | PRIMARY | 1 | ID | A | 1772 | NULL | NULL | | BTREE | | |
    | wp_posts | 1 | post_name | 1 |

      [Read more...]
    Big Data for Genomic Sequencing. Interview with Thibault de Malliard.
    +0 Vote Up -0Vote Down
    “Working with empirical genomic data and modern computational models, the laboratory addresses questions relevant to how genetics and the environment influence the frequency and severity of diseases in human populations” –Thibault de Malliard. Big Data for Genomic Sequencing. On this subject, I have interviewed Thibault de Malliard, researcher at the University of Montreal’s Philip Awadalla [...]
    A Visual Guide to the MySQL Performance Schema
    Employee +1 Vote Up -0Vote Down

    If you haven’t explored the MySQL Performance Schema yet, this is a good place to start.  This is Performance Schema 101, a basic introduction to the MySQL 5.6 performance_schema, which records runtime statistics from the MySQL database. The performance_schema is intended to provide access to useful information about server execution while having minimal impact on server performance.  Performance_schema is the name of both the storage engine and the database itself, and it was initially implemented  in MySQL 5.5. In MySQL 5.6 the engineers added quite a bit of new instrumentation.

    The performance_schema database uses views or temporary tables that actually use little to no persistent disk storage .Memory allocation is all done at server startup, so there is no ongoing memory reallocation or

      [Read more...]
    Interesting MySQL optimizer case
    +0 Vote Up -0Vote Down

    Jaime at the MySQL Performance Blog had an interesting post regarding some unexpected behavior from the MySQL optimizer.  Although this particular case probably doesn’t affect most people, it does give some insight into how the optimizer works, and how subtle changes to a query can change performance.

    Asynchronous MySQL queries in PHP
    +0 Vote Up -0Vote Down

    I was made aware today by a post on the MySQL Performance Blog that the mysqlnd driver for PHP has support for asynchronous PHP queries.  At the Midwest PHP Conference, I gave a talk on database optimization for web developers, and in a follow-up discussion with an attendee, I apparently misinformed him.

    Midwest PHP Presentation Notes
    +0 Vote Up -0Vote Down

    Thanks for attending my presentation at the Midwest PHP conference!

    Slides as PDF
    Leave feedback on joind.in

    Useful links:

      [Read more...]
    FOSDEM 2013 Slides are available on [Plus]
    +0 Vote Up -0Vote Down

    I was at FOSDEM for the first time this year and it was very exciting.
    I was very glad to see Percona, MariaDB and Oracle together.
    If you’ve never been at FOSDEM, I recommend you to join us next year, this is truly a magical event!

    You can find some of the slides on [Plus], enjoy! :

    If you would like to add your slides, post a comment with the link.
    And remember to visit this page for more slides about MySQL : http://www.mysqlplus.net/mysql-docs/

      [Read more...]
    Changing the Size of the InnoDB Log Files In MySQL 5.6
    Employee_Team +6 Vote Up -0Vote Down

    In MySQL 5.5 and earlier, the steps to resize the InnoDB log files were a bit involved and for example included manually moving the log files out of the way as InnoDB would only create new files, if none existed.

    In MySQL 5.6 a not so much talked about feature is the support to resize the log files in a way much more similar to changing other settings in MySQL. Now you simply update your MySQL configuration file and restart MySQL.

    Let us look at an example. In MySQL 5.5 and earlier the total size of the InnoDB log files has to be less than 4G in total, so one way of staying within this limit is to have two files each 2047M large:

    innodb $ ls -1s ib_logfile*
    2096132 ib_logfile0
    2096144 ib_logfile1

    Now update the configuration file to take advantage of the fact that MySQL 5.6 allows much larger InnoDB log files; the actual limit

      [Read more...]
    MySQL Replication – Creating a New Master/Slave Topology with or without Virtual Machines
    +4 Vote Up -0Vote Down

    In my last few posts, I wrote about “How to install MySQL replication using GTID’s” (Part One, Part Two). In this post, I will show you how to install MySQL 5.6 and set up replication between two MySQL servers the “old fashioned way” using the binary log and binary log position.

    I am going to create some virtual machines instead of using individual servers. But, you can

      [Read more...]
    MariaDB 10.0: Named dynamic columns, now with documentation
    +3 Vote Up -0Vote Down

    During the last year, I was asked a number of times when/if MariaDB will support identifying dynamic columns by names instead of numbers. I am happy to say that named Dynamic Columns feature is in MariaDB 10.0.1 release. Now, after some literary effort, here is updated documentation: Dynamic Columns in MariaDB.

    MySQL-State of the Union. Interview with Tomas Ulin.
    +8 Vote Up -0Vote Down
    “With MySQL 5.6, developers can now commingle the “best of both worlds” with fast key-value look up operations and complex SQL queries to meet user and application specific requirements” –Tomas Ulin. On February 5, 2013, Oracle announced the general availability of MySQL 5.6. I have interviewed Tomas Ulin, Vice President for the MySQL Engineering team [...]
    MySQL 5.6 Windows MSI Installer
    Employee +2 Vote Up -0Vote Down

    First conference of the year is always a bit of a thrash for me. This time I have and RMOUG Training Days back to back. So I went to upgrade MySQL on my presentation laptop and got a pleasant surprise.

    The MySQL 5.6 upgrade included not only the server but connectors and workbench

    I had planned to install 5.6, the connectors, the docs, and Workbench separately as I packed. Low and behold. the upgrade did all the work for me. Very cool!

    Except the packing. That I still got to do! See you in Miami and Denver.

      [Read more...]
    Using MySQL Utilities Workbench Script mysqldbcompare To Compare Two Databases In Replication
    +4 Vote Up -0Vote Down

    In my last two posts, I wrote about setting up replication with MySQL 5.6 using Global Transaction Identifiers. Even when I set up replication “the old-fashioned way“, one thought always enters my mind – did all of the data copy over to the slave? And, even after the master/slave has been running for a while, I am always wondering if the data in the slave matches the master. Or did the change that I made to that table make it over to the slave? It is probably more of a case of paranoia on my part, as

      [Read more...]
    No, Sheeri, MySQL 5.6 does not optimize subqueries away
    +21 Vote Up -10Vote Down

    Sheeri wrote a blog post that claims that “IN Subqueries in MySQL 5.6 Are Optimized Away” and uses that as a basis to conclude that subquery optimizations in MySQL 5.6 are superior to MariaDB’s.
    The claim is incorrect (and so is the conclusion). As a person who has coded both of the mentioned FirstMatch and semi-join materialization, I think I need to write about this.

    Sheeri wrote:

  • “So MariaDB recognizes the subquery and optimizes it. But it is still optimized as a subquery”
  • “In MySQL 5.6, the subquery is actually optimized away”
  • The first statement is somewhat true. The second one is not. I’ll

      [Read more...]
    The Data Day, Two days: January 25/28 2013
    +0 Vote Up -0Vote Down

    Informatica’s revenue is in. Hortonworks joins OpenStack. And more.

    For 451 clients: Jitterbit gives integration stack makeover, continues to mine salesforce.com bit.ly/VJrwPE By Krishna Roy

    — Matt Aslett (@maslett) January 28, 2013

    For 451 clients: Enterprise cloud provider Skytap offers new service based on Cloudera Hadoop bit.ly/UpnXij By @rachelchalmers

    — Matt Aslett (@maslett) January 25, 2013

    For 451 clients: Alteryx plots course for 2013 as it seeks to bring business users into

      [Read more...]
    MySQL Replication with Global Transaction Identifiers – Step-by-Step Install and Addition of Slaves – Part Two
    +4 Vote Up -0Vote Down

    This post is part two of MySQL Replication with Global Transaction Identifiers – Step-by-Step Install and Addition of Slaves. In this post, I will be showing you how to use the MySQL Utility Script mysqlreplicate to create a new replication slave off a master database.

    This is also the fourth in a series that I will be doing on MySQL

      [Read more...]
    Previous 30 Newer Entries Showing entries 91 to 120 of 838 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.