1

I don't understand very well about servers and mysql, I just have an android application running, and when it reaches 1800 simultaneous users the server slows down. I don't know if it's normal or if I can optimize something

my android app has a laravel backend and the app code is java

Server Config

16 vCPU Intel Xeon 2.1 GHz 30 GB RAM DDR4 1 TB SSD

my my.cnf

[mysql]
port                            = 3306
socket                          = /var/lib/mysql/mysql.sock

[mysqld]
performance_schema = ON
# === Required Settings ===
basedir                         = /usr
bind_address                    = 127.0.0.1 # Change to 0.0.0.0 to allow remote connections
datadir                         = /var/lib/mysql
#default_authentication_plugin  = mysql_native_password # Enable in MySQL 8+ or MariaDB 10.6+ for backwards compatibility with common CMSs
max_allowed_packet              = 256M
max_connect_errors              = 1000000
pid_file                        = /var/lib/mysql/mysql.pid
port                            = 3306
skip_external_locking
socket                          = /var/lib/mysql/mysql.sock
tmpdir                          = /tmp
user                            = mysql

# === SQL Compatibility Mode ===
# Enable for b/c with databases created in older MySQL/MariaDB versions
# (e.g. when using null dates)
#sql_mode                       = ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES
# Crappy SQL queries/schema? Go bold!
#sql_mode                       = ""

# === InnoDB Settings ===
default_storage_engine          = InnoDB
innodb_buffer_pool_instances    = 27     # Use 1 instance per 1GB of InnoDB pool size - max is 64
innodb_buffer_pool_size         = 27G    # Use up to 70-80% of RAM
innodb_file_per_table           = 1
innodb_flush_log_at_trx_commit  = 0
innodb_flush_method             = O_DIRECT
innodb_log_buffer_size          = 16M
innodb_log_file_size            = 3G
innodb_sort_buffer_size         = 64M    # UPD - Defines how much data is read into memory for sorting operations before writing to disk (default is 1M / max is 64M)
innodb_stats_on_metadata        = 0
innodb_lru_scan_depth           = 100
# thread_pool_size                = 6

# innodb_use_fdatasync           = 1     # Only (!) for MySQL v8.0.26+

innodb_temp_data_file_path     = ibtmp1:64M:autoextend:max:20G # Control the maximum size for the ibtmp1 file
innodb_thread_concurrency      = 15     # Optional: Set to the number of CPUs on your system (minus 1 or 2) to better
                                        # contain CPU usage. E.g. if your system has 8 CPUs, try 6 or 7 and check
                                        # the overall load produced by MySQL/MariaDB.
innodb_read_io_threads          = 64
innodb_write_io_threads         = 64
innodb_io_capacity             = 2000  # Depends on the storage tech - use 2000 for SSD, more for NVMe
innodb_io_capacity_max         = 4000  # Usually double the value of innodb_io_capacity

# === MyISAM Settings ===
# The following 3 options are ONLY supported by MariaDB & up to MySQL 5.7
# Do NOT un-comment on MySQL 8.x+
query_cache_limit              = 64M    # UPD
query_cache_size               = 64M   # UPD
query_cache_type               = DEMAND     # Enabled by default

key_buffer_size                 = 1G   # UPD

low_priority_updates            = 1
concurrent_insert               = 2

# === Connection Settings ===
max_connections                 = 30   # UPD - Important: high no. of connections = high RAM consumption

back_log                        = 512
thread_cache_size               = 100
thread_stack                    = 192K

interactive_timeout             = 180
wait_timeout                    = 180

# For MySQL 5.7+ only (disabled by default)
max_execution_time             = 9000 # Set a timeout limit for SELECT statements (value in milliseconds).
                                        # This option may be useful to address aggressive crawling on large sites,
                                        # but it can also cause issues (e.g. with backups). So use with extreme caution and test!
                                        # More info at: https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_max_execution_time

# For MariaDB 10.1.1+ only (disabled by default)
#max_statement_time             = 90    # The equivalent of "max_execution_time" in MySQL 5.7+ (set above)
                                        # The variable is of type double, thus you can use subsecond timeout.
                                        # For example you can use value 0.01 for 10 milliseconds timeout.
                                        # More info at: https://mariadb.com/kb/en/aborting-statements/

# === Buffer Settings ===
# Handy tip for managing your database's RAM usage:
# The following values should be treated carefully as they are added together and then multiplied by your "max_connections" value.
# Other options will also add up to RAM consumption (e.g. tmp_table_size). So don't go switching your "join_buffer_size" to 1G, it's harmful & inefficient.
# Use one of the database diagnostics tools mentioned at the top of this file to count your database's potential total RAM usage, so you know if you are within
# reasonable limits. Remember that other services will require enough RAM to operate properly (like Apache or PHP-FPM), so set your limits wisely.
join_buffer_size                = 40M    # UPD
read_buffer_size                = 30M    # UPD
read_rnd_buffer_size            = 40M    # UPD
sort_buffer_size                = 40M    # UPD

