Great Brent, helps a lot!
it is very good to know your experience.
I will speak to developers and try to see if there is the opportunity to
apply the 'Divide et Impera' principle!
I am sorry to say MySQL it is a little out of control when dealing with
huge tables, it is the first time I had to kill MySQL deamon a couple of
times.
Thanks again Brent
Claudio
Brent Baisley wrote:
I've used a similar setup and hit up to 400 million with 5-7 million
records being added and deleted per day. Processing the table like you
mention gave me the exact same result. The query was just too big for
MySQL to handle. If you can break down your query into multiple
smaller queries, it will run much quicker. I went from 7-8 hours down
to 10 minutes. I broke my query into increments based on date/time and
merged the results.
I also switched to using MERGE tables so I could create a much narrow
set of tables to query on (i.e. current_month). Of course, this means
splitting your table into separate tables based on a certain criteria.
Basically, divide and conquer.
Hope that helps.
Brent Baisley
On Fri, Feb 27, 2009 at 4:42 PM, Claudio Nanni <claudio.na...@gmail.com> wrote:
Hi,
I have one 15GB table with 250 million records and just the primary key,
it is a very simple table but when a report is run (query) it just takes
hours,
and sometimes the application hangs.
I was trying to play a little with indexes and tuning (there is not great
indexes to be done though)
but eveytime I try to alter table for indexes it just hogs the disk space
and takes hours
to try to build indexes in various passages(.TMD) but it is a real pain
since I cannot even kill the mysql process,
and I had to kill the server with table corruption and had to stop/start and
repair table.
Does anybody experience problems in managing a simple MyISAM table with 250
million records and a primary key?
I tried also to duplicate the table, add indexes and insert into it (also
using INNODB for the new table) but it is really
taking ages everytime. And I had to move the 'tmpdir' to the data partition
because it was filling the / 100%.
MySQL is 5.0.x on 64bit RHEL 5 with 16GB RAM and NAS storage.
Any hint on how to manage big tables?
Thanks
Claudio Nanni
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
