How to project size requirements for /tmp partition for MySQL + CiviCRM

[mdlueck]

Greetings,

I just banged into a nasty awakening that MySQL uses /tmp file space to execute queries.

I have been setting my /tmp partitions to 1GB size. On a server I am building up, with no files other than a tiny .htaccess file in it... clicking on the Contribution Dashboard ends up filling the 1GB /tmp partition, at which point CiviCRM displays a very unhelpful

Code: [Select]

DB Error: a515ac9c2796ca0e23adbe92c68fc9fc error message.

Under the covers, CiviCRM logged:

Code: [Select]

"126 ** Incorrect key file for table '/tmp/#sql_55c_0.MYI'; try to repair it"
However I pulled the query trying to be executed out of the error log, ran it in PHPMyAdmin, and that is where I finally was told that /tmp disk space was being exceeded.

So, how am I able to better estimate /tmp partition size requirements now knowing that MySQL is going to use /tmp space while executing queries?

I am thankful,

[Chris Burgess]

MySQL is only using /tmp because it's run out of RAM. The best fix would be to give it more memory to work with, since when it's going to disk it's already slowed down by a factor of $heaps.

If you can't feed it bags of RAM, I would unmount the /tmp partition, run some big queries and watch the size of the directory as it runs, then multiply that by several.

Don't struggle with resource constraints if you can help it. GBs of disk and even GBs of RAM are so much cheaper to get than developer hours, it's simpler to throw hardware at this stuff than invest much time in fixing it. Optimise later!

[mdlueck]

MySQL is only using /tmp because it's run out of RAM. The best fix would be to give it more memory to work with

Additional RAM is additional monthly cost from the Cirtix Xen based hosting provider. Presently the client purchases 1GB of RAM. Peak RAM usage of this particular uptime has been 800MB, so it had 200MB left in fast RAM which it could have utilized.

I would still need some sort of formula to estimate additional RAM requirements.

If you can't feed it bags of RAM, I would unmount the /tmp partition, run some big queries and watch the size of the directory as it runs, then multiply that by several.

If I unmount the /tmp partition, where next does MySQL go to on disk for space when it runs out of RAM? "watch the size of the directory"... which directory?

Don't struggle with resource constraints if you can help it. GBs of disk and even GBs of RAM are so much cheaper to get than developer hours, it's simpler to throw hardware at this stuff than invest much time in fixing it. Optimise later!

This problem has taken me literally by surprise... never expected to bang into this brick wall. We have another NPO client we are running multiple websites on a similar Xen server, two of which have CiviCRM in them... one the production website, one development. That organization gets along perfectly with 1.5 GB RAM. So for this client - production site only - to "exhaust" 1GB RAM is unexpected. (But according to "free -m", like I said this IPL has peaked at 800MB usage.)

I am thankful,

[Chris Burgess]

Additional RAM is additional monthly cost from the Cirtix Xen based hosting provider. Presently the client purchases 1GB of RAM. Peak RAM usage of this particular uptime has been 800MB, so it had 200MB left in fast RAM which it could have utilized.

I would still need some sort of formula to estimate additional RAM requirements.

I don't think such a formula exists yet since it's dependent on your CiviCRM DB, so I'm proposing that you observe the behaviour until you can establish some expectations.

If I unmount the /tmp partition, where next does MySQL go to on disk for space when it runs out of RAM? "watch the size of the directory"... which directory?

MySQL will continue to use /tmp but on your root partition (which I am presuming is larger, but that might have been a risky assumption since the consequences of filling / are worse than filling /tmp). I probably should have said "give yourself tons of space in /tmp", which is what I'd expect unmounting /tmp to do. (Note that you may need to restart various services to handle the unmount.)

I'd then attempt to repeat the issue (which you say happens when viewing the Contributions dashboard?) with a couple of terminals open - one running du -sh /tmp repeatedly to show the size of /tmp, and the other running mytop to show what queries are being executed.

If you observe the MySQL query (it'll take ages since it's writing >1GB data) then capture it with

mysql -e 'show full processlist'

then use MySQL's EXPLAIN to understand why the query is writing so much to disk (probably an outlandish join of some sort).

