Should mysql selects be taking this long or are my settings bad

[Vroom]

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 , 30

Should 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.)

[RevengeFNF]

What Mysql  version are you using and what Storage Engine?

[Vroom]

I am using MySQL 5.6.23, and innodb storage engine.

[AutoItScript]

Can you post your my.cnf to see if it's something obvious?

[Vroom]

[mysqld]
datadir="/var/lib/mysql"
tmpdir="/home/mysqltmp"
long_query_time         = 100
slow_query_log=1
slow_query_log_file="/var/log/mysql/mysql-slow.log"
local-infile=0
bind-address=127.0.0.1
back_log=100
#skip-innodb
max_connections=1500
key_buffer_size=1G
myisam_sort_buffer_size=2G
myisam_max_sort_file_size=2048M
join_buffer_size=256K
read_buffer_size=256K
sort_buffer_size=256K
table_definition_cache=8000
table_open_cache=8000
thread_cache_size=384
wait_timeout=300
interactive_timeout=60
connect_timeout=60
tmp_table_size=256M
max_heap_table_size=256M
max_allowed_packet=268435456
max_seeks_for_key=1000
group_concat_max_len=1024
max_length_for_sort_data=1024
net_buffer_length=16384
max_connect_errors=100000
concurrent_insert=2
read_rnd_buffer_size=512K
bulk_insert_buffer_size=512M
query_cache_limit=1536K
query_cache_size=128M
query_cache_type=1
query_prealloc_size=262144
query_alloc_block_size=65536
range_alloc_block_size=4096
transaction_alloc_block_size=8192
transaction_prealloc_size=4096
default-storage-engine=MyISAM
max_write_lock_count=8
innodb_open_files=500
innodb_data_file_path=ibdata1:10M:autoextend
innodb_buffer_pool_size=128M
innodb_additional_mem_pool_size=32M
innodb_log_files_in_group=2
innodb_log_file_size=400M
innodb_log_buffer_size=8M
innodb_flush_log_at_trx_commit=2
innodb_thread_concurrency=8
open_files_limit=10000
innodb_file_per_table=1
[mysqld_safe]
nice=-5
open-files-limit=8192

[mysqldump]
quick
max_allowed_packet=64M
[myisamchk]
key_buffer_size=3000M
sort_buffer_size=64M
read_buffer_size=16M
write_buffer_size=8M
read_buffer_size=1M
write_buffer_size=1M
[mysqlhotcopy]
interactive-timeout

[James Ford]

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.

[AutoItScript]

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).

 

[RevengeFNF]

Ya, your my.cnf seems a bit misconfigured. Can you post here the output of mysqltuner?

[Vroom]

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 clauses
Variables 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)

[genrobgen]

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.

[media]

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...

[RevengeFNF]

1,5 Million Posts and my Database is 2.4GB.

66Gb is overkill. Something is very wrong there.

[Vroom]

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. 

[RevengeFNF]

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. 

[Vroom]

Just updated. But now is there anything I need to do to remove the extra growth or will it happen automatically?

[Vroom]

Here is some informaton from phpmyadmin:

forums_posts 12.4 GB

Data    12.1    GiB
Index    383.4    MiB
Total    12.4    GiB

When 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 455MB

Space usage
Data    207.7    MiB
Index    248.1    MiB
Total    455.9    MiB

Is that post index too small?

In comparison my 3.4 database shows this:

posts table:

Data    7.4    GiB
Index    3    GiB
Overhead    102.4    MiB
Effective    10.4    GiB
Total    10.5    GiB

And topics table:

Data    160.1    MiB
Index    210.1    MiB
Overhead    1.5    MiB
Effective    368.8    MiB
Total    370.3    MiB

[AutoItScript]

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?

[Vroom]

Yes I converted to UTF8 in 4.0

[B_U_R_I]

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.

[sasiko]

https://gtmetrix.com  webpagetest.org

both 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.

[B_U_R_I]

Thanks @maidos

As 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?

[RevengeFNF]

Do you have root access to your server? If yes, post here mysqltuner output.

[B_U_R_I]

Sadly no. I use a hosting provider. I only have cPanel and FTP access...

[RevengeFNF]

You are pretty much limited then. Also shared hosting is not ideal for performance.

[B_U_R_I]

I understand. These are the compromises you make when you're on a budget ...