New Technologies in Web Development

Sunday, June 21, 2009

MySQL Crash Recovery

MySQL Crash Recovery
MySQL is known for its stability but as any other application it has bugs so it may crash sometime. Also operation system may be flawed, hardware has problems or simply power can go down which all mean similar things – MySQL Shutdown is unexpected and there could be various inconsistences. And this is not only problem as we’ll see.
MySQL has angel process mysqld_safe which will restart MySQL Server in most cases. It is great, unless you have run into some bug which causes it to crash again – such crashes qucikly following one another are kind of worse because they explore many less tested code paths in MySQL and so problem potential is larger.
So lets look at the problem which happen during the crash which might need to take care of or which may seriously affect MySQL Performance.
MyISAM Corruption - If you’re writing to MyISAM tables there is very large chance of them becoming corrupted during the crash. Note corruption may be hidden and do not expose itself instantly – you may notice wrong query results days after crash. Sometimes corrupted tables may be reason for further crashes or hangs, and corruption may spread itself further in the table. You probably do not want any of these so it is very good idea to run MySQL with myisam_recover option which will make sure all improperly closed MyISAM tables are checked first time it is accessed. This option is however rather painful to use with web applications – users may issue different queries which may trigger check/repair running for many tables at onces, which typically make system extremely slow and also can use up all allowed connections or run out of memory ( myisam_sort_buffer_size is normally set pretty lage). If this becomes the problem I use tiny script which moves out all MyISAM tables out of MySQL database directory, checks them with MyISAMchk and moves them back to running server. This looks scary but it works great – until table is checked and ready application gets error rather than stalling forever which allows application to become partially functional as soon as possible. This hack is needed only in some cases – in most cases using Innodb for tables which you need to be recovered fast is better solution.
Innodb Recovery – Unless you have some hardware problems (99%) or found new Innodb bug (1%) Innodb recovery should be automatic and bring your database to consistent state. Depending on innodb_flush_lot_at_trx_commit setting you may lose few last committed transactions but it is it. It is Performance of this process which may cause the problems. As I already wrote innodb_log_file_size and innodb_buffer_pool_size affect recovery time significantly as well as your workload. I should also mention if you have innodb_file_per_table=1 your recovery speed will depend on number of Innodb tables you have, as well as many other operations, so beware.
Binary log corruption - Binary log may become corrupted and out of sync with database content. This will sometimes break replication but if you’re just planning on using binary log for point in time recovery it can go unnoticed. sync_binlog Is helping by syncing binary log, but at performance penalty. If using Innodb you also might with to use innodb-safe-binlog option in MySQL 4.1 so your Innodb log and binary log are synchronized. In MySQL 5.0 XA is taking care of this synchronization.
.frm Corruption – Few people know MySQL is not really ACID even with Innodb tables, at least not for DDL statements.
There is a chance of failing for example during CREATE statement with table created in Innodb dictionary but .frm not created or not completely written. Partially written .frm files or .frm being unsync with internal Innodb dictionary may cause MySQL to fail with wierd error messages. In MySQL 4.1 sync_frm option was added which reduces this problem as time window when it can happen is much less. Still if failure happens just during writting .frm file nasty things may happen, not to mention such potentially multiple operation DDL statements as RENAME TABLE – these are most vulnerable.
master.info corruption - If slave happens to crash you can also have relay logs corruption and master.info being corrupted. Not to mention MyISAM tables can contain partially completed statements as well as some of updates totally lost. The safe approach it to reclone the slaves if they crash or you can take the risks and try to continue. Sometimes you might be able to manually find appropriate position even if master.info file is out of sync but I would not be basing my failure handling scenarios.
Cold Start – If you restart MySQL server its caches (key_buffer, innodb_buffer_pool, query_cache,table_cache) are cleaned, so may be OS caches. This may reduce performance dramatically. So if you’re bringing server back after crash you might want to populate caches. For MyISAM key_cache this can be done by using LOAD INDEX INTO CACHE statement, for other storage engines it can be done by issuing large index scan queries. Full table scan queries allow to preload table data ether in storage engine caches or in OS cache. You can save these into .sql file and use –init-file to make sure it is run on startup. The other approach is to prime server with real servers (ie clone queries from other slave) before putting traffic to it.
In case application is not highly available so there is only one server you might with to start serving only some users initially (returning error to others) and gradually increase the load as server warms up. This may sound strange but makes a lot of sense as not only waiting for pages which never load is more frustrating for users than getting honest “try again later” message, but also – warmup takes longer time on extreme load.
Innodb statistics - Unlike MyISAM Innodb does not store index cardinality in tables, instead it computes them on first table access after startup. This may take significant time if you have very large number of tables (Some users have hundreds of thousands of tables per database host). This one is pretty much part of cold start problems but I wanted to point out it separately. To warmup this data you might run select 1 from _table_ limit 1 for each table or any other statement – it is table open which is important.
There are other problems which you may experience related to MySQL Crash Recovery – Restoring data from backup, corrupted Innodb tablespace recovery etc but I should write about them some other time.

