Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

MYSQL database Queries

Posted on 2013-11-22
10
Medium Priority
?
173 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 1000 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
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
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 1000 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

Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

Question has a verified solution.

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

Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL several years ago, it seemed like now was a good time to update it for object-oriented PHP.  This article does that, replacing as much as possible the pr…
Backups and Disaster RecoveryIn this post, we’ll look at strategies for backups and disaster recovery.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses

688 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