Category Archives: Performance Tuning

Day 25 Optimization

Locking is depends upon Isolation level and Storage Engine. MySQL uses table level locking (instead of page, row, or column locking) for all storage engines except InnoDB, which uses row level locking locking system in MySQL: Implicit Locking: To maintain the … Continue reading

Posted in DeadLock, Lock/Blocking, MySQL, Others, Performance Tuning, What I learned today | Tagged , , , | Leave a comment

Day 22 Other Storage Engines

So far we have discussed InnoDB, MyIASM and NDB Storage engine which are most standard/common and important storage engine MySQL has, there are some other Storage engine which are specific to the business requirement and used only for their special … Continue reading

Posted in MySQL, Others, Performance Tuning | Tagged , | Leave a comment

Day 15 Server Logs

There are 6 types of logs available for MySQL environment, Logs are important information for DBA to troubleshoot or understand the system. MySQL track the information in those logs. Following are the list of logs: Error Log (– start/stop/errors) Query … Continue reading

Posted in Error Log, MySQL, Others, Performance Tuning, Transaction Log, Troubleshooting | Tagged , , | Leave a comment

Day 14 MySQL sys Schema

MySQL 5.7.7 and higher includes the sys schema, This has View, Stored Procedures and Functions to gather the performance related information, as it works on Performance Schema the Performance Schema must be enabled for the sys database. Following some of the important tables: sys_config: … Continue reading

Posted in MySQL, Others, Performance Tuning, What I learned today | Tagged , , , | Leave a comment

Day 13 MySQL Performance_Schema

performace_schema is a dynamic database created when you restart the mysql, all the data from performance_schema database will be clear. This database has its own storage engine –“performance_schema” Even you may have this database but it could be possible that … Continue reading

Posted in MySQL, Others, Performance Tuning, What I learned today | Tagged , , , , | Leave a comment

TempDB Contention 2.1.%

Problem: When you are performing many DML operations and makes tempdb contesnion on it, You may get wait_resource of  2.1.103 check perfomance counter: Temp Tables Creation Rate Temp Tables For Destruction When you are performing many DML operations and makes … Continue reading

Posted in DB Design, DMV, Internal, Lock/Blocking, Performance Tuning, TempDB, Troubleshooting | Leave a comment

DBCC Cleaning CACHE – Memory

DBCC Cleaning CACHE – Memory I wanted to write this blog for quite some time, here you go, as you know at sql everything is on memory, memory information retrieve quickly compare to Disk. So if things are not in … Continue reading

Posted in Memory, Performance Tuning, Troubleshooting | Tagged , , , , | Leave a comment

Denali – Day 27: System Center Advisor (aka ‘Atlanta’)

Denali – Day 27: System Center Advisor (aka ‘Atlanta’) As Microsoft is working more on Cloud base system, on that basis Microsoft has introduced a Tool for sql server called “System Center Advisor – SCA – aka Atlanta. Its internet … Continue reading

Posted in Denali, Performance Tuning, Tool | Tagged | Leave a comment

Denali — Day 9: Additional Scalability Features

Denali – Day 9: Additional Scalability Features 15,000 Partitions earlier it was 1000 For the enterprise system having huge amount of data to achieve the best performance for huge data developer requires more partition to distribute the data and manage … Continue reading

Posted in Denali, Performance Tuning | Tagged , | Leave a comment

Denali — Day 5: Column-store indexes (aka Project Apollo)

Denali – Day 5: Column-store indexes (aka Project Apollo)   There is another great achievement for Denali, especially for Data warehouse, where data is static and very huge – READONLY, and user always require data to be retrieved quickly as … Continue reading

Posted in Denali, Index, Performance Tuning | Tagged , , | Leave a comment