List:General Discussion« Previous MessageNext Message »
From:Andrés Tello Date:May 14 2012 2:01pm
Subject:Re: Mysql is toying me... why sometimes an insert or update can be
slow!? I getting bald cuz this
View as plain text  
Yes, I'm using indexes, accountid is the primary key, and is numeric and
autoincrement.  The process doing the deadlock is no longer done...

The structure of the inserted database has changed.
Originaly it was a single table with 219millions rows, now I partitioned
the hable in... 60 tables, 1 for each month for 5 years..

I gain a lot of speed with the partitioning and sql adjustments, but I
think I lost a lot of speed at the insert...

The database stats are like 95% reading 5% writting...but that 5% is mostly
done in a batch process who happends to be done inside a time window...

I know I need to do more profiling, but... at least for now dev team is
updating the batch process from long secuencial process with huge slow
inserts, to small parallel task with burst of inserts...




On Mon, May 14, 2012 at 8:18 AM, Ananda Kumar <anandkl@stripped> wrote:

> is accountid a number or varchar column
>
>
> On Sat, May 12, 2012 at 7:38 PM, Andrés Tello
> <mr.criptos@stripped>wrote:
>
>> While doning a batch process...
>>
>> show full processlist show:
>>
>> | 544 | prod | 90.0.0.51:51262 | tmz2012 | Query   |    6 |
>> end                          | update `account` set `balance`= 0.00 +
>> '-4000' where accountid='2583092'
>>
>> No other process, lo locking no nothing...
>>
>> so you take this same query... run it isolated, and the mu....fu... is
>> just...f.... fast!!!!!
>>
>>
>> update `account` set `balance`= 0.00 + '-4000' where accountid='2583092'
>> Query OK, 0 rows affected (0.00 sec)
>> Rows matched: 1  Changed: 0  Warnings: 0
>>
>>
>> ARRRG!  I have seen this type of query take as long as 100+ seconds.. and
>> I
>> don't have a F*** clue...
>>
>
>

Thread
Mysql is toying me... why sometimes an insert or update can be slow!?I getting bald cuz thisAndrés Tello12 May
  • RE: Mysql is toying me... why sometimes an insert or update can be slow!? I getting bald cuz thisSteven Staples14 May
  • Re: Mysql is toying me... why sometimes an insert or update can beslow!? I getting bald cuz thisAnanda Kumar14 May
    • Re: Mysql is toying me... why sometimes an insert or update can beslow!? I getting bald cuz thisAndrés Tello14 May
      • Re: Mysql is toying me... why sometimes an insert or update can beslow!? I getting bald cuz thisAnanda Kumar14 May
        • Re: Mysql is toying me... why sometimes an insert or update can be slow!? I getting bald cuz thisJohan De Meersman14 May
          • Re: Mysql is toying me... why sometimes an insert or update can beslow!? I getting bald cuz thisAnanda Kumar14 May