MySQL/MariaDB Data extraction / String manipulation / "TopN" processing => Performance of C++ vs php vs python

Question

EDIT1: Suspicion that mariadb++ is affecting my performance results compared to the raw "mariadb/mysql Connector-C"??

EDIT2: I did a barebones C-Only implementation and it comes in at 222ms vs the 262ms using mariadb++. 40ms is significant, given the fixed DB overhead.

EDIT3: Changed mysql_store_result to mysql_use_result, which means "unbuffered query" in MySQL lingo (php was already using that)... => 130ms. Half (!) of where we were, and about 3x php speed now. mariadb++ seems to always use mysql_store_result. I have filed an issue on mariadb++. --- If someone could still have a look at my c++ string and map processing that would be great...

---

Possible answer with a c++ wrapper around the mysql_* functions in this answer below.

---

Original Question..

Somewhat simplified task is to efficiently extract about a 250,000 user records from a mariadb and do some string munging and then find the "TopN" (eg most common firstnames, etc) for 3 fields.

Focus is on performance (real life case is bigger). Focus is firstly on algorithm: I think the unordered_map hashtable followed by partial_sort_copy is pretty efficient, but happy to be proved wrong. Second focus is on the string munging mechanics. Am I extracting/munging and mapping efficiently? With as few std::string copies as possible? (I realise the std::tolower() is not properly utf8 compatible). I decided on a std::move in one place. See comments.

The benchmark is similar code in ("cough") php which is actually NOT HALF BAD! php was only about 50% slower than this c++ code compiled with clang-8 -O3. Database is localhost and data is in memory. If I repeat code in loop, I get 50% of one CPU core on this process and 50% of another CPU core on the mysqld process. I realise the database is a big part of the problem here. Trying to minimise the rest.

Not particularly "generic" and not trying to be, just clear fast code.

Code review / advice please? Timing results at bottom (my own mini timer class, not relevant). Connection details left off.

BTW: I thought of the "do it in the DB" way. I can do one (not 3!) fields in the DB in 300ms (more than the c++ takes for all 3 fields). The DB query can't do all 3 fields at the same time. I used: SELECT field, count(*) as cnt group by field order by cnt desc limit 10;. Even the php code is faster than that.

#include "mariadb++/account.hpp"
#include "mariadb++/concurrency.hpp"
#include "mariadb++/connection.hpp"
#include "mariadb++/statement.hpp"
#include "mariadb++/types.hpp"
#include <algorithm>
#include <iostream>
#include <string>
#include <unordered_map>

void ltrim(std::string& s) {
  s.erase(s.begin(), std::find_if(s.begin(), s.end(), [](int ch) { return !std::isspace(ch); }));
}

void rtrim(std::string& s) {
  s.erase(std::find_if(s.rbegin(), s.rend(), [](int ch) { return !std::isspace(ch); }).base(),
          s.end());
}

void trim(std::string& s) { ltrim(s); rtrim(s); }

void strtolower(std::string& s) {
  std::transform(s.begin(), s.end(), s.begin(),
                 [](unsigned char c) { return std::tolower(c); }
  );
}

std::vector<std::pair<std::string, int>> top_n(const std::unordered_map<std::string, int>& map, int n) {
  std::vector<std::pair<std::string, int>> top_n(n, {"", 0});
  std::partial_sort_copy(map.begin(), map.end(), top_n.begin(), top_n.end(),
                         [](auto& a, auto& b) { return a.second > b.second; });
  return top_n;
}

void print (const std::vector<std::pair<std::string, int>>& top_n) {
  std::for_each(top_n.begin(), top_n.end(),
                [](auto& e) { std::cout << e.first << ": " << e.second << "\n"; });
}

