Home     RSSRSS

Posts Tagged ‘MYSQL 5.5’

My SQL InnoDB Storage Engine

January 24, 2015 by kiranbadi1991 | Comments Off on My SQL InnoDB Storage Engine | Filed in Database, Environment, Performance Engineering, Small Businesses

MySQL comes with different level of support for InnoDB Storage engine for every version of its release. The Default configuration settings for InnoDB also varies version wise. So its always a wise decision to ensure that if we are upgrading the MySQL and are having InnoDB as default Storage engine, then it does no harm to revisit the configuration settings.

InnoDB is default general purpose storage engine and is recommended for all tables unless you have any special cases. MySQL Server has a pluggable storage engine architecture that enables storage engines to be loaded into and unloaded from a running MySQL server.

InnoDB has below features that separates it from other engine types,

  1. Row level Locking – blocks  reading or writing of table data by connections if another connection is currently using that data.(MyISAM has table level locks) .
  2. Acid ComplianceAtomicity means that if a transaction fails then the changes are rolled back and not committed. Consistency means that each successfully executed transaction will move the database ahead in time from one state to the next in a consistent manner without errors or data integrity issues. Isolation means that each transaction will see separate sets of data in time and not conflict with other transactional data access.Durability  ensures that any data that has been committed in a successful transaction will be written to disk in its final state, without the risk of data loss from errors or system failure, and will then be available to transactions that come in the future. So chances of transaction corruption is not possible or very small.All transactions are executed in isolations.
  3. Referential Integrity – It has ability to store data in multiple tables and maintain referential integrity and data consistency.

One can verify the type of engine used by MySQL by issuing below commands,(I have PhpMyAdmin and it looks something like below screenshot)

image

Also before creating any tables with InnoDB as engine, it makes sense to check transaction isolation levels. This can be checked with below command,

image

Transaction Isolation levels has of below options,

READ UNCOMMITTED -  Every select query operates without locks so you don’t get consistency and might contain dirt reads. So it violates ACID Principles and should never be used if your application issues transactions that require point-in-time consistent data reads.

READ COMMITTED – This setting offer consistent reads without table or row locks. Each consistent read, even within the same transaction, sets and reads its own fresh snapshot of point-in-time data.It offers  consistency and performance for applications that do not require full ACID.It does not fully comply with ACID.

REPEATABLE READ – The InnoDB default isolation level for ACID compliance.

SERIALIZABLE: This is the same as REPEATABLE READ but MySQL converts  select queries with the preface of LOCK IN SHARED MODE when auto-commit is enabled. If auto-commit is disabled then each select query is started in a separate transaction, which will ensure that all reads are consistent. This setting also allows for XA distributed transactions support, which you can read more about in the MySQL manual.The SERIALIZABLE value setting will impact database transaction execution performance, so only enable this if it is absolutely necessary.

Tags: , ,

How to Identify Slow Running SQL Query in MYSQL 5.5x

October 1, 2012 by kiranbadi1991 | Comments Off on How to Identify Slow Running SQL Query in MYSQL 5.5x | Filed in Database, Development, Environment, Performance Engineering, Testing

From past couple of days I have also been playing around with MYSQL 5.5X Database doing some bit of writing queries, creating tables, indexes ,routines here and there for one of project. MYSQL database seems to be bit easy to understand and provides almost all similar features as provided by MSSQL or Oracle. (Of course there might be some difference in the ways they are designed or in the way they use SQL).

As soon as someone reports that application is slow or during test if we find slowness, the find thing we need to do is to identify cause of slowness (Most people don’t do this step, they become defensive, at times even I have exhibited this behavior,its humanly approach). There could be many ways to identify the cause of slowness and there could be many reasons for this slowness. However for the sake of this post let’s assume that we have identified the slowness as MySQL database and we have ruled out other causes for this slowness.

In order to identify the slow running MySQL query, one can run the below command in MySQL workbench or via MySQL client and see whats going on in the MySQL box,

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.

mysql> show full processlist\G
*************************** 1. row ***************************
     Id: 1
   User: root
   Host: localhost:51606
    db: mydb
  Command: Sleep
   Time: 372
  State:
   Info: NULL
*************************** 2. row ***************************
     Id: 2
   User: root
   Host: localhost:51607
     db: mydb
Command: Query
   Time: 58
  State:Query
   Info: SELECT * FROM MYTABLE WHERE auto_id = 46102

 

As you can see from above that Select statement in itself is taking around 58 secs to execute.In addition to above,Show Process List command can also be used to get  insights as which threads are running in MySQL server and it is quite often used to debug connection issues.This link will provide more info about this command.

Once we know which SQL is taking more time, then the next task here is to replicate the issue outside the application using the same data and same statement but with using  MySQL client. Only when we are able replicate this issue outside application, we can say that issue is with SQL Query and not with any other elements of the environment or application.In almost all cases replication of issue happens successfully.(However do watch out for those smart and excellent communicator DBA, who often share the screen with businesses to show them that in spite of querying more rows of data, issue cannot be reproduced and query executes in fraction of eye blink,in such cases ensure that we use same set of data which is used in application during the time you saw slowness along with before and after row count for the table and also all condition remains the same.)

Moving on, once you are able to replicate the issue, the next step is to identify the Query plan generated by the query,in MySQL server, this can done  by using Explain Statement,

MySQL> EXPLAIN SELECT * FROM MYTABLE WHERE auto_id = 46102
           id: 1
  select_type: SIMPLE
        table: MYTABLE
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 47890
        Extra: Using where
 

In above query execution plan,any query that do not use an index signified by the key row above in the preceding output can be considered a poorly tuned SQL query. The number of rows read in evaluating this SQL statement,is as signified by the rows row,gives some indication to as how much data is read and can directly correlate to the amount of time required to execute the query. The type row with a value of ALL is also an indicator of a problem.

Adding the indexes to the table might help in these cases,but again it also depends a lot on the structure of the table, so before applying any fix to the situation, it makes more sense to understand the table structure and amount of the data the table holds,

Below command will give you the information about table structure,

SHOW CREATE TABLE ‘MYTABLE’ ;

The above statement will provide you the information about the table along with all column information.Once we understand the structure of the table it becomes quite easy to apply and try out various fixes.Below command will give you information about data length and other various table information

SHOW TABLE STATUS LIKE 'MYTABLE'

Both the above commands gives us very interesting information and this information can help in doing sizing of the databases along with capacity planning.

Once we have all these information, we can start working on applying fixes.Maybe after I fix some of my tables, I can write some more interesting things to do.

 

 

 
 

Tags: , , , ,