python vs php benchmarks: benchmark game + web based apache2 based benchmark – python keeps giving 1064 You have an error in your SQL syntax

01.May.2024

devPHP, devPython, python

WORK IN PROGRESS!!!

what they have in common

+ syntax readability + easy to learn
both interpreted languages
imho the config and setup of “runtime environment” both can be a bit complicated
both languages are under active development, so new functions will be added and some functions will be removed, which sometimes makes life of developers rather tricky, having to constantly update (often only small) changes in src to keep the program running on the latest interpreter version

python vs php?

it is important to do technical tests, before making decisions about what tech to use, for example:

python basically always generates bytecode after first run
- ```
time /usr/bin/python3.11 -m compileall benchmark_game.py
```
- will rerun from bytecode which will be a faster the re-interpreting the src over and over again
various projectes attempted it, but php does not have such a feature build in.

how long does it take to do this and that:
- compile time: there is (basically) none with interpreted-on-the-fly languages
- write 10 million lines into 10 million files?
  - then modify those 10 million lines in 10 million files
- insert 10 million records into the database?
- modify change called “update” 10 million records in the database?
- read 10 million records from database?
- do 10 million calculations?
- create key:value array with 10 million elements?
- quick sort the key:value array with 10 million elements?
- how many web requests can be processed per second?
  - this of course also strongy depends on the efficiency of the webserver software (default: apache2)
- …

benchmarks: benchmark game

all tests were done with:

hostnamectl; # tested with
Operating System: Debian GNU/Linux 12 (bookworm) 
Kernel: Linux 6.1.0-20-amd64
Architecture: x86-64

benchmark_game.php (backup)copy of this.

how to run it:

to be run with opcache optimizations:

su - root
apt update
# what is the latest available version?
apt search php8
# it is currently 8.2
apt install php8.2
# should automatically install opcache as well
apt install php-opcache
# test what php is installed
which php
/usr/bin/php

/usr/bin/php --version
PHP 8.2.18 (cli) (built: Apr 11 2024 22:07:45) (NTS)
Copyright (c) The PHP Group
Zend Engine v4.2.18, Copyright (c) Zend Technologies
with Zend OPcache v8.2.18, Copyright (c), by Zend Technologies <- enabled per default?

# run benchmark, and take the real time, in a console, so the terminal output speed is not relevant

screen -S benchmark_game
wget https://dwaves.de/software/benchmark_game/benchmark_game.php.txt
mv benchmark_game.php.txt benchmark_game.php

time /usr/bin/php -dzend_extension=/usr/lib/php/20220829/opcache.so -dopcache.enable_cli=1 -dopcache.jit_buffer_size=64M -n benchmark_game.php 16000

```
# Ctrl+A then D for detach from console
```

# when done, resume screen session to view result

```
screen -R benchmark_game
```

results:
- directly on hardware:
  - AMD Ryzen 5 5600G + 32GB RAM + NVMe: real 1m44.197s
- virtualized inside kvm vm (also Debian12 ):
  - real 1m55.259s (-10.61% slower)

benchmark_game.py (backup)copy of this.

apt update && apt install python3.11
# usage: where is python?
which python
# what version is python?
/usr/bin/python3.11 --version
Python 3.11.2
# then run it like:
screen -S benchmark_game
wget https://dwaves.de/software/benchmark_game/benchmark_game.py.txt
mv benchmark_game.py.txt benchmark_game.py
# actually run the benchmark
time /usr/bin/python3.11 -OO benchmark_game.py 16000

from man python: -O Remove assert statements and any code conditional on the value of __debug__; augment the filename for compiled (bytecode) files by adding .opt-1 before the .pyc extension.-OO Do -O and also discard docstrings; change the filename for compiled (bytecode) files by adding .opt-2 before the .pyc extension.
# Ctrl+A then D for detach from console
# when done, resume screen session to view result
```
screen -R benchmark_game
```

results:
- first run: real 25m39.897s, something was wrong here, this is insanely slow… no idea maybe it was not run in screen, so doing massive amounts of computations in short time is not python’s strength: this would be a task that is HIGHLY recommended to be outsourced to C, Rust or C++
- 2nd run: real 11m39.817s <- way faster still +671.62% slower than php8.2

compiling to bytecode like:

time /usr/bin/python3.11 -m compileall benchmark_game.py
# will create
__pycache__/benchmark_game.cpython-311.pyc
# run it
time /usr/bin/python3.11 -OO benchmark_game.py 16000

3rd run as bytecode: real 10m37.737s <- “only” +612.04% slower than php8.2

web based apache2 based benchmark

benching web based use-cases: benchmark_game.php: a single php process will use 1 to 2 cores max, which is fine for processing web requests as web requests can be seen as “more or less small junks of data” that are process per user request aka “user pushed a button”, so when many users are using the same (web) application more cores help, but if it’s only a single application, adding more cores to the server won’t help. php was not designed to process large amounts of data say a 10GByte files in a multi threaded way.

python probably will also hit it’s speed limits fast when it comes to large files.

python multi thread? while python can also not do multi-threading of a single program per default, there seems to be ways to make it work.

php multi thread? many attempts but UNTESTED!

this dwaves_benchmark_arrays will do:

ok array benchmark: (works fine within a few seconds)
- create 10x arrays with 10x million key:value pairs
  - key0: key:”let’s see how fast or slow this is”
  - key1: key:”let’s see how fast or slow this is”
  - key2: key:”let’s see how fast or slow this is”
  - …
- modify 10x arrays with 10x million key:value pairs
  - key0: key:”let’s see how fast or slow this can be changed”
  - key1: key:”let’s see how fast or slow this can be changed”
  - key2: key:”let’s see how fast or slow this can be changed”
  - …
ok files benachmark: (this works remarkably fast (inside a vm))
- create 10x files with 10x million lines
  - each line read: “let’s see how fast or slow this is”
  - modify all 10x million lines in 10x files
    - “let’s see how fast or slow this can be changed”
  - tidy up: delete all 10x test files
ok database benchmark: (this works remarkably slow, so had to give up on the idea to insert 10 million records per table and reduce to 100.000 thousand records per table)
- ok create database benchmark: creating 10x tables with each 100.000 records
  - each table will have 3x columns
  - each column will read: “let’s see how fast or slow this is”
- o modify database benchmark: creating 10x tables with each 10x million reading:
  - “let’s see how fast or slow this can be changed”
- o output the results as responsive html page

in order to run the benchmark:

apt -y install mariadb-server mariadb-client python3-pymysql
# define root password "root"
# or modify root_password = "root" in dwaves_benchmark_database.py
mysql_secure_installation
Change the root password? [Y/n] Y
New password: root
Re-enter new password: root
Password updated successfully!
Reloading privilege tables..
... Success!

download python v1
- wget https://dwaves.de/software/benchmark_game/2024-05-03-dwaves_benchmark-python.tar.gz
- tar fxvz 2024-05-03-dwaves_benchmark-python.tar.gz; # unpack
- run the benchmark:
  - ```
  time ./dwaves_benchmark_run.sh
```
- cleanup afterwards:
- ```
systemctl stop mariadb.service
apt --purge remove mariadb-server mariadb-client python3-pymysql
apt -y autoremove
```

this how-many-pages-per-second benchmark2 will do:

o create 3x files that hold actual html content that will be embedded into the output file
- each will contain path to images
o create 3x db records that holds actual html content that will be embedded into the output file
- o = means open, not yet implemented
the webserver will then be benched with it will be setup in kvm virtual machine and will run https://linuxconfig.org/how-to-benchmark-webserver-with-apache-bench against it

results:

what is strange: what are the limiting factors?
- neither mariadb nor python use 100% cpu during this benchmark
  - mariadb 2x processes each using a CPU core at 60%
  - python3 1x process using ~40% of a CPU core