int main() {
  std::shared_ptr<mariadb::connection> m_con = con();
  mariadb::statement_ref qry = m_con->create_statement("select email,firstname,lastname from member");

  std::unordered_map<std::string, int> domains, firstnames, lastnames;
  {
    Timer t1("fetch");
    mariadb::result_set_ref res = qry->query();
    while (res->next()) {
      // mariadb++ creates a copy, but it needs to. The result row will die shortly.
      std::string domain = res->get_string(0);
      trim(domain); // in place
      if (size_t pos = domain.find('@'); pos != std::string::npos) {
        strtolower(domain); // in place
        // this temporary will get moved
        domains[domain.substr(pos + 1)]++;
      }

      std::string firstname = res->get_string(1);
      trim(firstname);
      strtolower(firstname);
      // godbolt testing seems to show std::move prevents a copy here
      firstnames[std::move(firstname)]++;

      std::string lastname = res->get_string(2);
      trim(lastname);
      strtolower(lastname);
      lastnames[std::move(lastname)]++;
    }
  }
  {
    const int n = 10;
    Timer t1("freqs");
    std::cout << "\ndomains\n";
    print(top_n(domains, n));
    std::cout << "\nfirstnames\n";
    print(top_n(firstnames, n));
    std::cout << "\nlastnames\n";
    print(top_n(lastnames, n));
  }
}

Basic profiling results (and output so it's more obvious what the code does).

fetch=262.02ms

domains
t...sanitised: 53687
g...: 41827
h...: 17583
...

firstnames
david: 4042
john: 3348
james: 2774
...

lastnames
smith: 2142
jones: 1652
williams: 1187
...
freqs=2.87693ms

Just for comparison here is the php code:

<?php

$con = new PDO('...sanitised...');
$con->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, false);

$domains = [];
$firstnames = [];
$lastnames = [];

$start = microtime(true);
foreach ($con->query("select email,firstname,lastname from member;", PDO::FETCH_NUM) as $row)
{
  $domain = trim($row[0]);
  $domain = substr($domain, strpos($domain, '@') + 1);
  if (!isset($domains[$domain])) $domains[$domain] = 0;
  $domains[$domain]++;

  $firstname = strtolower(trim($row[1]));
  if (!isset($firstnames[$firstname])) $firstnames[$firstname] = 0;
  $firstnames[$firstname]++;

  $lastname = strtolower(trim($row[2]));
  if (!isset($lastnames[$lastname])) $lastnames[$lastname] = 0;
  $lastnames[$lastname]++;
}

echo "fetch=" . round((microtime(true) - $start) * 1000, 2) . "\n";

$start = microtime(true);

echo "\ndomains\n";
arsort($domains);
foreach (array_slice($domains, 0, 10) as $domain => $freq) {
  echo $domain . ': ' . $freq. "\n";
}

echo "\nfirstnames\n";
arsort($firstnames);
foreach (array_slice($firstnames, 0, 10) as $firstname => $freq) {
  echo $firstname . ': ' . $freq. "\n";
}

echo "\nlastnames\n";
arsort($lastnames);
foreach (array_slice($lastnames, 0, 10) as $lastname => $freq) {
  echo $lastname . ': ' . $freq. "\n";
}

echo "freqs=" . round((microtime(true) - $start) * 1000, 2) . "\n";

And the slightly depressing (from a c++ POV) performance results. 370ms vs 262ms (lowest on 10 trials for both). (UPDATE: at top. After bypassing mariadb++ and using C-lib directly with mysql_use_result the c++ dropped to 128ms or almost 3x faster than php with lots of mysqld overhead -- better). C++ was faster by 3x on the "freqs" section, but it's apples and oranges, because php is doing a sort of the whole array and c++ is doing a partial_sort_copy of the top10.

fetch=370.93ms

domains
...

firstnames
...

lastnames
...

freqs=11.1ms

Answer 1

String Operations

You're copying a string and then starting to make various transformations to it. As std::isspace and std::tolower end up being called and can have quite complex logic, avoiding them (and using plain for/if) can bring a significant improvement to this part, especially if you are only interested in ASCII.

