One thing I have noticed with MySQL alternatives is that they are stated to be faster than MySQL for large database / high volume usage.
That’s great and certainly useful for a lot of sites. My site, however, I am actually seeing worse performance with Percona and MariaDB.
The site has low traffic, includes a forum but the main use is for displaying stats from the database. In one particular report the time taken from generating to completing in the browser window is as follows:
MySQL 5.8sec
Percona 6.2sec
MariaDB 6.5sec
All are using the same my.cnf file, so I wonder if I need to do any further tweaking to see better performance with Percona or MariaDB?
Here’s some info on the stats table:
Consists of 200,000 rows and 220 fields. Total size around 300Mb.
This table is readonly and for optimal performance is stored in a Memory Table.
drop table if exists MEM_aw_races;
create table MEM_aw_races engine=memory select * from aw_races;
drop aw_races;
PHP is used to create a user form for selecting a report and for processing the data.
As the report contains dozens and dozens of sub reports I select data from the MEM_aw_races table and store into a smaller temporary memory table:
$temp_table = ‘temp_’ . substr(rand().rand().rand(), 0, 6);
create temporary table $temp_table ENGINE = MEMORY SELECT // around 60 columns here // from MEM_aw_races where // users query here //
//Sub reports here
function sub_reports($group, $heading) {
$result = @mysql_query("select $group,sum(if(position=1,1,0)),count(*),sum(if(position=1,sp ,-1)),sum(1/(sp+1)),sum(placed) from $temp_table group by $group");
while($row = @mysql_fetch_row($result)) {
//display table data
}
drop table $temp_table;
Is this method not suited to Percona or MariaDB?
Just ran a few more tests.
At the console:
mysql> create temporary table $temp_table ENGINE = MEMORY SELECT // around 60 columns here // from MEM_aw_races where // users query here //
MySQL 0.68sec
Percona 0.71sec
MariaDB 0.72sec