sabaTEL.net

Java, MySQL, Avaya

User Management

It is very important to maintain and improve the performance of your database when possible, but you should never forget about the security aspect either.

MySQL bases all its security access on the ‘mysql’ database itself. Below you can see the users that I have created and from which box they can access the database. And afterwards I check what the account ‘reader’ is allowed to do:

  • Able to connect to MySQL as long as the ‘reader’ logs in from the same machine ‘localhost’ with the password stored.
  • Execute any SELECT statement to any table of the ‘world’ database.
The list of privileges available at the moment for MySQL is:

Advertisements

August 20, 2011 Posted by | MySQL | Leave a comment

EXPLAIN

The EXPLAIN statement is a valuable tool in terms of tuning your database, but before you get too excited, it is important to pay attention to all it can offer you and its limitations:

  • Shows MySQL’s estimated query plan.
  • Only works for SELECT queries.
The EXPLAIN output columns are:

field : (possible values)  description
id : (number) which SELECT the row belongs to.
select type : (SIMPLE or COMPLEX : primary, subquery, derived, union, union result)
  • SIMPLE, No subqueries or unions.
  • PRIMARY, The outer query
  • SUBQUERY, Numbered according to the position in SQL text
  • DERIVED, Subquery in the FROM clause executed as a temp table
  • UNION, Rows are spooled into a table, then read out with a NULL id in a row that says UNION RESULT

table : (table name) the table accessed or its alias.
type : (ALL, index, range, ref, eq_ref, const, system, NULL) ordered from worst to best by the ‘join type’. How MySQL will access the rows to find results.
possible_keys : (table columns) which indexes looked useful to the optimizer, the indexes that can help make row lookups efficient.
key : (table columns) which indexes the optimizer chose, but that does NOT mean they will be the most efficient indexes, different criteria.
key_len : (number) the number of bytes of the index MySQL will use
ref : (const, column name) which columns/constants from preceding tables are used for lookups in the index named in the key column
rows : (number) estimated number of rows to read, NOT the number of rows in the result set!
extra : (text) Any additional information that help us to understand the execution plan used.
  • Using index: covering index
  • Using where: server post-filters rows from storage engine
  • Using temporary: an implicit temporary table (for sorting or grouping rows, DISTINCT)
  • Using filesort: external sort to order results

August 20, 2011 Posted by | MySQL | Leave a comment

Obtain the table storage format

As per usual, on MySQL, we have several ways to find this out.

Requesting it directly via the SHOW TABLE STATUS command:

The Row_format value is the one we are interested on, but we could obtain that information from the INFORMATION_SCHEMA also:

August 3, 2011 Posted by | MySQL | Leave a comment

PROFILER and EXPLAIN

If we want to improve the performance of a query, these 2 guys can help us in order to find out bottlenecks, but remember the most important job comes before, designing appropriately the database.

Times described at
http://dev.mysql.com/doc/refman/5.5/en/general-thread-states.html

June 7, 2011 Posted by | MySQL | Leave a comment

InnoDB Isolation Levels, Multi-Versioning and Concurrency

When multiple clients run transactions concurrently, three problems that may result are dirty reads, non-repeatable reads and phantoms.

  • A dirty read is a read by one transaction of uncommitted changes made by another.
  • A non-repeatable read occurs when a transaction performs the same retrieval twice but gets different results each time.
  • A phantom is a row that appears where it was not visible before.

InnoDB implements 4 isolation levels that control the visibility of changes made by one transaction to other concurrently executing transactions:

  • READ UNCOMMITTED allows a transaction to see uncommitted changes made by other transactions. Dirty reads, non-repeatable reads and phantoms.
  • READ COMMITTED allows a transaction to see changes made by other transaction only if they’ve been committed. Non-repeatable reads and phantoms.
  • REPEATABLE READ ensures that a transaction issues the same SELECT twice, it gets the same result both times.
  • SERIALIZABLE completely isolates the effects of one transaction from others.

InnoDB operates by default in REPEATABLE READ mode. The isolation level can be setup at startup time:
[mysql]
transaction-isolation = READ-COMMITTED
But it can also be setup dynamically for a running server to different levels:
SET GLOBAL TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

June 6, 2011 Posted by | MySQL | Leave a comment

InnoDB Engine

  • Similar to the MyISAM and MERGE Engines seen so far. The interesting features appear with the disk management. Each InnoDB table is represented by and .frm format file as well as data and index storage in the InnoDB Tablespace. By default a single tablespace will be used by different tables.
  • Supports transactions with commit and rollback. It provides full ACID.
  • Provides auto-recovery after a crash of the MySQL server or the host where it runs.
  • Performance wise it seems better than the last 2 Engines, but here you can get deadlock.

June 2, 2011 Posted by | MySQL | Leave a comment

MERGE Engine

Basically, MERGE is a copy of MyISAM, allowing us to exceed the maximum MyISAM table size. MERGE will be slower to read indexes because MySQL has to search the indexes of multiple tables.

The book example:

I’m still unsure about the error I’ve got, due obviously NorthAndSouth is not a MyISAM table.

May 30, 2011 Posted by | MySQL | Leave a comment

Which Storage Engine is my table using?

There are different ways to find that out, below these 2 examples:

We could access the INFORMATION_SCHEMA and get all Engines being used at our database:

May 28, 2011 Posted by | MySQL | Leave a comment

MyISAM Engine

Main characteristics of the MyISAM Engine at MySQL:

  • On disk each table uses 3 files: City.frm (format), City.MYD (contents), City.MYI (index).
  • Has the most flexible AUTO_INCREMENT column handling.
  • Can be used to setup MERGE tables.
  • Can be converted into fast, compressed, read-only tables to save space.
  • Supports FULLTEXT searching and spatial data types.
  • Queries for MyISAM use table-level locking. Deadlock cannot occur, but performance can be reduced in environments with mix of read and write queries.
  • To workaround that high level of locking we can use a query modifier such as LOW_PRIORITY or HIGH_PRIORITY. Inserts into a table can be buffered on the server side until the table is ready by using INSERT DELAYED.
  • Storage format is portable.
  • Extras like specify the number of rows that you a MyISAM table should hold.
  • Loading data into an empty table, you can disable indexes. LOAD DATA INFILE does that already.
  • If you run out of disk space while adding rows to a MyISAM table, no error occurs. The server suspends the operation until the space becomes available.

So far it looks the most flexible, and that’s probably why is the default storage engine, the main cons would be the table-level locking in certain scenarios.

May 28, 2011 Posted by | MySQL | Leave a comment