oqgraph-dev team mailing list archive

[Arjen Lentz <arjen@xxxxxxxxxxxxx>]

Hi all

MariaDB [test]> ALTER TABLE rsb ENGINE=innodb;
Query OK, 149988861 rows affected (10 days 38 min 43.06 sec)

Hmm, so the conversion of the 150M links from MyISAM to InnoDB took a very long time. I thought I'd configured the server sanely but apparently it had a reason to take its time - I didn't think it was sensible to abort-reconfigure-retry as even with the MariaDB progress stuff it's not that clear how long things will take in the end. It was in the 90% range for a long time....
Anyway, it completed.

So then I re-ran the original graph query same

MariaDB [test]> SELECT GROUP_CONCAT(linkid ORDER BY seq) FROM rsb_graph WHERE latch=1 AND origid=26 AND destid=902;
+--------------------------------------------------------------------------------------------------+
| GROUP_CONCAT(linkid ORDER BY seq)                                                                |
+--------------------------------------------------------------------------------------------------+
| 26,121173,464405,537450,618292,628498,860027,904681,610282,625967,990703,26641,905767,458952,902 |
+--------------------------------------------------------------------------------------------------+
1 row in set (2 hours 43 min 50.96 sec)


This is interesting. It actually takes longer on InnoDB.
Can't tell why from explain or anything since obviously OQGRAPH uses its internal voodoo with the underlying engine.

The schema is possibly not ideal for InnoDB:

CREATE TABLE `rsb` (
  `f` int(10) unsigned NOT NULL,
  `t` int(10) unsigned NOT NULL,
  `weight` float NOT NULL,
  PRIMARY KEY (`f`,`t`),
  KEY `t` (`t`)
)

in that the secondary key will have (t,f,t)
but on the other hand, a primary key lookup is direct rather than a two-step process.

What do you think, Antony - should we recommend using an auto-inc PK regardless?
I'll now set my test server to convert the table accordingly, to see how it behaves then.

alter table rsb
  drop primary key,
  add column id int unsigned auto_increment not null primary key,
  add unique (f,t);

I expect that to take "a while" again ;-)


Regards,
Arjen.


----- Original Message -----
> From: "Arjen Lentz" <arjen@xxxxxxxxxxxxx>
> To: oqgraph-dev@xxxxxxxxxxxxxxxxxxx
> Sent: Tuesday, 5 March, 2013 10:15:56 AM
> Subject: [Oqgraph-dev] social graph test
> Hi all
> 
> ./randsocial 1000000 50 50 3 |pv -l | mysql -u root
> --socket=/tmp/mysql.sock test
> (1M users, 50 friends, 50 fans/likes, max-weight 3)
> 
> ended up with 149988861 rows (1M * ((50+50) + 50) = 150M - duplicates
> that we ignore)
> 
> 
> Dijkstra on two arbitrary nodes:
> 
> 
> MariaDB [test]> SELECT GROUP_CONCAT(linkid ORDER BY seq) FROM
> rsb_graph WHERE latch=1 AND origid=26 AND destid=902;
> +--------------------------------------------------------------------------------------------------+
> | GROUP_CONCAT(linkid ORDER BY seq) |
> +--------------------------------------------------------------------------------------------------+
> | 26,121173,464405,537450,618292,628498,860027,904681,610282,625967,990703,26641,905767,458952,902
> | |
> +--------------------------------------------------------------------------------------------------+
> 1 row in set (1 hour 50 min 48.16 sec)
> 
> 
> Good news: it works cleanly.
> Bad news: slow. But this was MyISAM. I'm converting to InnoDB now and
> will retry.
> 
> 
> Cheers,
> Arjen.
> --
> Arjen Lentz, Exec.Director @ Open Query (http://openquery.com)
> Australian peace of mind for your MySQL/MariaDB infrastructure.
> 
> Follow us at http://openquery.com/blog/ & http://twitter.com/openquery
> 
> --
> Mailing list: https://launchpad.net/~oqgraph-dev
> Post to : oqgraph-dev@xxxxxxxxxxxxxxxxxxx
> Unsubscribe : https://launchpad.net/~oqgraph-dev
> More help : https://help.launchpad.net/ListHelp

-- 
Arjen Lentz, Exec.Director @ Open Query (http://openquery.com)
Australian peace of mind for your MySQL/MariaDB infrastructure.

Follow us at http://openquery.com/blog/ & http://twitter.com/openquery