Tag Archives: MySQL

Roll your own MAMP development environment

Pre-packaged MAMP, LAMP, and WAMP stacks have been common on developer’s computers for years. Such packages are convenient because they provide a single-step install process, with all components in the server stack preconfigured to work together, and off you go.

Except when they don’t.

I’ve learned from experience that these packages have ways of making you pay for the convenience you enjoyed up front. If you have ever needed to:

  • Install a PHP extension that wasn’t already provided in your stack
  • Run a specific version of PHP or MySQL
  • Install PEAR packages
  • Install SSL certificates
  • Run command-line PHP scripts

…you may have encountered some ugly, time-wasting surprises along the way.

It pays to know your environment inside and out. Today, it is quite easy to roll your own Apache-MySQL-PHP stack on Windows, Linux, or even Mac OS X. Continue reading

Writing MySQL schema migrations: best practices

Building MySQL web applications with a team of developers will inevitably present the challenge of database schema changes. Any good web developer understands the importance of keeping all code under version control, but how many follow the same principle for the database?

What follows are some best practices that I have learned over the past several years from the school of hard knocks. Continue reading

Master-Master MySQL Replication…that hurts less

If you have ever touched a MySQL slave, you know that they can and do frequently halt. While sync problems can be caused by many things—network outages, schema changes, etc—one of the most common problems in a dual-master setup is primary key collision.

Primary Key Collision

…happens when records are added on two different servers to the same table and get the same AUTO_INCREMENT value. Fortunately, there is a trivially easy way to prevent this from happening.

auto-increment-increment=N

Adding this to your my.cnf or my.ini file will make AUTO_INCREMENT increment by N rather than by 1. N is the number of replicated servers that are masters.

Combine this setting with…

auto-increment-offset=N

…to ensure that each replicated master uses unique AUTO_INCREMENT values. N should match the server-id setting.

With these two settings in place, your primary keys will never collide. Continue reading

Reasonable Defaults for MySQL Server

Jeremy Zawodny posted yet another great article over at Linux Magazine on some improved defaults that can save you a lot of grief when your network fails intermittently. In summary:

Faster replication heartbeat (old default is 3600 seconds = 1 hour):

slave_net_timeout 10

Disable DNS hostname lookups:

skip-name-resolve

Sane connection timeout (may need to be raised if your network is flaky):

connect_timeout 5

Disable host blacklisting after x number of failed connections:

max_connect_errors 1844674407370954751

Improving INSERT INTO … SELECT Performance

INSERT INTO…SELECT locks the table being read by the SELECT statement due to MySQL 5.0’s statement-based replication. Here’s a great post from the MySQL Performance Blog explaining the problem in detail and what to do about it.

Note: this problem is supposed to be eliminated when using MySQL 5.1 row-based replication.

I thought it would be interesting to see how the Drizzle developers where handling this scenario in their high-performance, high-concurrency remake of the MySQL. Below is an e-mail I got back from Jay Pipes on the subject: Continue reading

MySQL Style Guide

I’m a big believer in readability, and I love to write code that is easy to understand. Check out my newly-posted MySQL Style Guide which covers:

  • Simple queries
  • Complex queries
  • Subqueries
  • UNIONS
  • Advanced queries

Useful tips:

  • Use table aliases sparingly and only when absolutely necessary (a few extra keystrokes now will save you hours of gazing at cryptic queries later)
  • CAPITALIZE keywords religiously
  • Whitespace and tabs are your friends!

10 Tips for Optimizing MySQL Queries

In my quest to understand MySQL’s EXPLAIN statement and to learn more strategies for optimizing queries, I came across this excellent blog post from 20bits by Jesse Farmer:

http://20bits.com/articles/10-tips-for-optimizing-mysql-queries-that-dont-suck/

In summary:

  1. Benchmark, benchmark, benchmark!
  2. Profile, profile, profile!
  3. Tighten Up Your Schema
  4. Partition Your Tables
  5. Don’t Overuse Artificial Primary Keys
  6. Learn Your Indices
  7. SQL is Not C
  8. Understand your engines
  9. MySQL specific shortcuts
  10. Read Peter Zaitsev’s MySQL Performance Blog

Repair a broken MySQL slave

