Monday, May 20, 2024

Common MySQL Scalability Mistakes

April 11, 2011
download pdf
Download PDF Presentation

The most common mistakes are easy to avoid however many startups continue to fall prey, with the impact including large re-design costs, delays in new feature releases, lower staff productivity and less then ideal ROI. All growing and successful sites need to achieve higher Availability, seamless Scalability and proven Resilience. Know the right MySQL environment to provide a suitable architecture and application design to support these essential needs.

  • The different types of accessible data (e.g. R/W, R, none)
  • What limits MySQL availability (e.g software upgrades, blocking statements, locking etc)
  • The three components of scalability – Read Scalability/Write Scalability/Caching
  • Design practices for increasing scalability and not physical resources
  • Disaster is inevitable. Having a tested and functional failover strategy
  • When other products are better (e.g. Static files, Session management via Key/Value store)
  • What a lack of accurate monitoring causes
  • What a lack of breakability testing causes
  • What does “No Downtime” mean to your organization
  • Implementing a successful “failed whale” approach with pre-emptive analysis
  • Identifying when MySQL is not your bottleneck

Presenter: Ronald Bradford
Schedule: IOUG Collaborate 11 – Orlando, Florida. OTN LA Tour – South America. Surge Scalability 2010 – Baltimore, Maryland.

Filed Under: Presentation

Upcoming Conference Presentations for April 2011

March 24, 2011

Ronald Bradford will be presenting at two conferences in April, the O’Reilly MySQL Conference and the IOUG Collaborate 11 conference. His presentations include:

In addition, our monthly New York Meeting will be:

Filed Under: News

Better Indexes $ave You Money

March 24, 2011
download pdf
Download PDF
Presentation

Can database performance improvements be achieved with zero code changes? Learn how to use one simple advanced technique to make better MySQL indexes and improve your queries by 500% or more. Even with a highly indexed schema as shown in our 10 table join example, significant improvements in performance can be achieved.

This presentation introduces the approach for correct identification and verification of problem SQL statements and then describes the means of identifying index choices for optimization. Then discussed is not only how to apply indexes to improve query performance, but how to apply better indexes and provide even great performance gains.

This presentation includes:

  • 6 steps to successful SQL review
  • Basic ways via application logging and TCP/IP analysis to capture SQL
  • All the commands necessary to identify why and how to create indexes
  • How the number of table rows and different storage engines can effect query performance
  • How to create an iterative verification process

Adding indexes is not the only approach nor the best approach to query optimization however learning how MySQL indexes work can provide quick improvements without the need to deploy code changes.

Presenter: Ronald Bradford
Schedule: Tuesday, March 22, 2011 in New York

Filed Under: DBA, Indexes, Presentation Tagged With: ,

Upcoming Presentation – How better indexes save you money

March 7, 2011

Ronald Bradford will be speaking in New York on March 22nd at the offices of the startup incubator General Assembly starting at 6pm.

His presentation “How better indexes save you money” will be an enlightening experience about how index only improvements to already indexed queries and with no additional code changes resulted in huge performance improvements and significant savings in hosting costs for a client.

More information available at Meetup.com EffectiveMySQL Group.

Filed Under: News

MySQL Binary Log

March 2, 2011

The MySQL Binary Log records completed transactions that have applied to a MySQL instance. The MySQL Binary log is a critical necessary component for point-in-time MySQL Data Recovery and for MySQL Replication.

The Binary log is configured with the following MySQL Configuration setting:

[mysqld]
log-bin[=[/path/to/]file]

As the name suggests this file contains binary information. The mysqlbinlog utility can be used to extract text based information from the Binary Log.

Prior to MySQL 5.1, all SQL statements were logged in STATEMENT format. MySQL 5.1 introduced the Binary Log Row Format --binlog-format to support additional MIXED and ROW formats.

The full list of MySQL Variables that relate to the MySQL Binary Log are: [Read more...]

Filed Under: Article, MySQL Logs

SQL_MODE

March 1, 2011

SQL_MODE is a means of providing various SQL modes within MySQL that change the possible effect of statements. This system variable can be defined as either GLOBAL or SESSION scope.

mysql> SET [GLOBAL|SESSION] SQL_MODE = 'value[,value]';

The SQL_MODE can also be defined for a MySQL instance via the my.cnf configuration file.

[mysqld]
sql_mode='value[,value]';

The list of valid SQL_MODES include: [Read more...]

Filed Under: Syntax

MyISAM

February 24, 2011

MyISAM is the default storage engine of MySQL until version 5.5. This engine is an implementation of the well defined database storage ISAM architecture and has been available since MySQL 3.x.

Key features
• Non transactional
• Fast insert rate
• Btree based indexes
• Supports FULLTEXT index
• 16k data pages
• 4k index pages

Limitations
• Does not support transactions
• Table level locking on DML and DDL statements
• Not crash safe
• Relies on File System level cache to cache data pages

Important Parameters
[Read more...]

Filed Under: Article, Storage Engines Tagged With: , ,

Index Techniques

February 22, 2011

MySQL supports various different implementations of fundamental Index Techniques based on the different available Storage Engines. These include:

BTREE

This is the most common index implementation used by the majority of storage engines including MyISAM and InnoDB storage engines.

The InnoDB storage engine supports a clustered primary key B-Tree index. The Tokutek storage engine also supports via their Fractal Tree technology cluster indexes for all indexes.

More Information

  • B-Tree description via Wikipedia.

HASH

The HASH index is the default for the Memory Storage Engine.

Filed Under: Article, Indexes

Memory Storage Engine

February 22, 2011

The Memory storage engine, also known historically as the Heap storage engine as the name implies is an in memory only table that does not provide data persistence. The Memory storage engine is actually used internally by the mysql kernel when a temporary table is required.

Key features

• Very fast, in memory
• Non transactional
• Support the hash index by default
• Btree indexes are also supported
• Ideal for primary key lookups

Limitations

• Does not support transactions
• Table level locking on DML and DDL statements
• Data is not persistent
• Does not support TEXT/BLOB data types
• Fixed row width
• No ability to limit the total amount of memory for all Memory tables

Important Parameters

• max_heap_table_size – Defines the maximum size of a single Memory table.
• tmp_table_size – Defines the maximum size of the table when used for internally temporary tables.
• init_file – Defines a SQL file of commands that are executed when the MySQL instance is started. Used as a means to seed Memory tables.

Memory tables are confined to a maximum table size as defined by the max_heap_table_size system variable. In the following example we can demonstrate the table full error message, and correct the maximum size, based on our size of the comparison Fixed MyISAM table size to show the data in a Memory table

References

Filed Under: Article, Storage Engines

AUTO_INCREMENT

February 18, 2011

MySQL supports a unique incrementing number for a single database column using the AUTO_INCREMENT definition when specified with CREATE TABLE statement. This is a common use for a Primary Key. This implementation has some similar traits with a database sequence that is found in other RDBMS products.

You can determine the last value used for an AUTO_INCREMENT column using the LAST_INSERT_ID() function.

MySQL supports different ways of starting and incrementing an AUTO_INCREMENT column using the auto_increment_increment and auto_increment_offset system variables.

Only one table column can be defined as an AUTO_INCREMENT column and this column must be part of the table Primary Key.

References

Filed Under: Article
« Older Posts
Newer Posts »