MySQL Database Optimization

Once Upon a time.....

Naa... I m kidding... :P this is technical blog.
So lets come to the point..

I was totally unaware about the database optimization specially MySQL
I realized one day that my one of the MySQL server giving problem to all my sites.
Why ??

Because there were 2K tables on the same server.
:O ... 2K tables ??

I was shocked and had to do something to reduce such overhead on the MySQL server.

The Administrator from hosting team told me that it is always better to keep your Data-size less than 2GB and in terms of tables it should be less than 1000 tables.

Now I came to know why all this happened my number of tables exceeded the limit.
The size of database was also pretty out of range more than 2.5G. All spams only on 1 unprotected non-working site I quickly cleaned that site and removed the domain.

To check all other possibilities I had to google for the query of checking whole database store size and I found the following query. It shown me all databases with its sizes. It worked really well.

SELECT table_schema "Data Base Name",
SUM( data_length + index_length ) /1024 /1024 "Data Base Size in MB"
FROM information_schema.TABLES
GROUP BY table_schema
LIMIT 0 , 30

Thank god I realized it before server gone down.

Hope this will help to optimize the MySQL servers and the performance of hosting server.

Cheers !!!! :)