If a MySQL slave encounters an error while replicating commands from the master, the slave will abort.

One way this can happen is if you are using triggers on a table that calls a stored procedure, but the stored procedures are missing on the slave because you forgot to include the –routines option when generating a mysqldump from the master to import to the slave.

So what do you do? First, verify that the slave is indeed encountering errors:

mysql> SHOW SLAVE STATUS \G
*************************** 1. row ***************************
             Slave_IO_State: Waiting for master to send event
                Master_Host: 1.2.3.4
                Master_User: slave_user
                Master_Port: 3306
              Connect_Retry: 60
            Master_Log_File: mysql-bin.001079
        Read_Master_Log_Pos: 269214454
             Relay_Log_File: slave-relay.000130
              Relay_Log_Pos: 100125935
      Relay_Master_Log_File: mysql-bin.001079
           Slave_IO_Running: Yes
          Slave_SQL_Running: No
            Replicate_Do_DB: mydb
        Replicate_Ignore_DB:
         Replicate_Do_Table:
     Replicate_Ignore_Table:
    Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
                 Last_Errno: 1146
                 Last_Error: Error 'Table 'mydb.taggregate_temp_1212047760' doesn't exist' on query. Default database: 'mydb'. 
Query: 'UPDATE thread AS thread,taggregate_temp_1212047760 AS aggregate
        SET thread.views = thread.views + aggregate.views
        WHERE thread.threadid = aggregate.threadid'
               Skip_Counter: 0
        Exec_Master_Log_Pos: 203015142
            Relay_Log_Space: 166325247
            Until_Condition: None
             Until_Log_File:
              Until_Log_Pos: 0
         Master_SSL_Allowed: No
         Master_SSL_CA_File:
         Master_SSL_CA_Path:
            Master_SSL_Cert:
          Master_SSL_Cipher:
             Master_SSL_Key:
      Seconds_Behind_Master: NULL
1 row in set (0.00 sec)

mysql>

If  either Slave_IO_Running or Slave_SQL_Running are ‘No’ then the slave has aborted. Here’s how to fix it:

Stop the slave

mysql> STOP SLAVE;

Resolve the error

Pay attention to the Last_Error field and try to resolve it. If this is a recurring issue you may need to import a fresh dump from the master to bring your slave back into sync. If the situation merits it, you can run the following to have the slave skip the offending SQL query:

mysql> SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;

Be careful, though because this could have severe repercussions down the line.

Restart the slave

mysql> START SLAVE;

Verify that the slave is running

mysql> SHOW SLAVE STATUS \G

When Slave_IO_Running and Slave_SQL_Running both are set to ‘Yes’ then you’re good to go. It would probably be a good idea to have some sort of monitor to ensure your slaves are indeed running when you need them to. Perhaps I’ll throw together a quick script to do that…

Thanks to http://www.howtoforge.com/how-to-repair-mysql-replication for this information.

What to do when MySQL doesn’t use an index…

Sometimes MySQL doesn’t use the index on a column when performing a query.

Indexes are NOT used if MySQL can calculate that it will probably be faster to scan the whole table. For example if key_part1 is evenly distributed between 1 and 100, it’s not good to use an index in the following query:

  • SELECT * FROM table_name where key_part1 > 1 and key_part1 < 90

So what do you do in that case? This was my query:

SELECT dispId, outcome, contacted, householder, time_stamp
FROM `dispositions`
WHERE `leadId` = 1 OR (`leadId` IS NULL AND `campaignId` = 8 AND `xid` = 'AAA100000000148')
ORDER BY `dispId` DESC
LIMIT 1

In this table (~175,000 rows), leadId, campaignId, and xid all have indexes, but MySQL was doing a table scan (~2.2 sec) because of the duplicate use of leadId and the way the WHERE clause was structured. This rather unusual optimization was more than 10x faster (~.17 sec):

SELECT dispId, outcome, contacted, householder, time_stamp
FROM `dispositions`
WHERE `campaignId` = 8 AND `xid` = 'AAA100000000148' AND `leadId` IS NULL

UNION ALL

SELECT dispId, outcome, contacted, householder, time_stamp
FROM `dispositions`
WHERE `leadId` = 1

ORDER BY `dispId` DESC
LIMIT 1

An odd case where two queries are faster than one!