Vroom Posted July 4, 2015 Share Posted July 4, 2015 I have about 2 million posts in the database. It is running on a quad core Xeon E3-1240 (16GB Ram).I never really noticed any problems with the database speeds during regular running of the site but after having some problems during upgrades with some slow queries, I was looking into things and came across what seemed to me to be ridiculously long times for simple select queries. I was wondering if this is normal or is something messed up in my mysql config settings.For example, when looking at the forums_posts table in phpmyadmin (with 2 million posts), when I first browse the table it displays the first 30 rows in 0.0182 sec. Then when I clicked the double arrow to show the last entry it took 113 seconds to show the last 30 rows of the table. The query it runs is:SELECT * FROM `forums_posts` LIMIT 2573250 , 30Should it really be taking that long?Another slow query that I am seeing being ran by the upgrade background processes is:/*IteratorIterator::rewind:92*/ SELECT * FROM `forums_posts` WHERE pid > 7478563 ORDER BY post_date ASC LIMIT 0,50 Which is taking a similarly long amount of time, several minutes just to select and order 50 posts.Is it normal or are my settings broke?(P.S. Despite the high post IDs, there are only 2 million posts in the database. The high post IDs were because of several failed imports from vbulletin years ago.) Link to comment Share on other sites More sharing options...
RevengeFNF Posted July 4, 2015 Share Posted July 4, 2015 What Mysql version are you using and what Storage Engine? Link to comment Share on other sites More sharing options...
Vroom Posted July 5, 2015 Author Share Posted July 5, 2015 I am using MySQL 5.6.23, and innodb storage engine. Link to comment Share on other sites More sharing options...
AutoItScript Posted July 5, 2015 Share Posted July 5, 2015 Can you post your my.cnf to see if it's something obvious? Link to comment Share on other sites More sharing options...
Vroom Posted July 6, 2015 Author Share Posted July 6, 2015 [mysqld]datadir="/var/lib/mysql"tmpdir="/home/mysqltmp"long_query_time = 100slow_query_log=1slow_query_log_file="/var/log/mysql/mysql-slow.log"local-infile=0bind-address=127.0.0.1back_log=100#skip-innodbmax_connections=1500key_buffer_size=1Gmyisam_sort_buffer_size=2Gmyisam_max_sort_file_size=2048Mjoin_buffer_size=256Kread_buffer_size=256Ksort_buffer_size=256Ktable_definition_cache=8000table_open_cache=8000thread_cache_size=384wait_timeout=300interactive_timeout=60connect_timeout=60tmp_table_size=256Mmax_heap_table_size=256Mmax_allowed_packet=268435456max_seeks_for_key=1000group_concat_max_len=1024max_length_for_sort_data=1024net_buffer_length=16384max_connect_errors=100000concurrent_insert=2read_rnd_buffer_size=512Kbulk_insert_buffer_size=512Mquery_cache_limit=1536Kquery_cache_size=128Mquery_cache_type=1query_prealloc_size=262144query_alloc_block_size=65536range_alloc_block_size=4096transaction_alloc_block_size=8192transaction_prealloc_size=4096default-storage-engine=MyISAMmax_write_lock_count=8innodb_open_files=500innodb_data_file_path=ibdata1:10M:autoextendinnodb_buffer_pool_size=128Minnodb_additional_mem_pool_size=32Minnodb_log_files_in_group=2innodb_log_file_size=400Minnodb_log_buffer_size=8Minnodb_flush_log_at_trx_commit=2innodb_thread_concurrency=8open_files_limit=10000innodb_file_per_table=1[mysqld_safe]nice=-5open-files-limit=8192[mysqldump]quickmax_allowed_packet=64M[myisamchk]key_buffer_size=3000Msort_buffer_size=64Mread_buffer_size=16Mwrite_buffer_size=8Mread_buffer_size=1Mwrite_buffer_size=1M[mysqlhotcopy]interactive-timeout Link to comment Share on other sites More sharing options...
James Ford Posted July 6, 2015 Share Posted July 6, 2015 I suspect many of those settings are redundant, or even harmful.If I were you if save a backup of that file, then replace the live copy with the MySQL default. It only contains around 10 to 20 settings as opposed to your 60.I think one important setting to then add back in is innodb_buffer_pool_size which needs to be large enough to contain your entire database. With 2m posts that's probably around 5G. You currently have it set to only 128M. Link to comment Share on other sites More sharing options...
AutoItScript Posted July 6, 2015 Share Posted July 6, 2015 Yeah there's a lot of settings that as James said you generally never need to tweak. I checked a few of them and they were actually set to defaults so maybe go through those and remove them to clear things up. But the main things that jumped at me were:[mysqld] long_query_time = 100 max_connections=1500 key_buffer_size=1G innodb_buffer_pool_size=128M Your buffer sizes are set for a large MyISAM operation rather than InnoDB. You've got a big database, I'm guessing it's around 2-3GB? You ideally want innodb_buffer_pool_size set to a similar size. You've got lots of RAM so try and set it to 2G to start. That change alone should make a big difference, then you can look at the rest. key_buffer_size is safe to leave large even if you are using InnoDB because it only allocates memory as required. But if you are sure you have no MyISAM tables then that can be reduced.long_query_time is set to a massive value. 10 seconds is more normal. At the moment your slow query log might have some entries in because as you say some simple queries are taking minutes, but under normal circumstances you'd not have 2 minute queries so the log would always be empty.max_connections_1500? No, You'll run out of memory before waaaaaaay before that happens. I have mine set to 100 and I rarely use more than 20 for my board (sig). Link to comment Share on other sites More sharing options...
RevengeFNF Posted July 6, 2015 Share Posted July 6, 2015 Ya, your my.cnf seems a bit misconfigured. Can you post here the output of mysqltuner? Link to comment Share on other sites More sharing options...
Vroom Posted July 6, 2015 Author Share Posted July 6, 2015 Thanks for the replies. I was previously using MyISAM tables, and only converted to innodb last week. One database is 15gb and another is 40GB (both IPB). >> MySQLTuner 1.4.4 - Major Hayden <major@mhtx.net> >> 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.23-log[OK] Operating on 64-bit architecture-------- Storage Engine Statistics -------------------------------------------[--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MRG_MYISAM[--] Data in MyISAM tables: 74G (Tables: 2427)[--] Data in InnoDB tables: 66G (Tables: 138)[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 52)[--] Data in MEMORY tables: 252K (Tables: 2)[!!] Total fragmented tables: 294-------- Security Recommendations -------------------------------------------[OK] There is no anonymous account in all database users[OK] All database users have passwords assigned[!!] There is not basic password file list !-------- Performance Metrics -------------------------------------------------[--] Up for: 2d 12h 29m 17s (48M q [221.543 qps], 1M conn, TX: 345B, RX: 25B)[--] Reads / Writes: 67% / 33%[--] Total buffers: 1.5G global + 1.5M per thread (1500 max threads)[OK] Maximum possible memory usage: 3.7G (24% of installed RAM)[OK] Slow queries: 0% (66/48M)[OK] Highest usage of available connections: 6% (94/1500)[OK] Key buffer size / total MyISAM indexes: 1.0G/20.1G[OK] Key buffer hit rate: 99.9% (942M cached / 540K reads)[OK] Query cache efficiency: 62.7% (23M cached / 37M selects)[!!] Query cache prunes per day: 3268638[OK] Sorts requiring temporary tables: 1% (14K temp sorts / 904K sorts)[!!] Joins performed without indexes: 11276[!!] Temporary tables created on disk: 33% (157K on disk / 464K total)[OK] Thread cache hit rate: 99% (94 created / 1M connections)[OK] Table cache hit rate: 86% (3K open / 4K opened)[OK] Open file limit used: 34% (5K/17K)[OK] Table locks acquired immediately: 99% (25M immediate / 25M locks)-------- InnoDB Metrics -----------------------------------------------------[--] InnoDB is enabled.[--] InnoDB BufferPool Size :128.0M[--] InnoDB BufferPool Inst :8[!!] InnoDB buffer pool / data size: 128.0M/66.7G[!!] InnoDB buffer pool <= 1G and innodb_buffer_pool_instances(=1).[OK] InnoDB log waits: 0-------- Recommendations -----------------------------------------------------General recommendations: Run OPTIMIZE TABLE to defragment tables for better performance Adjust your join queries to always utilize indexes Temporary table size is already large - reduce result set size Reduce your SELECT DISTINCT queries without LIMIT clausesVariables to adjust: long_query_time (<= 10) query_cache_size (> 128M) join_buffer_size (> 256.0K, or always use indexes with joins) innodb_buffer_pool_size (>= 66G) innodb_buffer_pool_instances (=1) Link to comment Share on other sites More sharing options...
genrobgen Posted July 6, 2015 Share Posted July 6, 2015 66 GB in your Innodb tables?!!I don't think it should be that high if you have 2m posts. I have 2.5 million posts in my forum and the Innodb tables are only around 7 GB.Which version of IPS are you on? There was a problem with, I think, 4.0.8 where by the cache table wasn't managed properly and it grew and grew and grew. Link to comment Share on other sites More sharing options...
media Posted July 6, 2015 Share Posted July 6, 2015 I agree with @genrobgen I have 1.1 million posts and my database is 4.3 GB (I have gallery, blog too)..One thing though, I optimize my database every other day when board has lowest number of visitor... That helps to keep database clean... Link to comment Share on other sites More sharing options...
RevengeFNF Posted July 6, 2015 Share Posted July 6, 2015 1,5 Million Posts and my Database is 2.4GB.66Gb is overkill. Something is very wrong there. Link to comment Share on other sites More sharing options...
Vroom Posted July 7, 2015 Author Share Posted July 7, 2015 I am running 4.08. I will upgrade in a couple days and see if it changes anything. I am still waiting for background processes to complete after upgrading to 4.0. Link to comment Share on other sites More sharing options...
RevengeFNF Posted July 7, 2015 Share Posted July 7, 2015 I am running 4.08. I will upgrade in a couple days and see if it changes anything. I am still waiting for background processes to complete after upgrading to 4.0. Upgrade aasap because that version have a bug that is making your db going bigger and bigger. Link to comment Share on other sites More sharing options...
Vroom Posted July 8, 2015 Author Share Posted July 8, 2015 Just updated. But now is there anything I need to do to remove the extra growth or will it happen automatically? Link to comment Share on other sites More sharing options...
Vroom Posted July 8, 2015 Author Share Posted July 8, 2015 Here is some informaton from phpmyadmin:forums_posts 12.4 GBData 12.1 GiBIndex 383.4 MiBTotal 12.4 GiBWhen I browse the rows it shows numberof rows as ~2573267 total: Showing rows 0 - 29 (~2573267 total)In database structure page it shows a different number (~1,540,578).And for the topics table:forums_topics 455MBSpace usageData 207.7 MiBIndex 248.1 MiBTotal 455.9 MiBIs that post index too small?In comparison my 3.4 database shows this:posts table:Data 7.4 GiBIndex 3 GiBOverhead 102.4 MiBEffective 10.4 GiBTotal 10.5 GiBAnd topics table:Data 160.1 MiBIndex 210.1 MiBOverhead 1.5 MiBEffective 368.8 MiBTotal 370.3 MiB Link to comment Share on other sites More sharing options...
AutoItScript Posted July 8, 2015 Share Posted July 8, 2015 Nah, they moved the fulltext index out of forums_posts into a separate table in v4 so it will be way smaller. The actual posts data though should be similar, unless you weren't using utf8 on v3 and had to convert? Link to comment Share on other sites More sharing options...
Vroom Posted July 8, 2015 Author Share Posted July 8, 2015 Yes I converted to UTF8 in 4.0 Link to comment Share on other sites More sharing options...
B_U_R_I Posted August 6, 2015 Share Posted August 6, 2015 I'm going to borrow this thread. I tried doing a quick search, but I haven't found anything usefull. If there is a similar thread that answers my questions, I'll appreciate if you can post a link to it.I did an upgrade to 4.0.11 and my site is loading much slower than before. It loads a lot slower than this board also. What things should I check to speed up the site? What are the thing I can check in ACP settings or my cPanel? What are the thing my hosting provider should check? Thanks in advance. Link to comment Share on other sites More sharing options...
sasiko Posted August 8, 2015 Share Posted August 8, 2015 https://gtmetrix.com webpagetest.orgboth are exellent site to analyze ur site pageload speed. having google pagespeed application helped tons too I'm going to borrow this thread. I tried doing a quick search, but I haven't found anything usefull. If there is a similar thread that answers my questions, I'll appreciate if you can post a link to it.I did an upgrade to 4.0.11 and my site is loading much slower than before. It loads a lot slower than this board also. What things should I check to speed up the site? What are the thing I can check in ACP settings or my cPanel? What are the thing my hosting provider should check? Thanks in advance. Link to comment Share on other sites More sharing options...
B_U_R_I Posted August 18, 2015 Share Posted August 18, 2015 Thanks @maidosAs I've been dreading, the load times are measureably horrible and I have little to no idea how to improve... I've been reading some guides online, but they are pretty basic and offer only general information. I don't even know where to begin ...Any advice on what to look for or change? Link to comment Share on other sites More sharing options...
RevengeFNF Posted August 18, 2015 Share Posted August 18, 2015 Do you have root access to your server? If yes, post here mysqltuner output. Link to comment Share on other sites More sharing options...
B_U_R_I Posted August 18, 2015 Share Posted August 18, 2015 Sadly no. I use a hosting provider. I only have cPanel and FTP access... Link to comment Share on other sites More sharing options...
RevengeFNF Posted August 18, 2015 Share Posted August 18, 2015 You are pretty much limited then. Also shared hosting is not ideal for performance. Link to comment Share on other sites More sharing options...
B_U_R_I Posted August 18, 2015 Share Posted August 18, 2015 I understand. These are the compromises you make when you're on a budget ... Link to comment Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.