Reference by : http://www.mysqlperformanceblog.com/2006/07/30/mysql-crash-recovery/

Understanding MySQL Query Cache for PHP Developers

Problem Statement:

Many PHP developers using MySQL have unclear understanding of the MySQL query cache. So we decided to write a series of introductory articles to get everyone on the same page. This article is the first installment of the series and here we will introduce the basics of query cache in MySQL. Note that unlike a typical book chapter, this article will be of low-fat flavor — less theory and more actionables — of an introduction to query caching for MySQL.

What is a MySQL query cache?

It turns out that MySQL has a built-in query cache that can cache a specific type of queries — SELECT statements — to speed up delivery of the result sets. The cache can increase performance for many instances but can also hurt performance if not used wisely.

What can be cached in the MySQL query cache?

Only SELECT statements can be cached. This does not include prepared SELECT statements. Query caching only works for SELECT statements that are fully qualified and returns same result every time. This means you cannot use non deterministic functions that return data depending on situation. For example:

// Following SELECT query can be cached

$stmt = "SELECT * FROM user WHERE active = 1";

// Following SELECT query cannot be cached

$stmt = "SELECT * FROM user where signup_date >= NOW()";

// Following SELECT query cannot be cached

$stmt = "SELECT count(*) FROM user";

Here are the requirements a query must meet to take advantage of the query cache:

Only exact queries are serviced from the cache — must match the stored query in exact detail.
Queries with placeholders — such as the ones for prepared statements — are not cached in query cache
Queries with user defined functions or non-deterministic functions cannot be cached
Any table changes (such as issuing of an ALTER statement) will remove the queries from the cache for that table

Introduction to query cache parameters

The more you understand the query caching parameters, the better you are going to be at tuning the query cache to your advantage. First find out what are the global query caching parameters that you can fiddle with using the following query at the mysql command-line prompt.

mysql> show global variables like '%query_cache%';

A sample output is shown below:

+------------------------------+-----------+

| Variable_name | Value |

+------------------------------+-----------+

| have_query_cache | YES |

| query_cache_limit | 1048576 |

| query_cache_min_res_unit | 4096 |

| query_cache_size | 536870912 |

| query_cache_type | ON |

| query_cache_wlock_invalidate | OFF |

+------------------------------+-----------+

6 rows in set (0.00 sec)

The purpose of these parameters are described briefly as:

have_query_cache - size of query cache in bytes
query_cache_limit - the maximum size of result set (default: 1048576 bytes or 1 MB). If your query returns result set that is greater than the limit set here, it will NOT BE CACHED
query_cache_min_res_unit - the smallest block size allocated by query cache. Default is 4KB
query_cache_size - the total memory available to query cache
query_cache_type - when set to ON or 1, query caching is on for all applicable queries, when set to OFF (0) query caching is turned off and when set to DEMAND or 2, caching is on for queries with SQL_CACHE directive in the query
query_cache_wlock_invalidate-causes the query cache to invalidate any query in the cache if a write lock is executed against the table(s) it uses

Whats your query cache status right now?

