[Maria-discuss] Performance lags with MariaDB built from source
System config: OS: Ubuntu 18.04 CPU: 2x Intel Xeon E5-2660 (40 logical cores) RAM: 160GB I built a debug build of MariaDB server 10.5 from the Github source and tried to load and execute the TPC-H benchmarks on it with a database size of 1GB. While loading the data and executing queries, I observed that MariaDB takes an unusually large amount of time (more than 10x compared to other databases like QuickStep or MonetDB) to load the data and execute queries (more than 10 minutes per query, compared to less than a minute for other databases). I'm running the server with following options - ./bin/mysqld --user=nisargs --datadir=/fastdisk/mariadb-server-install-2/data/ --innodb-read-io-threads=40 --innodb-write-io-threads=40 --innodb-buffer-pool-size=16G --innodb-buffer-pool-instances=40 I just wanted to get some eyes on this and check if there is something wrong with the command line options (or whether I can tweak some options to improve performance). I'm just trying to conduct some benchmarking experiments and not very familiar with databases. Thanks Nisarg
It is very unusual to benchmark a debug build. No wonder it lags So this is the first, and most important performance “tweak” – benchmark an optimized build. Once you get there, rerun your benchmarks, and then more advanced things can be discussed. From: Nisarg Shah Sent: Wednesday, 4 December 2019 10:21 To: maria-discuss@lists.launchpad.net Subject: [Maria-discuss] Performance lags with MariaDB built from source System config: OS: Ubuntu 18.04 CPU: 2x Intel Xeon E5-2660 (40 logical cores) RAM: 160GB I built a debug build of MariaDB server 10.5 from the Github source and tried to load and execute the TPC-H benchmarks on it with a database size of 1GB. While loading the data and executing queries, I observed that MariaDB takes an unusually large amount of time (more than 10x compared to other databases like QuickStep or MonetDB) to load the data and execute queries (more than 10 minutes per query, compared to less than a minute for other databases). I'm running the server with following options - ./bin/mysqld --user=nisargs --datadir=/fastdisk/mariadb-server-install-2/data/ --innodb-read-io-threads=40 --innodb-write-io-threads=40 --innodb-buffer-pool-size=16G --innodb-buffer-pool-instances=40 I just wanted to get some eyes on this and check if there is something wrong with the command line options (or whether I can tweak some options to improve performance). I'm just trying to conduct some benchmarking experiments and not very familiar with databases. Thanks Nisarg _______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
Hi Nisarg, You mentioned that you compiled a debug build. Based on the configuration parameters that you mention, I assume that your tables are in the InnoDB storage engine. InnoDB contains very many debug assertions that make it notoriously slow. To make matters worse, by default, the debug builds are unoptimized. If you are only interested in debugging symbols and not debugging instrumentation, then I suggest using cmake -DCMAKE_BUILD_TYPE=RelWithDebInfo In that way, you should see meaningful output in tools like 'perf top'. The release build should be compiling with something like -O2 by default, but it could be improved by setting -march=native -mtune=native. For what it is worth, for developing InnoDB, I normally use the following build options, so that tests would run faster: cmake -DCMAKE_{C_COMPILER=clang,CXX_COMPILER=clang++}-9 \ -DCMAKE_C{,XX}_FLAGS='-O2 -march=native -mtune=native -Wno-unused-command-line-argument -fdebug-macro' \ -DCONC_WITH_{UNITTEST,SSL}=OFF -DWITH_EMBEDDED_SERVER=OFF -DWITH_UNIT_TESTS=OFF \ -DCMAKE_BUILD_TYPE=Debug \ -DPLUGIN_{ARCHIVE,TOKUDB,MROONGA,OQGRAPH,ROCKSDB,CONNECT,SPIDER}=NO \ -DWITH_SAFEMALLOC=OFF -DWITH_{ZLIB,SSL}=system \ /path/to/source For gcc, my spell is similar, except for the first two lines, which are replaced by the following: cmake -DCMAKE_C{,XX}_FLAGS='-O2 -m'{arch,tune}=native \ Above, I am disabling most other components than InnoDB, because I am working on InnoDB internals. It is important to set WITH_SAFEMALLOC=OFF if you are using WITH_ASAN=ON, because it would only add unnecessary overhead and could hide some errors from ASAN. If you do not know what ASAN is, I suggest visiting https://github.com/google/sanitizers/wiki/. Best regards, Marko On Wed, Dec 4, 2019 at 11:21 AM Nisarg Shah <nisargshah323@gmail.com> wrote:
System config: OS: Ubuntu 18.04 CPU: 2x Intel Xeon E5-2660 (40 logical cores) RAM: 160GB
I built a debug build of MariaDB server 10.5 from the Github source and tried to load and execute the TPC-H benchmarks on it with a database size of 1GB. While loading the data and executing queries, I observed that MariaDB takes an unusually large amount of time (more than 10x compared to other databases like QuickStep or MonetDB) to load the data and execute queries (more than 10 minutes per query, compared to less than a minute for other databases). I'm running the server with following options -
./bin/mysqld --user=nisargs --datadir=/fastdisk/mariadb-server-install-2/data/ --innodb-read-io-threads=40 --innodb-write-io-threads=40 --innodb-buffer-pool-size=16G --innodb-buffer-pool-instances=40
I just wanted to get some eyes on this and check if there is something wrong with the command line options (or whether I can tweak some options to improve performance). I'm just trying to conduct some benchmarking experiments and not very familiar with databases.
Thanks Nisarg
_______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
-- Marko Mäkelä, Lead Developer InnoDB MariaDB Corporation
Thank you Marko! RelWithDebInfo build type sounds like the best solution here, as I only need the debug symbols. I will try out this and the other InnoDB options you specified. Hopefully that should resolve the issue. On Wed, Dec 4, 2019 at 4:16 AM Marko Mäkelä <marko.makela@mariadb.com> wrote:
Hi Nisarg,
You mentioned that you compiled a debug build. Based on the configuration parameters that you mention, I assume that your tables are in the InnoDB storage engine. InnoDB contains very many debug assertions that make it notoriously slow. To make matters worse, by default, the debug builds are unoptimized.
If you are only interested in debugging symbols and not debugging instrumentation, then I suggest using cmake -DCMAKE_BUILD_TYPE=RelWithDebInfo
In that way, you should see meaningful output in tools like 'perf top'. The release build should be compiling with something like -O2 by default, but it could be improved by setting -march=native -mtune=native.
For what it is worth, for developing InnoDB, I normally use the following build options, so that tests would run faster:
cmake -DCMAKE_{C_COMPILER=clang,CXX_COMPILER=clang++}-9 \ -DCMAKE_C{,XX}_FLAGS='-O2 -march=native -mtune=native -Wno-unused-command-line-argument -fdebug-macro' \ -DCONC_WITH_{UNITTEST,SSL}=OFF -DWITH_EMBEDDED_SERVER=OFF -DWITH_UNIT_TESTS=OFF \ -DCMAKE_BUILD_TYPE=Debug \ -DPLUGIN_{ARCHIVE,TOKUDB,MROONGA,OQGRAPH,ROCKSDB,CONNECT,SPIDER}=NO \ -DWITH_SAFEMALLOC=OFF -DWITH_{ZLIB,SSL}=system \ /path/to/source
For gcc, my spell is similar, except for the first two lines, which are replaced by the following:
cmake -DCMAKE_C{,XX}_FLAGS='-O2 -m'{arch,tune}=native \
Above, I am disabling most other components than InnoDB, because I am working on InnoDB internals. It is important to set WITH_SAFEMALLOC=OFF if you are using WITH_ASAN=ON, because it would only add unnecessary overhead and could hide some errors from ASAN. If you do not know what ASAN is, I suggest visiting https://github.com/google/sanitizers/wiki/.
Best regards,
Marko
On Wed, Dec 4, 2019 at 11:21 AM Nisarg Shah <nisargshah323@gmail.com> wrote:
System config: OS: Ubuntu 18.04 CPU: 2x Intel Xeon E5-2660 (40 logical cores) RAM: 160GB
I built a debug build of MariaDB server 10.5 from the Github source and tried to load and execute the TPC-H benchmarks on it with a database size of 1GB. While loading the data and executing queries, I observed that MariaDB takes an unusually large amount of time (more than 10x compared to other databases like QuickStep or MonetDB) to load the data and execute queries (more than 10 minutes per query, compared to less than a minute for other databases). I'm running the server with following options -
./bin/mysqld --user=nisargs --datadir=/fastdisk/mariadb-server-install-2/data/ --innodb-read-io-threads=40 --innodb-write-io-threads=40 --innodb-buffer-pool-size=16G --innodb-buffer-pool-instances=40
I just wanted to get some eyes on this and check if there is something wrong with the command line options (or whether I can tweak some options to improve performance). I'm just trying to conduct some benchmarking experiments and not very familiar with databases.
Thanks Nisarg
_______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
-- Marko Mäkelä, Lead Developer InnoDB MariaDB Corporation
participants (3)
-
Marko Mäkelä -
Nisarg Shah -
Vladislav Vaintroub