MySQL Bugs: #35899: With a SPARC 8 cpu 8 core, a simple query (select1;) cannot go over 26K QPS

Bug #35899	With a SPARC 8 cpu 8 core, a simple query (select1;) cannot go over 26K QPS
Submitted:	8 Apr 2008 11:23	Modified:	29 May 2008 9:45
Reporter:	Philippe Campos	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.1.23	OS:	Solaris (10)
Assigned to:		CPU Architecture:	Any

Description:
MySQL 5.1.23 running under Solaris 10 with dual 32x SUNW,UltraSPARC-T1  1.2 GHZ with Sun STK6140 mirrored stripe fiberchannel, 16 disks (15K) 270GB disk volume and 4GB memory, connected to dedicated Gigabit switch.

Query : select 1;
Threads : 80

The number of QPS is 26K and cannot be increased.

How to repeat:
Create a shell script with 80 threads issuing 'select 1;'

this issue is an impotant one for the customer and lead to a scale problem.

Sorry, but I don't understand this issue.

What do you mean with 26K QPS?

First I thought you mean: 26*10^3 queries per second here.

But therefor a test with only 80 threads isn't enough.

I created a short test with 80 threads (connection + select 1;) and it worked fine.

Please can you explain what you mean with 26K QPS.
Also, please, could you provide a short test.

There are two performance bugs in my opinion:
1. Remote execution is a factor two slower then local
2. Jdbc execution is a factor two slower then mysql native

Below matrix gives the throughput of 'select 1;' in KQPS (kilo queries per seconds). Even with the fastest execution (mysql-local) only 50% of the cpu is used.

	local	remote
mysql	133	57
jdbc	63	26

The number '26' is the situation we use: jdbc remote. So this means no more then 26000 queries can be executed per second.

See testit.tar.gz to reproduce the mysql figures
Usage: testit.sh <hostname> <port> <password>

For jdbc I will add later on a test program.

Test utility for load test mysql with 80 threads with 100K queries.

Attachment: testit.tar.gz (application/x-gzip, text), 2.16 KiB.

Test utility for jdbc load test. Change connect string in testjdbc.sh, and run testitjdbc.sh

Attachment: testitjdbc.tar.gz (application/x-gzip, text), 931 bytes.

Test utility for jdbc load test. Change connect string in testjdbc.sh, and run

Attachment: testitjdbc.tar.gz (application/x-gzip, text), 931 bytes.

Corrected version: Test utility for jdbc load test. Change connect string in testjdbc.sh, and run testitjdbc.sh

Attachment: testitjdbc.tar.gz (application/x-gzip, text), 3.34 KiB.

Wow!
Many thanks for this great feedback.

I will set this back to open, because the feedback was given.

Hi Donald. I will stay on Java side of problem as it really shouldn't be that slower than C API.

I do not see any optimization in your c/J test! Did you check for maxPerformance settings:

#
# A configuration that maximizes performance, while
# still staying JDBC-compliant and not doing anything that
# would be "dangerous" to run-of-the-mill J2EE applications
#
# Note that because we're caching things like callable statements
# and the server configuration, this bundle isn't appropriate
# for use with servers that get config'd dynamically without
# restarting the application using this configuration bundle.

cachePrepStmts=true
cacheCallableStatements=true

cacheServerConfiguration=true

#
# Reduces amount of calls to database to set
# session state. "Safe" as long as application uses
# Connection methods to set current database, autocommit
# and transaction isolation
#

useLocalSessionState=true
elideSetAutoCommits=true
alwaysSendSetIsolation=false

# Can cause high-GC pressure if timeouts are used on every
# query
enableQueryTimeouts=false

and on Solaria OS:
#
# Solaris has pretty high syscall overhead, so these configs
# remove as many syscalls as possible.
#

# Reduce recv() syscalls

useUnbufferedInput=false
useReadAheadInput=false

# Reduce number of calls to getTimeOfDay()

maintainTimeStats=false

You might also take a look at other properties like useDirectRowUnpack, useFastIntParsing and so on and vary them according to your environment and needs (ie. on such short query "useCompression" will only further decrease performance while it can help on "bigger" queries ...). Please also see ConnectionPropertiesImpl.java in c/J sources to see how options relate to performance.

As for remote test, I can not go deeper into this as I can't replicate your network setup.

Please try optimizing your test and inform me of results.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".