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

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 <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; });
}

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() {
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");
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


• Did you try using any stored procedures? It might improve the performance for both the c++ and php. Sorting in the database should be faster if you have indexes set up. – pacmaninbw Dec 7 '19 at 23:32
• I didn't. Because a) This is a sort of proof of concept of how easy and performant it is do this sort of thing in c++ vs php. and b) you can write the SQL easily for one field, but to do 3 fields you have to run 3 group by queries. One query in the DB is already slower than 3 field in php or c++ (see above). A stored procedure (in Mariadb or Mysql at least) won;t change that. stored procedures only help when you are running many queries, saves the round trip formatting of data... this is not the case here. Happy to be corrected, but in MySQL i think that's right. – Oliver Schönrock Dec 7 '19 at 23:36
• I, just now, tried adding an index to the firstname field and reran the query. It's 250ms now vs 300ms before. It's a complete table scan that's why, the index only marginally helps with the group by. new SQL: select SQL_NO_CACHE lower(trim(firstname)) as fname, count(*) as cnt from member group by fname order by cnt desc limit 10 – Oliver Schönrock Dec 7 '19 at 23:42
• here is mysql's "explain": SIMPLE member index NULL firstname 98 NULL 228383 Using index; Using temporary; Using filesort After 20yrs with MySQL, i have always found that for some jobs, it's best to pull the data back and do the "processing and summarising" in the application. This is one of those cases. That's why we are considering doing it in c++ hoping that's quicker. Not much it seems... – Oliver Schönrock Dec 7 '19 at 23:43

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.

• The string ops point, is a good one, I will experiment. Yes I have spent much time trying to do it server side (as mentioned above), but a DB server is a "general query machine", which is not optimal for all operations. Sometimes, not often but regularly, it is faster so share the load server/client side. On use case: yes, the real life case is bigger and slower and it is running interactive reports of such variety that caching or pre-computing doesn't necessarily make sense (it does in some cases though, so your point is well made). – Oliver Schönrock Dec 8 '19 at 13:33
• On the whole I find people treat DB servers like a holy grail, They are not that, but are just convenient for common operations. As soon as it gets more specialised, a combination with client side code can easily be faster... even in relatively slow client side technologies like php as above example shows. – Oliver Schönrock Dec 8 '19 at 13:35
• Some databases allow you to write code in C/C++ and execute it inside the server process, e.g. postgresql.org/docs/current/xfunc-c.html MySQL/MariaDB might offer something similar. – D. Jurcau Dec 8 '19 at 13:42
• Nice! I hadn't seen that. That looks more flexible than what Mysql/mariadb offers for UDFs written in C. I am sure that would be fast as it avoids the "data pumping" like you say. You would have to be very very sure that you are not going to take the whole server down, or lock it up on some way, etc.... ;-) – Oliver Schönrock Dec 8 '19 at 13:51

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

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.

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; });
}

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;
}

$$$$
`