directly-on-hardware: AMD Ryzen 5 5600G + 32GB RAM + NVMe
- 1st run: time ./dwaves_benchmark_run.sh==== running all dwaves_benchmarks v1.0 ====
  … and taking the time
  ……running benchmark part:
  ==== running dwaves_benchmark_arrays ====
  … create 10x arrays with 1000000 key:value pairs
  … done, creating array0
  … done, creating array1
  … done, creating array2
  … done, creating array3
  … done, creating array4
  … done, creating array5
  … done, creating array6
  … done, creating array7
  … done, creating array8
  … done, creating array9
  …modify 10x arrays with 1000000 key:value pairs
  … done, modifying array0
  … done, modifying array1
  … done, modifying array2
  … done, modifying array3
  … done, modifying array4
  … done, modifying array5
  … done, modifying array6
  … done, modifying array7
  … done, modifying array8
  … done, modifying array9
  … donereal 0m3.229s
  user 0m3.184s
  sys 0m0.044s
  ……running benchmark part:
  ==== running dwaves_benchmark_files ====
  … create 10x files with 1000000 lines
  …done creating file ./dwaves_benchmark_files_0.txt
  …done creating file ./dwaves_benchmark_files_1.txt
  …done creating file ./dwaves_benchmark_files_2.txt
  …done creating file ./dwaves_benchmark_files_3.txt
  …done creating file ./dwaves_benchmark_files_4.txt
  …done creating file ./dwaves_benchmark_files_5.txt
  …done creating file ./dwaves_benchmark_files_6.txt
  …done creating file ./dwaves_benchmark_files_7.txt
  …done creating file ./dwaves_benchmark_files_8.txt
  …done creating file ./dwaves_benchmark_files_9.txt
  …modify all 1000000x lines in all 10x files
  …done modifying file ./dwaves_benchmark_files_0.txt
  …done modifying file ./dwaves_benchmark_files_1.txt
  …done modifying file ./dwaves_benchmark_files_2.txt
  …done modifying file ./dwaves_benchmark_files_3.txt
  …done modifying file ./dwaves_benchmark_files_4.txt
  …done modifying file ./dwaves_benchmark_files_5.txt
  …done modifying file ./dwaves_benchmark_files_6.txt
  …done modifying file ./dwaves_benchmark_files_7.txt
  …done modifying file ./dwaves_benchmark_files_8.txt
  …done modifying file ./dwaves_benchmark_files_9.txt
  … tidy up: delete all 10 test files
  …donereal 0m3.968s
  user 0m2.328s
  sys 0m1.640s
  ……running benchmark part:
  ==== running dwaves_benchmark_database ====
  … mariadb can be installed like this: apt -y install mariadb-server mariadb-client python3-pymysql
  … print all databases:
  ((‘dwaves_benchmark_database’,), (‘information_schema’,), (‘mysql’,), (‘performance_schema’,), (‘sys’,))
  …database ‘dwaves_benchmark_database’ has been deleted.
  … user TestUser already exists, deleting user
  … creating database user TestUser
  … creating database dwaves_benchmark_database
  … creating table0 out of 10
  … inserting 100000 records into table0
  … creating table1 out of 10
  … inserting 100000 records into table1
  … creating table2 out of 10
  … inserting 100000 records into table2
  … creating table3 out of 10
  … inserting 100000 records into table3
  … creating table4 out of 10
  … inserting 100000 records into table4
  … creating table5 out of 10
  … inserting 100000 records into table5
  … creating table6 out of 10
  … inserting 100000 records into table6
  … creating table7 out of 10
  … inserting 100000 records into table7
  … creating table8 out of 10
  … inserting 100000 records into table8
  … creating table9 out of 10
  … inserting 100000 records into table9
  … creating table10 out of 10
  … inserting 100000 records into table10
  … create 10x tables with 100000 records
  … donereal 1m16.051s
  user 0m11.838s
  sys 0m16.381s
  ……running benchmark web: (not implemented yet)real 1m23.251s
  user 0m17.350s
  sys 0m18.069s
