StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POOut of memory MySQL with 24GB Server
text
Body
copied!<p>I have come across an annoying problem.</p> <p>I have made a system and now users are telling me that it is giving them the message:</p> <p>Out of memory (Needed 268435427 bytes)</p> <p>The entire database is 12MB in size and the query that is having the problem has ran fine for several months and isn't really that complex or large.</p> <p>The database is innodb. My server has 24GB of ram so I seriously doubt it is actually out of memory.</p> <p>my.cnf is as follows:</p> <blockquote> <p>key_buffer = 8000M</p> <p>max_allowed_packet = 1M</p> <p>table_cache = 2048M</p> <p>sort_buffer_size = 1M</p> <p>net_buffer_length = 1024M</p> <p>read_buffer_size = 1M</p> <p>read_rnd_buffer_size = 24M</p> <p>innodb_log_file_size = 5M</p> <p>innodb_log_buffer_size = 8M</p> <p>innodb_flush_log_at_trx_commit = 1</p> <p>innodb_lock_wait_timeout = 50</p> <p>innodb_buffer_pool_size = 1024M</p> <p>innodb_additional_mem_pool_size = 2M</p> <p>max_connections = 100</p> <p>query_cache_size = 128M</p> <p>query_cache_min_res_unit = 1024</p> <p>query_cache_limit = 16MB</p> <p>thread_cache_size = 100</p> <p>max_heap_table_size = 4096MB</p> </blockquote> <p>When looking in windows task manager I am seeing 18.8GB available but with only 100MB free. It is Windows 2008 64bit Server, could this be the source of the problem?</p> <hr> <p>Here is the query:</p> <pre><code>$currency = '(SELECT currencies.symbol FROM parts_trading, currencies WHERE parts_trading.enquiryRef = enquiries.id AND parts_trading.sellingCurrency = currencies.id LIMIT 1 )' ; $amountDueSQL = '( (SELECT SUM(quantity*(parts_trading.sellingNet + parts_trading.sellingVat)) FROM parts_trading WHERE parts_trading.enquiryRef = enquiries.id ) + (SELECT SUM(enquiries_custom_fees.feeAmountNet + enquiries_custom_fees.feeAmountVat) FROM enquiries_custom_fees WHERE enquiries_custom_fees.enquiryRef = enquiries.id ) )' ; $amountPaidSQL = 'COALESCE( (SELECT SUM(jobs_payments_advance.amount) FROM jobs_payments_advance WHERE jobs_payments_advance.jobRef = jobs.id ), 0 )' ; $result = $dbh->prepare("SELECT SQL_CALC_FOUND_ROWS jobs.id, jobs_states.state, jobs.creationDate, users.username, entity_details.name, enquiries.id as enquiryId, pendingCancelation, IF(entity_details.paymentTermsRef = 1, # Outer IF condition IF($amountDueSQL-$amountPaidSQL = 0.00, # Inner IF condition CONCAT('Paid in full (', $currency, $amountDueSQL, ')') # Inner IF TRUE , # End of inner IF TRUE IF($amountPaidSQL > 0, CONCAT('Part paid (', $currency, $amountPaidSQL, ')'), 'Unpaid' ) ) # End of inner IF , # End of TRUE for outer IF (SELECT entity_payment_terms.term FROM entity_details, entity_payment_terms WHERE entity_details.paymentTermsRef = entity_payment_terms.id AND entity_details.id = enquiries.entityRef ) # End of FALSE for outer IF ) AS payState, enquiries.orderNumber, IF((SELECT COUNT(*) FROM invoices_out, (SELECT * FROM invoices_out_reference GROUP BY jobRef) AS tb1 WHERE invoices_out.id = tb1.invoiceRef AND tb1.jobRef = jobs.id) > 1, 'Part-invoiced', (SELECT invoices_out.date FROM invoices_out, (SELECT * FROM invoices_out_reference GROUP BY jobRef) AS tb1 WHERE invoices_out.id = tb1.invoiceRef AND tb1.jobRef = jobs.id) ) AS invoicedDate, enquiries.id AS enquiryId, IF((SELECT COUNT(*) FROM invoices_out, (SELECT * FROM invoices_out_reference GROUP BY jobRef) AS tb1 WHERE invoices_out.id = tb1.invoiceRef AND tb1.jobRef = jobs.id) > 1, 'Multiple invoices', (SELECT invoices_out.id FROM invoices_out, (SELECT * FROM invoices_out_reference GROUP BY jobRef) AS tb1 WHERE invoices_out.id = tb1.invoiceRef AND tb1.jobRef = jobs.id) ) AS invoiceNumber, # If the state is 0 (i.e. they have an account, if true find out their payment terms, if false, instead reference the payment state directly. (SELECT MAX(etaDate) FROM parts_trading WHERE parts_trading.enquiryRef = enquiries.id) AS maxEtaDate, (SELECT COUNT(DISTINCT DATE(etaDate)) FROM parts_trading WHERE parts_trading.enquiryRef = enquiries.id) AS etaCounts, entity_credit_limits.creditLimit AS cLimit, COALESCE((SELECT SUM(qty*parts_trading_buying.buyingNet / (SELECT rateVsPound FROM currencies WHERE currencies.id = parts_trading_buying.buyingCurrency)) FROM parts_trading_buying WHERE parts_trading_buying.enquiryRef = enquiries.id ), 0 ) AS nonInvoicedBuyingCosts, COALESCE((SELECT SUM(feeAmountNet/(SELECT rateVsPound FROM currencies WHERE currencies.id = parts_trading_buying.buyingCurrency)) FROM parts_trading_buying_charges, parts_trading_buying WHERE parts_trading_buying_charges.partRef = parts_trading_buying.id AND parts_trading_buying.enquiryRef = enquiries.id ), 0 ) AS nonInvoicedBuyingFeeCosts, (SELECT SUM(quantity*parts_trading.sellingNet) / COALESCE( (SELECT invoices_out.rate FROM invoices_out, invoices_out_reference WHERE invoices_out.id = invoices_out_reference.invoiceRef AND invoices_out_reference.jobRef = jobs.id LIMIT 1), (SELECT rateVsPound FROM currencies WHERE currencies.id = parts_trading.sellingCurrency) ) FROM parts_trading WHERE parts_trading.enquiryRef = enquiries.id ) AS sellingParts, COALESCE((SELECT SUM(enquiries_custom_fees.feeAmountNet) / COALESCE( (SELECT rate FROM invoices_out, invoices_out_reference WHERE invoices_out.id = invoices_out_reference.invoiceRef AND invoices_out_reference.jobRef = jobs.id LIMIT 1), (SELECT rateVsPound FROM currencies WHERE currencies.id = parts_trading.sellingCurrency ) ) FROM enquiries_custom_fees, parts_trading WHERE enquiries_custom_fees.enquiryRef = enquiries.id AND parts_trading.enquiryRef = enquiries.id), 0) AS sellingFees, COALESCE((SELECT SUM(parts_shipping_out.shippingOutCost) FROM parts_shipping_out, parts_shipping_arrival_dates, parts_shipping_v2 WHERE parts_shipping_out.arrivalsRef = parts_shipping_arrival_dates.id AND parts_shipping_arrival_dates.shippingRef = parts_shipping_v2.id AND parts_shipping_v2.jobRef = jobs.id ), 0 ) AS actualShippingOutFromEua, (SELECT SUM(quantity*parts_trading.sellingNet) / COALESCE( (SELECT invoices_out.rate FROM invoices_out, invoices_out_reference WHERE invoices_out.id = invoices_out_reference.invoiceRef AND invoices_out_reference.jobRef = jobs.id LIMIT 1), (SELECT rateVsPound FROM currencies WHERE currencies.id = parts_trading.sellingCurrency) ) + COALESCE((SELECT SUM(enquiries_custom_fees.feeAmountNet) / COALESCE( (SELECT rate FROM invoices_out, invoices_out_reference WHERE invoices_out.id = invoices_out_reference.invoiceRef AND invoices_out_reference.jobRef = jobs.id LIMIT 1), (SELECT rateVsPound FROM currencies WHERE currencies.id = parts_trading.sellingCurrency ) ) FROM enquiries_custom_fees WHERE enquiries_custom_fees.enquiryRef = enquiries.id), 0) - COALESCE((SELECT SUM(qty*parts_trading_buying.buyingNet / (SELECT rateVsPound FROM currencies WHERE currencies.id = parts_trading_buying.buyingCurrency)) FROM parts_trading_buying WHERE parts_trading_buying.enquiryRef = enquiries.id ), 0 ) - COALESCE((SELECT SUM(feeAmountNet/(SELECT rateVsPound FROM currencies WHERE currencies.id = parts_trading_buying.buyingCurrency)) FROM parts_trading_buying_charges, parts_trading_buying WHERE parts_trading_buying_charges.partRef = parts_trading_buying.id AND parts_trading_buying.enquiryRef = enquiries.id ), 0 ) - COALESCE((SELECT SUM(parts_shipping_out.shippingOutCost) FROM parts_shipping_out, parts_shipping_arrival_dates, parts_shipping_v2 WHERE parts_shipping_out.arrivalsRef = parts_shipping_arrival_dates.id AND parts_shipping_arrival_dates.shippingRef = parts_shipping_v2.id AND parts_shipping_v2.jobRef = jobs.id ), 0 ) FROM parts_trading, parts_trading_buying WHERE parts_trading.enquiryRef = enquiries.id AND parts_trading_buying.counterpartRef = parts_trading.id ) AS margin FROM jobs, jobs_states, enquiries, users, jobs_payment_status, entity_details LEFT JOIN entity_credit_limits ON entity_details.id = entity_credit_limits.entityRef WHERE jobs.stateRef = jobs_states.id AND IF(paymentStateRef = 0, 1, (jobs_payment_status.id = jobs.paymentStateRef)) # ^ If true it causes a result for each payment state (i.e. 3), so we group on state below, shouldn't cause probs. AND jobs.enquiryRef = enquiries.id AND enquiries.entityRef = entity_details.id AND users.id = enquiries.traderRef AND enquiries.traderRef = ? LIMIT ?, ?") ; </code></pre> <p>If I try setting PHP execution memory above 3.5GB Apache wont start (I'm using xampp). I must be using a 32-bit version of PHP? This is the same for the INNODB_BUFFER_POOL_SIZE, which I would like to be 14GB, but mysql won't start if I do that. </p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload