Announcement

Collapse
No announcement yet.

ow to make faster a 10 year old board

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • ow to make faster a 10 year old board

    Hi all!
    I've a 10 years old board with 187,155 thread, 3,087,551 messages and 45,654 users, it's hosted on a dedicated server (Dell R510 with 2x Intel Quad-Core Xeon E5506 4 Cores and 16Gb ram), before you start, with or without themes and plugins that board isn't fast, admin panel is really slow, browsing it's slow when a lot of users are online and I want try to speed up it.
    I think to get all old post and freeze it to remove it from the database (any suggest to do it?) but if I do it I loose it from the search engine and that isn't a good idea (SEO troubles too), than I'm here asking for suggests, how can I speed up it?

    My server is a FreeBSD 10, with Nginx as web server, PHP 5.4.45 running with php-fpm, MySQL 5.6.26, memcached 1.4.24, to speed up the search engine I use your suggest about sphinx search

    My configurations are:
    MySQL: http://pastebin.com/5vQ8dzkw
    Nginx: http://pastebin.com/t1Rr6S4g
    php-fpm: http://pastebin.com/1ZF18Yyu
    php: http://pastebin.com/z9sFjruh
    php extensions: http://pastebin.com/3QPKeBe8

    Thank's

  • #2
    You dont mention which vB version, I suspect 4.2.3 given you are on php 5.4.

    You really need someone to look at why you are runing slow, it should be really fast.
    I have about 2.5 million posts, on a lesser spec machine and its really nippy (vB3, but even so, its not that much slower on vB4)


    Some of you mysql settings look a bit suspect to me, for example ;

    max_connections = 40 : This seems a low number unless you are a quiet forum.

    wait_timeout = 8000 : This is insanely high, I run with 180 or 240.
    Baby, I was born this way

    Comment


    • #3
      It's slow after I upgrade from vb3 to vb4, and yes I've lastes vb4 I forgot that information.

      For connection, I don't reach maximum connections because I optimized caches and put all search external with sphynx, maybe wait_timeout=8000 it's a bit too high but it was to don't close mysql connect before page loads... any suggest about both?

      Comment


      • #4
        Some mysql stats:
        Uptime: 45 days, 18 hours, 43 mins e 23 secs.

        Click image for larger version

