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
select SQL_NO_CACHE lower(trim(firstname)) as fname, count(*) as cnt from member group by fname order by cnt desc limit 10
\$\endgroup\$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... \$\endgroup\$