ubuntu 3.10.33

BoxRec,

You are filing it fine, but we need much more information to be able to even start looking into the issue.
What is the "Daily batch update program"? What does it do? Which statements does it execute?
Which tables does it work on? (engines, structures, number of rows)
What does show processlist show during this time?
Does the error log contain any error messages?

Please also attach your cnf file(s), we might need them when we gather enough information to actually start looking into it.

Thanks.

Hi Elena,
many thanks for picking this up. Daily batch is a perl script which updates 2 mysql tables, one with 500,000 rows the other 2,000,000 rows. I will add extra code tonight to try and narrow down which table (or if both) have slowed. show processlist shows the queries executing one by one, should I be looking for anything in particular ?

all the best, John

BoxRec,

Do you mean the script runs two huge updates, or it runs a huge number of small updates on each of 2 tables?

In the first case, the process list will probably be very static, it's enough to run SHOW PROCESSLIST a few times to see which stages (states) the queries spend time on. Then we'll need to know what these updates look like, and output EXPLAIN UPDATE ... might help to understand what's wrong with them.

In the second case, if they are tiny quick updates, it might be useful to set up a processlist monitor which will run SHOW PROCESSLIST every several seconds.
It might help to see whether the server freezes (temporarily) on some updates for long time, or the execution time is evenly distributed between updates.

When we know more on this level, we might have a better idea where to dig.

If it's just two tables, it would be a big help if you could provide SHOW CREATE TABLE statements for each table and, ideally, a data dump (it can be uploaded to ftp.askmonty.org/private, so only MariaDB developers will have access to it). If the data is confidential and you can't provide it, please at least run SHOW INDEX IN .. and SHOW TABLE STATUS LIKE ... for each of two tables.

Hi Elena,
the updates are huge number of

UPDATE humans set r = '0.0009999'*100000, RD = '0'*100000, ranking = '0' where human_id = '15673'

where r, RD, ranking and human_id vary

show process list shows many waiting for table level lock (below) and running the batch program on the test machine with no other reqs being made to DB only took 4 mins. So I guess the problem lies in the table locking ?

I have uploaded table to ftp.askmonty.org/private v3.2015-09-04_v3.humans.sql

thanks again, John

Kill	4477055	root	localhost	v3	Query	0	Waiting for table level lock	

UPDATE humans set r = '0.0009999'*100000, RD = '0'*100000, ranking = '0' where human_id = '15673

Kill	4489481	v3user	localhost:49333	v3	Query	0	Waiting for table level lock	

select count(*)+1 AS position from humans WHERE division ='Heavyweight' AND sex='M' and is_boxe

Kill	4489483	v3user	localhost:49336	v3	Query	0	Sending data	

select count(*) AS division_size from humans WHERE division ='' AND sex='M' AND is_boxer != "0"

Kill	4489487	v3user	localhost:49340	v3	Query	0	Waiting for table level lock	

select count(*) AS division_size from humans WHERE division ='Middleweight' AND sex='M' AND is_

Kill	4489491	v3user	localhost:49344	v3	Query	0	Waiting for table level lock	

SELECT bout_id,vacant, interim, titles.division as division, first_name, last_name, supervisor_id, t

Kill	4489493	v3user	localhost:49346	v3	Query	0	Waiting for table level lock	

SELECT country_code, region_name, towncity_name, venue_name, ticketing_number, comment, star_ra

Kill	4489495	v3user	localhost:49348	v3	Query	0	Waiting for table level lock	

SELECT *, residence.towncity_name as residence_towncity_name, residence.country_code as residenc

Kill	4489499	v3user	localhost:49352	v3	Query	0	Waiting for table level lock	

SELECT *, residence.towncity_name as residence_towncity_name, residence.country_code as residencI installed 

Hi Elena,
I have done that now ....

SELECT plugin_status FROM information_schema.plugins WHERE plugin_name = 'feedback'

plugin_status ACTIVE

John

The issue fell through the cracks, sorry about that; but now 10.0 has gone EOL.
I hope you have long upgraded to a newer version. Have you experienced the problem since?