List:Cluster« Previous MessageNext Message »
From:Dmitry Strygin Date:March 23 2010 2:41pm
Subject:Ordered indexes don't work?
View as plain text  
Hello guys,

we are trying to dry run NDB cluster (7.0.13), on 4 data nodes (single
replica just for now) with 16
gigs of memory on each:
ndb_mgm> show
Cluster Configuration
---------------------
[ndbd(NDB)]     5 node(s)
id=11   @172.18.40.9  (mysql-5.1.41 ndb-7.0.13, Nodegroup: 0, Master)
id=12   @172.18.40.10  (mysql-5.1.41 ndb-7.0.13, Nodegroup: 1)
id=13   @172.18.40.11  (mysql-5.1.41 ndb-7.0.13, Nodegroup: 2)
id=14   @172.18.40.12  (mysql-5.1.41 ndb-7.0.13, Nodegroup: 3)

[ndb_mgmd(MGM)] 1 node(s)
id=1    @172.18.40.6  (mysql-5.1.41 ndb-7.0.13)

[mysqld(API)]   1 node(s)
id=31   @172.18.40.9  (mysql-5.1.41 ndb-7.0.13)

ndb_mgm> all report mem
Node 11: Data usage is 62%(184316 32K pages of total 294912)
Node 11: Index usage is 29%(45524 8K pages of total 153632)
Node 12: Data usage is 62%(184213 32K pages of total 294912)
Node 12: Index usage is 29%(45548 8K pages of total 153632)
Node 13: Data usage is 62%(184399 32K pages of total 294912)
Node 13: Index usage is 29%(45572 8K pages of total 153632)
Node 14: Data usage is 62%(184576 32K pages of total 294912)
Node 14: Index usage is 29%(45528 8K pages of total 153632)

But we have an issue with the following query:
mysql> SELECT COUNT(*) FROM `users` WHERE `blocked` = 0;
+----------+
| COUNT(*) |
+----------+
|   499963 |
+----------+
1 row in set (15.50 sec)

It takes too long.
The primary key is (`id`, `blocked`) + btree key on `blocked`.
We also tried:
- primary (`id`) + BTREE key on `blocked` -- same effect 15 seconds.
- primary (`id`) + unique HASH key for (`blocked`,`id`) -- 5 seconds (3
times faster).
- primary (`id`) and no separate key for `blocked` -- 15 seconds.

It looks like ordered indexes are not being used. We also tryed 7.1.12beta
-- same effect.

mysql> EXPLAIN PARTITIONS SELECT COUNT(*) FROM `users` WHERE `blocked`
= 0;
+----+-------------+-------+--
--------------+------+---------------+---------+---------+-------+------+-------+
| id | select_type | table | partitions     | type | possible_keys |
key     | key_len | ref   | rows | Extra |
+----+-------------+-------+----------------+------+---------------+---------+---------+-------+------+-------+
|  1 | SIMPLE      | users | p0,p1,p2,p3,p4 | ref  | blocked       |
blocked | 4       | const |   10 |       |
+----+-------------+-------+----------------+------+---------------+---------+---------+-------+------+-------+
1 row in set (0.00 sec)

Current table `user`:
mysql> SHOW CREATE TABLE `users` \G
*************************** 1. row ***************************
      Table: users
