Home     RSSRSS

Archives: Small Businesses

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: , ,

Transaction Response time–First Indicator of Application Performance

February 14, 2014 by kiranbadi1991 | Comments Off on Transaction Response time–First Indicator of Application Performance | Filed in Performance Center, Performance Engineering, Performance Test Tools, Quality, SilkPerformer, Small Businesses, Web Server

What do you mean by Transaction ?

What do you mean by Transaction Response time ?

Why is the Transaction Response time a key Performance Indicator for the application or system ?

Lot of young people who wants to become Performance Engineer asks me about these types of questions. Seriously I believe Transaction is often associated with the context.For Database DBA it could be something like commit the output of the SQL statement to the DB or rollback the entire output of the SQL Statement and bringing the DBA to its initial state due to some failures.For Developer, it could be related to 1 business requirement equal to 1 transaction and to the banker or domain specialist, it could be one entry in the ledger log.For the prospective of Performance Engineer, it could means any of the 3. It just depends on what is your objective of your tests or what are we looking for measure in the system or application.

The way Performance Engineer sees the transaction is bit different than the way Developer sees them.Some of the examples of the Transaction as seen by Performance Engineer are,

  • Time taken to do the login of the application
  • Time taken to generate and publish the report to the users.
  • Time taken to load the data to the database or Time taken to process xx amount of data and load it into the database(Batch Jobs).

If you look closely at above example, you can see that transaction is always associated with Time. Time taken to do X activity is prime focus of the Performance Engineer.The same might not be true for other folks like DBA,Domain experts or Developers.

One of the reasons as why Performance Engineer always associates time to transaction is because most performance tools have taught us to see transaction this way.Wrap the activity/event/Business functionality between start and end transaction markers and calculate the difference between these start and end time and say that these are our transaction response time for that activity.Most Load testing tools works this way and this logic works for almost 99.9999% of the application.However this kind of logic does not gel well with few of the technologies where non blocking of UI is more preferable than blocking of the UI. Comet/Push are some of the technologies where Marker based transactions do not work favorably unless you do some kind of the hacking.So I believe that Transaction marker based solutions work only for technologies where users waits for the response to come back.

Transaction Response time is most important characteristic of the System Performance from the Users point of view.If they are satisfied with the speed of the system to do more work in short time, then probably no effort is required to speed up the system, else lot of effort is required to increase the speed. Sometimes users are also satisfied with bit of high response time because they know the there is lot analytical calculation program/application has to do to bring data back to them.However when as more and more users starts getting into the system , they start experiencing slowness of the application.Application starts to more time to respond to the users request and the functionality which was taking 3 to 5 sec now starts to give response in 5 to 8 sec.As the user load increases, response time also starts to increase and after a certain point, it fails to respond to the users request.Performance Engineers calls this behavior a breaking point of the application.The reason for the application to stop responding could be many reasons ,however from the performance engineering’s prospective,its suggested that we do bit of analysis based on queuing theory.Rate at which application is receiving requests is more than the rate at which it can serve the requests keeping in mind the environmental constraints.Response time degradation can  happen not only when the number of users or volume of data exceeds some thresholds, but also after the application is up and running for a particular period. A period can be as short as hours or as long as days or weeks. That is often an indication that
some system resources are not released after transactions are completed
and there is a scarcity of remaining resources to allocate to new transactions.
This is called  resource leak  and is  usually due to programming/coding defects.

We say application is having performance issues after analyzing the trend of transaction and can conclude the below points based on data we have collected

    • Transaction response time is getting longer as more users are actively
      working with the system
    • Transaction response time is getting longer as more data are loaded
      into application databases.
    • Transaction response time is getting longer over time even for the same
      number of users or data volume primarily due to leaking of resources like memory,connections etc..

Transaction response time matters a lot when your application is web based application and its public facing site. The revenue of the company depends on how fast the application responds to the user’s request. If your application is internal , then it increases the productivity of the team and company over all.

Tags: , ,

Checklist for Troubleshooting Web Application in Internet

March 6, 2013 by kiranbadi1991 | Comments Off on Checklist for Troubleshooting Web Application in Internet | Filed in Browser, Development, Environment, Quality, Small Businesses

One of the hard things of troubleshooting the production issues is to identify the root cause of the issue. When the application is deployed in the open internet and accessed by the variety of the browsers, and has large number of hops in the network, it becomes quite a challenging task. So in this post I will walk you through the list of things which needs to be checked at high level on client side in order to identify the root cause of the issue and before proceeding to do the review of the code base.

Browser Setting

  • What is the proxy setting of the browser, does it access the application via proxy or connection is directly to the internet.
  • How is browser configured? Does the user have administrative rights or he has basic rights on the browser settings.
  • Is the browser configured to show user friendly messages? If Yes than can we reproduce the issue by removing those user friendly messages and just displaying the actual message which application is throwing.(Please note if your application does not do proper error handling, there exists a risk that you are displaying nasty code to the users. E.g. Famous yellow screens of the .net)
  • Is the browser configured to run in standard mode or compatibility mode? This point applies to IE. (Ajax and UI Issues are most often related to compatibility mode)
  • In case the issue is related to certificate, then checking whether the relevant certificate is present in trusted store often helps.
  • If your application uses popup windows to display some information, then checking if there are any add on or setting in browsers which is blocking pop ups also helps. Some browser add on silently block pop ups without giving any information to the users.
  • Is the browser setting on default? These are the setting which is factory default. One of the easiest ways to troubleshoot issues is reset the browsers to default setting.

Client Computer Settings

  • Is the client computer behind the firewall? If yes than verifying that it’s correctly configured saves lot of time.
  • Checking the antivirus software installed on the client machine also helps. Sometimes in case where your application uses specials character, there exists a chance that badly configured antivirus might filter out or block the incoming responses.
  • Hardware and software configuration of the user’s machine. In case if your application does lot of heavy lifting on the client side, then it often helps to educate the users that minimum configuration needs to be met.

Network Infrastructure and configuration

  • Is the network correctly configured? Using the bidirectional ping command often helps to identify the network issues.
  • Is the client able to resolve the application host name correctly?
  • Checking how many hops the user needs to make to connect to the server also helps
  • In increasing user experiences. General Thumb rule I often use is more the hops user does to connect to the server, more the response time he is going to get.
  • Is there any load balancer or firewall between the server and client? If yes than checking if they are correctly configured also helps.

User access or Login Issues

  • Is the user giving the right credentials and if yes then checking if server is doing correct validation of credentials also helps.
  • Is the identity and access validation done by application or by third party component like Site minder? If by third party, then checking the third party component in isolation often helps.
  • Does the user have appropriate access level to access the resources? If yes then further troubleshooting is required. Else its waste of time.
  • If browser is client, then disabling friendly error message settings in browser will reduce the time to identify the issue by almost 50% since no extra debugging tools are required unless the case if of missing or stolen headers.

Tags: , , ,