Mindaugas, can you post the output of
SHOW CREATE TABLE ipaddr;
and
EXPLAIN select ip from ipaddr
where pool='INTERNET' and stype='S' and ls_id=3 and allocated is null
limit 1;
When you say it's too slow, how slow is it? And how fast when it is a
memory table?
Also, which specific version of 5.0 are you on? 5.0.x ... what is x?
Dan
On 10/17/06, Mindaugas <mind@stripped> wrote:
>
> Hello,
>
> For the Radius server we're using MySQL cluster and the following query
> looks too slow:
>
> select ip from ipaddr
> where pool='INTERNET' and stype='S' and ls_id=3 and allocated is null
> limit 1;
>
> Table ipaddr is small (~6MB, 38000 records). Fields in WHERE clause have
> few values and no indexes:
> - pool: 2 distinct values;
> - stype: 6 distinct values;
> - ls_id: 5 distinct values;
> - allocated is null for ~30000 of records.
>
> Table type is NDB. If I change it to MEMORY everything starts to fly.
>
> Of course there are a lot of updates to ipaddr table too. For every select
> there are 3 updates. But updates are of type "update something where
> ip=ipaddr" and ipaddr is unique key.
>
> What can cause slowdown in NDB case? Table is small and is in memory (5.0
> cluster). Maybe I can rewrite it in some better form for such case?
> MySQL setting are basically default. I did not find something in
> documentation about improving performance of NDB engine tables.
> Maybe increase read_buffer_size which is currently the default 128k?
> Server has 4GB of memory and runs x86_64 version of CentOS4 Linux.
>
> Thanks,
>
> Mindaugas
