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
>> While doning a batch process...
>> show full processlist show:
>> | 544 | prod | 220.127.116.11: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
>> don't have a F*** clue...