Name:	mysql-stats.jpg
Views:	19
Size:	79.8 KB
ID:	4329012

        Comment


        • #5
          Tuning-prime response:
          -- MYSQL PERFORMANCE TUNING PRIMER --
          - By: Matthew Montgomery -

          MySQL Version 5.6.25-log amd64

          Uptime = 45 days 18 hrs 52 min 32 sec
          Avg. qps = 51
          Total Questions = 205195983
          Threads Connected = 2

          Server has been running for over 48hrs.
          It should be safe to follow these recommendations

          To find out more information on how each of these
          runtime variables effects performance visit:
          http://dev.mysql.com/doc/refman/5.6/...variables.html
          Visit http://www.mysql.com/products/enterprise/advisors.html
          for info about MySQL's Enterprise Monitoring and Advisory Service

          SLOW QUERIES
          The slow query log is NOT enabled.
          Current long_query_time = 1.000000 sec.
          You have 32973 out of 205196007 that take longer than 1.000000 sec. to complete
          [: 0.00000000000000000000: bad number
          Your long_query_time seems to be fine

          BINARY UPDATE LOG
          The binary update log is enabled
          Binlog sync is not enabled, you could loose binlog records during a server crash

          WORKER THREADS
          Current thread_cache_size = 34
          Current threads_cached = 31
          Current threads_per_sec = 0
          Historic threads_per_sec = 0
          Your thread_cache_size is fine

          MAX CONNECTIONS
          Current max_connections = 40
          Current threads_connected = 2
          Historic max_used_connections = 41
          The number of used connections is 102% of the configured maximum.
          You should raise max_connections

          INNODB STATUS
          Current InnoDB index space = 37 M
          Current InnoDB data space = 73 M
          Current InnoDB buffer pool free = 0 %
          Current innodb_buffer_pool_size = 64 M
          Depending on how much space your innodb indexes take up it may be safe
          to increase this value to up to 2 / 3 of total system memory

          MEMORY USAGE
          Max Memory Ever Allocated : 2.17 G
          Configured Max Per-thread Buffers : 676 M
          Configured Max Global Buffers : 1.50 G
          Configured Max Memory Limit : 2.16 G
          Physical Memory : 16.00 G
          Max memory limit seem to be within acceptable norms

          KEY BUFFER
          Current MyISAM index space = 1.44 G
          Current key_buffer_size = 1.17 G
          Key cache miss rate is 1 : 45574
          Key buffer free ratio = 50 %
          Your key_buffer_size seems to be too high.
          Perhaps you can use these resources elsewhere

          QUERY CACHE
          Query cache is enabled
          Current query_cache_size = 256 M
          Current query_cache_used = 120 M
          Current query_cache_limit = 32 M
          Current Query cache Memory fill ratio = 47.14 %
          Current query_cache_min_res_unit = 1 K
          MySQL won't cache query results that are larger than query_cache_limit in size

          SORT OPERATIONS
          Current sort_buffer_size = 256 K
          Current read_rnd_buffer_size = 256 K
          Sort buffer seems to be fine

          JOINS
          Current join_buffer_size = 16.00 M
          You have had 146142 queries where a join could not use an index properly
          You have had 7405 joins without keys that check for key usage after each row
          join_buffer_size >= 4 M
          This is not advised
          You should enable "log-queries-not-using-indexes"
          Then look for non indexed joins in the slow query log.

          OPEN FILES LIMIT
          Current open_files_limit = 470889 files
          The open_files_limit should typically be set to at least 2x-3x
          that of table_cache if you have heavy MyISAM usage.
          Your open_files_limit value seems to be fine

          TABLE CACHE
          Current table_open_cache = 3000 tables
          Current table_definition_cache = 16384 tables
          You have a total of 2284 tables
          You have 2866 open tables.
          Current table_cache hit rate is 20%
          , while 95% of your table cache is in use
          You should probably increase your table_cache

          TEMP TABLES
          Current max_heap_table_size = 320 M
          Current tmp_table_size = 320 M
          Of 4953742 temp tables, 20% were created on disk
          Created disk tmp tables ratio seems fine

          TABLE SCANS
          Current read_buffer_size = 128 K
          Current table scan ratio = 63 : 1
          read_buffer_size seems to be fine

          TABLE LOCKING
          Current Lock Wait ratio = 1 : 3041
          You may benefit from selective use of InnoDB.
          If you have long running SELECT's against MyISAM tables and perform
          frequent updates consider setting 'low_priority_updates=1'
          If you have a high concurrency of inserts on Dynamic row-length tables
          consider setting 'concurrent_insert=ALWAYS'.

          Comment


          • #6
            mysqltuner response: >> MySQLTuner 1.3.0 - Major Hayden <[email protected]>
            >> Bug reports, feature requests, and downloads at http://mysqltuner.com/
            >> Run with '--help' for additional options and output filtering
            [OK] Currently running supported MySQL version 5.6.25-log
            [OK] Operating on 64-bit architecture

            -------- Storage Engine Statistics -------------------------------------------
            [--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MRG_MYISAM
            [--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 52)
            [--] Data in MyISAM tables: 3G (Tables: 1209)
            [--] Data in InnoDB tables: 71M (Tables: 989)
            [--] Data in MEMORY tables: 1M (Tables: 6)
            [!!] Total fragmented tables: 157

            -------- Security Recommendations -------------------------------------------
            [OK] All database users have passwords assigned

            -------- Performance Metrics -------------------------------------------------
            [--] Up for: 45d 19h 6m 40s (205M q [51.872 qps], 9M conn, TX: 3033B, RX: 65B)
            [--] Reads / Writes: 75% / 25%
            [--] Total buffers: 1.8G global + 16.9M per thread (40 max threads)
            [OK] Maximum possible memory usage: 2.5G (15% of installed RAM)
            [OK] Slow queries: 0% (32K/205M)
            [!!] Highest connection usage: 100% (41/40)
            [OK] Key buffer size / total MyISAM indexes: 1.2G/1.4G
            [OK] Key buffer hit rate: 100.0% (17B cached / 383K reads)
            [OK] Query cache efficiency: 64.0% (90M cached / 141M selects)
            [!!] Query cache prunes per day: 136204
            [OK] Sorts requiring temporary tables: 1% (183K temp sorts / 9M sorts)
            [!!] Joins performed without indexes: 153580
            [OK] Temporary tables created on disk: 20% (1M on disk / 6M total)
            [OK] Thread cache hit rate: 99% (129 created / 9M connections)
            [OK] Table cache hit rate: 20% (2K open / 13K opened)
            [OK] Open file limit used: 0% (2K/470K)
            [OK] Table locks acquired immediately: 99% (133M immediate / 133M locks)
            [!!] InnoDB buffer pool / data size: 64.0M/71.8M
            [OK] InnoDB log waits: 0
            -------- Recommendations -----------------------------------------------------
            General recommendations:
            Run OPTIMIZE TABLE to defragment tables for better performance
            Reduce or eliminate persistent connections to reduce connection usage
            Increasing the query_cache size over 128M may reduce performance
            Adjust your join queries to always utilize indexes
            Variables to adjust:
            max_connections (> 40)
            wait_timeout (< 8000)
            interactive_timeout (< 8000)
            query_cache_size (> 256M) [see warning above]
            join_buffer_size (> 16.0M, or always use indexes with joins)
            innodb_buffer_pool_size (>= 71M)

            Comment


            • #7
              Now I'm changing wait_timeout to 180 and max connections to 80

              Comment

              widgetinstance 262 (Related Topics) skipped due to lack of content & hide_module_if_empty option.
              Working...
              X