[SirFulgeruL]

Hello guys ,

today I had the opening of my SA:MP server and at the opening we were about 200 people .
In the begining it was a bit of lag because a lot of people were creating accounts and I didn't really took it as a issue cause i knew that it would happen . But after about 1200 accounts it started to get worst , textdraws wouldn't show anymore almost everyone had a desync people wouldn't get the Login Dialog box anymore and they could just spawn without a passsword .

I runned a mysqltunner and the result was this :

Код:

root@efnetwork:~# perl mysqltuner.pl
 >>  MySQLTuner 1.6.16 - Major Hayden <major@mhtx.net>
 >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
 >>  Run with '--help' for additional options and output filtering

[--] Skipped version check for MySQLTuner script
[OK] Logged in using credentials from debian maintenance account.
[OK] Currently running supported MySQL version 5.5.47-0+deb7u1
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics ---------------------------------------------                                                                                 --------------------
[--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MEMORY +MRG_MYISAM +My                                                                                 ISAM +PERFORMANCE_SCHEMA
[--] Data in MyISAM tables: 445K (Tables: 23)
[--] Data in InnoDB tables: 13M (Tables: 198)
[OK] Total fragmented tables: 0

-------- Security Recommendations ----------------------------------------------                                                                                 --------------------
[OK] There are no anonymous accounts for any database users
[OK] All database users have passwords assigned
[!!] User 'admin_default@%' hasn't specific host restriction.
[!!] User 'admin_forum@%' hasn't specific host restriction.
[!!] User 'admin_samp@%' hasn't specific host restriction.
[!!] User 'admin_zc@%' hasn't specific host restriction.
[!!] User 'fulgeratii_frm@%' hasn't specific host restriction.
[!!] There is no basic password file list!

-------- CVE Security Recommendations ------------------------------------------                                                                                 --------------------
[--] Skipped due to --cvefile option undefined

-------- Performance Metrics ---------------------------------------------------                                                                                 --------------------
[--] Up for: 24m 25s (62K q [42.558 qps], 9K conn, TX: 1G, RX: 4M)
[--] Reads / Writes: 89% / 11%
[--] Binary logging is disabled
[--] Physical Memory     : 2.0G
[--] Max MySQL memory    : 988.5M
[--] Other process memory: 745.8M
[--] Total buffers: 176.0M global + 1.6M per thread (500 max threads)
[--] P_S Max memory usage: 0B
[--] Galera GCache Max memory usage: 0B
[OK] Maximum reached memory usage: 231.2M (11.49% of installed RAM)
[OK] Maximum possible memory usage: 988.5M (49.11% of installed RAM)
[OK] Overall possible memory usage with other process is compatible with memory available
[OK] Slow queries: 0% (0/62K)
[OK] Highest usage of available connections: 6% (34/500)
[OK] Aborted connections: 0.00%  (0/9414)
[!!] name resolution is active : a reverse name resolution is made for each new connection and can reduce performance
[!!] Query cache may be disabled by default due to mutex contention.
[OK] Sorts requiring temporary tables: 0% (8 temp sorts / 13K sorts)
[OK] No joins without indexes
[!!] Temporary tables created on disk: 82% (1K on disk / 1K total)
[!!] Table cache hit rate: 16% (64 open / 398 opened)
[OK] Open file limit used: 1% (40/2K)
[OK] Table locks acquired immediately: 100% (52K immediate / 52K locks)

-------- Performance schema ------------------------------------------------------------------------
[--] Performance schema is disabled.

-------- ThreadPool Metrics ------------------------------------------------------------------------
[--] ThreadPool stat is disabled.

-------- MyISAM Metrics ----------------------------------------------------------------------------
[!!] Key buffer used: 18.3% (3M used / 16M cache)
[OK] Key buffer size / total MyISAM indexes: 16.0M/167.0K
[OK] Read Key buffer hit rate: 97.0% (99 cached / 3 reads)
[!!] Write Key buffer hit rate: 0.0% (49 cached / 49 writes)

-------- AriaDB Metrics ----------------------------------------------------------------------------
[--] AriaDB is disabled.

-------- InnoDB Metrics ----------------------------------------------------------------------------
[--] InnoDB is enabled.
[OK] InnoDB buffer pool / data size: 128.0M/13.3M
[OK] InnoDB buffer pool instances: 1
[!!] InnoDB Used buffer: 20.25% (1659 used/ 8191 total)
[OK] InnoDB Read buffer efficiency: 99.99% (19011008 hits/ 19012561 total)
[!!] InnoDB Write Log efficiency: 70.62% (12352 hits/ 17490 total)
[OK] InnoDB log waits: 0.00% (0 waits / 5138 writes)

-------- TokuDB Metrics ----------------------------------------------------------------------------
[--] TokuDB is disabled.

-------- Galera Metrics ----------------------------------------------------------------------------
[--] Galera is disabled.

-------- Replication Metrics -----------------------------------------------------------------------
[--] Galera Synchronous replication: NO
[--] No replication slave(s) for this server.
[--] This is a standalone server.

-------- Recommendations ---------------------------------------------------------------------------
General recommendations:
    Restrict Host for user@% to user@SpecificDNSorIp
    MySQL started within last 24 hours - recommendations may be inaccurate
    Enable the slow query log to troubleshoot bad queries
    Configure your accounts with ip or subnets only, then update your configuration with skip-name-resolve=1
    When making adjustments, make tmp_table_size/max_heap_table_size equal
    Reduce your SELECT DISTINCT queries which have no LIMIT clause
    Increase table_open_cache gradually to avoid file descriptor limits
    Read this before increasing table_open_cache over 64: http://*******/1mi7c4C
    Beware that open_files_limit (2500) variable
    should be greater than table_open_cache ( 64)
Variables to adjust:
    query_cache_type (=0)
    tmp_table_size (> 16M)
    max_heap_table_size (> 16M)
    table_open_cache (> 64)

My my.cnf mysql looks like :

Код:

[client]
port=3306
socket=/var/run/mysqld/mysqld.sock

[mysqld_safe]
socket=/var/run/mysqld/mysqld.sock

[mysqld]
user=mysql
pid-file=/var/run/mysqld/mysqld.pid
socket=/var/run/mysqld/mysqld.sock
port=3306
basedir=/usr
datadir=/var/lib/mysql
tmpdir=/tmp
lc-messages-dir=/usr/share/mysql
log_error=/var/log/mysql/error.log

symbolic-links=0
  
skip-external-locking
key_buffer_size = 16M
max_allowed_packet = 128M
table_open_cache = 64
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M

#innodb_use_native_aio = 0
innodb_file_per_table

max_connections=500
max_user_connections=250
wait_timeout=10
interactive_timeout=50
long_query_time=5

!includedir /etc/mysql/conf.d/

I also forgot to mention I have also a panel , but I don't think that is the main reason .

After few hours of thinking , I came with few options .
Adding a timer to the lines that are updating the database.
Or the update is happening only when the players disconnects from the server.

Does someone have any ideeas what I could do ?

Update: Because a lot of people were connecting at the same time the mysql couldn't keep up with the server . We don't have cache .