Details
Minor
Description
Hi Guys,
im having issue with aria engine, ONLY when using TRANSACTIONAL=1, which is unfortunately the default setting it seems. Basically IO subsystem on the server gets exhausted completely and queries like
SELECT * FROM "raw_stats_other" LIMIT 1 take eg. 200-600 seconds to return a row that has 30 bytes of data. Traffic is almost write-only with 5-10 concurrent writes at a time, with occassional reads. Each single write consist of "packed" write to 100 rows with ON DUPLICATE KEY UPDATE.
The difference is so huge that when i switched the table to TRANSACTIONAL=0, the server behaved like there's no traffic at all.
SELECT * FROM "raw_stats_other" LIMIT 1 time dropped from 200-600 seconds to just <0.05s.
The problem isn't really the very bad performance, but lack of documentation. There should be some info about flushing strategy, and how TRANSACTIONAL=1 affects the IO (i don't know maybe that's how it's supposed to work as it's flushing every write to disk?). And most important, if there's a way of improving it or not. Is it not suitable for conventional HDD's and high write enviroments at all?
While i have not much experience with mariadb and considering lack of documentation i thought at first that... the engine is just broken which is a shame, because when properly configured it works very nicely... where there's not a single line of info of how badly transactional options i affecting write performance and how it really works.
I marked this issue as critical, because i think many people would drop the DB altogether thinking it's broken because lack of information.
CREATE TABLE `raw_stats_other` (
`raw_stats_lookup_id` int(11) NOT NULL,
`attrib_id` smallint(6) NOT NULL,
`date` date NOT NULL,
`raw_stats_value_id` int(255) NOT NULL,
`impressions` int(11) NOT NULL,
`_cf` int(11) NOT NULL,
UNIQUE KEY `tag_stats_lookup_id` (`date`,`raw_stats_lookup_id`,`attrib_id`,`raw_stats_value_id`) USING BTREE
) ENGINE=Aria DEFAULT CHARSET=utf8 TRANSACTIONAL=1
/*!50100 PARTITION BY HASH (TO_DAYS(date) DIV 10)
PARTITIONS 5 */;
Gliffy Diagrams
Attachments
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
First some background:
TRANSACTIONAL=1 is not on by default. This can be seen with:
MariaDB [test]> create table t1 (a int) engine=aria; Query OK, 0 rows affected (0.02 sec) MariaDB [test]> show create table t1; +-------+-----------------------------------------------------------------------------------------------------+ | Table | Create Table | +-------+-----------------------------------------------------------------------------------------------------+ | t1 | CREATE TABLE `t1` ( `a` int(11) DEFAULT NULL ) ENGINE=Aria DEFAULT CHARSET=latin1 PAGE_CHECKSUM=1 | +-------+-----------------------------------------------------------------------
TRANSACTIONAL=1 means that all changes are logged to a transaction log
which is synced at end of statement. The sync will of course slow down
writes and updates.
TRANSACTIONAL=1 makes the Aria table crash-proof. This means that if the
server dies before the statement ends all changes will roll back to
the state at the beginning of the statement.
The other overhead with TRANSACTIONAL=1 is that we need up to 6 byte more
for each row and key to store the transaction id (to allow concurrent
insert's and selects).
One can combine many writes/updates to one entity (ie sync) by using
LOCK TABLES. In this case the write and sync will happen at UNLOCK
TABLES.
In effect, this is the same thing as using BEGIN ... COMMIT with
InnoDB.
With the current version if Aria INSERT and SELECT works concurrently,
but UPDATE and DELETE needs a table lock.
We never had the time to remove the table lock for Aria with
UPDATE/DELETE as we have focused all efforts on developing MariaDB.
SELECT speed should be about equal if you are using TRANSACTIONAL=1 or
not. INSERT into non empty tables would block more easily for tables
with TRANSACTIONAL=0 as these would normally require table locks.
Back to the JIRA entry:
I don't have any straight explanation why a SELECT should take a long
time. The only reason I can think of is that there is an update or
delete that is waiting for a table lock and the select is waiting for
this to end so it can get it's concurrent read lock.
One should be able to verify this with a 'show processlist'
One problem shown in your example is that your insert queries are in
state 'waiting for table'. This means that the statements are
requiring a table lock and not running concurrently, which is wrong.
I tried the following with the mysql client:
In one connection I did:
insert into t1 select *,1 from seq_1_to_100_step_1; insert into t1 select *,1 from seq_101_to_1000000_step_1;
(Inserting a lot of rows into t1)
In another connection I did while the second insert was running:
select count(*) from t1; select count(*) from t1 where a < 10000;
Both queries was instant.
I also checked with having two insert queries run at the same time:
MariaDB [test]> show processlist; +----+-------+-----------+------+---------+------+--------------+--------------------------------------------------------------+----------+ | Id | User | Host | db | Command | Time | State | Info | Progress | +----+-------+-----------+------+---------+------+--------------+--------------------------------------------------------------+----------+ | 3 | monty | localhost | test | Query | 40 | Sending data | insert into t1 select *,1 from seq_1000001_to_2000001_step_1 | 0.000 | | 4 | monty | localhost | test | Query | 0 | init | show processlist | 0.000 | | 5 | monty | localhost | test | Query | 9 | Sending data | insert into t1 select *,1 from seq_3000001_to_4000001_step_1 | 0.000 | +----+-------+-----------+------+---------+------+--------------+--------------------------------------------------------------+----------+ 3 rows in set (0.00 sec)
As you can see, both are in sending data, which means they are
executing.
To be able to help you, I would need a way to repeat this.
Could you by any chance create a short program that I could run to see
this myself?
I would also need the result of 'show create table' for your test
table and a copy of the insert statements you where running.
Regards,
Monty
Hello Monty, first thank you for taking time to explain... I have written code to replicate the issue. It's working 100%...
First as you say, when i switch the engine to innodb - no issues here, multiple queries are updating rows, as you said, some are waiting, etc. Most important... i can still do select.
So i'll quickly explain why i reported this as critical issue...
You're issuing 10 queries A, B, C ... at some point in time, and in between you're making a SELECT. Everything on the same table. I was accustomed that if server is having hard time processing the queries, eg. because of load spike then the queries will get processed in serial or semi-serial for (table / row level) locking. It always works like that
INSERT INTO X INSERT INTO X INSERT INTO X INSERT INTO X SELECT COUNT(*) FROM X INSERT INTO X INSERT INTO X INSERT INTO X
Now this is 100% ok...
Now look at this... im issuing INSERTS over and over... the server can't process them fast... and in the middle of these INSERT's im doing SELECT. If i had 10 unfinished INSERTS when i issued SELECT... the server should complete these 10 inserts, because these were issued BEFORE SELECT. Then do SELECT, then if inserts will continue to come - confinue with inserts.
The point is that this SELECT should be finished before 11'th INSERT could be made. Or at least they should be made together.
Now the behaviour im experiencing with ariadb with TRANSACTIONAL = 1...
Im keep throwing eg. 10 inserts from 10 threads... then one thread needs to SELECT...
INSERT
INSERT
INSERT
SELECT -> DEADLOCK
INSERT -> FINISHED
INSERT -> FINISHED
And new inserts keep finishing while SELECT is locked.
Now that SELECT will never happen, We'll have a deadlock. This select from the middle will get postponed indefinitely, untill INSERTS will stop to come, doesn't matter that the select was issued 10 minutes ago and new INSERT just NOW. The insert will execute first. Like said, this isn't happening when im using innodb nor myisam. The select will just wait for its "turn", for me it seems that aria has some priority queue where inserts are pushed to top and selects to bottom, so when the engine has some "free time" it'll just process select like something that can be delayed untill load is low.
As you can see on the screen below, in TIME column... new inserts are executed BUT select, that was issued before is deadlocked. It'll never finish untill i stop inserting data. Now that's extreme case, but when i had less load in real-life scenario, this SELECT COUNT(*) query would just wait for 10 seconds, sometimes 40 seconds... sometimes it'd execute instantly... WHILE inserts were always instantly executed. So insert performance was predictable, while select times was ... "unstable"...
http://screencast.com/t/7wzSTWZ4
Im not sure that's a bug or something that's made by-design and it should work like that.
Im including PHP code that'll spawn 20 worker processes using CURL and old mysql functions, so pls disregard the warnings. The queries issued are randomly generated, like the one below...
FILE FOR DOWNLOAD:
https://drive.google.com/file/d/0Bzxwnf_vTMd8bmdiYTZCVS1Oems/edit?usp=sharing
Thanks,
Slawomir.
INSERT INTO `raw_stats_other_TR`
(`raw_stats_lookup_id`, `attrib_id`, `date`, `raw_stats_value_id`, `impressions`, `_cf`)
VALUES ('50','74','2014-03-08','0','10','10'),('11','13','2014-03-08','1','10','10'),('38','89','2014-03-08','2','10','10'),('87','91','2014-03-08','3','10','10'),('24','28','2014-03-08','4','10','10'),('97','8','2014-03-08','5','10','10'),('16','11','2014-03-08','6','10','10'),('24','63','2014-03-08','7','10','10'),('74','12','2014-03-08','8','10','10'),('3','3','2014-03-08','9','10','10'),('8','52','2014-03-08','10','10','10'),('19','23','2014-03-08','11','10','10'),('77','2','2014-03-08','12','10','10'),('6','71','2014-03-08','13','10','10'),('91','85','2014-03-08','14','10','10'),('48','44','2014-03-08','15','10','10'),('8','72','2014-03-08','16','10','10'),('80','89','2014-03-08','17','10','10'),('38','86','2014-03-08','18','10','10'),('86','2','2014-03-08','19','10','10'),('40','98','2014-03-08','20','10','10'),('100','31','2014-03-08','21','10','10'),('25','30','2014-03-08','22','10','10'),('7','72','2014-03-08','23','10','10'),('50','54','2014-03-08','24','10','10'),('41','72','2014-03-08','25','10','10'),('26','92','2014-03-08','26','10','10'),('87','28','2014-03-08','27','10','10'),('13','16','2014-03-08','28','10','10'),('78','87','2014-03-08','29','10','10'),('18','47','2014-03-08','30','10','10'),('100','46','2014-03-08','31','10','10'),('2','58','2014-03-08','32','10','10'),('87','52','2014-03-08','33','10','10'),('75','70','2014-03-08','34','10','10'),('22','1','2014-03-08','35','10','10'),('94','56','2014-03-08','36','10','10'),('41','41','2014-03-08','37','10','10'),('71','36','2014-03-08','38','10','10'),('28','68','2014-03-08','39','10','10'),('64','84','2014-03-08','40','10','10'),('17','29','2014-03-08','41','10','10'),('82','20','2014-03-08','42','10','10'),('92','21','2014-03-08','43','10','10'),('86','17','2014-03-08','44','10','10'),('88','91','2014-03-08','45','10','10'),('83','97','2014-03-08','46','10','10'),('90','36','2014-03-08','47','10','10'),('35','31','2014-03-08','48','10','10'),('31','3','2014-03-08','49','10','10'),('49','42','2014-03-08','50','10','10'),('96','88','2014-03-08','51','10','10'),('86','1','2014-03-08','52','10','10'),('19','38','2014-03-08','53','10','10'),('55','30','2014-03-08','54','10','10'),('86','51','2014-03-08','55','10','10'),('15','1','2014-03-08','56','10','10'),('29','73','2014-03-08','57','10','10'),('76','36','2014-03-08','58','10','10'),('34','2','2014-03-08','59','10','10'),('96','6','2014-03-08','60','10','10'),('35','83','2014-03-08','61','10','10'),('86','35','2014-03-08','62','10','10'),('16','13','2014-03-08','63','10','10'),('5','92','2014-03-08','64','10','10'),('12','41','2014-03-08','65','10','10'),('61','1','2014-03-08','66','10','10'),('7','62','2014-03-08','67','10','10'),('15','34','2014-03-08','68','10','10'),('35','24','2014-03-08','69','10','10'),('76','11','2014-03-08','70','10','10'),('81','22','2014-03-08','71','10','10'),('3','5','2014-03-08','72','10','10'),('80','5','2014-03-08','73','10','10'),('6','39','2014-03-08','74','10','10'),('14','69','2014-03-08','75','10','10'),('44','83','2014-03-08','76','10','10'),('20','61','2014-03-08','77','10','10'),('26','59','2014-03-08','78','10','10'),('81','78','2014-03-08','79','10','10'),('12','41','2014-03-08','80','10','10'),('31','67','2014-03-08','81','10','10'),('11','48','2014-03-08','82','10','10'),('56','24','2014-03-08','83','10','10'),('82','54','2014-03-08','84','10','10'),('89','96','2014-03-08','85','10','10'),('35','80','2014-03-08','86','10','10'),('15','81','2014-03-08','87','10','10'),('30','98','2014-03-08','88','10','10'),('67','25','2014-03-08','89','10','10'),('75','29','2014-03-08','90','10','10'),('32','25','2014-03-08','91','10','10'),('29','47','2014-03-08','92','10','10'),('42','28','2014-03-08','93','10','10'),('4','72','2014-03-08','94','10','10'),('83','30','2014-03-08','95','10','10'),('57','76','2014-03-08','96','10','10'),('88','80','2014-03-08','97','10','10'),('98','82','2014-03-08','98','10','10'),('42','73','2014-03-08','99','10','10')
ON DUPLICATE KEY UPDATE _cf = _cf + VALUES(_cf), impressions = impressions + VALUES(impressions)
Slawomir, thank you for the test.
Monty,
I converted it to an MTR test, with some modifications. The test case is at the end of the comment. It does the following:
- creates a table with ENGINE=$engine and TRANSACTIONAL=$transactional (I removed partitioning, it's irrelevant);
- creates a procedure which performs $inserts number of atomic inserts (I removed the unique key and ON DUPLICATE KEY UPDATE to make the scenario more obvious);
- starts $threads number of connections, all but one calling the procedure, thus executing 2000 inserts each, and one executing a single SELECT COUNT;
- waits for the selecting thread to finish the job, meanwhile running show processlist every 0.5 seconds;
- runs the final show processlist after the selecting thread finishes the job;
- performs the cleanup.
Autocommit is always on.
Variables can be set at the beginning of the test, they might require some tweaking depending on how fast your machine is (for example, on mine the test with transactional aria, 20 threads, 2000 inserts, takes ~30 seconds, and the results described below are quite obvious there).
Observations:
- For Aria, both transactional and non-transactional, and for MyISAM, it works pretty much the same way, and it is as Slawomir described.
Inserting threads keep inserting even though the SELECT is already waiting for a table-level lock. Not only do they do the previously started INSERT, but they continue running new INSERTs, while SELECT is waiting. I don't really find this surprising since things like that have been documented in MySQL manual for long time; but since in your previous comment you said that it should work for transactional Aria, you might want to take a look at it.
What I do find strange though is that none of the recommended workarounds seem to have any effect. I tried --low-priority-updates, SELECT HIGH_PRIORITY, concurrent-insert=2, it's all the same, and --max-write-lock-count makes things even worse. Maybe you know why.
- For transactional Aria, the scenario takes almost twice as much time as for non-transactional, which is probably why the problem affects Slawomir on transactional tables only (on non-transactional ones, INSERTs get executed faster, and SELECT comes through). Again, I don't find it surprising since writing to the transactional log should have a cost, but I'll leave it to you to decide if it's expected or not.
Here is the test case:
let $threads = 20;
let $inserts = 2000;
let $transactional = 1;
let $engine = Aria;
DROP TABLE IF EXISTS t1;
eval
CREATE TABLE IF NOT EXISTS `t1` (
`raw_stats_lookup_id` int(11) NOT NULL,
`attrib_id` smallint(6) NOT NULL,
`date` date NOT NULL,
`raw_stats_value_id` int(11) NOT NULL,
`impressions` int(11) NOT NULL,
`_cf` int(11) NOT NULL,
KEY `raw_stats_lookup_id_2` (`raw_stats_lookup_id`,`attrib_id`,`date`)
) ENGINE=$engine
;
--delimiter ||
eval
create procedure ins ()
begin
declare i int default 0;
wl_loop: WHILE i<=$inserts DO
SET i=i+1;
INSERT INTO `t1`
(`raw_stats_lookup_id`, `attrib_id`, `date`, `raw_stats_value_id`, `impressions`, `_cf`)
VALUES (i,i,'2014-03-08', i, 10, 10);
END WHILE wl_loop;
END||
--delimiter ;
let $worker=$threads;
let $select_thread = `SELECT CAST($threads/4 AS INT)`;
while($worker)
{
--connect (con$worker,localhost,root,,)
if ($worker==$select_thread)
{
let $con_select_id = `SELECT CONNECTION_ID()`;
send SELECT COUNT(*) FROM t1;
}
if ($worker!=$select_thread)
{
--send CALL ins()
}
--dec $worker
--sleep 0.3
}
--connection default
while(`SELECT state FROM INFORMATION_SCHEMA.PROCESSLIST WHERE id = $con_select_id`)
{
show processlist;
--sleep 0.5
}
--connection default
show full processlist;
let $worker=$threads;
while($worker)
{
--connection con$worker
--reap
--disconnect con$worker
--dec $worker
}
--connection default
DROP TABLE t1;
DROP PROCEDURE ins;
Typical output looks like this:
Soon after the beginning:
show processlist;
Id User Host db Command Time State Info Progress
2 root localhost test Query 0 NULL show processlist 0.000
3 root localhost test Query 0 Waiting for table level lock INSERT INTO `t1`
(`raw_stats_lookup_id`, `attrib_id`, `date`, `raw_stats_value_id`, `impressio 0.000
4 root localhost test Query 0 Waiting for table level lock INSERT INTO `t1`
(`raw_stats_lookup_id`, `attrib_id`, `date`, `raw_stats_value_id`, `impressio 0.000
5 root localhost test Query 0 Waiting for table level lock INSERT INTO `t1`
(`raw_stats_lookup_id`, `attrib_id`, `date`, `raw_stats_value_id`, `impressio 0.000
6 root localhost test Query 0 Waiting for table level lock INSERT INTO `t1`
(`raw_stats_lookup_id`, `attrib_id`, `date`, `raw_stats_value_id`, `impressio 0.000
7 root localhost test Query 0 Waiting for table level lock INSERT INTO `t1`
(`raw_stats_lookup_id`, `attrib_id`, `date`, `raw_stats_value_id`, `impressio 0.000
8 root localhost test Query 0 Waiting for table level lock INSERT INTO `t1`
(`raw_stats_lookup_id`, `attrib_id`, `date`, `raw_stats_value_id`, `impressio 0.000
9 root localhost test Query 0 Waiting for table level lock INSERT INTO `t1`
(`raw_stats_lookup_id`, `attrib_id`, `date`, `raw_stats_value_id`, `impressio 0.000
10 root localhost test Query 0 Waiting for table level lock INSERT INTO `t1`
(`raw_stats_lookup_id`, `attrib_id`, `date`, `raw_stats_value_id`, `impressio 0.000
11 root localhost test Query 0 Waiting for table level lock INSERT INTO `t1`
(`raw_stats_lookup_id`, `attrib_id`, `date`, `raw_stats_value_id`, `impressio 0.000
12 root localhost test Query 0 Waiting for table level lock INSERT INTO `t1`
(`raw_stats_lookup_id`, `attrib_id`, `date`, `raw_stats_value_id`, `impressio 0.000
13 root localhost test Query 0 Waiting for table level lock INSERT INTO `t1`
(`raw_stats_lookup_id`, `attrib_id`, `date`, `raw_stats_value_id`, `impressio 0.000
14 root localhost test Query 0 Waiting for table level lock INSERT INTO `t1`
(`raw_stats_lookup_id`, `attrib_id`, `date`, `raw_stats_value_id`, `impressio 0.000
15 root localhost test Query 0 Waiting for table level lock INSERT INTO `t1`
(`raw_stats_lookup_id`, `attrib_id`, `date`, `raw_stats_value_id`, `impressio 0.000
16 root localhost test Query 0 Waiting for table level lock INSERT INTO `t1`
(`raw_stats_lookup_id`, `attrib_id`, `date`, `raw_stats_value_id`, `impressio 0.000
17 root localhost test Query 0 Waiting for table level lock INSERT INTO `t1`
(`raw_stats_lookup_id`, `attrib_id`, `date`, `raw_stats_value_id`, `impressio 0.000
18 root localhost test Query 3 Waiting for table level lock SELECT COUNT(*) FROM t1 0.000
19 root localhost test Query 0 Waiting for table level lock INSERT INTO `t1`
(`raw_stats_lookup_id`, `attrib_id`, `date`, `raw_stats_value_id`, `impressio 0.000
20 root localhost test Query 0 Waiting for table level lock INSERT INTO `t1`
(`raw_stats_lookup_id`, `attrib_id`, `date`, `raw_stats_value_id`, `impressio 0.000
21 root localhost test Query 0 query end INSERT INTO `t1`
(`raw_stats_lookup_id`, `attrib_id`, `date`, `raw_stats_value_id`, `impressio 0.000
22 root localhost test Query 0 Waiting for table level lock INSERT INTO `t1`
(`raw_stats_lookup_id`, `attrib_id`, `date`, `raw_stats_value_id`, `impressio 0.000
(Note thread 18 which performs SELECT, it has already been waiting for 3 seconds for the lock, while INSERT threads keep inserting )
Closer to the end:
2 root localhost test Query 0 NULL show processlist 0.000
3 root localhost test Sleep 15 NULL 0.000
4 root localhost test Sleep 9 NULL 0.000
5 root localhost test Sleep 7 NULL 0.000
6 root localhost test Sleep 5 NULL 0.000
7 root localhost test Sleep 4 NULL 0.000
8 root localhost test Sleep 3 NULL 0.000
9 root localhost test Sleep 2 NULL 0.000
10 root localhost test Sleep 2 NULL 0.000
11 root localhost test Sleep 1 NULL 0.000
12 root localhost test Sleep 1 NULL 0.000
13 root localhost test Sleep 1 NULL 0.000
14 root localhost test Sleep 1 NULL 0.000
15 root localhost test Sleep 0 NULL 0.000
16 root localhost test Sleep 0 NULL 0.000
17 root localhost test Sleep 0 NULL 0.000
18 root localhost test Query 28 Waiting for table level lock SELECT COUNT(*) FROM t1 0.000
19 root localhost test Sleep 0 NULL 0.000
20 root localhost test Query 0 Waiting for table level lock INSERT INTO `t1`
(`raw_stats_lookup_id`, `attrib_id`, `date`, `raw_stats_value_id`, `impressio 0.000
21 root localhost test Query 0 Waiting for table level lock INSERT INTO `t1`
(`raw_stats_lookup_id`, `attrib_id`, `date`, `raw_stats_value_id`, `impressio 0.000
22 root localhost test Query 0 Waiting for table level lock INSERT INTO `t1`
(`raw_stats_lookup_id`, `attrib_id`, `date`, `raw_stats_value_id`, `impressio 0.000
(Many threads have already finished inserting, which means they have run 2000 inserts each, remaining threads are finishing the job, and SELECT is still waiting).
It seems that even when running only SELECTs on an Aria table, enabling TRANSACTIONAL=1 has a significant impact on performances.
Testing with sysbench from trunk (05 dec 14) using the oltp.lua script, using TRANSACTIONAL=1 makes the runs between 16% and 18% slower (from ~315tps to ~260tps).
Here are the parameters i used (on MariaDB 10.0.15 using debian wheezy packages) :
sysbench --test=xxx/sysbench/tests/db/oltp.lua \ --mysql-socket=xxx \ --mysql-db=xxx \ --mysql-user=xxx \ --mysql-password=xxx \ --oltp-test-mode=complex \ --oltp-table-size=100000 \ --mysql-ignore-duplicates=on \ --max-requests=10000 \ --num-threads=4 \ --report-interval=5 \ --report-checkpoints=60 \ --oltp-reconnect=on \ --oltp-read-only=on \ --mysql-table-engine=aria \ --mysql-table-options='ROW_FORMAT=PAGE TRANSACTIONAL=x PAGE_CHECKSUM=1'
(the --oltp-read-only=on option makes the test to only run SELECTs)
Here is the oltp.lua script i used (the only modifications i did from the one shipped with sysbench are that i added the test for the aria engine and that i specified the right table name (sbtest1) for the LOCK TABLES) :
pathtest = string.match(test, "(.*/)") or ""
dofile(pathtest .. "common.lua")
function thread_init(thread_id)
set_vars()
if (((db_driver == "mysql") or (db_driver == "attachsql")) and (mysql_table_engine == "myisam" or mysql_table_engine == "aria")) then
begin_query = "LOCK TABLES sbtest1 WRITE"
commit_query = "UNLOCK TABLES"
else
begin_query = "BEGIN"
commit_query = "COMMIT"
end
end
function event(thread_id)
local rs
local i
local table_name
local range_start
local c_val
local pad_val
local query
table_name = "sbtest".. sb_rand_uniform(1, oltp_tables_count)
if not oltp_skip_trx then
db_query(begin_query)
end
for i=1, oltp_point_selects do
rs = db_query("SELECT c FROM ".. table_name .." WHERE id=" .. sb_rand(1, oltp_table_size))
end
for i=1, oltp_simple_ranges do
range_start = sb_rand(1, oltp_table_size)
rs = db_query("SELECT c FROM ".. table_name .." WHERE id BETWEEN " .. range_start .. " AND " .. range_start .. "+" .. oltp_range_size - 1)
end
for i=1, oltp_sum_ranges do
range_start = sb_rand(1, oltp_table_size)
rs = db_query("SELECT SUM(K) FROM ".. table_name .." WHERE id BETWEEN " .. range_start .. " AND " .. range_start .. "+" .. oltp_range_size - 1)
end
for i=1, oltp_order_ranges do
range_start = sb_rand(1, oltp_table_size)
rs = db_query("SELECT c FROM ".. table_name .." WHERE id BETWEEN " .. range_start .. " AND " .. range_start .. "+" .. oltp_range_size - 1 .. " ORDER BY c")
end
for i=1, oltp_distinct_ranges do
range_start = sb_rand(1, oltp_table_size)
rs = db_query("SELECT DISTINCT c FROM ".. table_name .." WHERE id BETWEEN " .. range_start .. " AND " .. range_start .. "+" .. oltp_range_size - 1 .. " ORDER BY c")
end
if not oltp_read_only then
for i=1, oltp_index_updates do
rs = db_query("UPDATE " .. table_name .. " SET k=k+1 WHERE id=" .. sb_rand(1, oltp_table_size))
end
for i=1, oltp_non_index_updates do
c_val = sb_rand_str("###########-###########-###########-###########-###########-###########-###########-###########-###########-###########")
query = "UPDATE " .. table_name .. " SET c='" .. c_val .. "' WHERE id=" .. sb_rand(1, oltp_table_size)
rs = db_query(query)
if rs then
print(query)
end
end
i = sb_rand(1, oltp_table_size)
rs = db_query("DELETE FROM " .. table_name .. " WHERE id=" .. i)
c_val = sb_rand_str([[
###########-###########-###########-###########-###########-###########-###########-###########-###########-###########]])
pad_val = sb_rand_str([[
###########-###########-###########-###########-###########]])
rs = db_query("INSERT INTO " .. table_name .. " (id, k, c, pad) VALUES " .. string.format("(%d, %d, '%s', '%s')",i, sb_rand(1, oltp_table_size) , c_val, pad_val))
end -- oltp_read_only
if not oltp_skip_trx then
db_query(commit_query)
end
end
The tables are dropped then re-created at each runs ; aria pagecache size on the server is of 128M which is way more than the test DB size and nothing else is running on the server and on MariaDB and there is still plenty of spare memory and no swapping occuring nor a CPU bottleneck (none of the two cores gets less than 25% idle and there is no huge iowait bursts.
ps: as a side note, running the same test using ROW_FORMAT=DYNAMIC (and thus TRANSACTIONAL=0) in both Aria (while still having PAGE_CHECKSUM=1) and MyISAM performs a little bit faster than using ROW_FORMAT=PAGE TRANSACTIONAL=1 (~270tps instead of ~260tps).
Setting PAGE_CHECKSUM=0 doesnt seems to have a significant impact on SELECT queries.
Ian Gilfillan, could you please take that? Ask Monty for any information you need about Aria.