List:General Discussion« Previous MessageNext Message »
From:Reindl Harald Date:May 31 2012 9:42am
Subject:Re: Best practice to minimize DB server performance effect (large
query)
View as plain text  
DO NOT TOP POST

Am 31.05.2012 11:32, schrieb Roland Roland:
> On 5/31/12 11:23 AM, Reindl Harald wrote:
>>
>> Am 31.05.2012 09:13, schrieb Roland RoLaNd:
>>> Dear all,I have a task to anonymize data in 82000
>>> record (for the time being)i've tested my script against
>>> about 30  of them, and it takes about 2.4 sec for each
>>> query to be executed.
>> provide table structure and query example
>> sounds like a bad design without key
>>
>> how can 30 updates take 2.4 seconds?
>>
>>> i'm seeking help with the following:
>>> - what's the best way to run such a script without affecting the DB server
> performance?
>>>    Should i limit the script to implement N number of records at a time?
>>>    and then sleep or is there a better way?
>>> - What's the best practice of handling errors and warnings in such a
> situation?
>>> - How can i prevent the runtime errors and mysql locks?
>> wrong question
>>
>> [--] Data in InnoDB tables: 6G (Tables: 49)
>> [--] Up for: 5d 11h 17m 15s (493M q [1K qps], 43K conn, TX: 38B, RX: 14B)
>> [--] Reads / Writes: 89% / 11%
>>
>> as you can see 30 queries or even 88.000 queries must not
>> make any trouble if you table-design is OK
>
> i agree it wouldn't cause trouble, though it might lock mysql as
> there's a number of other databases running on the same  server.
> so performance is an issue even if it's just a CPU/RAM peak.

if your queries are fast enough that should not matter
as long 30 updates take more than 2 seconds something
goes terrible wrong on your machine

as said:
if you need help post informations!

* mysql-configuration
* hardware-specs (especially RAM)
* table definition
* query example

you can also use "EXPLAIN" to test your queries and
see what is going wrong: http://dev.mysql.com/doc/refman/5.0/en/explain.html

as long you do not provide specific informations nobody will and
can help you - there is no magical "do thsi to get best performance"
anywhere in the IT




Attachment: [application/pgp-signature] OpenPGP digital signature signature.asc
Thread
Best practice to minimize DB server performance effect (large query)Roland RoLaNd31 May
  • Re: Best practice to minimize DB server performance effect (largequery)Reindl Harald31 May
    • Re: Best practice to minimize DB server performance effect (largequery)Roland Roland31 May
      • Re: Best practice to minimize DB server performance effect (largequery)Reindl Harald31 May