MySQL Clustering

by Davies, Alex; Fisk, Harrison

• ISBN13: 9780672328558
• ISBN10: 0672328550
• Edition: 1st
• Format: Paperback
• Copyright: 2006-03-20
• Publisher: MySQL Press

Summary

"MySQL Clustering is the comprehensive guide to installing, administering, and troubleshooting a MySQL database cluster. With useful examples that show how to accomplish back-up, security, performance, and troubleshooting, it is the definitive reference and guide."--BOOK JACKET.

Author Biography

Alex Davies is the authority on MySQL clustering, establishing himself in the MySQL community after having written a HOWTO on clustering that is widely viewed as defining its use. A system administrator with extensive practical experience setting up and maintaining MySQL clusters, Alex is active on the cluster mailing lists and forums.

 

Harrison Fisk is one of the leading trainers at MySQL AB, a consultant for MySQL AB specializing in performance tuning and application design, and a speaker on MySQL clustering.

Table of Contents

Introduction	1	(1)
Introduction to Database Clustering	1	(4)
Why Cluster a Database?	2	(1)
Types of Clustering	2	(1)
Replication Versus Clustering	3	(1)
Hardware Considerations with MySQL Cluster	4	(1)
Networking Considerations with MySQL Cluster	4	(1)
Why Use a MySQL Cluster?	5	(1)
High Availability	5	(1)
Scalability	5	(1)
Performance	6	(1)
Other Advanced Features	6	(1)
Cluster Terminology	6	(9)
How a MySQL Cluster Works	6	(1)
Different Parts of a Cluster in MySQL Cluster	7	(1)
The MySQL Cluster Storage Engine	8	(1)
MyISAM Versus NDB	8	(1)
InnoDB Versus NDB	8	(1)
Limitations of NDB	9	(1)
Data Partitioning	10	(1)
Benefits of Partitioning	10	(1)
Synchronous Replication	11	(1)
Node Groups	11	(1)
A Practical Example of Partitioning and Node Groups	12	(1)
Network Partitioning and Arbitration	12	(1)
A Brief History of MySQL Cluster	13	(1)
What's New in MySQL Cluster 5.0	14	(1)
What's New in MySQL Cluster 5.1	14	(1)
Designing a Cluster	15	(2)
Networking	15	(1)
Determining the Number of Replicas	15	(1)
Determining the Amount of RAM	15	(1)
Determining the Amount of Disk Space	16	(1)
Choosing an Operating System	17	(1)
What This Book Covers	17	(3)
Software Versions	20	(1)
More Sources of Information	20	(1)
Installation	21	(46)
Before You Begin with MySQL Cluster	21	(3)
Obtaining and Installing MySQL Cluster	22	(1)
Designing a Cluster	23	(1)
Installing MySQL-Max	24	(7)
Installing MySQL Cluster from a Binary Tarball	25	(3)
Installing from a Binary RPM	28	(1)
Compiling from Source	29	(2)
Retrieving the Latest Snapshot from BitKeeper	31	(2)
Upgrading a BitKeeper Installation	33	(1)
Ensuring That MySQL Cluster Works	33	(9)
Configuring and Starting a Cluster	34	(2)
An Example of a config.ini File	36	(2)
Starting a Management Node	38	(1)
Starting Storage Nodes	39	(1)
Connecting SQL Nodes	40	(1)
An Example of a Working Cluster	41	(1)
Creating a Table	41	(1)
Auto-discovery of Databases	42	(1)
Startup Phases	43	(1)
Restarting a Cluster	43	(3)
Restarting on Failure	44	(2)
Testing Your Cluster	46	(1)
Upgrading MySQL Cluster	47	(7)
Upgrading Through Major Versions	49	(1)
Other Methods of Starting a Cluster	49	(5)
Obtaining, Installing, and Configuring MySQL Cluster on Other Platforms	54	(1)
RAM Usage	55	(7)
Memory Usage	56	(4)
Using MySQL Cluster on 64-Bit Operating Systems	60	(1)
Calculating the DataMemory and IndexMemory Parameters	61	(1)
Reducing Memory Usage	61	(1)
Adding Tables	62	(2)
Using Alter Table	62	(1)
Using Create Table	63	(1)
Common Errors While Importing Tables	64	(3)
Error 1114 (HY000) at line 227: The table `table_log' is full	64	(1)
Error 1005 (HY000) at line x: Can't create table' dbname/tablename.frm' (errno: 4242)	65	(1)
Error 1005 (HY000) at line x: Can't create table' dbname/tablename.frm' (errno: 4335)	65	(1)
Error 1015 (HY000): Can't lock file (errno: 4006)	66	(1)
Error 1050 (42S01): Table `ctest' already exists	66	(1)
``Out of operation records in transaction coordinator''	66	(1)
Configuration	67	(32)
The Structure of config.ini	68	(2)
All Nodes	70	(1)
Management Nodes	70	(3)
Parameters to Define for All Management Nodes (NDB_MCMD_Default)	70	(2)
Parameters to Define for Each Management Node (NDB_MCMD)	72	(1)
An Example of a Management Section of a config.ini File	72	(1)
Storage Nodes	73	(20)
Parameters to Define for All Storage Nodes (NDBD_DEFAULT)	73	(3)
Transaction Parameters	76	(2)
Transaction Temporary Storage	78	(1)
Scans and Buffering	79	(1)
Logging and Checkpointing	80	(1)
Metadata Objects	81	(1)
Boolean Parameters	82	(1)
Controlling Timeouts, Intervals, and Disk Paging	83	(5)
Buffering	88	(2)
Backup Parameters	90	(1)
Logging	91	(1)
Parameters to Define for Each Storage Node (NDBD)	92	(1)
An Example of a Storage Section of a config.ini File	92	(1)
SQL Nodes	93	(2)
Parameters to Define for All SQL Nodes (Mysqld_Default)	93	(1)
Parameters to Define for Each SQL Node	94	(1)
A Final Note on SQL Nodes	95	(1)
Using Multiple Management Nodes	95	(4)
Backup and Recovery	99	(10)
MySQL Cluster's Native Backup Tool	99	(3)
Using mysqldump for Backups	102	(2)
Single-User Mode	102	(2)
Restoring a Backup	104	(3)
Restoring a Backup by Using Native Hot Backup	104	(2)
Restoring a Backup Made Using mysqldump	106	(1)
Speeding Up Restoration	106	(1)
Troubleshooting Backups	107	(2)
Disk Is Full	107	(1)
Disk Is Too Slow	107	(1)
Not Enough Memory	107	(2)
Security and Management	109	(20)
Security for MySQL Cluster	109	(10)
Software Firewalls	114	(3)
Standard Security Procedures	117	(2)
Managing MySQL Cluster	119	(6)
The Management Console and Scripting	119	(2)
Doing a Rolling Cluster Restart	121	(4)
Issuing a SQL Command to Each SQL Node	125	(1)
Running MySQL in a Chrooted Environment	125	(4)
Performance	129	(22)
Performance Metrics	129	(1)
MySQL Cluster Performance	130	(1)
Benchmarking	130	(2)
Using the Wrong Data Size	131	(1)
Using a Data Set That Is Not Representative	131	(1)
Using Inaccurate Data Access Patterns	131	(1)
Failing to Consider Cache Effects	131	(1)
Using Too Little Load or Too Few Users	131	(1)
Benchmarking Solutions	132	(1)
Indexes	132	(3)
Primary Key Indexes	132	(1)
Unique Hash Indexes	133	(1)
Ordered Indexes	133	(1)
Index Statistics	134	(1)
Query Execution	135	(2)
The Query Cache in MySQL Cluster	136	(1)
Data Retrieval in MySQL Cluster	137	(7)
Primary Key Access	137	(1)
Unique Key Access	137	(1)
Ordered Index Access	138	(1)
Full Table Scan	138	(2)
Using Explain	140	(4)
Physical Factors	144	(1)
High-Speed Interconnects	145	(6)
TCP/IP	146	(1)
Shared Memory	146	(1)
SCI	147	(2)
Adding More Transports	149	(2)
Troubleshooting	151	(12)
What to Look for When Something Goes Wrong	151	(1)
MySQL Cluster Errors	152	(11)
NDBD Won't Connect	152	(1)
NDBD Won't Start	153	(1)
Arbitrator Decides to Shut Down a Node	154	(2)
Table Is Full	156	(1)
Error 708	157	(1)
DBTUP Could Not Allocate Memory	157	(1)
Multiple Storage Nodes per Server	157	(3)
Cluster Nodes Apparently Randomly Shut Down or Crash	160	(3)
Common Setups	163	(10)
Load Balancing and Failover	163	(4)
JDBC Driver Support	164	(1)
Round-Robin DNS	165	(1)
Hardware Load Balancing Solutions	165	(1)
Software Load Balancing Solutions	166	(1)
Cluster Topographies	167	(3)
The Simplest Setup	167	(1)
Web Farm/MySQL Farm	168	(1)
Client/Server Applications	169	(1)
Network Redundancy	170	(3)
A MySQL Cluster Binaries	173	(6)
An Overview of Binary Options	173	(1)
Configuration Files	173	(1)
Individual Programs	174	(5)
The mysqld Program	174	(1)
The mysql Program	174	(1)
The ndbd Program	175	(1)
The ndb_mgmd Program	175	(1)
The ndb_mgm Program	175	(1)
The ndb_desc Program	176	(1)
The ndb_restore Program	176	(1)
The ndb_show_tables Program	177	(1)
The ndb_waiter Program	177	(1)
The perror Program	177	(2)
B. Management Commands	179	(8)
Displaying the Status of the Cluster/Controlling Nodes	179	(2)
The Show Command	179	(1)
The <id> Start, Stop, and Restart Commands	180	(1)
The <id> STATUS Command	180	(1)
The <id> Dump 1000 Command	180	(1)
Logging Commands	181	(3)
The Clusterlog on and Clusterlog off Commands	181	(1)
The Clusterlog Toggle Command	181	(1)
The Clusterlog Info Command	182	(1)
The Clusterlog Filter <severity> Command	182	(1)
The <id> Clusterlog <category>=<threshold level> Command	183	(1)
Miscellaneous Commands	184	(3)
The Enter Single User Mode <sql-node-id> Command	184	(1)
The Exit Single User Mode Command	184	(1)
The Shutdown Command	184	(1)
The Purge Stale Sessions Command	185	(1)
The Start Backup Command	185	(1)
The Abort Backup <backup id> Command	185	(2)
C. Glossary of Cluster Terminology	187	(4)
Index	191