MySQL Database Performance Tuning Best Practices Video Tutorial

MySQL is one of the most popular database management system that powers the most dynamically served website , including sites with millions traffic and visitors everyday. Many web pages and web applications or services rely on MySQL to store contents data which will be retrieved and read by web servers to dynamically built the web pages that visitors will read.

As each visit will mean that web server is required to access the MySQL database server at least once to assemble, build and serve the page to reader, so the performance of MySQL server is very important and essential to the overall speed of website, especially on the busy and heavy loaded web host. To improve the performance of MySQL databases, optimize, or know how to optimize MySQL is a needed skill.

Jay Pipes had given a talk during Google TechTalks in April 28, 2006 on best practices and techniques for performance tuning for MySQL DB server. Jay Pipes is a MySQL Community Relations Manager for North America, and co-author of the Pro MySQL published by Apress in 2005, which covers all of the newest MySQL 5 features, as well as in-depth discussion and analysis of the MySQL server architecture, storage engines, transaction procesing, benchmarking, and advanced SQL scenarios.

Although the talk is a little bit old, but it’s definitely information with over 40 minutes of talk, and the same principles applied on performance tuning on MySQL. Inside the talk, which was recorded in video clip embedded below, Jay Pipes shared on where to best focus your attention when tuning the performance of your applications and database servers, and how to effectively find the “low hanging fruit” on the tree of bottlenecks, which are useful intermediate and advanced MySQL skills for database administrators or webmasters.

Some techniques for tuning performance on MySQL with examples shown in the video tutorial are core concepts of profiling and benchmarking, debugging and analyzing common performance problems or issues, best practices for table and index design, coding guidelines, query and join operations, and server variable parameters adjustments and tuning.


Advertisements

Download Free Watermark Image To Watermark or Add Logo to Photos In Batch

Adding watermark or logo to the images is one of the common ways to protect your images or pictures from being easily distributed around or copied by others. Here is a watermark image program which called as Watermark Image Software. The watermark image program allows the users to add digital watermark to their favorite images, photos and pictures in a batch.

watermark-image

The program is featured with the option for users to resize the images before the digital watermark is added. The users can insert any text and image watermarks into the pictures.

Watermark Image Software includes the following features:

  • Able to Watermark digital images, pictures and photos.
  • Resize images while watermarking the image.
  • Supports multi threaded.
  • Maintain directory structure in destination directory or combine all watermarked images in one directory.
  • Skip or overwrite existing watermarked images in destination directory.
  • Automatically check for new version of watermark image.

It is available as a free download from here. This tiny program does not require any installation, therefore the user’s just need to extract ZIP archive to any folder and double click on WatermarkImage.exe to execute it.

Get next auto increment value using mysql

Recently, I needed to get the auto increment value for the next inserted row in a MySQL table. Generally, this is not a good idea and you should stop reading right now. You may end up with duplicate entries in your application, which is not a good thing. At a first glance you may think: “I can get the top id value from my table, add 1 and that’s all, I got the next auto_increment value I need.”

Something like this:

1. $query = mysql_query(SELECT MAX(id) as max_id FROM tablename);
2. $row = mysql_fetch_array($query);
3. $next_id = $row[‘max_id’] + 1;

Using the above example, you got the current max auto_increment id plus 1. As you may already notice, this is wrong. Auto increment values use unique ids. Imagine the following table:

1. Id country
————-
2. 1 Italy
3. 2 Germany
4. 3 Greece
5. 4 Turkey

If you delete the record 4 and run the above script, you will get “4” as next_id. MySQL will use “5” as next_id.

So, I looked all over the web for a solution, and found several similar scenarios described, but no answers. In fact, I came across several forums and help sites where it was actually stated as fact that you CANNOT get the next auto_increment. I knew this to be false, because this information is displayed in the table structure view of phpMyAdmin. So I scoured the source code of phpMyAdmin, and after many desperate combinations of GREPs, FINDs, and XARGSs, I finally came up with this  (assumes we’re already connected to the dB) solution is to use the “SHOW TABLE STATUS” query.

