High Performance MySQL: Optimization, Backups, Replication, and More

Author: Baron Schwartz, Arjen Lentz
4.6
All Stack Overflow 23
This Year Stack Overflow 1
This Month Stack Overflow 1

High Performance MySQL: Optimization, Backups, Replication, and More

4.6

Review Date:

Comments

by anonymous   2017-08-20

In general this is a very subtle issue and not trivial whatsoever. I encourage you to read mysqlperformanceblog.com and High Performance MySQL. I really think there is no general answer for this.

I'm working on a project which has a MySQL database with almost 1TB of data. The most important scalability factor is RAM. If the indexes of your tables fit into memory and your queries are highly optimized, you can serve a reasonable amount of requests with a average machine.

The number of records do matter, depending of how your tables look like. It's a difference to have a lot of varchar fields or only a couple of ints or longs.

The physical size of the database matters as well: think of backups, for instance. Depending on your engine, your physical db files on grow, but don't shrink, for instance with innodb. So deleting a lot of rows, doesn't help to shrink your physical files.

There's a lot to this issues and as in a lot of cases the devil is in the details.

by anonymous   2017-08-20

Not PHP specific (as others have mentioned, "advanced" MySQL knowledge should be language-independent), but here you go (from this question and this question):

Understanding MySQL Internals :

Learn how data structures and convenience functions operate, how to add new storage engines and configuration options, and much more

High Performance MySQL:

Learn how to design schemas, indexes, queries and advanced MySQL features for maximum performance, and get detailed guidance for tuning your MySQL server, operating system, and hardware to their fullest potential. You'll also learn practical, safe, high-performance ways to scale your applications with replication, load balancing, high availability, and failover.

Pro MySQL:

Topics include transaction processing and indexing theory, benchmarking and profiling, and advanced coverage of storage engines, data types, subqueries, derived tables, and joins. Also covers MySQL 5's new enterprise features like stored procedures, triggers, and views.

(partial descriptions from Amazon included, see respective product page for more detailed info).

by anonymous   2017-08-20

This sounds like a job of a special type of MySQL Replication Topology

The topology I have in mind is called "the Distribution Master," which comes from pages 368-370 of the book High Performance MySQL : Optimizations, Backups, Replication and more under the subheading "Master, Distribution Master, and Slaves."

You will need the following

1 DB Master Server
1 DB Distribution Master (known as a /dev/null Slave, or Blackhole Slave)
Whatever Number of DB Slaves


SETUP OF DB MASTER

  1. Install MySQL 5.1.x
  2. Make sure /etc/my.cnf
          server-id=1
          log-bin=mysql-bin
          expire-logs-days=14
          default-storgae-engine=InnoDB
  3. Startup MySQL
  4. RESET MASTER; (Clear Binary Logs From DB Master)
  5. Load in Data into Master
  6. GRANT SELECT,REPLICATION SLAVE ON . TO replicator@'%' IDENTIFIED BY 'repl-password';

SETUP OF DB DISTRIBUTION MASTER

  1. Install MySQL 5.1.x
  2. Make sure /etc/my.cnf
          server-id=2
          log-bin=mysql-bin
          expire-logs-days=14
          default-storage-engine=BLACKHOLE
          skip-innodb
  3. Startup MySQL
  4. CHANGE MASTER TO MASTER_HOST='IP Address of DB Master', MASTER_PORT=3306, MASTER_USER='replicator', MASTER_PASSWORD='repl-password', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=106;
  5. START SLAVE;
  6. GRANT SELECT,REPLICATION SLAVE ON . TO replicator@'%' IDENTIFIED BY 'repl-password';
  7. For every table that is not in information_schema database and not in the mysql database, convert each table to the BLACKHOLE storage engine like this: ALTER TABLE tblname ENGINE=BLACKHOLE;
  8. RESET MASTER; (Clear Binary Logs From DB Distribution Master)

SETUP OF DB SLAVES

  1. Install MySQL 5.1.x
  2. Make sure /etc/my.cnf
          server-id=3
          default-storage-engine=InnoDB
  3. Startup MySQL
  4. CHANGE MASTER TO MASTER_HOST='IP Address of DB Master', MASTER_PORT=3306, MASTER_USER='replicator', MASTER_PASSWORD='repl-password', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=106;
  5. START SLAVE; (let replication catch up, check SHOW SLAVE STATUS\G)
  6. STOP SLAVE;
  7. CHANGE MASTER TO MASTER_HOST='IP Address of DB Distribution Master', MASTER_PORT=3306, MASTER_USER='replicator', MASTER_PASSWORD='repl-password', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=106;
  8. START SLAVE;

The purpose of ths setup is to have the DB Master only process SQL meant for the DB Master only. The SQL to be replicated to the DB Slaves are handled by the DB Distribution Master. This will alleviate the DB Master processing the transmission of SQL to Slave; that becomes the responsibility of the DB Distribution Master. All Slaves read SQL changes from the DB Distribution Master rather than the DB Master.

Although I do not fully understand your application, this topology should properly support one central database and multiple read slaves without I/O bounding the central database. Your application should organization INSERTs, UPDATEs, and DELETEs to client database as long as each laptop handles a unique set of clients different and distinct from other laptops.


CAVEAT

You may have to do serious testing on the DB Slaves to make sure data doesn't vanish because of the the BLACKHOLE setup. If this occurs, try removing 'default-storage-engine=BLACKHOLE' and reloading everything.


Future questions of this nature should be asked in dba.stackexchange.com

by anonymous   2017-08-20

For your first question:

X row(s) fetched in Y s (Z s)

X = number of rows (of course); Y = time it took the MySQL server to execute the query (parse, retrieve, send); Z = time the resultset spent in transit from the server to the client;

(Source: http://forums.mysql.com/read.php?108,51989,210628#msg-210628)

For the second question, you will never ever know how the query performs unless you test with a realistic number of records. Here is a good example of how to benchmark correctly: http://www.mysqlperformanceblog.com/2010/04/21/mysql-5-5-4-in-tpcc-like-workload/

That blog in general as well as the book "High Performance MySQL" is a goldmine.

by anonymous   2017-08-20

I would recommend two books I recently read:

  1. High Performance MySQL
  2. Refactoring SQL Applications
by anonymous   2017-08-20

Well the best approach to break down your MySQL query is to run the EXPLAIN command as well as looking at the MySQL documentation for Optimization with the EXPLAIN command.

MySQL provides some great free GUI tools as well, the MySQL Query Browser is what you need to use.

When running the EXPLAIN command this will break down how MySQL interprets your query and displays the complexity. It might take some time to decode the output but thats another question in itself.

As for a good book I would recommend: High Performance MySQL: Optimization, Backups, Replication, and More