Migration from MySQL to MSSQL our solutions

Last post I did had all of my feelings, and despair, and now I am sure those were out of sheer negligence and mostly because I was unaware of the MS SQL product. Though I still do not agree with certain things like the row_count workaround for the limit feature, and escaping of quotes. But for our project we could more or less maintain a streamline by using the same code base for MySQL, SQLite3 and MSSQL 2005. Will try to explain this across a couple of posts.

Continue reading “Migration from MySQL to MSSQL our solutions”

Binary tree in MySQL for MLM

Recently for a student, I was asked to explain the design considerations of a Binary Tree which was to be used in an MLM solution. About 10 years back it was a nightmare, and in my career, I was lucky to get that privilage for more than a dozen times with varying schemes and structures. But now with MySQL having procedures, and functions, the tree design and related functions were a breze. Reproducing it here for future reference. This is in no way a complete solution, but just bits and pieces which may even be discarded as crap.

CREATE TABLE  `binTree` (
  `nodeid` int(10) unsigned NOT NULL auto_increment,
  `lnode` int(10) unsigned NOT NULL default '0',
  `rnode` int(10) unsigned NOT NULL default '0',
  `pnode` int(10) unsigned NOT NULL default '0',
  `pside` enum('l','r') NOT NULL default 'l',
  `tLevel` int(10) unsigned NOT NULL default '1',
  PRIMARY KEY  (`nodeid`),
  KEY `parent` (`pnode`),
  KEY `treelevel` (`tLevel`),
  KEY `lside` (`lnode`),
  KEY `rside` (`rnode`)

The above is the basic structure of the tree table, and some complementary functions and procedures are accompanied to make the usage simple, otherwise would be a herculian task for the developer to do the same.
Continue reading “Binary tree in MySQL for MLM”

Invoke shell from MySQL trigger

No I am not insane, and after a long days search over the wide Internet, even google admitted defeat, there seemed to be no way to do this. Finally I had already dropped the idea or even let off the thing altogether. But recently for another project I needed to check in for an entirely different requirement and stumbled on the fact. And yes I checked it, voila the shell invoke from mysql trigger is possible.
Continue reading “Invoke shell from MySQL trigger”

MySQL Query Profiler; Better late than never

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 (https://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.

rschumacherRobin 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.

phpbb MySQL queries; need optimizations

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`)

Continue reading “phpbb MySQL queries; need optimizations”

Tunning Mysql; Rephrase queries Continued

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

Continue reading “Tunning Mysql; Rephrase queries Continued”

Tunning Mysql; Rephrase queries

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] @ www.jijutm.com []
# 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] @ www.jijutm.com []
# 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;

Continue reading “Tunning Mysql; Rephrase queries”

Consider Query Cache performance when designing tables

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.

Responsible use of Indexes

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.
Continue reading “Responsible use of Indexes”

Using Foreign Keys in 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