PC Benchmarking with GridDB

# The data and entire project is available for download at: 
# https://github.com/retowyss/pc-benchmarking-r-griddb

library(tidyverse)
library(jsonlite)

# This is just some janitorial data wrangling
# The function reads the data captured by CapFrameX and makes it available
# in a easy to use format.
# Returns a list of three things: system-info, capture-data, sensor-data
read_capframex_json <- function(x) {
  capframe_data <- read_json(x, simplifyVector = TRUE)
  
  # We need a common length for the captured sensor data because some rows
  # are length n and some are n-k 
  common_n <- length(capframe_data$Runs$SensorData2$MeasureTime$Values[[1]])
  
  list(
    info    = capframe_data$Info %>% 
      keep(~!is.null(.)) %>% 
      as_tibble(),
    capture = as_tibble(map(capframe_data$Runs$CaptureData, ~ .[[1]])),
    sensors = imap_dfc(capframe_data$Runs$SensorData2, function(df, names) {
      
      m <- length(df$Values[[1]])
      vals <- vector(mode = typeof(df$Values[[1]]), common_n)
      
      # if vector is shorter, we fill last value
      if(m < common_n) {
        vals <- c(df$Values[[1]], rep(df$Values[[1]][m], common_n - m))
      } else {
        vals <- (df$Value[[1]])[1:common_n]
      }
      
      res <- list()
      # Some list names are not appropriate as tibble headers
      n <- str_remove(str_replace_all(names, "/", "_"), "_")
      res[[n]] <- vals
      
      as_tibble(res)
    })
  )
}

capframex_data <- read_capframex_json("data/CapFrameX-DOOMEternalx64vk.exe-2.json")

capframex_data$info %>% 
  select(Processor, GPU, SystemRam, GameName, OS, Motherboard) %>% 
  knitr::kable()

capframex_data$capture %>% 
  select(TimeInSeconds, MsBetweenPresents) %>% 
  head(n = 3) %>% 
  knitr::kable()

capframex_data$sensors %>% 
  select(MeasureTime, BetweenMeasureTime,
   cpu_power = amdcpu_0_power_0,
   cpu_temperature = amdcpu_0_temperature_0,
   gpu_clock = nvidiagpu_0_clock_0, 
   gpu_power = nvidiagpu_0_power_0, 
   gpu_temperature = nvidiagpu_0_temperature_0
  ) %>% 
  head(n = 2) %>% 
  knitr::kable()

# We are using a docker based setup that can run this project ootb
# it's available here: https://github.com/retowyss/rstudio-server-griddb-docker

# You can set this up without docker, the containers use:

# Latest version griddb                 https://docs.griddb.net
# Latest version of R and tidyverse     R >= 4.0.0
# Latest version of JDBC                https://github.com/griddb/jdbc

# For general setup without docker you can follow this post:
# https://griddb.net/en/blog/analyzing-nba-play-by-play-data-using-r-and-griddb/

library(RJDBC)
drv <- JDBC(
  driverClass = "com.toshiba.mwcloud.gs.sql.Driver",
  # Point this to your gridstore jar
  classPath = "/jdbc/bin/gridstore-jdbc.jar"
)

# IP and port depend on your setup
griddb <- dbConnect(
  drv, 
  "jdbc:gs://172.20.0.42:20001/dockerGridDB/public", 
  "admin", 
  "admin"
)

# System info table: cfx_info
dbSendUpdate(griddb, paste(
  "CREATE TABLE IF NOT EXISTS cfx_info", 
  "(id INTEGER, Processor STRING, GPU STRING, SystemRam STRING,", 
  "GameName STRING, OS STRING, Motherboard STRING);"
))

# Frame time capture data: cfx_capture
dbSendUpdate(griddb, paste(
  "CREATE TABLE IF NOT EXISTS cfx_capture", 
  "(info_id INTEGER, TimeInSeconds FLOAT, MsBetweenPresents FLOAT);"
))

