Description:
Hello,
first let my say that I love MySQL and I am already running some great software on it for over 5 years.
I am currently installing a Glassfish 3.1 + MySQL Cluster 7.1. After finishing that I figured out that JOINs are horrible slow. On my local laptop the query takes 0.6 s on the cluster it takes 2:54 minutes.
Here is the query:
SELECT DISTINCT t1.ID AS a1, t1.DTYPE AS a2, t1.CREATION_DATE AS a3, t1.LABEL AS a4, t1.MODIFICATIONDATE AS a5, t1.ORDER_NUMBER AS a6, t1.STATE AS a7, t1.MAKER_FK AS a8, t2.ID AS a9, t2.LOGIN AS a10, t2.PASSWORD_S_H_A256 AS a11, t3.ID AS a12, t3.AUTHORIZED_REPRESENTATIVE_FK AS a13 FROM THING t1 LEFT OUTER JOIN (HIRED_BY_ORGANIZATIONS2TRUSTED t21 JOIN THING t17 ON (t17.ID = t21.TRUSTED_CONSULTANTS_ID_FK) JOIN PERSON t20 ON (t20.ID = t17.ID)) ON (t21.HIRED_BY_ORGANIZATIONS_ID_FK = t1.ID) LEFT OUTER JOIN (THING2CONFIGS t22 JOIN CONFIG t0 ON (t0.ID = t22.CONFIGS_ID_FK)) ON (t22.THING_ID_FK = t1.ID) LEFT OUTER JOIN (AGENTS2OWNS t23 JOIN THING t4 ON (t4.ID = t23.OWNS_ID_FK)) ON (t23.AGENTS_ID_FK = t1.ID) LEFT OUTER JOIN (AGENT2MBOXES t24 JOIN INTERNET_ADDRESS t5 ON (t5.ID = t24.MBOXES_ID_FK)) ON (t24.AGENT_ID_FK = t1.ID) LEFT OUTER JOIN (THING2SAME_AS t25 JOIN THING t6 ON (t6.ID = t25.SAME_AS_ID_FK)) ON (t25.THING_ID_FK = t1.ID) LEFT OUTER JOIN (THING t7 JOIN USER_INTERACTION t8 ON (t8.ID = t7.ID)) ON (t8.PROVIDER_FK = t1.ID) LEFT OUTER JOIN (AGENT2ADDRESSES t26 JOIN THING t9 ON (t9.ID = t26.ADDRESSES_ID_FK) JOIN ADDRESS t10 ON (t10.ID = t9.ID)) ON (t26.AGENT_ID_FK = t1.ID) LEFT OUTER JOIN (GROUPS2MEMBERS t27 JOIN THING t11 ON (t11.ID = t27.GROUPS_ID_FK) JOIN GROUPS t13 ON (t13.ID = t11.ID)) ON (t27.MEMBERS_ID_FK = t1.ID) LEFT OUTER JOIN (THING2COMMENTS t28 JOIN COMMENT t14 ON (t14.ID = t28.COMMENTS_ID_FK)) ON (t28.THING_ID_FK = t1.ID) LEFT OUTER JOIN (AGENT2PHONES t29 JOIN THING t15 ON (t15.ID = t29.PHONES_ID_FK) JOIN PHONE t16 ON (t16.ID = t15.ID)) ON (t29.AGENT_ID_FK = t1.ID), ORGANIZATION t3, AGENT t2, CONTACT_PERSON t19, AGENT t18 WHERE ((t17.ID IN ('913')) AND (((t3.ID = t1.ID) AND (t2.ID = t1.ID)) AND (t1.DTYPE = 'Organization'))) LIMIT 0, 250
Actually this query should return all Organizations that trust a certain consultant.
So I am trying MySQL Cluster 7.2.1 that is what I currently got:
Local
0.6 s
MySQLCluster 7.1
2:54 minutes
MySQLCluster 7.2.1
3:18 minutes
Now I wanted to run the Analyze Table command for 81 tables. Unfortunately one node of the system crashes after about 40 tables processed.
Here is the system I am using:
Linux ip-10-48-98-35 2.6.35.14-97.44.amzn1.i686 #1 SMP Mon Oct 24 16:03:22 UTC 2011 i686 i686 i386 GNU/Linux
It is an amazon ec2 instance.
How to repeat:
Actually I don't know. Have a look into the ndb_error report.
I can reproduce it when I run:
ANALYZE TABLE PAYMENT_METHOD;
Description: Hello, first let my say that I love MySQL and I am already running some great software on it for over 5 years. I am currently installing a Glassfish 3.1 + MySQL Cluster 7.1. After finishing that I figured out that JOINs are horrible slow. On my local laptop the query takes 0.6 s on the cluster it takes 2:54 minutes. Here is the query: SELECT DISTINCT t1.ID AS a1, t1.DTYPE AS a2, t1.CREATION_DATE AS a3, t1.LABEL AS a4, t1.MODIFICATIONDATE AS a5, t1.ORDER_NUMBER AS a6, t1.STATE AS a7, t1.MAKER_FK AS a8, t2.ID AS a9, t2.LOGIN AS a10, t2.PASSWORD_S_H_A256 AS a11, t3.ID AS a12, t3.AUTHORIZED_REPRESENTATIVE_FK AS a13 FROM THING t1 LEFT OUTER JOIN (HIRED_BY_ORGANIZATIONS2TRUSTED t21 JOIN THING t17 ON (t17.ID = t21.TRUSTED_CONSULTANTS_ID_FK) JOIN PERSON t20 ON (t20.ID = t17.ID)) ON (t21.HIRED_BY_ORGANIZATIONS_ID_FK = t1.ID) LEFT OUTER JOIN (THING2CONFIGS t22 JOIN CONFIG t0 ON (t0.ID = t22.CONFIGS_ID_FK)) ON (t22.THING_ID_FK = t1.ID) LEFT OUTER JOIN (AGENTS2OWNS t23 JOIN THING t4 ON (t4.ID = t23.OWNS_ID_FK)) ON (t23.AGENTS_ID_FK = t1.ID) LEFT OUTER JOIN (AGENT2MBOXES t24 JOIN INTERNET_ADDRESS t5 ON (t5.ID = t24.MBOXES_ID_FK)) ON (t24.AGENT_ID_FK = t1.ID) LEFT OUTER JOIN (THING2SAME_AS t25 JOIN THING t6 ON (t6.ID = t25.SAME_AS_ID_FK)) ON (t25.THING_ID_FK = t1.ID) LEFT OUTER JOIN (THING t7 JOIN USER_INTERACTION t8 ON (t8.ID = t7.ID)) ON (t8.PROVIDER_FK = t1.ID) LEFT OUTER JOIN (AGENT2ADDRESSES t26 JOIN THING t9 ON (t9.ID = t26.ADDRESSES_ID_FK) JOIN ADDRESS t10 ON (t10.ID = t9.ID)) ON (t26.AGENT_ID_FK = t1.ID) LEFT OUTER JOIN (GROUPS2MEMBERS t27 JOIN THING t11 ON (t11.ID = t27.GROUPS_ID_FK) JOIN GROUPS t13 ON (t13.ID = t11.ID)) ON (t27.MEMBERS_ID_FK = t1.ID) LEFT OUTER JOIN (THING2COMMENTS t28 JOIN COMMENT t14 ON (t14.ID = t28.COMMENTS_ID_FK)) ON (t28.THING_ID_FK = t1.ID) LEFT OUTER JOIN (AGENT2PHONES t29 JOIN THING t15 ON (t15.ID = t29.PHONES_ID_FK) JOIN PHONE t16 ON (t16.ID = t15.ID)) ON (t29.AGENT_ID_FK = t1.ID), ORGANIZATION t3, AGENT t2, CONTACT_PERSON t19, AGENT t18 WHERE ((t17.ID IN ('913')) AND (((t3.ID = t1.ID) AND (t2.ID = t1.ID)) AND (t1.DTYPE = 'Organization'))) LIMIT 0, 250 Actually this query should return all Organizations that trust a certain consultant. So I am trying MySQL Cluster 7.2.1 that is what I currently got: Local 0.6 s MySQLCluster 7.1 2:54 minutes MySQLCluster 7.2.1 3:18 minutes Now I wanted to run the Analyze Table command for 81 tables. Unfortunately one node of the system crashes after about 40 tables processed. Here is the system I am using: Linux ip-10-48-98-35 2.6.35.14-97.44.amzn1.i686 #1 SMP Mon Oct 24 16:03:22 UTC 2011 i686 i686 i386 GNU/Linux It is an amazon ec2 instance. How to repeat: Actually I don't know. Have a look into the ndb_error report. I can reproduce it when I run: ANALYZE TABLE PAYMENT_METHOD;