Hi Marko,
On 2025-01-07 12:22, Marko Mäkelä wrote:
No history length increase issue on 10.6.13 with 4GB log file.
BUT quite bad behaviour on 10.6.13 with 256MB log file, i.e. some
history length caused by cleanup
process still remained at the beginning, and increased history length
remained even after everything
finished. Also 1-2 seconds stall happened several times.
(see the attached graphs)
On 10.5.20, I had no such issue on both 4GB log file and 256MB log
file,
and its behaviour was quite
10.4.34.
Can you (or anyone) post a test script as well as some details of the
test environment that is reproducing this regression? I would need to
reproduce this regression in order to analyze and fix it.
I could reproduce 'history list increase' issue with the attached schema
and python script,
that executes 50% R-C DML and 50% R-R DQL using 64 threads.
On 10.5.20, history length at the end of each iteration is quite stable,
like < 50.
On 10.6.13, history length keeps increasing.
My configuration is not that special.
innodb_flush_method = O_DIRECT
innodb_change_buffering = none
innodb_buffer_pool_size = 17179869184
innodb_buffer_pool_instances = 64
Kazuhiko
import threading
import time
import pymysql
import random
host = "10.0.1.1"
port = 3306
user = "testuser_1"
password = "testpassword1"
database = "erp5_test_1"
num_threads = 64
num_items = 100
query_list = [
[
"SET TRANSACTION ISOLATION LEVEL READ COMMITTED",
"BEGIN",
"SELECT uid, path from catalog WHERE path IN (" + ", ".join("'%s'" for _ in range(num_items)) + ")",
"SELECT uid, path from catalog WHERE uid IN (" + ", ".join("%s" for _ in range(num_items)) + ")",
"REPLACE INTO catalog (`uid`, `path`) VALUES " + ", ".join("(%s, '%s')" for _ in range(num_items)),
"COMMIT",
],
[
"BEGIN",
"SELECT uid, path from catalog WHERE path IN (" + ", ".join("'%s'" for _ in range(num_items)) + ")",
"SELECT uid, path from catalog WHERE uid IN (" + ", ".join("%s" for _ in range(num_items)) + ")",
"COMMIT",
],
]
connection_list = [
pymysql.connect(host=host, port=port, user=user, password=password, database=database, cursorclass=pymysql.cursors.DictCursor)
for _ in range(num_threads + 1)
]
def worker(thread_id):
"""
Worker function for each thread.
"""
conn = connection_list[thread_id]
cursor = conn.cursor()
try:
for _ in range(100):
uid_list = [random.randint(1, 10**12) for _ in range(num_items)]
if thread_id % 2 == 0:
query = query_list[0]
args = sum(([e, e, e, e] for e in uid_list), [])
else:
query = query_list[1]
args = sum(([e, e] for e in uid_list), [])
for q in query:
num_args = q.count('%s')
args_ = args[:num_args]
cursor.execute(q, args_)
args = args[num_args:]
except Exception as e:
print(f"Thread {thread_id}: Error: {e}")
raise
finally:
cursor.close()
if __name__ == "__main__":
conn = connection_list[-1]
cursor = conn.cursor()
for i in range(100):
threads = []
start_time = time.time()
for thread_id in range(num_threads):
thread = threading.Thread(target=worker, args=(thread_id,))
threads.append(thread)
thread.start()
for thread in threads:
thread.join()
end_time = time.time()
print(f"Iteration {i}: executed in {end_time - start_time:.4f} seconds")
cursor.execute("SHOW TABLE STATUS LIKE 'catalog'")
print(cursor.fetchall())
cursor.execute("SHOW ENGINE INNODB STATUS")
print([e for e in cursor.fetchone()['Status'].splitlines() if 'History' in e][0])
CREATE TABLE `catalog` (
`uid` BIGINT UNSIGNED NOT NULL,
`security_uid` INT UNSIGNED,
`owner` varchar(255) binary NOT NULL default '',
`viewable_owner` varchar(255) binary NOT NULL default '',
`path` varchar(255) NOT NULL default '',
`relative_url` varchar(255) NOT NULL default '',
`parent_uid` BIGINT UNSIGNED default '0',
`id` varchar(255) default '',
`description` text,
`title` varchar(255) default '',
`meta_type` varchar(255) default '',
`portal_type` varchar(255) default '',
`opportunity_state` varchar(255) default '',
`corporate_registration_code` varchar(255),
`ean13_code` varchar(255),
`validation_state` varchar(255) default '',
`simulation_state` varchar(255) default '',
`causality_state` varchar(255) default '',
`invoice_state` varchar(255) default '',
`payment_state` varchar(255) default '',
`event_state` varchar(255) default '',
`immobilisation_state` varchar(255) default '',
`reference` varchar(255) binary default '',
`grouping_reference` varchar(255) default '',
`grouping_date` datetime,
`source_reference` varchar(255) default '',
`destination_reference` varchar(255) default '',
`string_index` varchar(255),
`int_index` INT,
`float_index` real,
`has_cell_content` bool,
`creation_date` datetime,
`modification_date` datetime,
`indexation_timestamp` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE
CURRENT_TIMESTAMP,
PRIMARY KEY (`uid`),
KEY `security_uid` (`security_uid`),
KEY `owner` (`owner`),
KEY `viewable_owner` (`viewable_owner`),
KEY `Parent` (`parent_uid`),
KEY `Path` (`path`),
KEY `Title` (`title`),
KEY `Reference` (`reference`),
KEY `relative_url` (`relative_url`),
KEY `Portal Type` (`portal_type`, `reference`),
KEY `opportunity_state` (`opportunity_state`),
KEY `validation_state_portal_type` (`validation_state`, `portal_type`),
KEY `simulation_state_portal_type` (`simulation_state`, `portal_type`),
KEY `causality_state_portal_type` (`causality_state`, `portal_type`),
KEY `invoice_state` (`invoice_state`),
KEY `payment_state` (`payment_state`),
KEY `event_state` (`event_state`)
) ENGINE=InnoDB;
_______________________________________________
discuss mailing list -- [email protected]
To unsubscribe send an email to [email protected]