- 2nd run: real 1m23.072s
on the same hardware but inside kvm virtual machine with 4x CPU cores: (it is 2sec faster than directly on hardware! crazy! :D)
- time ./dwaves_benchmark_run.sh
  ==== running all dwaves_benchmarks v1.0 ====
  … and taking the time
  ……running benchmark part:
  ==== running dwaves_benchmark_arrays ====
  … create 10x arrays with 1000000 key:value pairs
  … done, creating array0
  … done, creating array1
  … done, creating array2
  … done, creating array3
  … done, creating array4
  … done, creating array5
  … done, creating array6
  … done, creating array7
  … done, creating array8
  … done, creating array9
  …modify 10x arrays with 1000000 key:value pairs
  … done, modifying array0
  … done, modifying array1
  … done, modifying array2
  … done, modifying array3
  … done, modifying array4
  … done, modifying array5
  … done, modifying array6
  … done, modifying array7
  … done, modifying array8
  … done, modifying array9
  … donereal 0m3.642s
  user 0m3.605s
  sys 0m0.032s
  ……running benchmark part:
  ==== running dwaves_benchmark_files ====
  … create 10x files with 1000000 lines
  …done creating file ./dwaves_benchmark_files_0.txt
  …done creating file ./dwaves_benchmark_files_1.txt
  …done creating file ./dwaves_benchmark_files_2.txt
  …done creating file ./dwaves_benchmark_files_3.txt
  …done creating file ./dwaves_benchmark_files_4.txt
  …done creating file ./dwaves_benchmark_files_5.txt
  …done creating file ./dwaves_benchmark_files_6.txt
  …done creating file ./dwaves_benchmark_files_7.txt
  …done creating file ./dwaves_benchmark_files_8.txt
  …done creating file ./dwaves_benchmark_files_9.txt
  …modify all 1000000x lines in all 10x files
  …done modifying file ./dwaves_benchmark_files_0.txt
  …done modifying file ./dwaves_benchmark_files_1.txt
  …done modifying file ./dwaves_benchmark_files_2.txt
  …done modifying file ./dwaves_benchmark_files_3.txt
  …done modifying file ./dwaves_benchmark_files_4.txt
  …done modifying file ./dwaves_benchmark_files_5.txt
  …done modifying file ./dwaves_benchmark_files_6.txt
  …done modifying file ./dwaves_benchmark_files_7.txt
  …done modifying file ./dwaves_benchmark_files_8.txt
  …done modifying file ./dwaves_benchmark_files_9.txt
  … tidy up: delete all 10 test files
  …donereal 0m3.907s
  user 0m2.337s
  sys 0m1.472s
  ……running benchmark part:
  ==== running dwaves_benchmark_database ====
  … mariadb can be installed like this: apt -y install mariadb-server mariadb-client python3-pymysql
  … print all databases:
  ((‘dwaves_benchmark_database’,), (‘information_schema’,), (‘mysql’,), (‘performance_schema’,), (‘sys’,))
  …database ‘dwaves_benchmark_database’ has been deleted.
  … user TestUser already exists, deleting user
  … creating database user TestUser
  … creating database dwaves_benchmark_database
  … creating table0 out of 10
  … inserting 100000 records into table0
  … creating table1 out of 10
  … inserting 100000 records into table1
  … creating table2 out of 10
  … inserting 100000 records into table2
  … creating table3 out of 10
  … inserting 100000 records into table3
  … creating table4 out of 10
  … inserting 100000 records into table4
  … creating table5 out of 10
  … inserting 100000 records into table5
  … creating table6 out of 10
  … inserting 100000 records into table6
  … creating table7 out of 10
  … inserting 100000 records into table7
  … creating table8 out of 10
  … inserting 100000 records into table8
  … creating table9 out of 10
  … inserting 100000 records into table9
  … creating table10 out of 10
  … inserting 100000 records into table10
  … create 10x tables with 100000 records
  … donereal 1m14.332s
  user 0m17.880s
  sys 0m13.374s
  ……running benchmark web: (not implemented yet)
  
  real 1m21.885s
  user 0m23.825s
  sys 0m14.879s

python keeps giving 1064 You have an error in your SQL syntax

the developer’s mysql syntax is correct, the problem is: python mysql connector or python itself can; not; run; too; many; sql; commands; in-one-go:

# thils will fail with 1064
query = f"CREATE USER '{database_user}'@'localhost' IDENTIFIED BY '{database_user_pwd}'; GRANT SELECT, INSERT, UPDATE, DELETE ON {database_name}.* TO '{database_user}'@'localhost'; FLUSH PRIVILEGES;"

# this will work, but it's 3x steps
print(f"... creating database user {database_user}")
query = f"CREATE USER '{database_user}'@'localhost' IDENTIFIED BY '{database_user_pwd}';"
cursor.execute(query)
query = f"GRANT SELECT, INSERT, UPDATE, DELETE ON {database_name}.* TO '{database_user}'@'localhost';"
cursor.execute(query)
query = f"FLUSH PRIVILEGES;"
cursor.execute(query)

liked this article?

only together we can create a truly free world
plz support dwaves to keep it up & running!
(yes the info on the internet is (mostly) free but beer is still not free (still have to work on that))
really really hate advertisement
contribute: whenever a solution was found, blog about it for others to find!
talk about, recommend & link to this blog and articles
thanks to all who contribute!

admin