Solved

MYSQL database Queries

Posted on 2013-11-22
10
172 Views
Last Modified: 2014-02-06
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
Comment
Question by:crazywolf2010
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
10 Comments
 
LVL 58

Expert Comment

by:Gary
ID: 39670642
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
 
LVL 27

Assisted Solution

by:Zberteoc
Zberteoc earned 250 total points
ID: 39670664
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
 

Author Comment

by:crazywolf2010
ID: 39674351
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
Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

 
LVL 58

Expert Comment

by:Gary
ID: 39674414
What type of db is it InnoDB, MyISAM...?
0
 

Author Comment

by:crazywolf2010
ID: 39674426
InnoDB
0
 
LVL 58

Expert Comment

by:Gary
ID: 39675158
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
 

Author Comment

by:crazywolf2010
ID: 39677422
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
 
LVL 58

Expert Comment

by:Gary
ID: 39677600
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
 

Author Comment

by:crazywolf2010
ID: 39677617
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
 
LVL 58

Accepted Solution

by:
Gary earned 250 total points
ID: 39677631
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] How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them. Thursday, July 13, 2017 10:00 A.M. PDT

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

630 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question