• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 175
  • Last Modified:

MYSQL database Queries

Hi,
My database size jumped from 200MB to 1217.82 MB in last month.
I need to pull it down to reasonable size due to shared hosting env.

My queries
1. How do I find biggest tables in mysql database
2. If I delete,truncate,drop tables/data over time how can I shrink my database
3.How do I monitor database growth over time like tables grew in last 4 weeks

Thanks
0
crazywolf2010
Asked:
crazywolf2010
  • 5
  • 4
2 Solutions
 
GaryCommented:
1. Do you have access to phpMyAdmin or similar?
2. What database type are you using?
3. Refer 1 - what kind of data is filling up the db

But a 1GB db is nothing for MySQL
0
 
ZberteocCommented:
If you run the query which is given below in MySQL Query Browser then you will get the two columns first will display the Data Base Name and the second will display the Data Base Size in MB.
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 ; 

Open in new window


If you want to view the free space available for database in MySQL run the below query:
SELECT table_schema "Data Base Name", 
sum( data_length + index_length ) / 1024 / 
1024 "Data Base Size in MB", 
sum( data_free )/ 1024 / 1024 "Free Space in MB" 
FROM information_schema.TABLES 
GROUP BY table_schema ; 

Open in new window

You cannot shrink database files.

To monitor you will have to create a cron task(linux) or windows task(windows) with the queries above but insert the results in a table. You would schedule it once  a day and then you can set some alters if thresholds are reached or passed.
0
 
crazywolf2010Author Commented:
Hi,
I can get list of databases and their sizes which is OK but I need SQL to find big TABLES within database.

1GB is very small for mySQL -> I do agree . Unfortunately I am on shared hosting and they won't allow more than 1GB databases.

Thanks
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
GaryCommented:
What type of db is it InnoDB, MyISAM...?
0
 
crazywolf2010Author Commented:
InnoDB
0
 
GaryCommented:
InnoDb can not be compacted, when data is removed it just reuses that empty space.
You could dump and reimport the db.

What is the format of the data that is causing the DB to rapidly grow? Could it be archived?
0
 
crazywolf2010Author Commented:
Hi,
It's drupal and the cache tables were holding hundreds of thousands of rows which I don't need.

Is there an easy way to say in MYSQL "TRUNCATE ALL TABLES START with CACHE"?

Thanks
0
 
GaryCommented:
You can disable logging, may have a small effect.
You could disable caching but then it would likely have an adverse effect on the website speed and is not recommended.
Can you not upgrade your hosting?
0
 
crazywolf2010Author Commented:
No I can't upgrade hosting that easily. Also it's unreal how much unnecessary thngs are dumped into database
Until yesterday my mySQL db was 1.2 GB with mysqldump backup files in region of 700MB. Now they are just 20MB.

Can you tell me how to dynamically truncate these tables?
0
 
GaryCommented:
Administration / Configuration / Development / Performance
And click Clear all caches

There are a few other ways here
https://drupal.org/node/42055
0

Featured Post

[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now