# Sensor data: cfx_sensors
dbSendUpdate(griddb, paste(
  "CREATE TABLE IF NOT EXISTS cfx_sensors", 
  "(info_id INTEGER, MeasureTime FLOAT, BetweenMeasureTime FLOAT,", 
  "cpu_power FLOAT, cpu_temperature FLOAT, gpu_clock FLOAT, gpu_power FLOAT,", 
  "gpu_temperature FLOAT);"
))

dbInsertTable <- function(conn, name, df, append = TRUE) {
  for (i in seq_len(nrow(df))) {
    dbWriteTable(conn, name, df[i, ], append = append)
  }
  invisible(TRUE)
}

# This is not a "production" ready approach of doing this, but it's sufficient
# for this demo :)
# Inserts dataset (cfx_data) into database (conn) using ID (uid)
insert_cfx_data <- function(conn, cfx_data, uid) {
  
  dbInsertTable(conn, "cfx_info", 
    cfx_data$info %>% 
      transmute(id = uid, Processor, GPU, SystemRam, GameName, OS, Motherboard)
  )
  
  dbInsertTable(conn, "cfx_capture", 
    cfx_data$capture %>% 
      transmute(info_id = uid, TimeInSeconds, MsBetweenPresents)
  )
  
  dbInsertTable(conn, "cfx_sensors",
    cfx_data$sensors %>% 
      transmute(
        info_id = uid, 
        MeasureTime, 
        BetweenMeasureTime,
        cpu_power = amdcpu_0_power_0,
        cpu_temperature = amdcpu_0_temperature_0,
        gpu_clock = nvidiagpu_0_clock_0, 
        gpu_power = nvidiagpu_0_power_0, 
        gpu_temperature = nvidiagpu_0_temperature_0
      )            
  )
  invisible(TRUE)
}

# Insert (this takes a while, don't run while knitting the doc)
json_files <- list.files("data")
walk2(json_files, 1:length(json_files), function(x, i) {
  insert_cfx_data(griddb, read_capframex_json(paste0("data/", x)), i)
})

# Check
dbGetQuery(griddb,"SELECT * FROM cfx_info;") %>% 
  knitr::kable()

# Check
dbGetQuery(griddb,"SELECT COUNT(*) FROM cfx_capture;")

##         
## 1 778780

# We use paste and str_interp to build up our SQL in a readable and easy to
# understand way
get_performance_summary <- function(conn, cpu, gpu, game) {
  system_info <- paste(
    "SELECT ID",
    "FROM cfx_info",
    "WHERE Processor = '${cpu}' AND GPU = '${gpu}' AND GameName = '${game}'"
  )
  
  capture <- paste(
    "SELECT AVG(MsBetweenPresents) AS frame_time,",
    "MAX(TimeInSeconds) AS session_time,",
    "COUNT(*) AS frame_count",
    "FROM (", system_info, ") info",
    "LEFT JOIN (SELECT * FROM cfx_capture) capture",
    "ON info.id = capture.info_id"
  )

  sensors <-paste(
    "SELECT AVG(cpu_power) AS cpu_power, ",
    "AVG(cpu_temperature) AS cpu_temperature,",
    "AVG(gpu_clock) AS gpu_clock, AVG(gpu_power) AS gpu_power,",
    "AVG(gpu_temperature) AS gpu_temperature",
    "FROM (", system_info, ") info",
    "LEFT JOIN (SELECT * FROM cfx_sensors) sensors",
    "ON info.id = sensors.info_id"
  )
  
  capture_res <- dbGetQuery(conn, str_interp(capture))
  sensors_res <- dbGetQuery(conn, str_interp(sensors))
  
  as_tibble(bind_cols(
    tibble(cpu = cpu, gpu = gpu, game = game),
    capture_res %>% 
      mutate(fps = 1000 / frame_time), 
    sensors_res
  ))
}

gtx_1650_rollerdrome <- get_performance_summary(griddb, 
  cpu =  "AMD Ryzen 9 5900X", 
  gpu =  "NVIDIA GeForce GTX 1650", 
  game = "ROLLERDROME"
)

