Hi!

I've recently upgraded from 3.23.27 to 3.23.35 and immediately started noticing updates that should be simple and quick (and were before) starting to take ages on rows that are very recently inserted.

My general problem is that I can't go hacking at the live site too much so I ensured all tables were checked as okay and then copied the data to a test machine. As usual it doesn't reproduce. I'm going to go out on a limb and suggest it's something to do with table/key/something caching.

If I could freeze the live system cache and all and move them over I might be ahead. As it is you're probably going to tell me that you need a test case (just ignore me if that's the case) and I'm going to have to setup the dev like the live (under load) to try and prove it (oh fun).

I switch back to 3.23.27 and I'm nice and quick again, exactly the same data
files.

mysql> show fields from EndUserClientTrans; +------------------+------------------+------+-----+---------------------+----------------+---------------------------------+ | Field | Type | Null | Key | Default | Extra
| Privileges | +------------------+------------------+------+-----+---------------------+----------------+---------------------------------+ | EndUserID | int(11) | | MUL | 0 |
| select,insert,update,references | | TransID | int(10) unsigned | | PRI | NULL |
auto_increment | select,insert,update,references | | ParentTransID | int(11) | | | 0 |
| select,insert,update,references | | StatusID | int(11) | | MUL | 0 |
| select,insert,update,references | | Closed | datetime | | MUL | 0000-00-00 00:00:00 |
| select,insert,update,references | | Void | datetime | | MUL | 0000-00-00 00:00:00 |
| select,insert,update,references | | Reversed | datetime | | MUL | 0000-00-00 00:00:00 |
| select,insert,update,references | | MerchantID | int(11) | | MUL | 0 |
| select,insert,update,references | | Value | float(10,2) | | MUL | 0.00 |
| select,insert,update,references | | Description | blob | | | |
| select,insert,update,references | | Approved | smallint(6) | | MUL | 0 |
| select,insert,update,references | | AuthCode | varchar(20) | | MUL | |
| select,insert,update,references | | SettlementTicket | varchar(255) | | | |
| select,insert,update,references | | TimeandDay | datetime | | MUL | 0000-00-00 00:00:00 |
| select,insert,update,references | | TransactionTimer | int(11) | | | 0 |
| select,insert,update,references | | OriginID | int(11) | | MUL | 0 |
| select,insert,update,references | +------------------+------------------+------+-----+---------------------+----------------+---------------------------------+ 16 rows in set (0.00 sec)

mysql> show keys from EndUserClientTrans; +--------------------+------------+----------------------+--------------+-------------+-----------+-------------+----------+--------+---------+ | Table | Non_unique | Key_name | Seq_in_index |
Column_name | Collation | Cardinality | Sub_part | Packed | Comment | +--------------------+------------+----------------------+--------------+-------------+-----------+-------------+----------+--------+---------+ | EndUserClientTrans | 0 | TransID | 1 |
TransID | A | NULL | NULL | NULL | NULL | | EndUserClientTrans | 1 | EndUserClientTrans0 | 1 |
Approved | A | NULL | NULL | NULL | NULL | | EndUserClientTrans | 1 | EndUserClientTrans1 | 1 |
AuthCode | A | NULL | NULL | NULL | NULL | | EndUserClientTrans | 1 | EndUserClientTrans2 | 1 | Closed
| A | NULL | NULL | NULL | NULL | | EndUserClientTrans | 1 | EndUserClientTrans3 | 1 |
EndUserID | A | NULL | NULL | NULL | NULL | | EndUserClientTrans | 1 | EndUserClientTrans5 | 1 |
MerchantID | A | NULL | NULL | NULL | NULL | | EndUserClientTrans | 1 | EndUserClientTrans8 | 1 |
Reversed | A | NULL | NULL | NULL | NULL | | EndUserClientTrans | 1 | EndUserClientTrans9 | 1 |
StatusID | A | NULL | NULL | NULL | NULL | | EndUserClientTrans | 1 | EndUserClientTrans10 | 1 |
TimeandDay | A | NULL | NULL | NULL | NULL | | EndUserClientTrans | 1 | EndUserClientTrans13 | 1 | Value
| A | NULL | NULL | NULL | NULL | | EndUserClientTrans | 1 | EndUserClientTrans14 | 1 | Void
| A | NULL | NULL | NULL | NULL | | EndUserClientTrans | 1 | EUCTTEMP1 | 1 |
OriginID | A | NULL | NULL | NULL | NULL | +--------------------+------------+----------------------+--------------+-------------+-----------+-------------+----------+--------+---------+ 12 rows in set (0.00 sec) (note my cardinality keeps vanishing a while after running --analyze, might be
normal, manual doesn't seem to say)

A query such as UPDATE EndUserClientTrans SET Approved = 1, SettlementTicket = '123abchello'
WHERE TransID = 987654; _can_ take up to 10 seconds.

Redhat Linux 6.2 SMP (2 x P3 933) 100GB HDD Raid10 (in hardware) 1GB RAM (as I say, normally it's pretty quick ;-) Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc /usr/bin/cc GCC: Reading specs from /usr/lib/gcc-lib/i386-redhat-linux/egcs-2.91.66/specs gcc version egcs-2.91.66 19990314/Linux (egcs-1.1.2 release) Compilation info: CC='gcc' CFLAGS='' CXX='c++' CXXFLAGS='' LDFLAGS='' LIBC: lrwxrwxrwx 1 root root 13 Mar 24 2000 /lib/libc.so.6 ->
libc-2.1.2.so -rwxr-xr-x 1 root root 4118299 Sep 20 1999 /lib/libc-2.1.2.so -rw-r--r-- 1 root root 20020054 Sep 20 1999 /usr/lib/libc.a -rw-r--r-- 1 root root 178 Sep 20 1999 /usr/lib/libc.so Configure command: ./configure Perl: This is perl, version 5.005_03 built for i386-linux

I see a premium support license looming ....

Cheers,