Create Table: CREATE TABLE `users` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `email` varchar(128) COLLATE utf8_unicode_ci NOT NULL,
 `password` varchar(32) COLLATE utf8_unicode_ci NOT NULL,
 `firstname` varchar(64) COLLATE utf8_unicode_ci NOT NULL,
 `lastname` varchar(64) COLLATE utf8_unicode_ci NOT NULL,
 `name` varchar(128) COLLATE utf8_unicode_ci NOT NULL,
 `birthday` date NOT NULL,
 `gender` char(1) COLLATE utf8_unicode_ci NOT NULL,
 `country` char(2) COLLATE utf8_unicode_ci NOT NULL,
 `city` int(11) NOT NULL,
 `city2` int(11) NOT NULL,
 `city3` int(11) NOT NULL,
 `oldcity` varchar(64) COLLATE utf8_unicode_ci NOT NULL,
 `headline` varchar(128) COLLATE utf8_unicode_ci NOT NULL,
 `action` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
 `industry` tinyint(4) NOT NULL,
 `industry2` tinyint(4) NOT NULL,
 `industry3` tinyint(4) NOT NULL,
 `summary` text COLLATE utf8_unicode_ci NOT NULL,
 `specialties` text COLLATE utf8_unicode_ci NOT NULL,
 `registered` int(11) NOT NULL,
 `modified` int(11) NOT NULL,
 `last` int(11) NOT NULL,
 `status` tinyint(1) NOT NULL,
 `admin` tinyint(4) NOT NULL,
 `connections` int(11) NOT NULL,
 `level2` int(11) NOT NULL,
 `level3` int(11) NOT NULL,
 `recommendations` int(11) NOT NULL,
 `avatar` int(11) NOT NULL,
 `keywords` text COLLATE utf8_unicode_ci NOT NULL,
 `confirmed` tinyint(4) NOT NULL,
 `preid` int(11) NOT NULL,
 `notify_messages` tinyint(4) NOT NULL DEFAULT '1',
 `blocked` int(11) NOT NULL,
 `messages_blocked` int(11) NOT NULL,
 `allowspam` tinyint(4) NOT NULL DEFAULT '1',
 `send_digest` tinyint(1) NOT NULL DEFAULT '1',
 `send_fdigest` tinyint(4) NOT NULL DEFAULT '1',
 `pollseen` tinyint(4) NOT NULL,
 `show_profile_preview` tinyint(4) NOT NULL,
 `show_profile_to_all` tinyint(1) NOT NULL,
 `fullness` varchar(20) COLLATE utf8_unicode_ci NOT NULL,
 `public` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT
'111111111111',
 `link` varchar(32) COLLATE utf8_unicode_ci DEFAULT NULL,
 `import_faq_read` tinyint(4) NOT NULL,
 `spam` tinyint(4) NOT NULL DEFAULT '1',
 `paid` tinyint(4) NOT NULL COMMENT 'Тип платного
аккаунта',
 `paid_until` int(11) NOT NULL COMMENT 'Дата окончания
платного акка',
 `paid_reminded` int(11) NOT NULL,
 `paid_was` tinyint(4) NOT NULL,
 `balance` int(11) NOT NULL,
 `show_connections_to` tinyint(4) NOT NULL DEFAULT '3',
 `allow_messages_from` tinyint(4) NOT NULL DEFAULT '9',
 `hide_email` tinyint(4) NOT NULL,
 `search_priority` tinyint(4) NOT NULL DEFAULT '1',
 `paid_promails` int(11) NOT NULL,
 `paid_messages` int(11) NOT NULL,
 `fooled` tinyint(1) NOT NULL,
 `show_prosign` tinyint(1) NOT NULL DEFAULT '1',
 `nokia` tinyint(4) NOT NULL,
 `phone` varchar(20) COLLATE utf8_unicode_ci NOT NULL,
 `skypeid` varchar(64) COLLATE utf8_unicode_ci NOT NULL,
 `icqid` varchar(16) COLLATE utf8_unicode_ci NOT NULL,
 `address` varchar(128) COLLATE utf8_unicode_ci NOT NULL,
 `privacy` tinyint(4) NOT NULL DEFAULT '63',
 `notify_holidays` tinyint(1) NOT NULL DEFAULT '1',
 `tipmask` int(11) NOT NULL DEFAULT '0',
 `checked` tinyint(1) NOT NULL,
 `notify_birthdays` tinyint(1) NOT NULL DEFAULT '1',
 `warned` tinyint(4) NOT NULL,
 `bd_gift` tinyint(4) NOT NULL,
 `bd_gift_type` tinyint(4) NOT NULL,
 `show_ads` tinyint(1) NOT NULL DEFAULT '1',
 `ad_budget` int(11) NOT NULL,
 `ad_budget_satisfied` tinyint(4) NOT NULL,
 `ad_budget_depleted` tinyint(4) NOT NULL,
 `has_ads` tinyint(4) NOT NULL,
 `allow_invitations_from` tinyint(4) NOT NULL DEFAULT '15',
 `request_invitation_reason` tinyint(1) NOT NULL DEFAULT '0',
 `acc_buy_notify` tinyint(4) NOT NULL,
 `allow_group_invitations` tinyint(1) NOT NULL DEFAULT '1',
 `marked_read` int(11) NOT NULL,
 `seenGiftNotify` tinyint(4) NOT NULL,
 `twitter_count` int(11) NOT NULL DEFAULT '0',
 `facebook_count` int(11) NOT NULL DEFAULT '0',
 `hidden` tinyint(4) NOT NULL DEFAULT '0',
 PRIMARY KEY (`id`,`blocked`),
 UNIQUE KEY `email` (`email`),
 UNIQUE KEY `link` (`link`),
 KEY `preid` (`preid`),
 KEY `lastname` (`lastname`),
 KEY `registered` (`registered`),
 KEY `firstname` (`firstname`),
 KEY `paid` (`paid`),
 KEY `country` (`country`),
 KEY `city` (`city`),
 KEY `industry` (`industry`,`industry2`,`industry3`),
 KEY `blocked` (`blocked`)
) ENGINE=ndbcluster AUTO_INCREMENT=514519 DEFAULT CHARSET=utf8
COLLATE=utf8_unicode_ci PACK_KEYS=0
1 row in set (0.00 sec)

