PHP Trivandrum

One of the great things about MySQL is the superior innovation model that’s used to deliver database server software. Rather than relying solely on internal engineers who create and maintain a piece of software (as in a traditional software company), MySQL partners with the millions of active users across the world who take advantage of the open source model and daily extend the MySQL server to do new and pioneering things. These innovations can then be submitted to MySQL AB, tested, validated, and rolled into the database server so everyone can benefit from the creativity of the very active MySQL community.

An example of this model in action is the recent release of a new SQL diagnostic facility that assists in the debugging and analysis of running SQL – the SQL Profiler. The new profiler became available in the 5.0.37 version of the MySQL Community Server and was created and submitted by Jeremy Cole of Proven Scaling (http://www.provenscaling.com/).

Let’s take a look at this new diagnostic utility Jeremy developed and see how it can help you create high-performing SQL code as well as assist in troubleshooting existing queries that aren’t providing the response times you’d like.

The Best Way to Diagnose Performance Problems

When it comes to overall performance, it’s important to remember that the #1 contributor is always a good database design. The second highest contributor to performance is well-tuned SQL code. Some try and switch the two in priority, but this is a mistake because a bad design has the potential to simply drown even the best-tuned SQL code (e.g. you can’t get index access in your SQL queries if the physical index design is poorly done). But make no mistake – bad SQL code that’s introduced into an otherwise well-running database can make a real train wreck of things.

So how do you go about analyzing database performance? There are three forms of performance analysis that are used to troubleshoot and tune database systems:

1. Bottleneck analysis – focuses on answering the questions: What is my database server waiting on; what is a user connection waiting on; what is a piece of SQL code waiting on?
2. Workload analysis – examines the server and who is logged on to determine the resource usage and activity of each.
3. Ratio-based analysis – utilizes a number of rule-of-thumb ratios to gauge performance of a database, user connection, or piece of code.

Of the three, bottleneck analysis is the most efficient approach in terms of fast problem resolution. By determining where the server, a user connection, or set of SQL code is spending its time, you can then work to eliminate the discovered blockages, increase throughput, and reduce overall response times. Unfortunately, this hasn’t always been easy in MySQL because the server hasn’t traditionally exposed the type of wait-based and resource statistics to quickly uncover bottlenecks.

But a better day has come along in MySQL 5.0.37 with the inclusion of the SQL Profiling utility. This interactive diagnostic aid allows a developer or DBA to gain insight into where a set of SQL code spends its time and the resources it is using. The best way to see the power of this new utility is to walk through some examples of how it’s used and see the clear benefits it supplies, so let’s do just that.

Robin Schumacher is MySQL’s Director of Product Management and has over 13 years of database experience in DB2, MySQL, Oracle, SQL Server and other database engines. Before joining MySQL, Robin was Vice President of Product Management at Embarcadero Technologies.

Read the full article at Mysql Developer Articles

Open PHP MyProfiler is just a trial to run query profiling on a php-mysql application, without changing the architecture too much. Any one who needs to do the analysis could make use of the same by downloading the profiler, and implementing with their code. The profiler would create logs depending on hostname and date. upload the profile logs to our Profile Sampler and you should be able to see the full profile of your application.

The Kerala’s favourite portal Keralaonline was built on top of the popular blogging and content managment system Wordpress. With about 30K hits per day, and 8000 entries across 100+ categories and video clips, the site started to slow down by about the end of March. This gave a spark, and the Open PHP MyProfiler, was built originally to run sql profiling on keralaonline.com.

General Articles, mysql

This one really hit me, but none would be much worried about these small things.

explain SELECT ban_ip, ban_userid, ban_email  FROM phpbb_banlist WHERE ban_ip IN ('5bcd7c03', '5bcd7cff', '5bcdffff', '5bffffff') OR ban_userid = -1;
+----+-------------+---------------+------+----------------+------+---------+------+------+-------------+
| id | select_type | table         | type | possible_keys  | key  | key_len | ref  | rows | Extra       |
+----+-------------+---------------+------+----------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | phpbb_banlist | ALL  | ban_ip_user_id | NULL | NULL    | NULL |  257 | Using where |
+----+-------------+---------------+------+----------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

mysql> show status like '%cost%';
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| Last_query_cost | 54.998609 |
+-----------------+-----------+
1 row in set (0.02 sec)

What was that, no indexes? well we should take a look, for forum like phpbb having so many users and loyal supporters, it should not be the case. Lets see the structure.

show create table phpbb_banlist;
CREATE TABLE `phpbb_banlist` (
  `ban_id` mediumint(8) unsigned NOT NULL auto_increment,
  `ban_userid` mediumint(8) NOT NULL default '0',
  `ban_ip` varchar(8) NOT NULL default '',
  `ban_email` varchar(255) default NULL,
  PRIMARY KEY  (`ban_id`),
  KEY `ban_ip_user_id` (`ban_ip`,`ban_userid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

Read more…

mysql

Hey, as of late, I had the opportunity to check the optimization for a heavily loaded phpbb system. It also shows lac of sql optimization in the code.

SELECT DISTINCT t.topic_id, t.topic_title, t.topic_poster, t.topic_first_post_id, t.forum_id, t.topic_replies, u.user_id, u.username, s.post_id, s.post_text
FROM phpbb_posts p, phpbb_topics t, phpbb_users u, phpbb_posts_text s
WHERE p.topic_id = t.topic_id AND t.topic_poster = u.user_id AND t.topic_first_post_id = s.post_id AND t.forum_id != 2 AND u.user_id != -1
ORDER BY p.post_time desc LIMIT 7;

This is one thing that is tough, and cannot be optimized without modifying the core code, and my exposure with the said script (phpbb) is too low that digging into the system will take many hours. I would recommend to design databases with the end user experience of much priority. For such things like `latest posts`, `latest news`, `new users` and other such things, keep a single table with almost the following structure

Read more…

mysql

The following is an excerpt from mysql slow log on a server which I was assigned to do MySQL tunning and optimizing. The following log was after the proper indexes were applied. Each query was taking about .9 seconds (query time is 0, because slow-query-time in mysql settings was 1, and that means logs will have an accuracy of seconds to the integer only)

# User@Host: articlec_bioeric[articlec_bioeric] @ localhost []
# Query_time: 0  Lock_time: 0  Rows_sent: 1  Rows_examined: 3
select a.*,p.pen_name from ahm_articles a,ahm_penname p where publish<>0 and publish<>2 and publish_date<='2009-01-24' and nick='1073534' or a.id='1073534' and a.author=p.id order by date desc limit 0,30;
# User@Host: articlec_bioeric[articlec_bioeric] @ localhost []
# Query_time: 0  Lock_time: 0  Rows_sent: 1  Rows_examined: 3
select a.*,p.pen_name from ahm_articles a,ahm_penname p where publish<>0 and publish<>2 and publish_date<='2009-01-24' and nick='1073534' or a.id='1073534' and a.author=p.id order by date desc limit 0,30;

Read more…

mysql

I have seen many web applications with poor database design. As of recent to increase performance, every body is utilizing some sort of caching. The primary one being query cache inside database server itself. Most of the developers are not aware of this, and do not make use of this. For example lets consider an article site or a blog application. The final element article or blog will need a view count, and adding a view or hit count to the same table is what 99% are doing. This is a field which should be separated from the semi static content. Keeping the count separate and never using this count table in joins will help cache the query. Also when the table is updated, index updates will be faster.

For showing top ten etc, it would be better to use a trigger to aggregate the top ten into another table. Also for aggregate pages like front portion etc, even better would be to create a single table which will hold information from different parts of the system which will be updated using triggers, and configuration directives in db. Then the page building code will need to just read out the aggregated data and merge it into the template to output the page.

mysql

At several stages, I have been asked to benchmark web applications. I always tend to do the light weight stuff at the start, and when it gets into my mind, that the system which is being tested is working some what like it is expected, I steer my course and go for bigger feats. For instance the RatingHQ, was run with a test of hundred thousand registered users and about the same amount of items in each of the sections before releasing for public beta.

Having said the above, the most important matter is that seldom do the programmer check his / her code more than the unit or functional tests. And code thus created would crumple when subjected to higher loads. Several times I have seen code written which simply works, but will break, and take the server that hosts it too down to it knees.
Read more…

mysql

On the article at php|architect, Ligaya Turmelle explains you how to handle Foreign Keys in MySQL so they can serve your whims.

No, foreign keys aren’t from Brazil or Italy or even the US. Though they can be a bit strange to those who do not understand them, have no fear. We are here to teach you how to talk to them so they can serve your whims. So what are foreign keys exactly?

Not to put too fine a point on it, they are what make a relational database “relational.” They are the links between tables that keeps everything connected. They are what allows you to put a customer in one table and their order in another and the products they are ordering in a third table so the database has minimal data redundancies.

She continues with the article with a good example. Read it

Tips, mysql

A couple of years back, I had asked several people through groups, and posts the possibility of backing up a MySQL database programatically, from client side applications which could connect to remote servers running MySQL. There was a lot of suggestions,

* php script on server to take backup on server
* application code to select the data and do a round trip
* mysqldump from client machine with the server parameters

And finally from all those valuable suggestions from all the helpful people, I derived my own hybrid method which I would summarize in a few words. This is a method, which I have adopted for web applications in php as well as custom single machine applications in Visual Basic. How this can be termed as hybrid can be known by going into the structure, though it seems to be a bit complicated, it would serve the purpose and be worth the headache.

Read more…

General Articles, mysql

Designing a database as part of a big project, will need a lot of experience. There are several factors which should be considered before finalizing a database schema. Since once the project starts rollout, there is no going back, other than adding a new index, or adding another field, any major change will affect the whole code base.

Indexing is the most important method you should try first for speeding up queries. Other techniques are  also available, but generally the one thing that makes the most difference is the proper use of indexes. On the MySQL mailing list, people often ask for help in making a query run faster. In a surprisingly large number of cases, there are no indexes on the tables in question, and adding indexes often solves the problem immediately. It doesn’t always work like that, because optimization isn’t always simple. Nevertheless, if you don’t use indexes, in many cases you’re just wasting your time trying to improve performance by other means. Use indexing first to get the biggest performance boost and then see what other techniques might be helpful.

One should take absolute care to optimize the fields by keeping in mind, that larger the indexes, slower the operations. The most common example of a mistake is to add a timestamp field to select the data in chronological order. Now if the total rows is just a handful, this wont be a problem atall, but if the total rows is millions, the timestamp field may have a cardinality of say 50% of the total rows, making the index very huge, and operations slow. But if instead of the timestamp, the data is stored in two fields with date in one, and time is another the cardinality of time will be 86400, ie the no of seconds in a day, and the cardinality of date will also be under control, making the operations faster by an appreciable amount. For varchar fields, creating partial indexes instead of full index helps, reduce the size of the index file, and hence give the server breathing space.

mysql

Hmm.. yes.. I literally meant it.. for several things you can directly use SQL calculations, though the queries and functions shown here are tested only with mysql, it should work with most other sql servers too. The cliche is if you have the sql server on the same server where your webserver and php interpreter is running. In simple terms, if you connect to mysql on localhost, sql can be used to a great extend to do calculations.

Some time back I did read on some other site, where two dates where calculated for the current month, using php mktime and date functions, to be passed in an sql query, for a range selection. I am not for an argument, but it seems more efficient to use the date calculations directly in the sql query, than pre calculating this unless otherwise it is needed to be printed in the output. Even then, the calculation can be done by the sql, and the result returned.

SQL calculations along with SQL session variables, will help a programmer achieve a very high optimization level for the scripts, thereby reducing webserver, and interpreter load.

Date calculations can use date_format, date_sub, unix_timestamp and from_unixtime and other related functions. Aggregates has all complimentary functions like sum, abs and others. By using a combination of these, any calculation can be achieved by sending an sql query.

PHP, mysql