MySQL Best Practices Video Tutorial Download

Valuable Guidelines For Performance Tuning Of MySQL Database Server

Any dynamically served website with enormous traffic and visitors normally depends on a strong and powerful Database Management System (DBMS) for storing data which can be reclaimed or retrieved by web servers in order to build web pages for the website visitors to read.

MySQL is one such Database Management System that is highly popular and widely used. Every time a visitor visits a website, the MySQL DB server is accessed at least once by the web server so as to assemble and display the web page to the reader.

It is very important that the MySQL server gives a superior performance which in turn improves the speed of the website, especially if the web host is loaded with traffic. Therefore, it is essential to learn the skill of performance enhancement of MySQL database server.

During one of the Google TechTalks, Jay Pipes who is the Relations Manager of MySQL Community for North America, gave a talk on the finest techniques and practices which can be implemented for enhancing MySQL server performance.

Jay Pipes is also the co-author of Pro MySQL which was published in 2005 by Apress. Pro MySQL comes with all the latest features of MySQL 5 along with a thorough analysis and discussion of MySQL server architecture, transaction processing, storage engines, benchmarking and other scenarios of advanced SQL.

Even though the talk by Jay Pipes is an old one, it definitely provides some valuable information on MySQL performance tuning. In the talk, Jay Pipes has spoken about the key areas that you need to focus on while fine-tuning the performance of database servers and applications.

He has also explained how webmasters or database administrators can get the best results by applying some useful intermediate and complex MySQL skills.

The topics discussed in the talk are mentioned as follows:

Core Concepts of Benchmarking and Profiling – Benchmarking provides you with a track record. While benchmarking, change just one thing at a time and re-run the benchmarks in order to efficiently keep a track of the outcome of the change made. Record everything related to the benchmarks including the configuration files, information on OS, hardware, MySQL etc.

Particularly with MySQL, remember to disable query cache every time you run the benchmarks. Profiling helps in diagnosing a running system. Get familiar with the access types and make use of Slow query Log and mysqldumpslow. Make use of mytop in order to catch long-running queries.

Common Issues/ Problems with Performance – The various sources of problems include poor indexing choices, inefficient schema design, improper coding practices, improper tuning of server variables and hardware or/and network bottlenecks. These issues need to be properly focused on and resolved.

Schema issues can be resolved and faster performance can be achieved by using smaller data types, narrower index records, more records per block and fewer reads. Making use of proper indexing (i.e. covering index opportunities, proper selection of index fields, removing redundant indexes, etc) plays an important role in performance enhancing.

Following proper coding guidelines (i.e. using stored procedures, using counter tables and calculated fields, using joins, eliminating correlated sub queries etc) is equally necessary.

Tuning of Server Parameters – Tuning of server parameters provides a speedy solution but a temporary one. Having awareness about global and per thread parameters is very much necessary. Try making small changes at a time and test to check the effect. Turn the query cache on and give it a size. Within the configuration files, set a size for “query_cache_size”.

Maximum memory has to be allocated for saving data and index pages. Memory is an affordable, fastest and easiest way to enhance MySQL performance.

You can watch the Video here:

If you are using Firefox, then you can download the video with the help of this extension.

Free, Open Source Reporting Tool With Drag And Drop GUI

DataVision is a fantastic reporting tool which works with almost all databases in the world such as MySql, Oracle and Access to name a few. The generated reports can be easily customized by using the drag and drop tool which is part of an extremely friendly user interface. The reports can be exported to any of your favorite formats such as PDF, HTML, XML, CSV etc.

Several advanced features add to the attraction of this reporting tool. You can have visual table linker and can also tightly link your applications with DataVision. It is also possible to create sub reports using this tool.

Since Java is the underlying language used for creating DataVision, the tool is compatible with almost every platform in the world. It can be connected to any database with a JDBC driver. Apart from the major ones mentioned before it also works very well with Informix, PostgreSQL, Progress and many others.

DataVision can also generate reports from data files in the text format. XML format is used to store the report descriptions which facilitate the usage of any of your favorite text editor for editing the reports.

DataVision has a variety of options available for formatting the reports such as page headers, group headers, footers, various font formats and colors, formulas and the facility for defining run time parameters while the report is being generated.

[ Download Data Vision ]

Free, Powerful Client For MySQL – HeidiSQL Earstwhile MySQL-Front

HeidiSQL is a fantastic tool for controlling MySQL databases. The tool is simple to operate, easy to use and is open source software which means that it does not burn a hole in your pocket. It can be downloaded, installed and configured with minimal effort and in good speed. On the left side of the screen you have the hierarchy of the menus through which you can easily move through different database objects.

The selected database objects can be controlled through the options in the main window. You can also access the data in the selected tables using the tab assigned to data. You also can have the grid view of the entire table with a dedicated BLOB viewer displaying the BLOB fields separately at the bottom of the screen.

HeidiSQL has an excellent query editor which makes working with the databases an easy task by giving suggestions on the field and helping the completion of the query. This reduces typographical and other errors drastically and increases the efficiency of the tool usage. Other features include user management and export and import of databases,

The tool has a user friendly interface which is a boon for those who are developing web applications using MySQL database. The task of browsing and managing the databases and tables is made easy by the adoption of HeidiSQL.

[ Download HeidiSQL ]

Software To Run Apache From USB Without Installation Under Windows

The only requisite to host a website on your system is a web server. The most popular web servers optimized by users are IIS and Tomcats. However, as observed, all the systems do not support the above web servers. Some systems might have inbuilt features to support web servers, but they are present in a disabled state. WOS Portable can help users host web servers in their system regardless of their compatibility with web servers. WOS Portable is a multi-utility portable web server that helps users host web servers and download any site on their system.

WOS Portable is an efficient modular web server that allows you to run web servers on your system using a USB stick. In other words, this application is free software which can be easily downloaded in your USB pen drive or any other removable media like hard drive, flash cards etc. Then connect this removable media to your system to run this application. Open the web window and start surfing the net. To access this application you do not need to download any additional software or plug-ins. The software is highly compatible with Window 98 and its advanced versions as well as Windows Vista.

WOS Portable is now called MoWeS Portable, meaning Modular Web server system. With this application, you can easily run varied web servers ranging from Apache, MySQL as well as PHP. The application is available for free to all the users. It has been released under GNU / GPL and is loaded with pre-installed software like PO3, Mambo, Joomla, Moodle, WordPress, Drupal, OS Commerce, PHPMyAdmin etc.

[ Download WOS Portable ]