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, Hardware / Reviews, python

WORK IN PROGRESS!!!

quick answers:
- PHP + Python are both great languages
  - PHP performs FASTER on the benchmark game than Python
  - but Python performs FASTER on marix multiplications
  - file performance wise they should also be equal X-D
- yes virtualization will slow down calculation a wee bit
- yes single threaded the AMD Ryzen 5 5600G consumer CPU is faster the EEC capable AMD EPYC 7402P 24-Core

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, but if it is in the name of security + simplify +simplify + simplify it is (probably) worth it

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

the “benchmark game” benchmark:

https://sschakraborty.github.io/benchmark/index.html
the benchmark game implements the same program on many many languages and tests pure calculation performance of a language
benchmark_game.php: a bash based program that performs mathematical calculations to compare it to other programming languages
each python or php process will mostly only use 1 to 2 cores
- this inability for multi threading is fine for web requests as web requests can be seen as “more or less small junks of data” that are process per user per request aka “user pushed a button”, so when many users are using are pushing buttons (sending requests) having a CPU with more cores might help
- but if it’s only a single terminal based bash cli application, single-core speed is the limiting factor and adding more cores to the server won’t help
- 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!
php and python per default can not run the same program on multiple cores
php was not designed to process large 10GByte files files in a multi threaded way
python probably will also hit it’s speed limits fast when it comes to large files

all tests were done on Debian 12

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 on this hardware with Debian12 kvm: real 1m55.259s (-10.61% slower)
  - AMD EPYC 7402P 24-Core Processor + NVMe: real 3m7.412s

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:
- AMD Ryzen 5 5600G + 32GB RAM + NVMe:
  - ```
  time /usr/bin/python3.11 -OO benchmark_game.py 16000
```
- first run: real 25m39.897s
  - something bust be wrong?
  - this is insanely slow…
  - doing massive amounts of computations not python’s strength
    - this would be a task that is HIGHLY recommended to be outsourced to C, Rust or C++ or yes if it has to be Go or Java
- 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
```
- AMD EPYC 7402P 24-Core Processor + NVMe:
  - 1st run: real 27m13.372s (+6.070% slower than on Ryzen 5 5600G)
  - 2nd run bytecode: real 17m23.637s (almost 2x times faster, +49.12% slower than Ryzen 5 5600G WHILE using more power)

matrix multiplications: (ChatGPT 4.0 generated benchmark)

# python:
# uses https://numpy.org/doc/stable/reference/generated/numpy.dot.html
wget https://dwaves.de/scripts/bench/bench_cpu_matrix_multiplications_v1.1.py.txt

time /usr/bin/python3.11 bench_cpu_matrix_multiplications_v1.1.py.txt

=== bench_cpu_optimized v1.1 ===
Completed 0.0%
Completed 10.0%
Completed 20.0%
Completed 30.0%
Completed 40.0%
Completed 50.0%
Completed 60.0%
Completed 70.0%
Completed 80.0%
Completed 90.0%
Python Matrix Multiplication Time: 1.4431729316711426 seconds
Python Matrix Multiplications per Second: 692917.6525241752 <- +1444% faster than php

real 0m1.524s
user 0m1.599s
sys 0m0.672s

# php:
wget https://dwaves.de/scripts/bench/bench_cpu_matrix_multiplications_v1.1.php.txt

/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

time /usr/bin/php bench_cpu_matrix_multiplications_v1.1.php.txt
=== bench_cpu_optimized v1.1 ===
Completed 0%
Completed 10%
Completed 20%
Completed 30%
Completed 40%
Completed 50%
Completed 60%
Completed 70%
Completed 80%
Completed 90%
PHP Matrix Multiplication Time: 20.846071958542 seconds
PHP Matrix Multiplications per Second: 47970.668142601

real 0m20.859s
user 0m20.840s
sys 0m0.016s

ChatGPT4: Q: how come php is faster at the “benchmark game”? (know this benchmark?)

A; “Yes, I’m familiar with the “benchmark game” or the Computer Language Benchmarks Game, which is a collection of performance tests comparing implementations of different programming languages.”

“PHP might perform relatively well in certain benchmarks on the “benchmark game” due to several reasons:

Benchmark Design: Some benchmarks in the “benchmark game” may favor the execution model or features of PHP, showcasing its strengths in certain scenarios.
Specific Optimization: PHP may have been specifically optimized for certain tasks within those benchmarks, leading to competitive performance.
Interpreter Optimizations: PHP interpreters might have undergone optimizations over time, improving their performance for specific types of tasks.
Caching: PHP often runs in environments where code caching (e.g., OPcache) is enabled by default, which can improve performance for repeated executions of the same code.

However, it’s important to note that PHP’s primary design focus is web development, where it typically performs well. In more general-purpose or CPU-bound tasks, languages like Python, especially when leveraging optimized libraries like NumPy, tend to outperform PHP.”

dwaves’ benchmark:

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
  … done
- real 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
  …done
- real 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
  ……running benchmark web: (not implemented yet)
- total: real 1m23.251s
- 2nd run: total: 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
- total: real 1m21.885s (+1.640% slower than directly on hardware)
directly-on-hardware:
- lscpu |head
  Model name: AMD EPYC 7402P 24-Core Processor
  BIOS Model name: AMD EPYC 7402P 24-Core Processor Unknown CPU @ 2.8GHz
  hostnamectl
  Operating System: Debian GNU/Linux 12 (bookworm)
  Kernel: Linux 6.1.0-21-amd64
  Architecture: x86-64
  Hardware Vendor: GIGABYTE
  Hardware Model: G292-Z20-00
  Firmware Version: R23
- time ./dwaves_benchmark_run.sh
- total: real 1m47.917s (+17.55% slower than Ryzen 5 5600G WHILE using more power)

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