config.ini:
[NDBD DEFAULT]
NoOfReplicas=1
DataMemory=10G
IndexMemory=1500M
MaxNoOfExecutionThreads=8
MaxNoOfTables=8192
MaxNoOfAttributes=49512
MaxNoOfOrderedIndexes=8192
MaxNoOfUniqueHashIndexes=2048
MaxNoOfTriggers=8K
MaxNoOfConcurrentTransactions=4096
MaxNoOfConcurrentOperations=1000000
MaxNoOfLocalOperations=1100000

BatchSizePerLocalScan=512

FragmentLogFileSize=64M
NoOfFragmentLogFiles=2000
UndoIndexBuffer=256M
RedoBuffer=8M

##NoOfDiskPagesToDiskAfterRestartTUP=150
##NoOfDiskPagesToDiskAfterRestartACC=83
Diskcheckpointspeed=10M
Diskcheckpointspeedinrestart=1G

TimeBetweenGlobalCheckpoints=1000
TimeBetweenLocalCheckpoints=20

TimeBetweenWatchDogCheck=6000
TimeBetweenWatchDogCheckInitial=6000

LogLevelStartup=5
LogLevelShutdown=5
LogLevelNodeRestart=5
LogLevelCheckpoint=1
LogLevelConnection=1

StartPartialTimeout=60000
LockPagesInMainMemory=2
ODirect=1

[MYSQLD DEFAULT]

[NDB_MGMD DEFAULT]

[TCP DEFAULT]

# Managment Server
[NDB_MGMD]
Id=1
HostName=172.18.40.6

# Storage Engines

[NDBD]
Id=11
DataMemory=8G
IndexMemory=1100M
HostName=172.18.40.9
DataDir= /var/lib/mysql-cluster

[NDBD]
Id=12
HostName=172.18.40.10
DataDir= /var/lib/mysql-cluster

[NDBD]
Id=13
HostName=172.18.40.11
DataDir=/var/lib/mysql-cluster

[NDBD]
Id=14
HostName=172.18.40.12
DataDir=/var/lib/mysql-cluster

# SQL API Engines
[MYSQLD]
Id=31
HostName=172.18.40.9


Any ideas?

Thank you.

Thread
Ordered indexes don't work?Dmitry Strygin23 Mar
  • Re: Ordered indexes don't work?Geert Vanderkelen23 Mar
    • Re: Ordered indexes don't work?Dmitry Strygin23 Mar
      • Re: Ordered indexes don't work?Pekka Nousiainen23 Mar