To find out whats going on with your query cache, run the following command from the MySQL command-line prompt:

mysql> show status like '%qc%';

Here is a sample result:

+-------------------------+-----------+

| Variable_name | Value |

+-------------------------+-----------+

| Qcache_free_blocks | 978 |

| Qcache_free_memory | 527371984 |

| Qcache_hits | 645545 |

| Qcache_inserts | 130796 |

| Qcache_lowmem_prunes | 0 |

| Qcache_not_cached | 417579 |

| Qcache_queries_in_cache | 4973 |

| Qcache_total_blocks | 11167 |

+-------------------------+-----------+

8 rows in set (0.00 sec)

Here are some brief explanations of these status metrics:

Qcache_free_blocks - number of memory blocks marked as free, which indicates memory fragmentation
Qcache_free_memory - total amount of memory free for query cache
Qcache_hits - number of times query result was found in the query cache
Qcache_inserts - number of times queries were written to the query cache
Qcache_not_cached - number of queries removed from cache due to low cache memory
Qcache_queries_in_cache - number of queries that could not be cached
Qcache_total_block - total number of blocsk in query cache

Calculating query cache hits vs misses

Here is the formula for calculating hit ratio for query cache:

$totalSelectQueryCount = $comSelect + $qcacheHits

$percentHits = ($qcacheHits * 100)/ $totalSelectQueryCount

What the above formula does is adds up all the SELECT queries in the system using two MySQL global variables: com_select and qcache_hits.

To set $comSelect, run show global status like '%com_select%' query. For example:

mysql> show global status like '%com_select%';

+---------------+---------+

| Variable_name | Value |

+---------------+---------+

| Com_select | 1739663 |

+---------------+---------+

To set $qcacheHits, run show status like '%qcache_hit%'. For example:

mysql> show status like '%qcache_hit%';

+---------------+----------+

| Variable_name | Value |

+---------------+----------+

| Qcache_hits | 20786961 |

+---------------+----------+

With the above sample number, the percent hit is 92.28% which is great.

Managing query cache

To manipulate your query cache, you can use the following MySQL statements from the MySQL command-line:

To remove all the queries from your query cache, run:

RESET QUERY CACHE;

To defragment the query cache memory, run:

FLUSH QUERY CACHE;

Tuesday, June 16, 2009

8 Popular Open Source Forums

The list below is a collection of 8 popular Open Source Forum software.

· - phpBB is one of the most popular forum softwares. With phpBB you can customize with mods and you can stylize your forum. For a fully Modded phpBB forum with about 500 different mods that all have been fully tested click here.

· - Vanilla is a lightweight open source forum that was developed by Mark O’Sullivan using PHP and MySQL. Vanilla comes loaded with extensions, and you can customize the forum with your own add-ons.

· - YetAnotherForum.NET is a Open Source forum or bulletin board for web sites running ASP.NET. YetAnotherForum.NET is fully coded in C# ASP.Net and uses Microsoft SQL Server. It comes with all the standard forum features and even WYSIWYG editors.

· - YaBB is an Open Source forum system (bulletin board, message board) written in Perl. This system is the first and most popular open-source perl forum software and it runs very fast.

· - IceBB is an open-source forum solution powered by PHP and MySQL. IceBB is written with the prototype and scriptaculous frameworks and the code output is very clean. IceBB has mostly all the same features as some of the paid forums but it even has an RSS feed.

· - WP-Forum is a simple discussion forum plugin for WordPress. It has three themes that you can choose from and is a simple solution if you have a WordPress powered site.

· - miniBB is an open source message board script written in PHP. miniBB is a lightweight and compact alternative to phpBB. With miniBB you can customize the software with add-ons and it is Search Engine Optimized.

· - SMF is a free professional grade forum software that actually isn’t open source. SMF has loads of features with a great community and tons of free modifications.

If you know of any more Open Source Forums then please comment below. Thanks!!!

Popularity: 100%

Monday, May 25, 2009

Learning Programming ,Products,Videos,Books,Questions And Others

Wednesday, May 6, 2009

