I have a small gaming website that has about 20 regular users everday at the forums (invision board 1.3)... less then 30k posts and the posts table is about 7mb. Problem is that threads load extremelyyyy slow on the site. I have a 2.2 p4 with 1 gig ram, on RHE.
Every other part of the site / forum loads quick, except forum threads. The thread doesnt have to have numerous posts or pics in it - it doesnt matter, it loads really slow. I have analyzed and optimized the db tables and it has had no effect.
What are some things I can put in "my.cnf" to optimize MySQL? Right now I have 3 copies on my server... one holds the root password, the other two dont really hold any info at all. Only this:
[mysqld]
set-variable = max_connections=500
safe-show-database
Please, HELP!
Full Version: MySQL issues with forum software...
Backup your my.cnf and try this. Don't forget to restart mysqld. I worked fine for me on a similar system with 100's of simultaneous users on phpBB
Good luck!
Good luck!
CODE
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
skip-locking
skip-innodb
query_cache_limit=1M
query_cache_size=32M
query_cache_type=1
max_connections=500
interactive_timeout=100
wait_timeout=100
connect_timeout=10
thread_cache_size=128
key_buffer=16M
join_buffer=1M
max_allowed_packet=16M
table_cache=1024
record_buffer=1M
sort_buffer_size=2M
read_buffer_size=2M
max_connect_errors=10
# Try number of CPU's*2 for thread_concurrency
thread_concurrency=2
myisam_sort_buffer_size=64M
#log-bin
server-id=1
[mysql.server]
user=mysql
basedir=/var/lib
[safe_mysqld]
err-log=/var/log/mysqld.log
pid-file=/var/lib/mysql/mysql.pid
open_files_limit=8192
[mysqldump]
quick
max_allowed_packet=16M
[mysql]
no-auto-rehash
#safe-updates
[isamchk]
key_buffer=64M
sort_buffer=64M
read_buffer=16M
write_buffer=16M
[myisamchk]
key_buffer=64M
sort_buffer=64M
read_buffer=16M
write_buffer=16M
[mysqlhotcopy]
interactive-timeout
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
skip-locking
skip-innodb
query_cache_limit=1M
query_cache_size=32M
query_cache_type=1
max_connections=500
interactive_timeout=100
wait_timeout=100
connect_timeout=10
thread_cache_size=128
key_buffer=16M
join_buffer=1M
max_allowed_packet=16M
table_cache=1024
record_buffer=1M
sort_buffer_size=2M
read_buffer_size=2M
max_connect_errors=10
# Try number of CPU's*2 for thread_concurrency
thread_concurrency=2
myisam_sort_buffer_size=64M
#log-bin
server-id=1
[mysql.server]
user=mysql
basedir=/var/lib
[safe_mysqld]
err-log=/var/log/mysqld.log
pid-file=/var/lib/mysql/mysql.pid
open_files_limit=8192
[mysqldump]
quick
max_allowed_packet=16M
[mysql]
no-auto-rehash
#safe-updates
[isamchk]
key_buffer=64M
sort_buffer=64M
read_buffer=16M
write_buffer=16M
[myisamchk]
key_buffer=64M
sort_buffer=64M
read_buffer=16M
write_buffer=16M
[mysqlhotcopy]
interactive-timeout
thanxxxxxxxxxxxxxx!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
oh my gawddd my forums are blazinggg now.
Damn i wish i asked this before i got the memory upgrade, lol.
thanks again!!!!
oh my gawddd my forums are blazinggg now.
Damn i wish i asked this before i got the memory upgrade, lol.
thanks again!!!!
My queries just broke the sound barrier based on your my.cnf! :shock: I'm going to nominate you for president.
Hey, is this for Linux OS's? I can't seem to find my.cnf on my windows installation only my.ini but it doesn't have these parameters. I just wanted to compare settings not becasue I"m having problems with my set up. Queries are pretty quick on my win2k3 box but I wanted to see if it could be further optimized.
My.cnf is the configuration file for UNIX systems. My.ini is what you'll need for Windows MySQL installations. You should be able to put 95% of what you see (namely everything except paths) in a my.cnf file and have it work in my.ini.
wow.. fantastic... worked a treat for our travel booking engine. Halved the processing time! good post!
Just wanted to post from my experiment with these settings on a win2k mySQL install and phpbb.
I found that the db performed much slower and pegged the CPU much of the time forum posts were viewed.
I put my old my.ini back in place and my CPU was happier and the forum ran better.
Maybe these are best used on Linux mysql installs.
I found that the db performed much slower and pegged the CPU much of the time forum posts were viewed.
I put my old my.ini back in place and my CPU was happier and the forum ran better.
Maybe these are best used on Linux mysql installs.
I use this my.ini conf on my dedicated database/mail/dns/Helm server. I keep the database files on a separate drive from the system/program drive for extra speed. This conf also makes heavy use of RAM so as to limit the need for accessing the disk.
System Specs:
Dual 2.8 P4 Xeons
2 GB RAM
2 x 120 GB HDD
Windows 2003 Standard
MySQL 4.1.12-nt
You can remove old-passwords if you're using only PHP 5. log-warnings also tends to fill up the event log with lots of non-issue stuff. I just like to know what's going on, so you can disable that if you prefer.
I don't really have any busy phpBB forums on any of my servers, so I couldn't tell you how well it'd perform under this configuration. Some of the busier boards I host are http://www.lordkane.co.uk/forums (SMF 1.01) http://www.emuboards.net (IPB 2.0) and http://forums.flaretech.net (IPB 1.3). If you take a bit of time to navigate around, you'll notice that things tend to load instantaneously.
System Specs:
Dual 2.8 P4 Xeons
2 GB RAM
2 x 120 GB HDD
Windows 2003 Standard
MySQL 4.1.12-nt
QUOTE
[mysqld]
basedir="C:/Program Files/MySQL/MySQL Server 4.1/"
datadir="D:/MySQL Datafiles/"
default-character-set=latin1
default-storage-engine=INNODB
port=3306
max_connections=800
wait_timeout=3600
connect_timeout=10
max_allowed_packet=16M
query_cache_limit=4M
query_cache_size=128M
query_cache_type=1
table_cache=1520
tmp_table_size=30M
thread_cache_size=128
myisam_max_sort_file_size=100G
myisam_max_extra_sort_file_size=100G
myisam_sort_buffer_size=64M
join_buffer=1M
record_buffer=1M
key_buffer_size=256M
read_buffer_size=64K
read_rnd_buffer_size=256K
sort_buffer_size=2M
innodb_data_home_dir="D:/MySQL Datafiles/"
innodb_additional_mem_pool_size=6M
innodb_flush_log_at_trx_commit=1
innodb_log_buffer_size=3M
innodb_buffer_pool_size=249M
innodb_log_file_size=50M
innodb_thread_concurrency=10
old-passwords
safe-user-create
skip-name-resolve
log-warnings
low-priority-updates
basedir="C:/Program Files/MySQL/MySQL Server 4.1/"
datadir="D:/MySQL Datafiles/"
default-character-set=latin1
default-storage-engine=INNODB
port=3306
max_connections=800
wait_timeout=3600
connect_timeout=10
max_allowed_packet=16M
query_cache_limit=4M
query_cache_size=128M
query_cache_type=1
table_cache=1520
tmp_table_size=30M
thread_cache_size=128
myisam_max_sort_file_size=100G
myisam_max_extra_sort_file_size=100G
myisam_sort_buffer_size=64M
join_buffer=1M
record_buffer=1M
key_buffer_size=256M
read_buffer_size=64K
read_rnd_buffer_size=256K
sort_buffer_size=2M
innodb_data_home_dir="D:/MySQL Datafiles/"
innodb_additional_mem_pool_size=6M
innodb_flush_log_at_trx_commit=1
innodb_log_buffer_size=3M
innodb_buffer_pool_size=249M
innodb_log_file_size=50M
innodb_thread_concurrency=10
old-passwords
safe-user-create
skip-name-resolve
log-warnings
low-priority-updates
You can remove old-passwords if you're using only PHP 5. log-warnings also tends to fill up the event log with lots of non-issue stuff. I just like to know what's going on, so you can disable that if you prefer.
I don't really have any busy phpBB forums on any of my servers, so I couldn't tell you how well it'd perform under this configuration. Some of the busier boards I host are http://www.lordkane.co.uk/forums (SMF 1.01) http://www.emuboards.net (IPB 2.0) and http://forums.flaretech.net (IPB 1.3). If you take a bit of time to navigate around, you'll notice that things tend to load instantaneously.
Hi, Electron33.
I just used your config file, and mysql did not work. What did i wrong.
Of course now i have uploaded the original again back wich looks like this:
[mysqld]
safe-show-database
innodb_data_file_path=ibdata1:10M:autoextend
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
set-variable=max_connections=150
[mysql.server]
user=mysql
basedir=/var/lib
[safe_mysqld]
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
But your did not worked.
I just used your config file, and mysql did not work. What did i wrong.
Of course now i have uploaded the original again back wich looks like this:
[mysqld]
safe-show-database
innodb_data_file_path=ibdata1:10M:autoextend
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
set-variable=max_connections=150
[mysql.server]
user=mysql
basedir=/var/lib
[safe_mysqld]
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
But your did not worked.
I've utilized some of these config settings too and have noticed a little gain in speed.
Right-on!
Right-on!
I tried several times to use this Config file. I have Red Hat Es, with Plesk. Could someone explaing me why it doesnt work on my server?
I have the same paths on my current config so why the issue?
This is how my my.cnf looks now and works.
[mysqld]
safe-show-database
innodb_data_file_path=ibdata1:10M:autoextend
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
set-variable=max_connections=300
[mysql.server]
user=mysql
basedir=/var/lib
[safe_mysqld]
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
I have the same paths on my current config so why the issue?
This is how my my.cnf looks now and works.
[mysqld]
safe-show-database
innodb_data_file_path=ibdata1:10M:autoextend
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
set-variable=max_connections=300
[mysql.server]
user=mysql
basedir=/var/lib
[safe_mysqld]
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
You probobl have MySQL 3.23.x. Some of the options in the above configurations are only compatable with MySQL 4.0 and up so your MySQL would die from unknown configuration options.
You are right, i have 3.23.58
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.