Server-side VS client-side processing

Have you tried writing the code so that it runs inside the database server? Passing all this data around is costly.

Have you tried other database technologies that might offer good enough performance using simple queries?

Use case

As we're talking about milliseconds, isn't it enough to just run the query in the database? Will the statistics be computed often (e.g. every time a page is openned)? If so, computing them differently (say an update when a change occurs) might make more sense.

Answer 2

Another "comparative self answer" of sorts

---

EDIT: Against my better judgement I tried buffered=True on the python connection. And it's faster: 790ms => only 2x slower than php

---

Just for laughs I tried python3 (caveat I am not a python programmer, so this may not be the most efficient way). Code below. Result 1200ms! ~3x slower than php and ~9x slower than c++. So for python running 3 x group by queries on DB server makes more sense.

To be honest this is the sort of difference (ie 10x) I expected between php and c++. However i think php7 got a lot quicker on arrays, and php and mysql are a "highly optimized matched pair".

import mysql.connector

cnx = mysql.connector.connect(user='...', database='...', password='...')
cursor = cnx.cursor()

query = ("select email,firstname,lastname from member")

domains = dict()
firstnames = dict()
lastnames = dict()

cursor.execute(query)

for (email, firstname, lastname) in cursor:
    pieces = email.strip().split("@")
    if len(pieces) == 2:
        domain = pieces[1].lower()
        if domain not in domains:
            domains[domain] = 0
        domains[domain] = domains[domain] + 1

    fname = firstname.strip().lower()
    if fname not in firstnames:
        firstnames[fname] = 0
    firstnames[fname] = firstnames[fname] + 1

    lname = lastname.strip().lower()
    if lname not in lastnames:
        lastnames[lname] = 0
    lastnames[lname] = lastnames[lname] + 1

print("\domains")
# tried this as well, no time difference
# top_domains = heapq.nlargest(10, domains.items(), key=lambda kv: kv[1])
top_domains = sorted(domains.items(), key=lambda kv: kv[1], reverse=True)
for domain in top_domains[0:10]:
    print(domain[1], domain[0])

print("\nfirstnames")
top_firstnames = sorted(firstnames.items(), key=lambda kv: kv[1], reverse=True)
for firstname in top_firstnames[0:10]:
    print(firstname[1], firstname[0])

print("\nlastnames")
top_lastnames = sorted(lastnames.items(), key=lambda kv: kv[1], reverse=True)
for lastname in top_lastnames[0:10]:
    print(lastname[1], lastname[0])


cursor.close()
cnx.close()

But the python code is definitely the prettiest and was really quick to write, given I don't know python. ;-)

Before someone shoots me, note that I used the following options on the connection:

