mysql ndb cluster - poor performance, hanging queries, I'm lost

Hi,

I’ve installed 2-node cluster for highly visited web application in php. I’m using memcached for caching, I’m caching whole pages, every page changes two times a day but needs lots of queries to build.

I thought it doesn’t matter of mysql performance, because pages are not generated very often.

But I have big problems when page cache is cleared at once. Many pages are generated at the same time and mysql starts to hold some queries. When I look at show processlist, it shows something like that:

±----±-----------±--------------------±-------±-------- ±-----±-------------±------------------------------------ ------------------------+
| Id | User | Host | db | Command | Time | State | Info |
±----±-----------±--------------------±-------±-------- ±-----±-------------±------------------------------------ ------------------------+
| 273 | php-app | localhost | phpapp | Query | 146 | Sending data | select model_name from (select im_model_id as mid from issues_d gro |
| 274 | php-app | localhost | phpapp | Query | 146 | NULL | SELECT file_name FROM files JOIN data_file on file_id=df_fileder by |
| 275 | php-app | localhost | phpapp | Query | 144 | NULL | SELECT site_id FROM sites WHERE site_name='example.com |
… many same rows
| 276 | php-app | localhost | phpapp | Query | 144 | NULL | SELECT site_id FROM sites WHERE site_name='example.com |
| 277 | php-app | localhost | phpapp | Query | 142 | NULL | SELECT site_id FROM sites WHERE site_name='example.com |
| 278 | php-app | localhost | phpapp | Query | 141 | NULL | SELECT site_id FROM sites WHERE site_name='example.com

and database goes out of connections. No more client can connect any more and these hanging queries in null state are there until manual server restart.

I have ubuntu edgy server with mysql 5.0.24a-9 from standard package, 64bit.

Anyone has any idea, how to get rid of these hanging queries or how to improve cluster performance? Thanks for any idea!

(edit - I also find these lines in syslog, on both machines, so it’s probably not hardware issue):

mysqld got signal 11;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
We will try our best to scrape up some info that will hopefully help diagnose
the problem, but since we have already crashed, something is definitely wrong
and this may fail.

key_buffer_size=16777216
read_buffer_size=131072
max_used_connections=5
max_connections=150
threads_connected=1
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 342782 K
bytes of memory
Hope that’s ok; if not, decrease some variables in the equation.

You “sites” table seem to be part of most of the faulty connections.
Test reparing the sites table and see if that works.

Otherwise you can have a problem with PHP’s pooling of mysql connections.
Try setting down the mysql server “wait_timeout” parameter so that mysqld is garbage collecting inactive query threads.

Thanks for your advice, I’ve recreated sites table and set that wait_timeout. It looks promising!