MyBB Community Forums

Bad news, but ok.
Thanks!

Your current settings:

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
max_connections = 600 --> if you're using Apache, lower it!! 100-200
key_buffer = 16M --> if you have limited RAM, comment this

--------- this setting below need attention.
use analysis tools luke mysqltuner or else
and monitor your forum for few days

myisam_sort_buffer_size = 32M
join_buffer_size = 1M
read_buffer_size = 1M
sort_buffer_size = 2M
table_cache = 1024
thread_cache_size = 286
max_allowed_packet = 16M
---------------

interactive_timeout = 25 --> enough
wait_timeout = 1000 --> this is bad, I suggest 20-30 to prevent many holding queries
connect_timeout = 10
max_connect_errors = 10

Both of 4 settings above, did the trick to drop slow query.
Also solve slow network, you may have from your host or user.


-- this should be set carefully
query_cache_limit = 1M
query_cache_size = 16M
query_cache_type = 1
--

tmp_table_size = 16M
skip-innodb
skip-bdb
user=mysql
local-infile=0
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1



Last time I've use VPS, RAM only 1.5GB which is for 1.5 million posts.
If posts 900MB, I think 1GB RAM is enough, depend on your VPS Software (like Cpanel, Directadmin or LxAdmin)..

Here example my setting, but feel free to modified.
Just example. Working good on my old VPS and now I switch Dedicated Dual Core (not Quad) server for few years..
There is now time out, even with more than sixty active plugins.

[mysqld]
query_cache_type=1 ## 0 disable, 1 enable all, 2 on demand use sql_cache
query_cache_size=512M ## 32MB for every 1GB of RAM
thread_cache_size=128
table_cache=4k

max_heap_table_size = 64M
tmp_table_size = 64M

log_slow_queries=/root/mysqld.slow.log
long_query_time=2

max_user_connections=150
max_connections=200

interactive_timeout=10
wait_timeout=20
connect_timeout=20
thread_concurrency=4 ## Number of CPUs x 2

You can search Labrocca's posts from this forum for other trick/tips.

Thank you so much!
I've already lowered the max_connections setting, but i try your other suggestions too.

Thanks again!

---

Here's my current my.cnf:

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
max_connections = 200
key_buffer = 16M
myisam_sort_buffer_size = 32M
join_buffer_size = 1M
read_buffer_size = 1M
sort_buffer_size = 2M
table_cache = 1024
thread_cache_size = 286
interactive_timeout = 25
wait_timeout = 30
connect_timeout = 10
max_allowed_packet = 16M
max_connect_errors = 10
query_cache_limit = 1M
query_cache_size = 16M
query_cache_type = 1
max_heap_table_size = 64M
tmp_table_size = 64M
skip-innodb
skip-bdb
user=mysql
local-infile=0
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
open_files_limit = 8192

[isamchk]
key_buffer = 32M
sort_buffer = 32M
read_buffer = 16M
write_buffer = 16M

[myisamchk]
key_buffer = 32M
sort_buffer = 32M
read_buffer = 16M
write_buffer = 16M
[mysqlhotcopy]
interactive-timeout

skip-networking

#log-slow-queries = /var/log/mysql-slow.log

Mysqltuner report:

[--] Up for: 2m 6s (2K q [22.746 qps], 139 conn, TX: 30M, RX: 585K)
[--] Reads / Writes: 57% / 43%
[--] Total buffers: 106.0M global + 4.4M per thread (200 max threads)
[OK] Maximum possible memory usage: 993.5M (24% of installed RAM)
[OK] Slow queries: 0% (0/2K)
[OK] Highest usage of available connections: 3% (6/200)
[!!] Key buffer size / total MyISAM indexes: 16.0M/287.6M
[!!] Key buffer hit rate: 81.8% (136K cached / 24K reads)
[OK] Query cache efficiency: 42.8% (654 cached / 1K selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (1 temp sorts / 142 sorts)
[!!] Joins performed without indexes: 2
[OK] Temporary tables created on disk: 21% (12 on disk / 56 total)
[OK] Thread cache hit rate: 95% (6 created / 139 connections)
[OK] Table cache hit rate: 24% (48 open / 193 opened)
[OK] Open file limit used: 3% (89/2K)
[OK] Table locks acquired immediately: 99% (1K immediate / 1K locks)

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    MySQL started within last 24 hours - recommendations may be inaccurate
    Enable the slow query log to troubleshoot bad queries
    Adjust your join queries to always utilize indexes
Variables to adjust:
    key_buffer_size (> 287.6M)
    join_buffer_size (> 1.0M, or always use indexes with joins)

If I was you I'd lower the max connections to 100. Even if unused resources can be placed aside to each one.

What version of MySQL are you using?

mysql: 5.0.92

Ok, i set the max connections to 100. Thx!

Try upgrading to 5.5, I know that there were multiple performance improvements in that version.

I'm using a webhost control panel, so i think i can't upgrade it without any problem.

Don't you have a VPS?

VPS with kloxo panel.

I'm thinking about to use innodb with some tables. Is that a good idea?

(2011-06-03, 06:42 AM)Wiz01 Wrote: [ -> ]VPS with kloxo panel.
I'm thinking about to use innodb with some tables. Is that a good idea?

yeah, old VPS (Kloxo) :lol:

No, you don't need to change innodb unless you have too.
Once you convert, there is no way back to mysql (easily).
MyBB 1.6 far far better than 1.4.
Your traffic still lower than I was.

Before tuning with Mysqltuner, please wait for few days.
As far as I know, the original default MySQL setting from Kloxo is good.
I was like kloxo (with HyperVM), easy to remotely reformat your VPS few minutes..
Since, there is an security issue. I leave them.

The good reported value from mysqltuner is 80% - 99%
Normally, key_buffer_size is equal (near) with your database size (eg.900MB).
If you have limited memory, leave it at default value.

VPS still have a risk for the data. If you worry about it, use memcache or xcache to handle your temporary (buffer) query. If you have money, increase your RAM. Size is double from your Database size..

Goodluck.
It's just my opinion.

See my old topic 3 years ago.
You may find some clue..
http://community.mybb.com/thread-38757.html

And you can learn from Labrocca
http://community.mybb.com/thread-63048.html

This is comparison form Data Accelerator
http://community.mybb.com/thread-34440.html