1. $query = mysql_query(“SHOW TABLE STATUS LIKE ‘$ tablename'”);
2. $row = mysql_fetch_array($query);
3. $next_id = $row[‘Auto_increment’] ;

“SHOW TABLE STATUS” produces a two-dimensional array with one row for each table, and a slew of columns: Name, Type, Row_format, Rows, Avg_row_length, Data_length, Max_data_length, Index_length, Data_free, Auto_increment, etc.

Author: Leo and Jamie

Solve PHP Fatal error: Allowed memory size of 8388608 bytes exhausted (tried…

This error message can spring up in a previously functional PHP script when the memory requirements exceed the default 8MB limit. Don’t fret, though, because this is an easy problem to overcome.

To change the memory limit for one specific script by including a line such as this at the top of the script:

ini_set(“memory_limit”,”12M”);

The 12M sets the limit to 12 megabytes (12582912 bytes). If this doesn’t work, keep increasing the memory limit until your script fits or your server squeals for mercy.

You can also make this change permanently for all PHP scripts running on the server by adding a line like this to the server’s php.ini file:

memory_limit = 12M

Keep in mind that a huge memory limit is a poor substitute for good coding. A poorly written script may inefficiently squander memory which can cause severe problems for frequently executed scripts. However, some applications are run infrequently and require lots of memory like importing and processing a big data file.

Still if you have problem, then delete the cache file or write a condition to delete the cache file, when the size exceed max memory limit.

Author: Rex

Determine Whether JavaScript Is Enabled/Disabled Via PHP

Recently i was working on a project where there is a need to determine whether JavaScript is enabled or was disabled by the user. Depending whether the JavaScript is enable or not, the system will rely on JavaScript operation if it does and PHP operation if it doesn’t. The fundamental solution to this is to detect whether JavaScript is enable before the system can determine which approach can be used. However, there is no easy solution to determine whether a client scripting is enable in a server scripting language (PHP) without finish loading the page! Therefore, in this article we will discuss whether there is such possibility to use PHP to determine whether JavaScript is enabled for your web application.

The Problem

The main problem is that a server script language can never be able to determine whether a client script language is available as the server script language will always run first. Furthermore, the client script is always run on the client side and never executed on the server side. Therefore, when the server scripting is running at the server side and send to the client for display, the server scripting language will have no idea what is going on with the client environment. Hence, strictly speaking will be unable to determine JavaScript is enable or disable.

The Solution

Although it sounds impossible for server side to determine whether a client scripting is available such as JavaScript but certain tricks can be perform in order to achieve this. However, it won’t be a convenient one. Recall that every web system should have a redirect index.php page to prevent our code from showing in plaintext if anything happen? We can use that page to determine whether javascript is enable by writing a script to either append a value and post over to the next page or a better alternative is to store it into the user cookie. If you store a value and post it to the next page, the validation can only occur within the main page. However, if you utilize cookie to determine whether JavaScript is available, you can always use php to determine whether that cookie value is available. If it is not available (they delete their browser cookie on the way) you can redirect that user to the index.php to revalidate JavaScript is enable. Once it is being verify, you will just show a message to the user after index.php has redirect or run on pure php.

On the index.php script, it will be something like this,

01.<script type='javascript/text'>
02.function createCookie(name,value,days) {
03. if (days) {
04. var date = new Date();
05. date.setTime(date.getTime()+(days*24*60*60*1000));
06. var expires = "; expires="+date.toGMTString();
07. }
08. else var expires = "";
09. document.cookie = name+"="+value+expires+"; path=/";
10.}
11.createCookie('verify_cookie', 'Y', 1);
12.</script>
13.<meta http-equiv="refresh" content="2;url=main.php?c=1">

We have a function that help us to create a cookie if JavaScript available. Once this is done, we redirect the user to main.php where our real page is located with a get value of c=1. This value is needed to avoid recursive request. We can’t use PHP header function because it will redirect before JavaScript has the opportunity to run and the code should be placed before the head tag to make this valid. On all other pages we will have something like this before the header.

01.<?php
02. //filter the global variable first.
03. if(!isset($_COOKIE['verify_cookie']) && $_GET['c'] == 1){
04. echo 'JavaScript is disable';
05. }else if(!isset($_COOKIE['verify_cookie'])){
06. //perform check to determine whether the cookie expire OR it really was disabled.
07. header('location: index.php');
08. }else{
09. //perform another check on javascript similar to index.php if you afraid that the cookie exist but javascript was disabled.
10. }
11.?>

The above is to verify whether javascript exist in each page and use to run either pure php or combination with JavaScript as these script can be imported using PHP if needed. The solution above can be use as a references and not necessary a solid solution.

Alternative Solution

The alternative solution to this is to use the noscript tag which is very simple and make your life a better place to live in.

1.<script>
2.document.getElementsByTagName('body')[0].innerHTML = 'JavaScript is enable.';
3.<script>
4.<noscript>
5.JavaScript is disabled.
6.<noscript>

Conclusion

Many will turn to noscript tag that can really ease and simplify the way we code. However, for some system which required to determine whether script is enabled for different server script to run. This might help those that are doing such approach as noscript tag will only run after the server has processed its information. On the other hand, you can combine this approach with the no tag approach to better validate your logic.

Author: Clay Lua

« Older entries