database

If you’re serious about web performance optimization, then you must run an efficient database. One necessary skill is an ability to analyze a database slow query log and optimize the most expensive, slowest results. While one relatively slow query may not be the end of the world, many slow queries will add up quickly and frustrate users.

Setting Up a Slow Query Log

Running a slow query log is not a default setting in MySQL, so the first thing you’ll need to do is set one up. Open the server’s configuration file and enter “log-slow-queries” as well as an output file. On the next line, enter “long_query_time=x”. The value x represents a certain number of seconds, and any query taking longer than that number will appear in the output log.

Although needs will vary, a value of 1 is generally recognized as a good starting point. For heavily used servers, a value of 1 may result in a slow query log which is too large, creating additional performance problems.

In addition, you may want add a third line which reads “log-queries-not-using-indexes”. (Evidently the creators of MySQL did not realize that the plural of “index” is actually “indices.”) This will instruct MySQL to log any query which does not use an index, whether or not the query time exceeds the value listed on the second line. If you only want to log queries not using indices, you can set the value on the second line very high.

Test data is needed for any quality application that goes through a quality analysis department. Test data is important for any development firm in that it provides a method for quality analysis to manipulate, change, and even destroy otherwise sensitive data. Test data needs to be openly available to internal testing departments so that they can do heavy testing. Their job is to try to destroy your application (although it sounds harsh, it is better for them to find the bug rather than a customer).

Ethical Aspects

Whenever you have a large scale database with multiple tables and numerous requests from users, building tables with perfect indexing can be the difference between great performance or angry users with time outs. Table indexing is often ignored while designing an application. It is usually left to the database administrators to pick up the pieces after an application is promoted and poor indexing causes performance issues. As a great developer, you can avoid the pitfalls of poor table indexing and create a tuned application even on the database backend.

Primary Keys

Table design is one of those little idiosyncrasies that developers rarely follow, but the best table design can create the best scalability and performance value for a company application rather than worry about it when problems arise.

For most developers, worrying about optimization when writing SQL queries is not an issue until performance becomes a problem with an application. Although SQL query optimization is important, it can be seen as a tedious process that some programmers fail to follow thoroughly. Some best practices can be observed during the development process that will help maximize performance for future application scalability.

Use SELECT Instead of Using SELECT *

SQL Server performance tuning is usually left until problems arise. Most of the time developers and even database administrators only focus on the tuning elements of their MSSQL servers when performance starts to become slow and adversely affects users. Although the typical reason for slow issues is configuration or development issues (poor stored procedure design), occasionally hardware can be an issue.

One of the biggest issues a database administrator deals with is the impending performance issues that seem to hit an expanding environment. Smaller businesses can usually handle the simple problems that may interrupt daily activities, but large or medium sized businesses need real solutions to quickly fix problems since performance issues equal lower revenue.

If you are starting a Forex company, then it is no secret that you need to have a finely tuned database for efficient trades for your customers. Forex is the trade of different currencies. It is not a widely known method of trading, but for someone who knows how to trade in Forex it is an extremely profitable outlet.

Microsoft SQL Server 2000 and Microsoft SQL Server 2005 do not inherently support load balancing out of the box like most information technology experts might think. They have intrinsic tools that allow for scalability and load balancing, however, they do not have the defined load balancing functions that are commonly known among network administrators. Instead, Microsoft SQL Server 2005 comes with some tools to help with scalability and performance for your enterprise organization.

Microsoft Clustering Services (MSCS)

At the Web 2.0 Expo, Geir Magnusson delivered his prediction of the upcoming demise of relational systems: “The Sequel to SQL: Why You Won’t Find Your RDBMS in the Clouds.” This reminds me of the old saying that the rumors of my death are greatly exaggerated.