glimpse(gtx_1650_rollerdrome)

## Rows: 1
## Columns: 12
## $ cpu             <chr> "AMD Ryzen 9 5900X"
## $ gpu             </chr><chr> "NVIDIA GeForce GTX 1650"
## $ game            </chr><chr> "ROLLERDROME"
## $ frame_time      <dbl> 2.660829
## $ session_time    </dbl><dbl> 400.8515
## $ frame_count     </dbl><dbl> 150650
## $ fps             </dbl><dbl> 375.8227
## $ cpu_power       </dbl><dbl> 95.65372
## $ cpu_temperature </dbl><dbl> 56.58357
## $ gpu_clock       </dbl><dbl> 1848.533
## $ gpu_power       </dbl><dbl> 74.72834
## $ gpu_temperature </dbl><dbl> 60.41905</dbl></chr>

summary2text <- function(summary_data) {
  spinable <-paste0(
    "Your system featuring a ${cpu} and ${gpu} performance in ", 
    "a ${round(session_time/60)} minutes session of ${game} ",
    "was ${round(fps)} FPS.",
    "The graphics card used ${round(gpu_power)}W and ",
    "ran at ${round(gpu_temperature)}°C, ",
    "while the CPU used ${round(cpu_power)}W and ", 
    "ran at ${round(cpu_temperature)}°C"
  )
  
  str_interp(spinable, env = summary_data)
}

summary2text(gtx_1650_rollerdrome)

## [1] "Your system featuring a AMD Ryzen 9 5900X and NVIDIA GeForce GTX 1650 performance in a 7 minutes session of ROLLERDROME was 376 FPS.The graphics card used 75W and ran at 60°C, while the CPU used 96W and ran at 57°C"

get_sensor_data <- function(conn, cpu, gpu, game) {
  system_info <- paste(
    "SELECT ID",
    "FROM cfx_info",
    "WHERE Processor = '${cpu}' AND GPU = '${gpu}' AND GameName = '${game}'"
  )

  sensors <-paste(
    "SELECT *",
    "FROM (", system_info, ") info",
    "LEFT JOIN (SELECT * FROM cfx_sensors) sensors",
    "ON info.id = sensors.info_id"
  )

  sensors_res <- dbGetQuery(conn, str_interp(sensors))
  
  cbind(tibble(cpu = cpu, gpu = gpu, game = game), as_tibble(sensors_res))
}

gtx_1080_ti_doom <- get_sensor_data(griddb, 
  cpu =  "AMD Ryzen 9 5900X", 
  gpu =  "NVIDIA GeForce GTX 1080 Ti", 
  game = "Doom Eternal"                                  
) 

gtx_1650_doom <- get_sensor_data(griddb, 
  cpu =  "AMD Ryzen 9 5900X", 
  gpu =  "NVIDIA GeForce GTX 1650", 
  game = "Doom Eternal"                                  
) 

doom_data <- bind_rows(gtx_1080_ti_doom, gtx_1650_doom)

ggplot(doom_data, aes(x = MeasureTime, y = gpu_power, color = gpu)) +
  geom_line()

ggplot(doom_data, aes(x = MeasureTime, y = gpu_temperature, color = gpu)) +
  geom_line()

# Technically there may be a small delay until the gpu-die heats up and
# the current temperature of the heatsink may also affect the gpu temperature 
cor.test(gtx_1080_ti_doom$gpu_power, gtx_1080_ti_doom$gpu_temperature)

## 
##  Pearson's product-moment correlation
## 
## data:  gtx_1080_ti_doom$gpu_power and gtx_1080_ti_doom$gpu_temperature
## t = 48.622, df = 7071, p-value < 2.2e-16
## alternative hypothesis: true correlation is not equal to 0
## 95 percent confidence interval:
##  0.4828882 0.5178252
## sample estimates:
##       cor 
## 0.5005605