arve_lek

Mail: arve_lek[m]wp.pl GG: 2223666 Zlecę konfigurację serwera. FS-6T-V - Pentium G4400 - 8GB - SoftRaid 3x2To Ubuntu Server 16.04 "Xenial Xerus" LTS

Jeszcze podsyłam link do pdf. Bardzo fajnie wytłumaczone: https://issuu.com/panagiotistheodoropoulos/docs/pldc2012-optimizing-mysql-configura Może komuś się przyda.

Dzięki, Skrypt wykrył sporo rekomendacji, także będę analizował jutro Najmniej to podoba mi się: *** MySQL's maximum memory usage is dangerously high ** -------- Storage Engine Statistics ----------------------------------------------------------------- [--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MEMORY +MRG_MYISAM +MyISAM +PERFORMANCE_SCHEMA [--] Data in InnoDB tables: 185.3M (Tables: 1276) [--] Data in MyISAM tables: 943.8M (Tables: 5969) [OK] Total fragmented tables: 0 -------- Analysis Performance Metrics -------------------------------------------------------------- [--] innodb_stats_on_metadata: OFF [OK] No stat updates during querying INFORMATION_SCHEMA. -------- CVE Security Recommendations -------------------------------------------------------------- [--] Skipped due to --cvefile option undefined -------- Performance Metrics ----------------------------------------------------------------------- [--] Up for: 3h 39m 1s (802K q [61.042 qps], 24K conn, TX: 2G, RX: 101M) [--] Reads / Writes: 98% / 2% [--] Binary logging is disabled [--] Physical Memory : 7.7G [--] Max MySQL memory : 7.2G [--] Other process memory: 0B [--] Total buffers: 6.1G global + 1.1M per thread (1000 max threads) [--] P_S Max memory usage: 72B [--] Galera GCache Max memory usage: 0B [OK] Maximum reached memory usage: 6.1G (78.84% of installed RAM) [!!] Maximum possible memory usage: 7.2G (92.89% of installed RAM) [OK] Overall possible memory usage with other process is compatible with memory available [OK] Slow queries: 0% (0/802K) [OK] Highest usage of available connections: 1% (12/1000) [OK] Aborted connections: 0.00% (1/24156) [!!] name resolution is active : a reverse name resolution is made for each new connection and can reduce performance [OK] Query cache is disabled by default due to mutex contention on multiprocessor machines. [OK] Sorts requiring temporary tables: 0% (6 temp sorts / 167K sorts) [!!] Joins performed without indexes: 5746 [!!] Temporary tables created on disk: 77% (70K on disk / 90K total) [OK] Thread cache hit rate: 99% (12 created / 24K connections) [!!] Table cache hit rate: 9% (10K open / 104K opened) [OK] Open file limit used: 1% (12K/1M) [OK] Table locks acquired immediately: 99% (744K immediate / 744K locks) -------- Performance schema ------------------------------------------------------------------------ [--] Memory used by P_S: 72B [--] Sys schema is installed. -------- ThreadPool Metrics ------------------------------------------------------------------------ [--] ThreadPool stat is enabled. [--] Thread Pool Size: 2 thread(s). [!!] thread_pool_size between 16 and 36 when using InnoDB storage engine. -------- MyISAM Metrics ---------------------------------------------------------------------------- [OK] Key buffer used: 99.7% (33M used / 33M cache) [OK] Key buffer size / total MyISAM indexes: 32.0M/331.0M [OK] Read Key buffer hit rate: 98.7% (12M cached / 170K reads) [OK] Write Key buffer hit rate: 99.9% (1K cached / 1K writes) -------- InnoDB Metrics ---------------------------------------------------------------------------- [--] InnoDB is enabled. [--] InnoDB Thread Concurrency: 0 [OK] InnoDB File per table is activated [OK] InnoDB buffer pool / data size: 6.0G/185.3M [!!] Ratio InnoDB log file size / InnoDB Buffer pool size (8.33333333333333 %): 256.0M * 2/6.0G should be equal 25% [!!] InnoDB buffer pool instances: 8 [--] Number of InnoDB Buffer Pool Chunk : 48 for 8 Buffer Pool Instance(s) [OK] Innodb_buffer_pool_size aligned with Innodb_buffer_pool_chunk_size & Innodb_buffer_pool_instances [OK] InnoDB Read buffer efficiency: 99.93% (2174879 hits/ 2176322 total) [!!] InnoDB Write Log efficiency: 175.54% (1701 hits/ 969 total) [OK] InnoDB log waits: 0.00% (0 waits / 2670 writes) -------- AriaDB Metrics ---------------------------------------------------------------------------- [--] AriaDB is disabled. -------- TokuDB Metrics ---------------------------------------------------------------------------- [--] TokuDB is disabled. -------- XtraDB Metrics ---------------------------------------------------------------------------- [--] XtraDB is disabled. -------- Galera Metrics ---------------------------------------------------------------------------- [--] Galera is disabled. -------- Replication Metrics ----------------------------------------------------------------------- [--] Galera Synchronous replication: NO [--] No replication slave(s) for this server. [--] Binlog format: ROW [--] XA support enabled: ON [--] Semi synchronous replication Master: Not Activated [--] Semi synchronous replication Slave: Not Activated [--] This is a standalone server -------- Recommendations --------------------------------------------------------------------------- General recommendations: Control warning line(s) into /var/log/mysql/error.log file Control error line(s) into /var/log/mysql/error.log file Set up a Password for user with the following SQL statement ( SET PASSWORD FOR 'user'@'SpecificDNSorIp' = PASSWORD('secure_password'); ) MySQL was started within the last 24 hours - recommendations may be inaccurate Reduce your overall MySQL memory footprint for system stability Configure your accounts with ip or subnets only, then update your configuration with skip-name-resolve=1 Adjust your join queries to always utilize indexes 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://bit.ly/1mi7c4C Read this before increasing for MariaDB https://mariadb.com/kb/en/library/optimizing-table_open_cache/ This is MyISAM only table_cache scalability problem, InnoDB not affected. See more details here: https://bugs.mysql.com/bug.php?id=49177 This bug already fixed in MySQL 5.7.9 and newer MySQL versions. Beware that open_files_limit (1048576) variable should be greater than table_open_cache (10240) Thread pool size for InnoDB usage (2) Before changing innodb_log_file_size and/or innodb_log_files_in_group read this: https://bit.ly/2TcGgtU Variables to adjust: *** MySQL's maximum memory usage is dangerously high *** *** Add RAM before increasing MySQL buffer variables *** join_buffer_size (> 256.0K, or always use indexes with JOINs) tmp_table_size (> 32M) max_heap_table_size (> 32M) table_open_cache (> 10240) thread_pool_size between 16 and 36 for InnoDB usage innodb_log_file_size should be (=768M) if possible, so InnoDB total log files size equals to 25% of buffer pool size. innodb_buffer_pool_instances(=6)