• buffered = false (same as php and c++, should be faster) this is wrong refer top
• use_pure = false (use the C-extension, the pure python one is MUCH slower)
• raw = false (ie DO convert to python types. I am not good enough at python to understand how you do anything with the raw strings .split() .lower() and friends certainly don't work). I could well imagine that this forced type conversion from the mysql C-connector, where everything will be a null terminated c-style string ( a const char* in c++ lingo) , and python String Objects could be a major bottle neck.

Answer 3

Providing a possible answer to my own question. Leaving original in tact for the record.

In order to address the performance issues with mariadb++ I wrote a very slimline, and zero cost, c++ abstraction around mysql-Connector-C. Uses c++17 features like if () initialiser and std::optional.

Please comment on that abstraction as well as the string munge and mapping stuff which is still here, basically unchanged. Performance is identical to using the plain C mysql.h functions: 128ms or ~3x speed of the php code with mysqld maxed out at 100% CPU, ie it's now the bottleneck.

What does the c++ wrapper add?

• simpler, cleaner signatures
• no pointers
• RAII to free resources
• std::optional for "now more rows" and "field value is NULL"

#include "mysql.h"
#include <string>
#include <unordered_map>
#include <vector>
#include <algorithm>
#include <iostream>

// Very slimline wrapper around mysql connector-C. Limited features. Zero cost abstraction
namespace mysqlpp {
class row {
public:
  explicit row(MYSQL_ROW row_) : _row{row_} {}

  // This helps with NULL columns, but does NOT make a copy. That's up to caller.
  std::optional<const char*> getField(size_t pos) {
    if (!_row[pos]) return std::nullopt;
    return std::optional<const char*>(_row[pos]);
  }

private:
  MYSQL_ROW _row;
};

class result {
public:
  result(MYSQL_RES* result_) : _result{result_} {}
  ~result() { mysql_free_result(_result); }  // RAII

  std::optional<row> next() {
    auto r = mysql_fetch_row(_result);
    if (!r) return std::nullopt;
    return std::optional<row>(row(r));
  }

private:
  MYSQL_RES* _result;
};

class conn {
public:
  conn(const char* host, const char* user, const char* passwd, const char* db, unsigned int port,
       const char* unix_socket, unsigned long clientflag) {

    mysql = mysql_init(NULL);
    if (!mysql_real_connect(mysql, host, user, passwd, db, port, unix_socket, clientflag))
      show_error();
  }

  ~conn() { mysql_close(mysql); }  // RAII

  result query(std::string sql) {
    if (mysql_real_query(mysql, sql.c_str(), sql.length())) show_error();
    MYSQL_RES* res = mysql_use_result(mysql);
    if (res == NULL) show_error();
    return result(res);
  }

private:
  void show_error() {
    std::cerr << "Error(" << mysql_errno(mysql) << ") "
              << "[" << mysql_sqlstate(mysql) << "] "
              << "\"" << mysql_error(mysql) << "\"";
    mysql_close(mysql);
    exit(EXIT_FAILURE);
  }

  MYSQL* mysql;
};

} // end namespace mysqlpp

typedef std::unordered_map<std::string, int> TopNMap;
typedef std::vector<std::pair<std::string, int>> TopNResult;

TopNResult top_n(const TopNMap& map, int n) {
  TopNResult top_n(n, {"", 0});
  std::partial_sort_copy(map.begin(), map.end(), top_n.begin(), top_n.end(),
                         [](auto& a, auto& b) { return a.second > b.second; });
  return top_n;
}

void report(const TopNMap& map, int n, std::string label) {
  std::cout << "\n" << std::to_string(map.size()) << " unique " << label << "\n";
  TopNResult result = top_n(map, n);
  std::for_each(result.begin(), result.end(),
                [](auto& e) { std::cout << e.first << ": " << e.second << "\n"; });
}

int main() {
  mysqlpp::conn mysql("localhost", "...", "...", "...", 0,
                      "/var/run/mysqld/mysqld.sock", 0);

  TopNMap domains;
  TopNMap firstnames;
  TopNMap lastnames;
  auto result = mysql.query("select email,firstname,lastname from member");
  while (auto row = result.next()) {
      if (auto maybe_domain = row->getField(0)) {
        std::string domain = maybe_domain.value(); // make that copy!
        trim(domain);
        if (size_t pos = domain.find('@'); pos != std::string::npos) {
            strtolower(domain);
            domains[domain.substr(pos + 1)]++;
        }
      }

      if (auto maybe_firstname = row->getField(1)) {
        std::string firstname = maybe_firstname.value();
        trim(firstname);
        strtolower(firstname);
        firstnames[std::move(firstname)]++;
      }

      if (auto maybe_lastname = row->getField(2)) {
        std::string lastname = maybe_lastname.value();
        trim(lastname);
        strtolower(lastname);
        lastnames[std::move(lastname)]++;
      }
  }

  const int n = 10;
  report(domains, n, "domains");
  report(firstnames, n, "firstnames");
  report(lastnames, n, "lastnames");
  return EXIT_SUCCESS;
}

```