Understanding slow observations performance

[htmlboss]

Observations are slow to load on the website.

-- MySQL dump 10.19  Distrib 10.3.34-MariaDB, for debian-linux-gnu (x86_64)
--
-- Host: localhost    Database: navigator
-- ------------------------------------------------------
-- Server version   10.3.34-MariaDB-0ubuntu0.20.04.1

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Table structure for table `datatypes`
--

DROP TABLE IF EXISTS `datatypes`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `datatypes` (
  `key` varchar(64) NOT NULL,
  `name` varchar(256) DEFAULT NULL,
  `unit` varchar(256) DEFAULT NULL,
  PRIMARY KEY (`key`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `platform_metadata`
--

DROP TABLE IF EXISTS `platform_metadata`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `platform_metadata` (
  `platform_id` int(11) NOT NULL,
  `key` varchar(64) NOT NULL,
  `value` varchar(256) DEFAULT NULL,
  PRIMARY KEY (`platform_id`,`key`),
  CONSTRAINT `platform_metadata_ibfk_1` FOREIGN KEY (`platform_id`) REFERENCES `platforms` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `platforms`
--

DROP TABLE IF EXISTS `platforms`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `platforms` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `type` enum('drifter','argo','glider','mission','animal') NOT NULL,
  `unique_id` varchar(128) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_id` (`unique_id`)
) ENGINE=InnoDB AUTO_INCREMENT=16954 DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `samples`
--

DROP TABLE IF EXISTS `samples`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `samples` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `datatype_key` varchar(64) DEFAULT NULL,
  `value` float DEFAULT NULL,
  `depth` float DEFAULT NULL,
  `station_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `station_id` (`station_id`),
  KEY `idx_dt_st` (`datatype_key`,`station_id`),
  CONSTRAINT `samples_ibfk_1` FOREIGN KEY (`datatype_key`) REFERENCES `datatypes` (`key`),
  CONSTRAINT `samples_ibfk_2` FOREIGN KEY (`station_id`) REFERENCES `stations` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1520831199 DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `stations`
--

DROP TABLE IF EXISTS `stations`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `stations` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(256) DEFAULT NULL,
  `platform_id` int(11) NOT NULL,
  `time` datetime NOT NULL,
  `latitude` float NOT NULL,
  `longitude` float NOT NULL,
  PRIMARY KEY (`id`),
  KEY `ix_stations_platform_id` (`platform_id`),
  KEY `idx_t_lat_lon` (`time`,`latitude`,`longitude`),
  KEY `idx_stations_pid_time` (`platform_id`,`time`),
  CONSTRAINT `stations_ibfk_1` FOREIGN KEY (`platform_id`) REFERENCES `platforms` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10936407 DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2022-04-10 17:11:12

[htmlboss]

Some observations:

• Not sure why our charset is latin1 when the default in the wild is utf8. Latin 1 encodes < 256 characters, whereas utf8 is more robust.
• There are indeed indexes in the database! (my bad 😝) KEY == INDEX

TO DO

• Run a CPU profile against an observational API query to find out where the bottleneck is. Could leverage @JustinElms prior work with the plotting scripts?
• EXPLAIN QUERY should help with figuring out what the DB is doing under the hood IF the DB is the bottleneck.

[dwayne-hart]

That must come in from sqlalchemy.

On Wed, Apr 13, 2022 at 2:02 PM Nabil @.***> wrote:

Some observations:

• Not sure why our charset is latin1 when the default appears to be utf-8. Latin 1 encodes < 256 characters, whereas utf-8 is more robust.
• There are indeed indexes in the database! (my bad 😝) KEY == INDEX

[image: image] https://user-images.githubusercontent.com/5572045/163227760-72cab643-8989-40ce-b16c-f0a97ac2ad0c.png

— Reply to this email directly, view it on GitHub https://github.com/DFO-Ocean-Navigator/Ocean-Data-Map-Project/issues/1006#issuecomment-1098257379, or unsubscribe https://github.com/notifications/unsubscribe-auth/AKSZ5OOYFX4AK72Y4HDM4BDVE3ZLBANCNFSM5TLFVSTA . You are receiving this because you are subscribed to this thread.Message ID: <DFO-Ocean-Navigator/Ocean-Data-Map-Project/issues/1006/1098257379@ github.com>