GoAdmio

speedtest

Time 1,000 single-row inserts into a temporary MariaDB database to gauge how fast the server can write, then delete the test database

On this page: Overview · Quick start · Syntax · Output · Examples · Troubleshooting · Details · See also


Overview

/go/speedtest measures how long this server takes to insert 1,000 rows into MariaDB, one row at a time. Use it to compare servers, or to check the effect of a change to the disk or to the MariaDB settings (my).

It creates a database named SpeedTest with one table, runs the 1,000 inserts, prints how many seconds that took, and drops the database.

[!WARNING] The command drops the database SpeedTest when it finishes. If you already have a database with that name, its table T is emptied at the start and the whole database is deleted at the end. Make sure no database is named SpeedTest before you run it.


Quick start

/go/speedtest | tail -n 1

Syntax

/go/speedtest

The command takes no arguments, and the number of rows can't be changed. Anything you type after it is ignored, and is only written to /var/log/go/speedtest.log with a timestamp. Run it as root.


Output

The command prints each number from 1 to 1000 as it goes, then the result:

1
2
3
...
1000
Time taken: 12 seconds

The time above is only an illustration.

The time is in whole seconds. Lower is faster. To turn it into a rate, divide 1,000 by the number of seconds, which gives the inserts per second.

Each insert is sent as a separate command, so the time also includes starting sudo and the mariadb client for every row. It shows the speed of the whole path, not only the database. Use it to compare runs on the same server, or between servers, and not as a pure database benchmark.


Examples

# Show only the result
/go/speedtest | tail -n 1
# Check that no database is already called SpeedTest
sudo mariadb -e "SHOW DATABASES LIKE 'SpeedTest';"
# Remove the temporary file the test leaves behind
rm -f /tmp/SpeedTest.test
# Review recent calls
tail /var/log/go/speedtest.log

Troubleshooting

Symptom Likely cause Fix
Can't create database 'SpeedTest'; database exists A SpeedTest database already exists. Either an earlier run was stopped, or you have a database with this name. If it is a leftover, ignore it. The next run reuses it and deletes it at the end. If it is a real database, stop and rename it first.
Table 'T' already exists Same as above. Same as above.
Access denied or Can't connect to local server MariaDB is not running, or the command was not run as root. Start MariaDB. Run the command as root.
mariadb: command not found The MariaDB client is not installed. Install MariaDB (see installmariadb).
The test takes a very long time A slow or busy disk. Each insert is saved to disk before the next one starts. Check the disk with speed, and close other heavy jobs.
The time changes a lot between runs Other load on the server, or a shared disk on a virtual server. Run it a few times when the server is quiet.
A long list of numbers is printed Each row's number is shown. Use | tail -n 1.
/tmp/SpeedTest.test is left behind The file is not deleted at the end. Delete it by hand.

Details

What it does

  1. Records the call in /var/log/go/speedtest.log.
  2. Notes the start time.
  3. Creates the database SpeedTest and the InnoDB table SpeedTest.T, and empties the table.
  4. Writes 0 to /tmp/SpeedTest.test.
  5. For each number from 1 to 1000:
    1. Prints the number.
    2. Appends it to /tmp/SpeedTest.test.
    3. Runs sudo mariadb -e "INSERT INTO SpeedTest.T (N) VALUES ('<n>');".
  6. Drops the database SpeedTest.
  7. Prints the elapsed time in seconds.
flowchart TD
    A["speedtest"] --> B["Create SpeedTest database and table"]
    B --> C["Loop 1 to 1000: print, append to file, INSERT one row"]
    C --> D["Drop SpeedTest database"]
    D --> E["Print Time taken"]

What it creates

Item Lifetime
Database SpeedTest, with table T (ID, N) Dropped at the end. Left behind if the run is stopped.
/tmp/SpeedTest.test Never deleted. About 4 KB. It is overwritten on the next run.

What affects the result


See also

Command Description
speed Check hard disk type, speed, and health
my Regenerate the MariaDB configuration file with settings tuned to the server
mysqltop Report current MariaDB activity and today's heaviest queries
querytop Show the ten most frequently executed SELECT queries
installmariadb Install or upgrade MariaDB
myisam2innodb Convert MariaDB/MySQL tables from MyISAM to InnoDB
cpu Show the server's CPU load and its top CPU-consuming processes
loadavg Check the weekly server load average