Damn, would have told you to use InnoDB, but InnoDB doesn't have fulltext.
MyISAM, as I learned today, locks an entire table during an insert, so
other records can't be inserted or updated while an insert is taking
place. So 8 minutes of constant inserts will cause some problems here.
Aye, best split up the batch script if you really have to do 8000
inserts nightly.
On Wed, Jan 18, 2012 at 1:30 PM, Eric Cope <
eric.cope@gmail.com> wrote:
> good point. Its just a series of INSERTs (8000 or so), but the table
> has a fulltext index, so its pretty slow (10 records/sec).
> It sounds like I should split up the batch script, then load a few,
> pause, load a few, pause, etc.
> Any ideas?
>
> Eric
>
> On Wed, Jan 18, 2012 at 12:20 PM, Alex Dean <alex@crackpot.org> wrote:
>> On Jan 18, 2012, at 11:58 AM, Eric Cope wrote:
>>
>>> Hello all,
>>> I have a nightly script that executes a mysql batch file that takes
>>> approximately 8 minutes to run. Unfortunately it makes my web server
>>> unresponsive for all 8 minutes. I've tried ionice and nice to lower
>>> the priority, but it still seems to consume all resources. The weird
>>> thing is "top" and "iostat" look like nothing is running. Any ideas?
>>>
>>> Its mysql 5.1.x
>>
>> What process are you nice-ing? If your script just fires off a bunch of SQL queries, it's the MySQL server which is going to end up eating all your resources. Nicing the script which is issuing the queries probably won't help much.
>>
>> Try using EXPLAIN to see if you can make your queries more efficient. After that, look into setting up MySQL replication. You can run your reporting queries against a slave database, and keep that load off the master.
>>
>> alex
>> ---------------------------------------------------
>> PLUG-discuss mailing list - PLUG-discuss@lists.plug.phoenix.az.us
>> To subscribe, unsubscribe, or to change your mail settings:
>> http://lists.PLUG.phoenix.az.us/mailman/listinfo/plug-discuss
> ---------------------------------------------------
> PLUG-discuss mailing list - PLUG-discuss@lists.plug.phoenix.az.us
> To subscribe, unsubscribe, or to change your mail settings:
> http://lists.PLUG.phoenix.az.us/mailman/listinfo/plug-discuss
---------------------------------------------------
PLUG-discuss mailing list -
PLUG-discuss@lists.plug.phoenix.az.us
To subscribe, unsubscribe, or to change your mail settings:
http://lists.PLUG.phoenix.az.us/mailman/listinfo/plug-discuss
(text/plain)