Showing entries 31 to 40 of 992
« 10 Newer Entries | 10 Older Entries »
Displaying posts with tag: Uncategorized (reset)
Tips to Improve MySQL Performance

Adding indexes can provide significant performance benefits. However, the most effective SQL optimization for a relational database is to eliminate the need to execute the SQL statement completely. For a highly tuned application, the greatest amount of time for the total execution of the statement is the network overhead.

Removing SQL statements can reduce the application processing time. Additional steps necessary for each SQL statement include parsing, permission security checks, and generation of the query execution plan.

These are all overheads that add unnecessary load to the database server when statements are unnecessary. You can use the profiling functionality to get detailed timing of steps within the execution of a query.

Here is an example:

mysql> show profile source for query 7;

[Read more]
Improving Performance with MySQL Index Columns

In addition to creating new indexes to improve performance, you can improve database performance with additional schema optimizations. These optimizations include using specific data types and/or column types. The benefit is a smaller disk footprint producing less disk I/O and results in more index data being packed in available system memory.

Data Types

Several data types can be replaced or modified with little or no impact to an existing schema.


When a primary key is defined as a BIGINT AUTO_INCREMENT data type, there is generally no requirement why this datatype is required. An INT UNSIGNED AUTO_INCREMENT datatype is capable of supporting a maximum value of 4.3 billion. If the table holds more than 4.3 billion rows, other architecture considerations are generally necessary before this requirement.

The impact of modifying a BIGINT data type to an INT data type is a 50 percent reduction …

[Read more]
Optimizing MySQL Indexes

The management of indexes—how they are created and maintained—can impact the performance of SQL statements.

Combining Your DDL

An important management requirement when adding indexes to MySQL is the blocking nature of a DDL statement. Historically, the impact of an ALTER statement required that a new copy of the table be created. This could be a significant operation for time and disk volume when altering large tables. With the InnoDB plugin, first available in MySQL 5.1, and with other third party storage engines, various ALTER statements are now very fast, as they do not perform a full table copy. You should refer to the system documentation for the specific storage engine and MySQL version to confirm the full impact of your ALTER statement.

Combining multiple ALTER statements into one SQL statement is an easy optimization improvement. For example, if you needed to add a new index, modify an index, and add a new …

[Read more]
What You Should Know About MySQL Replication Types

For companies that live and die by their databases, a one-shot backup isn’t really the perfect solution. Typically for such companies (think Yahoo! or Google), database access is a near-constant process, and database content changes continually, often on a second-by-second basis. Data replication, which involves continual data transfer between two (or more) servers to maintain a replica of the original database, is a better backup solution for these situations.

MySQL supports two (or three, depending on how you look at it) different methods of replicating databases from master to slave. All of these methods use the binary log; however, they differ in the type of data that is written to the master’s binary log.

  • Statement-based replication Under this method, the binary log stores the SQL statements used to …
[Read more]
What You Should Know About MySQL Replication

Replication in MySQL is the dynamic process of synchronizing data between a primary (master) database server and one or more secondary (slave) database servers in near-real time. Using this process, it’s possible to create copies of one or more databases so that even if the primary server fails, data can still be recovered from one of the secondary servers.

Replication is essential for many applications, and the lack of replication support was a major drawback to MySQL compared to other relational database management systems (RDBMSs). MySQL 3.23 was the first version to introduce replication support, and support has improved continually in subsequent versions. However, MySQL is still best suited for one-way replication, where you have one master and one or more slaves.


As much as possible, try to use the same version of MySQL for both the master and slave server(s). A version …

[Read more]
Tips for Restoring Data in a MySQL Database

Equally important as backing up data in a MySQL database is the ability to restor data.

Most books on the subject emphasize the importance of backing up your data regularly (and rightly so), but restoring the data is an often-overlooked aspect of this process. Backed-up files are useless if they can’t be accessed. Accordingly, you should regularly restore your files from backup to make certain they can be used in an emergency. In fact, it might not be too much to say that a backup job isn’t complete until you’ve confirmed that the backup files can be restored. Besides the peace of mind you’ll achieve, it pays to be thoroughly familiar with the process, because you certainly don’t want to waste time learning the restore procedure after the system goes down.

In the preceding section, you learned that the output of …

[Read more]
Checking and Repairing MySQL Tables

You might need to restore corrupted tables (or even an entire database) from your backups and use the update logs if a table gets damaged or deleted by accident. In case of relatively minor damage, however, MySQL provides several options for table repair. This next section deals with what you can do if this is the case.

Checking Tables for Errors

The first thing to do if you suspect something is wrong is to check the table for errors. The myisamchk utility is one way to check a table. To invoke this utility, execute the command myisamchk table-file.

Because myisamchk requires exclusive access to the tables, a good idea is to take the server offline before running it. This way, you needn’t worry about coordinating access between clients. In addition, you can run several options when you check a table for errors, as shown in Table 1.

The following example runs myisamchk with the …

[Read more]
MariaDB/MySQL: ON REPLACE triggers

There are several reasons why, generally, in MySQL/MariaDB one should not use REPLACE as a shortcut for SELECT + (UPDATE or INSERT). One of these reasons is the way REPLACE fires triggers. Of course it does not fire UPDATE triggers, and there is no such thing as a REPLACE trigger; DELETE and INSERT triggers are fired instead. What is not obvious is the order in which REPLACE activates triggers (UPDATE: this beavior was undocumented; I documented it here):

  2. BEFORE DELETE (if a row is being replaced);
  3. AFTER DELETE (if a row is being replaced);

Knowing this, we can create …

[Read more]
MySQL & Friends Devroom for Fosdem 2016

Hi all MySQL afficionados !

I just wanted to keep you informed that Fosdem’s organization opened the proposals for Devrooms.

As every year now, we submitted our participation. As soon as our devroom will be approved, the Call For Papers will be published.

Stay tuned for more information.

Cheers !

ARIN Reaches IPv4 Depletion | Team ARIN

ARIN is the American Registry for Internet Numbers, the organisation that hands out the blocks IP addresses.

Each region has its own, for instance Australia/Pacific has APNIC. Naturally, they work very closely together.

Open Query can assist you with planning and deployment of IPv6 capabilities on your front-end servers, as well as at the back-end if you wish. Not all hosting providers support IPv6, but many good ones do. MySQL/MariaDB can operate in a native IPv6 or dual stack environment. Open Query enabled its own front-end servers for IPv6 a number of years ago already.

[Read more]
Showing entries 31 to 40 of 992
« 10 Newer Entries | 10 Older Entries »