To akurat dodałem dzisiaj. Wygenerowało mi w https://tools.percona.com/ Więc widzę, że bez sensu zapisywać zapytania

# # The Percona Server 5.7 configuration file. # # One can use all long options that the program supports. # Run program with --help to get a list of available options and with # --print-defaults to see which it would actually understand and use. # # For explanations see # http://dev.mysql.com/doc/mysql/en/server-system-variables.html [mysqld_safe] open_files_limit = 1024000 [mysqld] bind-address=127.0.0.1 user = mysql default-storage-engine = InnoDB 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 explicit_defaults_for_timestamp # Errors # log-error = /var/log/mysql/error.log log-queries-not-using-indexes = 1 slow-query-log = 1 slow-query-log-file = /var/log/mysql/mysql-slow.log # INNODB # innodb-flush-method = O_DIRECT innodb-log-files-in-group = 2 innodb-log-file-size = 256M innodb-flush-log-at-trx-commit = 1 innodb-file-per-table = 1 innodb-buffer-pool-size = 6G # Recommended in standard MySQL setup sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_ALL_TABLES # Disabling symbolic-links is recommended to prevent assorted security risks symbolic-links=0 # MyISAM # key-buffer-size = 32M myisam-recover-options = FORCE,BACKUP max_allowed_packet = 32M max-connect-errors = 1000000 # CACHES AND LIMITS # tmp-table-size = 32M max-heap-table-size = 32M query-cache-type = 0 query-cache-size = 0 max-connections = 500 thread-cache-size = 50 open-files-limit = 65535 table-definition-cache = 4096 table-open-cache = 10240 expire_logs_days = 14 Czy dobrze mam skonfigurowany plik ? Obecnie stron na serwerze jest około 400. Każda strona to blog na WordPress. FS-6T-V - Pentium G4400 - 8GB - SoftRaid 3x2To Ubuntu Server 16.04 "Xenial Xerus" LTS CPU mam przeważnie na poziomie 20-60% Wcześniej miałem o wiele mniej więc nie wiem czy czasem czegoś nie popsułem W htop mam od sporo: /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid Nie wiem czy CPU na taką ilość stron to dużo czy mało. Jakieś sugestie, rady ?

Tylko takich ip ciągle mi przybywa. Ręcznie IP muszę dodawać ? Jeżeli ktoś korzysta z publicznych proxy to ręcznie będzie ciężko to zrobić. Plik errors z dnia dzisiejszego zawiera już 35tys wierszy i tak każdego dnia.

Ktoś się na mnie uwziął i nie popuszcza. Ciągle szuka plików. 1. Dodałem konkretne IP w pliku htaccess - /var/www/html ale to nie pomogło. 2. Włączyłem firewalla w ovh, ale to też nie pomogło. Choć nie konfigurowałem, więc nie wiem czy się da zablokować tego typu ataki ? Apache mi się wiesza co kilka dni i strony się wykruszają. Restart apache i kłopot na kilka dni załatwiony. Jakieś sugestie czy da się zablokować te ataki ? [Tue Dec 11 20:18:33.856735 2018] [access_compat:error] [pid 9114] [client 47.52.26.165:11797] AH01797: client denied by server configuration: /var/www/html/7.php [Tue Dec 11 20:18:34.075947 2018] [access_compat:error] [pid 9116] [client 132.232.32.150:58354] AH01797: client denied by server configuration: /var/www/html/myadmin [Tue Dec 11 20:18:34.491850 2018] [access_compat:error] [pid 9117] [client 47.52.26.165:12038] AH01797: client denied by server configuration: /var/www/html/xiaoma.php [Tue Dec 11 20:18:35.137162 2018] [access_compat:error] [pid 9119] [client 47.52.26.165:12245] AH01797: client denied by server configuration: /var/www/html/xiaomae.php [Tue Dec 11 20:18:35.784413 2018] [access_compat:error] [pid 9123] [client 47.52.26.165:12432] AH01797: client denied by server configuration: /var/www/html/xiaomar.php [Tue Dec 11 20:18:36.418219 2018] [access_compat:error] [pid 9134] [client 47.52.26.165:12652] AH01797: client denied by server configuration: /var/www/html/qq.php

Naprawdę nikt nie wie ?

ext4

Jako, że jest to mój pierwszy post, to chciałbym wszystkich przywitać, więc cześć. Czy da się zrobić obraz konfiguracji serwera w OVH ? Tak żeby np. w przypadku awarii mógłbym od razu zrobić kompletny backup (konfiguracja i pliki na serwerze + bazy danych) ?