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
Solved

MYSQL database Queries

Posted on 2013-11-22
10
169 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
  • 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 26

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
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 
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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

Suggested Solutions

Foreword This is an old article.  Instead of using the MySQL extension that was used in the original code examples, please choose one of the currently supported database extensions instead.  More information is available here: MySQLi / PDO (http://…
Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.
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…

790 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