https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=42127 Bug ID: 42127 Summary: Inventory tool ( inventory.pl ) does not work well with very large numbers of items Initiative type: --- Sponsorship --- status: Product: Koha Version: Main Hardware: All OS: All Status: NEW Severity: enhancement Priority: P5 - low Component: Tools Assignee: koha-bugs@lists.koha-community.org Reporter: kyle@bywatersolutions.com QA Contact: testopia@bugs.koha-community.org tools/inventory.pl calls GetItemsForInventory with offset => 0 but no size parameter. In C4::Items, the LIMIT clause is conditional: $query .= " LIMIT $offset, $size" if ( $offset and $size ); Because $offset is 0, the LIMIT is never applied. The entire matching result set is loaded into memory via fetchall_arrayref({}). For a library with 500k+ items and broad filters, this easily exhausts available RAM and triggers the OOM killer. The problem is compounded when "Compare barcodes list to inventory" is checked because GetItemsForInventory is called a second time, and the second result set is duplicated into a hash, tripling the in-memory footprint. Test Plan: 1. Have a Koha instance with a large items table (500k+ items) 2. Go to Tools > Inventory 3. Select a branch but leave other filters broad 4. Check "Compare barcodes list to inventory" 5. Upload a barcode file and submit 6. Note the Plack worker's memory consumption spikes, the process may even be killed by the OOM killer I used this query to create 500,000 items: CREATE TEMPORARY TABLE numbers (n INT PRIMARY KEY); INSERT INTO numbers (n) SELECT a.n + b.n*10 + c.n*100 + d.n*1000 + e.n*10000 + f.n*100000 FROM (SELECT 0 n UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) a, (SELECT 0 n UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) b, (SELECT 0 n UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) c, (SELECT 0 n UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) d, (SELECT 0 n UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) e, (SELECT 0 n UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) f WHERE a.n + b.n*10 + c.n*100 + d.n*1000 + e.n*10000 + f.n*100000 BETWEEN 1 AND 500000; INSERT INTO items (biblionumber, biblioitemnumber, barcode, homebranch, holdingbranch, itype, notforloan, damaged, itemlost, withdrawn, itemcallnumber, cn_sort, dateaccessioned, location) SELECT b.biblionumber, b.biblioitemnumber, CONCAT('TESTBARCODE', LPAD(numbers.n, 7, '0')), br.branchcode, br.branchcode, (SELECT itemtype FROM itemtypes LIMIT 1), 0, 0, 0, 0, CONCAT('QA ', LPAD(numbers.n, 7, '0')), CONCAT('QA ', LPAD(numbers.n, 7, '0')), CURDATE(), 'GEN' FROM numbers CROSS JOIN (SELECT biblionumber, biblioitemnumber FROM biblioitems LIMIT 1) b CROSS JOIN (SELECT branchcode FROM branches LIMIT 1) br; DROP TEMPORARY TABLE numbers; -- You are receiving this mail because: You are the assignee for the bug. You are watching all bug changes.