The MySQL database supporting this blog was consuming massive amount of CPU, which effectively lead to site going down 3-4 times this week.���̃u���O�́A mysql�f�[�^�x�[�X�Ɏx���������鋐�z��CPU��A�ǂ̃T�C�g����ʓI�Ƀ��[�h������A���̏T�Ԉȓ���3-4��ł��B I knew an obvious culprit.����m���Ă��������Ȍ����ł��B However reality differed.�����������ɈقȂ��Ă��܂��B

I knew for sometime I had a jumbo query, which was not cached, and is likely to contribute to MySQL server load:���‚����͎���m���Ă����̃W�����{�N�G���A����́A����̃L���b�V���A����ѐ��������Ɋ�^���邱�Ƃ�MySQL�T�[�o�ɕ��ׁF
SELECT cat_ID, cat_name AS Category, count( * ) AS 'Count' cat_id��I�����A�쐬�J�e�S��cat_name�Ƃ��āA���i * �j�Ƃ���'��'
FROM $wpdb->categories, $wpdb->post2cat, $wpdb->posts����$ wpdb - >�J�e�S���[�A $ wpdb - > post2cat �A $ wpdb - >�L��
WHERE cat_ID = category_id and�ǂ�cat_id = category_id��
category_nicename != 'headline' and ID = post_id and category_nicename �I = '���o��'�����id = post_id��
post_status = 'publish' post_status = '���s'
GROUP BY cat_name�O���[�v��cat_name
HAVING count( * ) > 10���Ƃ�count �i * �j > 10
ORDER BY 'Count' DESC���я�'��'�~��
LIMIT 0 , 10���0 �A 10

I couldn't see an obvious way to optimize this without curbing the functionality.���̖����ȕ�@���Q�Ƃł��܂���ł��������}������@�\���œK������B In case you are wondeering substituting category_nicename != 'headline' with cat_ID = 37 doesn't help.���Ȃ�������ɖ�肪���������ꍇwondeering category_nicename �I = '���o��'��cat_id = 37�w���v�͂���܂���B

I also thought about using�����l�������g�p���� slow query log�X���[�N�G�����O to pinpoint the problem.���̖�����肵�܂��B Fortunately none of them was required.�K���ȂȂ��̂��邱�Ƃ͕K�{�ł��B I got an email from friendly���͓d�q���[�����t�����h���[ WestHost westhost High Load support.�����ׂ��T�|�[�g���܂��B Jonny found out by repeatedly running 'SHOW FULL PROCESSLIST;' that the culprit was:�W���j�[�����‚���܂����A�E�g���J��Ԃ����s����'�ڍ׃t��PROCESSLIST��; '�́A�����́F

SELECT distinct cat_ID, cat_name�͂�����cat_id��I�����A cat_name
FROM st_categories, st_post2cat���st_categories �A st_post2cat
ORDER BY category_nicename;���я�category_nicename ;

This took me by surprise.����͎����������܂��B Th query takes anywhere between 3-5 seconds.�N�G����܂�3-5�b�Ԃ̔C�ӂ̏ꏊ�ł��B In high loads with multiple simultaneous requests that is good enough to overload the server.���ׂ̍��������̓����v�����\���ɂ́A�T�[�o�[�ɕ��ׂ������Ă��܂��B The solution was deceptively simple:�ꌩ�P���ȉ�����́F

SELECT distinct cat_ID, cat_name�͂�����cat_id��I�����A cat_name
FROM st_categories���st_categories
ORDER BY category_nicename;���я�category_nicename ;

The original query was redundant and wasteful.���̃N�G���́A�璷���△�ʂł��B Originally I intended to display only categories with one or more posts in them.���Ƃ��Ǝ��f�B�X�v���C�݂̂�ړI�Ƃ��A 1�‚܂��͕����̋L�����J�e�S���[�ʂɂ��Ă��܂��B However somewhere down the line I forgot to add the WHERE clause.�������ǂ����_�E���̍s��lj�����p�X���[�h��Y��ẮA where�߂ɂ��܂��B Today all my categories have posts.����̂��ׂĂ̎��̃J�e�S���[�ɓ��e���܂��B So simply removing st_post2cat from the FROM list is good enough to bring down the execution time to 0.01 seconds. st_post2cat����̂ŁA�P�Ƀ��X�g����폜����ɏ\���Ȃ��^��ł����l���ǂ��̎��s���Ԃ�0.01�b�ł��B

The take home lessons are:���b�X���́A�����ċA��F

  • Never guess bottleneck queries; find it.�v�������Ȃ��{�g���l�b�N�N�G��;���‚��邱�Ƃł��B
  • Simple tool like "SHOW FULL PROCESS LIST" is often as good as definitive, yet harder to analyze tool like slow query log.�ȒP�ȃc�[���̂悤��"�ڍ׃t���v���Z�X�̃��X�g"�́A���΂��Ό���I�Ƃ��ėǂ��Ƃ��āA�܂�����𕪐͂���c�[���̂悤�ȃX���[�N�G�����O�ɋL�^���܂��B
  • Having good support from your���ǂ��T�|�[�g������ web hosting provider�E�F�u�z�X�e�B���O�v���o�C�_ doesn't hurt either�����͂���܂���̂����ꂩ