This problem has taken me literally by surprise... never expected to bang into this brick wall. We have another NPO client we are running multiple websites on a similar Xen server, two of which have CiviCRM in them... one the production website, one development. That organization gets along perfectly with 1.5 GB RAM. So for this client - production site only - to "exhaust" 1GB RAM is unexpected. (But according to "free -m", like I said this IPL has peaked at 800MB usage.)

I should have been clearer that this isn't expected behaviour for CiviCRM - but I don't know the scale of your CiviCRM DB. (If you really have over a GB of contributions, congratulations! And please consider purchasing my employer's professional services /wink.)

[mdlueck]

All right, some additional findings:

/tmp is on a 1GB partition of its own. Usually it runs empty, just one .htaccess file in it.
/var is on a partition of its own, a 4GB partition. I moved MySQL to using /var/tmp rather than /tmp. Loading the Contribution Dashboard works. Hurray!

In so doing, it looks like /var/tmp space to run that one query peaked at about 2GB of space. Ouch!

I calculated the size of their CiviCRM DB, as follows:

Code: [Select]

SELECT table_schema "micc_civicrm", sum( data_length + index_length ) /1024 /1024 "Data Base Size in MB"
FROM information_schema.TABLES
GROUP BY table_schema
LIMIT 0 , 30

micc_civicrm Data Base Size in MB
information_schema 0.00878906
micc_civicrm 268.19322968
So I am baffled that MySQL needs 2GB of temp space to run one query against a 268MB database.

And it was a very long running query. According to PHPMyAdmin, when running the same query, it executed in something like 40 seconds. I just reran it again via PHPMyAdmin, and assuming caching assisting, this time it completed in a mere "Query took 0.0025 sec"

Any suggestions based on this additional research?

I am thankful,

[Chris Burgess]

Yes, capture the long-running query and inspect it to better understand why MySQL needs to do all this data. (This was mentioned above but I'll recap and expand a bit for you.)

mysql -e 'show full processlist' to output the queries running.

prepend EXPLAIN to the captured query and execute that in MySQL. Eg if your query was

SELECT * FROM a JOIN b ON b.x=a.x

then you want to ask MySQL,

EXPLAIN SELECT * FROM a JOIN b ON b.x=a.x

which will show you lots of information about how MySQL thinks that JOIN needs to be done.

Does all the Contribution dashboard data look right to you?

[mdlueck]

I captured the long running Contribution Dashboard query from the CiviCRM error log, and have been using that to run it via PHPMyAdmin. Here is the query:

Code: [Select]

SELECT  civicrm_contribution.id,
        contact_a.id as contact_id,
        contact_a.contact_type  as `contact_type`,
        contact_a.contact_sub_type  as `contact_sub_type`,
        contact_a.sort_name  as `sort_name`,
        contact_a.display_name  as `display_name`,
        civicrm_contribution.id as contribution_id,
        civicrm_contribution.currency as `currency`,
        civicrm_contribution.receive_date as `receive_date`,
        civicrm_contribution.non_deductible_amount as `non_deductible_amount`,
        civicrm_contribution.total_amount as `total_amount`,
        civicrm_contribution.fee_amount as `fee_amount`,
        civicrm_contribution.net_amount as `net_amount`,
        civicrm_contribution.trxn_id as `trxn_id`,
        civicrm_contribution.invoice_id as `invoice_id`,
        civicrm_contribution.cancel_date as `cancel_date`,
        civicrm_contribution.cancel_reason as `cancel_reason`,
        civicrm_contribution.receipt_date as `receipt_date`,
        civicrm_contribution.thankyou_date as `thankyou_date`,
        civicrm_contribution.source as `contribution_source`,
        civicrm_contribution.amount_level as `amount_level`,
        civicrm_contribution.is_test as `is_test`,
        civicrm_contribution.is_pay_later as `is_pay_later`,
        contribution_status.value as contribution_status_id,
        civicrm_contribution.check_number as `check_number`,
        civicrm_contribution.campaign_id as contribution_campaign_id,
        civicrm_financial_type.id as financial_type_id,
        civicrm_financial_type.name as financial_type,
        payment_instrument.id as instrument_id,
        payment_instrument.label as `payment_instrument`,
        civicrm_product.id as product_id,
        civicrm_product.name as `product_name`,
        civicrm_product.sku as `sku`,
        civicrm_contribution_product.id as contribution_product_id,
        civicrm_contribution_product.product_option as `product_option`,
        civicrm_contribution_product.fulfilled_date as `fulfilled_date`,
        civicrm_contribution_product.start_date as `contribution_start_date`,
        civicrm_contribution_product.end_date as `contribution_end_date`,
        civicrm_contribution.contribution_recur_id as `contribution_recur_id`,
        civicrm_financial_account.id as financial_account_id,
        civicrm_financial_account.accounting_code as accounting_code,
        civicrm_note.note as contribution_note,
        civicrm_batch.title as contribution_batch,
        contribution_status.label as contribution_status,
        payment_instrument.name as contribution_payment_instrument,
        civicrm_contribution.check_number as contribution_check_number
FROM civicrm_contact contact_a
LEFT JOIN civicrm_contribution ON civicrm_contribution.contact_id = contact_a.id
INNER JOIN civicrm_financial_type ON civicrm_contribution.financial_type_id = civicrm_financial_type.id
LEFT JOIN civicrm_entity_financial_account ON civicrm_entity_financial_account.entity_id = civicrm_contribution.financial_type_id
      AND civicrm_entity_financial_account.entity_table = 'civicrm_financial_type'
INNER JOIN civicrm_financial_account ON civicrm_financial_account.id = civicrm_entity_financial_account.financial_account_id
INNER JOIN civicrm_option_value cov ON cov.value = civicrm_entity_financial_account.account_relationship
      AND cov.name = 'Income Account is'
INNER JOIN civicrm_option_group cog ON cog.id = cov.option_group_id
      AND cog.name = 'account_relationship'
LEFT JOIN civicrm_contribution_product ON civicrm_contribution_product.contribution_id = civicrm_contribution.id
LEFT JOIN civicrm_product ON civicrm_contribution_product.product_id =civicrm_product.id
LEFT JOIN civicrm_entity_batch ON ( civicrm_entity_batch.entity_table = 'civicrm_contribution'
      AND civicrm_contribution.id = civicrm_entity_batch.entity_id )
LEFT JOIN civicrm_batch ON civicrm_entity_batch.batch_id = civicrm_batch.id
LEFT JOIN civicrm_note ON ( civicrm_note.entity_table = 'civicrm_contribution'
      AND civicrm_contribution.id = civicrm_note.entity_id )
LEFT JOIN civicrm_option_group option_group_payment_instrument ON ( option_group_payment_instrument.name = 'payment_instrument')
LEFT JOIN civicrm_option_value payment_instrument ON (civicrm_contribution.payment_instrument_id = payment_instrument.value
      AND option_group_payment_instrument.id = payment_instrument.option_group_id )
LEFT JOIN civicrm_option_group option_group_contribution_status ON (option_group_contribution_status.name = 'contribution_status')
LEFT JOIN civicrm_option_value contribution_status ON (civicrm_contribution.contribution_status_id = contribution_status.value
      AND option_group_contribution_status.id = contribution_status.option_group_id )
WHERE ( civicrm_contribution.is_test = 0 )
  AND (contact_a.is_deleted = 0)
GROUP BY civicrm_contribution.id
ORDER BY receive_date desc,
         contact_a.id
LIMIT 0, 10
It took 41 seconds to run the query after a server IPL.

Adding EXPLAIN to the beginning results in:

Code: [Select]

id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE cog const PRIMARY,UI_name UI_name 194 const 1 Using index; Using temporary; Using filesort
1 SIMPLE cov ref index_option_group_id_value,index_option_group_id_... index_option_group_id_name 391 const,const 1 Using where
1 SIMPLE civicrm_financial_account ALL PRIMARY NULL NULL NULL 12 Using join buffer
1 SIMPLE civicrm_entity_financial_account ref FK_civicrm_entity_financial_account_financial_acco... FK_civicrm_entity_financial_account_financial_acco... 4 micc_civicrm.civicrm_financial_account.id 2 Using where
1 SIMPLE civicrm_contribution ref FK_civicrm_contribution_contact_id,FK_civicrm_cont... FK_civicrm_contribution_financial_type_id 5 micc_civicrm.civicrm_entity_financial_account.enti... 3687 Using where
1 SIMPLE civicrm_financial_type eq_ref PRIMARY,UI_id PRIMARY 4 micc_civicrm.civicrm_contribution.financial_type_i... 1 Using where
1 SIMPLE civicrm_contribution_product ref FK_civicrm_contribution_product_contribution_id FK_civicrm_contribution_product_contribution_id 4 micc_civicrm.civicrm_contribution.id 1
1 SIMPLE civicrm_product eq_ref PRIMARY PRIMARY 4 micc_civicrm.civicrm_contribution_product.product_... 1
1 SIMPLE contact_a eq_ref PRIMARY,index_is_deleted PRIMARY 4 micc_civicrm.civicrm_contribution.contact_id 1 Using where
1 SIMPLE civicrm_entity_batch ref index_entity index_entity 199 const,micc_civicrm.civicrm_contribution.id 1
1 SIMPLE civicrm_batch eq_ref PRIMARY PRIMARY 4 micc_civicrm.civicrm_entity_batch.batch_id 1
1 SIMPLE civicrm_note ref index_entity index_entity 198 const,micc_civicrm.civicrm_contribution.id 1
1 SIMPLE option_group_payment_instrument const UI_name UI_name 194 const 1 Using index
1 SIMPLE payment_instrument ref index_option_group_id_value,index_option_group_id_... index_option_group_id_name 4 micc_civicrm.option_group_payment_instrument.id 3
1 SIMPLE option_group_contribution_status const UI_name UI_name 194 const 1 Using index
1 SIMPLE contribution_status ref index_option_group_id_value,index_option_group_id_... index_option_group_id_name 4 micc_civicrm.option_group_contribution_status.id 3

Does the "Using temporary" near the top of that EXPLAIN output mean that bit involved the file created in /var/tmp/ ?

And yes, the data seems not out of the ordinary.

I am thankful,

[Chris Burgess]

You're right about the tmp / filesort.

I tried the same query against a DB I had handy (DB size 6GB, 233K contributions) and the query took 22s, generating a 50MB file in /tmp to do it. The numbers you're seeing do suggest something's wrong, but I don't know what it is.

EXPLAIN on my DB looks very similar to yours. You might spot some difference if you look closer (sorry, don't have time to do so myself right now).

[mdlueck]

Major upset just now...

A day and half of work, four attempts to finally get the MySQL InnoDB CiviCRM database rebuilt... I had come across this document Tuesday EOD:

"Ensuring Schema Integrity on Upgrades"
http://wiki.civicrm.org/confluence/display/CRMDOC/Ensuring+Schema+Integrity+on+Upgrades

Which I used as the basis for the effort. I needed to add some steps as I chose to export from the old server / import to the new server, so I had to hand edit the export files to fix up the database and userid names. Finally the fourth time through I had a successful run, and...

ABSOLUTELY NO SPEED OR TMP FILE SIZE IMPROVEMENT!!! 

What else could possibly be wrong?

I am thankful,

[Chris Burgess]

Sorry, I'm all out of ideas based on what I can see via the forum

[mdlueck]

Greetings Chris,

One question just popped into my mind. The server hardware you ran your comparative tests again... how much RAM does your server have? This client's server has a 1GB slice. I am suspecting your server might have bookoo more RAM, thus masking the issue I am seeing.

I am thankful,

[Chris Burgess]

That DB was on a server with 8GB RAM. You mentioned a couple of other sites, but you might find your DB profiles differ because of more (activities|contributions|contacts|details) between DBs.

Might be informative to compare the sizes of the tables referenced in that query and see if something leaps out?

[mdlueck]

That DB was on a server with 8GB RAM.

Aaahhhh... I suspect the additional RAM is masking optimization issues. Thank you for that added context.

You mentioned a couple of other sites, but you might find your DB profiles differ because of more (activities|contributions|contacts|details) between DBs.

Might be informative to compare the sizes of the tables referenced in that query and see if something leaps out?

We have one other client using CiviCRM. That client is no where near 100,000 contributions in that table, etc... and are getting along just fine with 1.5GB RAM for multiple websites. We actually run CiviCRM on two Apache Multi-Site domains... Prod and Dev of the same site, and then other sites which are Drupal only.

So comparatively this new client with far less complex web app stack, single prod site only, to run into this extent of memory constraint issue was a total surprise to me. Back to the drawing board...

I am thankful,