Author Topic: How to project size requirements for /tmp partition for MySQL + CiviCRM (Read 3085 times)

mdlueck · July 02, 2014, 06:40:18 pm

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 · July 03, 2014, 01:37:02 am

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 · July 03, 2014, 04:48:22 am

Quote from: Chris Burgess on July 03, 2014, 01:37:02 am

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.

Quote from: Chris Burgess on July 03, 2014, 01:37:02 am

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?

Quote from: Chris Burgess on July 03, 2014, 01:37:02 am

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 · July 03, 2014, 01:07:11 pm

Quote from: mdlueck on July 03, 2014, 04:48:22 am

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.

Quote from: mdlueck on July 03, 2014, 04:48:22 am

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).

Quote from: mdlueck on July 03, 2014, 04:48:22 am

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 · July 03, 2014, 03:07:28 pm

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 · July 03, 2014, 03:24:06 pm

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 · July 03, 2014, 03:45:11 pm

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 · July 03, 2014, 04:29:05 pm

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 · July 10, 2014, 07:29:47 am

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 · July 14, 2014, 08:27:19 pm

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

mdlueck · July 15, 2014, 09:09:16 am

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 · July 15, 2014, 03:03:04 pm

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 · July 15, 2014, 07:27:15 pm

Quote from: Chris Burgess on July 15, 2014, 03:03:04 pm

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.

Quote from: Chris Burgess on July 15, 2014, 03:03:04 pm

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,

CiviCRM Community Forums (archive)

News:

Author Topic: How to project size requirements for /tmp partition for MySQL + CiviCRM (Read 3085 times)

mdlueck

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

Chris Burgess

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

mdlueck

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

Chris Burgess

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

mdlueck

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

Chris Burgess

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

mdlueck

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

Chris Burgess

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

mdlueck

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

Chris Burgess

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

mdlueck

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

Chris Burgess

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

mdlueck

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