# === Table Settings ===
# In systemd managed systems like Ubuntu 16.04+ or CentOS 7+, you need to perform an extra action for table_open_cache & open_files_limit
# to be overriden (also see comment next to open_files_limit).
# E.g. for MySQL 5.7, please check: https://dev.mysql.com/doc/refman/5.7/en/using-systemd.html
# and for MariaDB check: https://mariadb.com/kb/en/library/systemd/
table_definition_cache          = 600000 # UPD
table_open_cache                = 600000 # UPD
open_files_limit                = 800000 # UPD - This can be 2x to 3x the table_open_cache value or match the system's
                                        # open files limit usually set in /etc/sysctl.conf and /etc/security/limits.conf
                                        # In systemd managed systems this limit must also be set in:
                                        # - /etc/systemd/system/mysql.service.d/override.conf (for MySQL 5.7+ in Ubuntu) or
                                        # - /etc/systemd/system/mysqld.service.d/override.conf (for MySQL 5.7+ in CentOS) or
                                        # - /etc/systemd/system/mariadb.service.d/override.conf (for MariaDB)
                                        # otherwise changing open_files_limit will have no effect.
                                        #
                                        # To edit the right file execute:
                                        # $ systemctl edit mysql (or mysqld or mariadb)
                                        # and set "LimitNOFILE=" to something like 100000 or more (depending on your system limits for MySQL)
                                        # or use "LimitNOFILE=infinity" for MariaDB only.
                                        # Finally merge the changes with:
                                        # $ systemctl daemon-reload; systemctl restart mysql (or mysqld or mariadb)

max_heap_table_size             = 1G  # Increase to 256M or 512M if you have lots of temporary tables because of missing indices in JOINs
tmp_table_size                  = 1G  # Use same value as max_heap_table_size

# === Search Settings ===
ft_min_word_len                 = 3     # Minimum length of words to be indexed for search results

# === Binary Logging ===
disable_log_bin                 = 1     # Binary logging disabled by default
#log_bin                                # To enable binary logging, uncomment this line & only one of the following 2 lines
                                        # that corresponds to your actual MySQL/MariaDB version.
                                        # Remember to comment out the line with "disable_log_bin".
#expire_logs_days               = 1     # Keep logs for 1 day - For MySQL 5.x & MariaDB before 10.6 only
# binlog_expire_logs_seconds     = 86400 # Keep logs for 1 day (in seconds) - For MySQL 8+ & MariaDB 10.6+ only

# === Error & Slow Query Logging ===
log_error                       = /var/lib/mysql/mysql_error.log
log_queries_not_using_indexes   = 0     # Disabled on production
long_query_time                 = 5
slow_query_log                  = 1     # Disabled on production
slow_query_log_file             = /var/lib/mysql/mysql_slow.log

[mysqldump]
# Variable reference
# For MySQL 5.7+:  https://dev.mysql.com/doc/refman/5.7/en/mysqldump.html
# For MariaDB:     https://mariadb.com/kb/en/library/mysqldump/
quick
quote_names
max_allowed_packet=1024M
5
  • Does this answer your question? Can you help me with my capacity planning?
    – djdomi
    Commented Jan 7, 2022 at 7:49
  • 1
    You need to check your database that tables are properly indexed with respect to your queries. mysqltuner can also give some help with this. Commented Jan 7, 2022 at 7:53
  • Additional information request, please. Post on pastebin.com and share the links. From your SSH login root, Text results of: A) SELECT COUNT(*) FROM information_schema.tables; B) SHOW GLOBAL STATUS; after minimum 24 hours UPTIME C) SHOW GLOBAL VARIABLES; D) SHOW FULL PROCESSLIST; G) SHOW ENGINE INNODB STATUS; AND very helpful OS information, includes - htop OR top for most active apps, ulimit -a for a Linux/Unix list of limits, iostat -xm 5 3 for IOPS by device and core/cpu count, for server workload tuning analysis to provide suggestions. Welcome to SF. Commented Jan 9, 2022 at 18:38
  • Identify the slow queries; fixing them is likely to the the solution. mysql.rjweb.org/doc.php/mysql_analysis#slow_queries_and_slowlog
    – Rick James
    Commented Jan 11, 2022 at 1:37
  • Please edit the question to limit it to a specific problem with enough detail to identify an adequate answer.
    – Community
    Commented Jan 11, 2022 at 15:44

1 Answer 1

1

Firstly, I'd suggest that it's "unusual" to have android devices, "Out There" on the Wibbly-Wobbly Web, connecting directly to any database. it's far more usual (i.e. better practice) to have the client application talk to a web server which in turn talks to the database. There are many reasons for this, mostly revolving around the the [in]security of anything running "Out There" but also including scaling - web servers are built with "scaling out" (adding more capacity to handle more traffic) in mind. Databases are not.

Secondly, generally-speaking, databases servers do not slow down.
The queries that are run inside them do.

Start by looking to tune your Queries.

Then look to bolstering your architecture, adding a web server in the middle.
One thing that this will gain you is Connection Pooling - the web server can handle your 1800 incoming connections with only a few handfuls of database connections (and using database credentials that never leave your [safe] server environment).

Look to tune your database or server as a last resort, in the most extreme of cases.

  • A Database Engineer spends several days poring over server and database configuration settings.
    They might get 2-3 Percentage Points improvement.
  • A Developer spends several days designing proper database structures, indexing, etc.
    They might get 2-3 Orders of Magnitude improvement.
2
  • Although I agree with your conclusion that most of what is said to be "database problems" needs to be fixed in the code I would argue that often it requires the expertise of a DBA to point out to a developer that their code in its current form sucks and more specifically where. You would expect that developers know that in the real world indexes are needed, that queries should be constructed to NOT return all data when you're only displaying a subset etc. etc. but in reality frequently such shortcomings only become evident in the real world and not on the developers laptop.
    – Bob
    Commented Jan 7, 2022 at 13:10
  • Hallelujah, brother, you're preaching to the choir! I've been on both sides of this debate - having done both Development and DBA - and the Developers' complaint of "It worked on my Machine", especially prevalent since the onset of .Net, is the bane of the software world. Trying to persuade them that their code / computer / development process is /not/ the most important thing in the Universe is an on-going battle.
    – Phill W.
    Commented Jan 7, 2022 at 14:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.