Solved

MYSQL database Queries

Posted on 2013-11-22
10
171 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
Forrester Webinar: xMatters Delivers 261% ROI

Guest speaker Dean Davison, Forrester Principal Consultant, explains how a Fortune 500 communication company using xMatters found these results: Achieved a 261% ROI, Experienced $753,280 in net present value benefits over 3 years and Reduced MTTR by 91% for tier 1 incidents.

 
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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

Suggested Solutions

Popularity Can Be Measured Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients.  This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for …
Creating and Managing Databases with phpMyAdmin in cPanel.
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

751 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