Running a CiviCRM query at
http://pastebin.com/6M1emH14, I get
ERROR 5 (HY000): Out of memory (Needed 1640964 bytes)
Though I can submit a query change recommendation to CiviCRM, I'm hoping this can be fixed by adding an index or changing a parameter in the my.cnf file.
Should I increase key_buffer_size or table_cache or is it another parameter? An InnoDB parameter perhaps (CiviCRM tables are all InnoDB)?
+----+--------------------+-----------------------------------------+-----------------+------------------------------------------------------------------------------------+------------------+---------+---------------------------+--------+------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------------+-----------------------------------------+-----------------+------------------------------------------------------------------------------------+------------------+---------+---------------------------+--------+------------------------------+
| 1 | PRIMARY | contact_a | index | NULL | index_is_deleted | 1 | NULL | 149732 | Using index; Using temporary |
| 1 | PRIMARY | civicrm_group_contact-605 | ref | UI_contact_group | UI_contact_group | 4 | step2_crm_db.contact_a.id | 1 | Distinct |
| 1 | PRIMARY | civicrm_group_contact-608 | ref | UI_contact_group | UI_contact_group | 4 | step2_crm_db.contact_a.id | 1 | Distinct |
| 1 | PRIMARY | civicrm_group_contact-612 | ref | UI_contact_group | UI_contact_group | 4 | step2_crm_db.contact_a.id | 1 | Using where; Distinct |
| 7 | DEPENDENT SUBQUERY | civicrm_group_contact | unique_subquery | UI_contact_group,FK_civicrm_group_contact_group_id | UI_contact_group | 8 | func,const | 1 | Using where |
| 6 | DEPENDENT SUBQUERY | civicrm_value_1_riding__circonscription | eq_ref | unique_entity_id,FK_civicrm_value_1_riding__circonscription_entity_id,INDEX_riding | unique_entity_id | 4 | func | 1 | Using where; Using temporary |
| 6 | DEPENDENT SUBQUERY | contact_a | eq_ref | PRIMARY | PRIMARY | 4 | func | 1 | Using where; Using index |
| 5 | DEPENDENT SUBQUERY | civicrm_group_contact | unique_subquery | UI_contact_group,FK_civicrm_group_contact_group_id | UI_contact_group | 8 | func,const | 1 | Using where |
| 4 | DEPENDENT SUBQUERY | civicrm_value_1_riding__circonscription | eq_ref | unique_entity_id,FK_civicrm_value_1_riding__circonscription_entity_id,INDEX_riding | unique_entity_id | 4 | func | 1 | Using where; Using temporary |
| 4 | DEPENDENT SUBQUERY | contact_a | eq_ref | PRIMARY | PRIMARY | 4 | func | 1 | Using where; Using index |
| 3 | DEPENDENT SUBQUERY | civicrm_group_contact | unique_subquery | UI_contact_group,FK_civicrm_group_contact_group_id | UI_contact_group | 8 | func,const | 1 | Using where |
| 2 | DEPENDENT SUBQUERY | civicrm_value_1_riding__circonscription | eq_ref | unique_entity_id,FK_civicrm_value_1_riding__circonscription_entity_id,INDEX_riding | unique_entity_id | 4 | func | 1 | Using where; Using temporary |
| 2 | DEPENDENT SUBQUERY | contact_a | eq_ref | PRIMARY | PRIMARY | 4 | func | 1 | Using where; Using index |
+----+--------------------+-----------------------------------------+-----------------+------------------------------------------------------------------------------------+------------------+---------+---------------------------+--------+------------------------------+
Some my.cnf settings:
#
# * Fine Tuning
#
key_buffer = 256M
max_allowed_packet = 16M
thread_stack = 128K
thread_cache_size = 8
max_connections = 256
table_cache = 2048
sort_buffer_size = 2M
read_buffer_size = 2M
read_rnd_buffer_size =8M
myisam_sort_buffer_size = 64M
thread_concurrency = 16
tmp_table_size = 500M
max_heap_table_size = 500M
# * InnoDB
#
# InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/.
# Read the manual for more InnoDB related options. There are many!
# You might want to disable InnoDB to shrink the mysqld process by circa 100MB.
#skip-innodb
innodb_buffer_pool_size = 1500M
#innodb_log_file_size = 256M
innodb_log_buffer_size = 4M
innodb_flush_log_at_trx_commit = 2
innodb_thread_concurrency = 8
innodb_flush_method=O_DIRECT