LinuxQuestions.org
Review your favorite Linux distribution.
Go Back   LinuxQuestions.org > Forums > Linux Forums > Linux - Software
User Name
Password
Linux - Software This forum is for Software issues.
Having a problem installing a new program? Want to know which application is best for the job? Post your question in this forum.

Notices


Reply
  Search this Thread
Old 11-26-2007, 06:25 PM   #1
izghitu
Member
 
Registered: Oct 2005
Location: localhost.localdomain
Distribution: CentOS
Posts: 109

Rep: Reputation: 16
MySQL help with huge database


Hi,

I am looking for ways to improve the database performance in the situation when I have to modify a large table (several million rows), by e.g. adding a column. Currently this would take several hours which is too slow. The bottleneck is disk I/O. I am considering either partitioning the table over several innodb files on several disks, or going to a RAID-5 or RAID-10, it this will give me better write performance.

The database is 130GB large, and the problem table (which I make period changes to) is the largest table on the server. I cannot have downtime of 3 hours each time I make a change and adding blank fields (to be used later, when a new field is needed) is not an option.

Each time I add a column, the cpu goes into high (80%) io wait state for about 3 hours.

I have a hack which would allow me to split the large table into multiple smaller tables based on some criteria (for example, forumID or such). Here are a couple of things but would like to know which is best, and am open to new ideas. The ideas so far:

1. Split the table into 3 or 5 smaller tables each on it's own disk. The disk IO would then not be so bad, and it might only take 1 hour to perform the table change. But this might not work because the changes to the database (as in adding a column) might be serial, meaning only 1 disk is being written to at a time. (Then again, maybe it will work if I launch 3 different scripts, one to update each table at once).

2. Do RAID 5 or 10, and have 3 or 5 disks. This again might not help at all because of the above issue with MySQL writing serially.

Any ideas here on these, or any new ideas?

I am using latest MySQL 5.0.45 with InnoDB engine on Debian etch Linux

Please help

Thanks
 
Old 11-26-2007, 08:46 PM   #2
chrism01
LQ Guru
 
Registered: Aug 2004
Location: Sydney
Distribution: Centos 6.10, Centos 7.5
Posts: 17,707

Rep: Reputation: 2497Reputation: 2497Reputation: 2497Reputation: 2497Reputation: 2497Reputation: 2497Reputation: 2497Reputation: 2497Reputation: 2497Reputation: 2497Reputation: 2497
You prob ought to have a search/ask here: http://forums.mysql.com/, but I'd guess that splitting the table would be better for your qn.
I would question why you keep on adding new columns though, that's usually a bad sign/design.
Are you sure it shouldn't be new tables? Maybe you could enlighten us on this?
 
  


Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off



Similar Threads
Thread Thread Starter Forum Replies Last Post
How to overcome the RPM "mysql-5.0.27-1.fc6" conflict with mysql database starting kamakshiganesh Linux - General 0 10-22-2007 08:07 AM
mysql - can't access mysql database as root yogaboy Linux - Software 5 12-28-2006 07:58 AM
problem with a huge mysql db mailbinoy Linux - General 0 12-25-2005 10:07 AM
Writing an app that uses a mysql database without installing mysql server? QtCoder Programming 4 08-09-2004 03:43 PM
MYSQL help - mysql database missing eloviyandhi Linux - Software 1 03-20-2004 10:20 PM

LinuxQuestions.org > Forums > Linux Forums > Linux - Software

All times are GMT -5. The time now is 05:27 AM.

Main Menu
Advertisement
My LQ
Write for LQ
LinuxQuestions.org is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
Syndicate
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
Facebook: linuxquestions Google+: linuxquestions
Open Source Consulting | Domain Registration