From: John Comerford (johncoptionsystems.com.au)
Date: Wed May 28 2008 - 00:05:27 CDT

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]

Compare the my.cnf files on both machines, it might shed some light...

Mike Bolton wrote:
> Thanks Cor, I wasn't aware of the truncate command. My delete operation has
> now been reduced from 4 hours to an almost instantaneous result. I'm still,
> however, having a problem with my load data infile statement. It takes 43
> min 8.71 sec on the production machine and 7 minutes on the dev machine,
> with the same schema.
> I'm about to try uninstalling and reinstalling mysql. I'm
> using 5.1.12-beta-log on the development machine, so I want the production
> machine to be compatible, although I'm not using anything out of the
> ordinary in my queries, so I don't think there should be a problem if I
> downgrade to 5.0 (perhaps it's more stable?). In any case, when installing
> from source, are there any special optimization flags or anything that I may
> need to be aware of? I'm thinking that maybe when I installed mysql on the
> production machine, I may have missed some option or left some debug option
> on or something. I just can't think why else there would be such a large
> difference in query execution speed between the dev and production machines.
> Thanks again for your help
>
> Mike
>
>
> On Tue, May 27, 2008 at 6:44 AM, C.R.Vegelin <cr.vegelinhetnet.nl> wrote:
>
>
>> ----- Original Message ----- From: "Mike Bolton" <palefacerockergmail.com
>>
>> To: <mysqllists.mysql.com>
>> Sent: Tuesday, May 27, 2008 8:47 AM
>> Subject: delete takes 4 hours
>>
>>
>>
>> I've got two machines running mysql, a development box and a production
>>
>>> box.
>>> I have a database table with 795755 rows on both machines. The schema is
>>> the same. When I execute a "delete from my_large_table" statement, it
>>> takes
>>> 9 minutes on the dev box and 4 hours on the production machine. Needless
>>> to
>>> say, this is not usable in a production environment, especially when I
>>> have
>>> to delete and load the large table every single day (the table contains a
>>> list of products as given by a distributor). The configurations of the
>>> two
>>> machines are as follows:
>>>
>>> dev box:
>>> FreeBSD dev 6.1-RC FreeBSD 6.1-RC #7
>>> 512 Megs RAM
>>> Pentium 4
>>> MySQL: 5.1.12-beta-log FreeBSD port: mysql-server-5.1.12
>>>
>>>
>>> production box:
>>> CentOS 2.6.9-55.0.12.EL
>>> Intel 2.4 Ghz P4 Celeron
>>> 512 Megs RAM
>>> MySQL: 5.1.22-rc-community
>>>
>>>
>>> Can anyone tell me why this could possibly be taking so long? Or where
>>> else
>>> I should look to try to solve this issue? Your help is greatly
>>> appreciated
>>> since I'm at a complete loss here. Thanks,
>>>
>>> Mike
>>>
>>>
>> Hi Mike,
>>
>> In case all records must be deleted, see:
>> http://dev.mysql.com/doc/refman/5.1/en/truncate.html
>>
>> HTH, Cor
>>
>>
>>
>
>

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]