26.08.2016, 22:04
(
Последний раз редактировалось SirFulgeruL; 27.08.2016 в 05:50.
)
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 :
My my.cnf mysql looks like :
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 .
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)
Код:
[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/
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 .