Why Lower case table names are not possible in windows for MySql

· lower_case_table_names

If set to 1, table names are stored in lowercase on disk and table name comparisons are not case sensitive. If set to 2 table names are stored as given but compared in lowercase. This option also applies to database names and table aliases. See SectionÂ 9.2.2, â€œIdentifier Case Sensitivityâ€.

If you are using InnoDB tables, you should set this variable to 1 on all platforms to force names to be converted to lowercase.

You should not set this variable to 0 if you are running MySQL on a system that does not have case-sensitive filenames (such as Windows or Mac OS X). If this variable is not set at startup and the filesystem on which the data directory is located does not have case-sensitive filenames, MySQL automatically sets lower_case_table_names to 2.

Friday, May 1, 2009

One Bedroom Flat....

ONE BEDROOM FLAT... AN INDIAN SOFTWARE ENGINEER'S LIFE...

As the dream of most parents I had acquired a degree in Software Engineer and joined a company based in USA , the land of braves and opportunity. When I arrived in the USA, it was as if a dream had come true.

Here at last I was in the place where I want to be. I decided I would be staying in this country for about Five years in which time I would have earned enough money to settle down in India

My father was a government employee and after his retirement, the only asset he could acquire was a decent one bedroom flat.

I wanted to do some thing more than him. I started feeling homesick and lonely as the time passed. I used to call home and speak to my parents every week using cheap international phone cards. Two years passed, two years of Burgers at McDonald's and pizzas and discos and 2 years watching the foreign exchange rate getting happy whenever the Rupee value went down.

Finally I decided to get married. Told my parents that I have only 10 days of holidays and everything must be done within these 10 days. I got my ticket booked in the cheapest flight. Was jubilant and was actually enjoying hopping for gifts for all my friends back home. If I miss anyone then there will be talks. After reaching home I spent home one week going through all the photographs of girls and as the time was getting shorter I was forced to select one candidate.

In-laws told me,to my surprise, that I would have to get married in 2-3 days, as I will not get anymore holidays. After the marriage, it was time to return to USA , after giving some money to my parents and telling the neighbors to look after them, we returned to USA

My wife enjoyed this country for about two months and then she started feeling lonely. The frequency of calling India increased to twice in a week sometimes 3 times a week. Our savings started diminishing. After two more years we started to have kids. Two lovely kids, a boy and a girl, were gifted to us by the almighty. Every time I spoke to my parents, they asked me to come to India so that they can see their grand-children.

Every year I decide to go to India. But part work part monetary conditions prevented it. Years went by and visiting India was a distant dream. Then suddenly one day I got a message that my parents were seriously sick. I tried but I couldn't get any holidays and thus could not go to India The next message I got was my parents had passed away and as there was no one to do the last rights the society members had done whatever they could. I was depressed. My parents had passed away without seeing their grand children.

After couple more years passed away, much to my children's dislike and my wife's joy we returned to India to settle down. I started to look for a suitable property, but to my dismay my savings were short and the property prices had gone up during all these years. I had to return to the USA

My wife refused to come back with me and my children refused to stay in India. My 2 children and I returned to USA after promising my wife I would be back for good after two years.

Time passed by, my daughter decided to get married to an American and my son was happy living in USA I decided that had enough and wound-up every thing and returned to India. I had just enough money to buy a decent 02 bedroom flat in a well-developed locality.

Now I am 60 years old and the only time I go out of the flat is for the routine visit to the nearby temple. My faithful wife has also left me and gone to the holy abode.

Sometimes I wondered was it worth all this? My father, even after staying in India, had a house to his name and I too have the same nothing more.

I lost my parents and children for just ONE EXTRA BEDROOM .

Looking out from the window I see a lot of children dancing. This damned cable TV has spoiled our new generation and these children are losing their values and culture because of it. I get occasional cards from my children asking I am alright. Well at least they remember me.

Now perhaps after I die it will be the neighbors again who will be performing my last rights, God Bless them. But the question still remains 'was all this worth it?'

I am still searching for an answer...... ......... .!!!!

By an Indian SE who was in US.