[#MDEV-5649] Aria engine horrible performance TRANSACTIONAL=1

[MDEV-5649] Aria engine horrible performance TRANSACTIONAL=1 Created: 2014-02-11 Updated: 2022-09-08
Status:	Open
Project:	MariaDB Server
Component/s:	None
Affects Version/s:	5.5.36, 10.0.9
Fix Version/s:	5.5

Type:

Bug

Priority:

Minor

Reporter:

Slawomir Pryczek

Assignee:

Michael Widenius

Resolution:

Unresolved

Votes:

Labels:

None

Environment:

Debian-70-wheezy, 2xHDD in RAID1, 16GB Memory

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 */;

Comments

Comment by Sergei Golubchik [ 2014-02-11 ]

greenman, could you please take that? Ask Monty for any information you need about Aria.

Comment by Michael Widenius [ 2014-03-05 ]

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

Comment by Slawomir Pryczek [ 2014-03-13 ]

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)

Comment by Elena Stepanova [ 2014-03-13 ]

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).

Comment by Jean Weisbuch [ 2014-12-08 ]

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.

Comment by Jean Weisbuch [ 2014-12-08 ]

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.

Comment by Ruslan Altynbaev [ 2019-03-09 ]

Possibly related: https://bugs.mysql.com/bug.php?id=39437

Generated at Thu Feb 08 07:05:59 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.

[MDEV-5649] Aria engine horrible performance TRANSACTIONAL=1 Created: 2014-02-11 Updated: 2022-09-08