List:General Discussion« Previous MessageNext Message »
From:Ananda Kumar Date:May 14 2012 2:06pm
Subject:Re: Mysql is toying me... why sometimes an insert or update can be
slow!? I getting bald cuz this
View as plain text  
If numeric, then why are u using quotes. With quotes, mysql will ignore the
index and do a full table scan

On Mon, May 14, 2012 at 7:31 PM, Andrés Tello <mr.criptos@stripped> wrote:

>
>
> 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