php forum
php mysql forum
php mysql smarty
 
Topic Options
#93296 - 06/26/04 03:45 PM MySQL Optimization
cdavis Offline
Lurker

Registered: 06/26/04
Posts: 6

   MySQL Optimization to Del.icio.us Add to del.icio.us
  Digg MySQL Optimization Digg it
I need someone to optimize MySQL on my Apple Xserve.

Here are the specs:
1. Mac OS X Server 10.2.8
2. MySQL 4.0.18 - 2 Databases (24MB & 197MB)
3. Apache 2.0.49 - 55 Virtual Hosts

Occasionally, the larger database become inaccessible - pages in the forums cannot be accessed. During this time, there are usually quite a few "locked" MySQL processes relating to this database. As soon as MySQL is restarted, the forums function properly for several more hours.

I have optimized a couple of the largest tables in this database several times, but that doesn't seem to have an effect.

I can provide 24 hour system-wide access via SSH in order to optimize MySQL.

Top
#93297 - 07/07/04 05:16 PM Re: MySQL Optimization
1QuickSI Offline
Coder

Registered: 06/10/01
Posts: 705
Loc: CT
Backup your my.cnf and try this...

Code:
[mysqld]
datadir=/usr/local/mysql
socket=/var/lib/mysql/mysql.sock
set-variable = max_connections=450
set-variable = key_buffer=16M
set-variable = myisam_sort_buffer_size=64M
set-variable = join_buffer=1M
set-variable = record_buffer=1M
set-variable = sort_buffer=2M
set-variable = table_cache=1024
set-variable = thread_cache_size=256
set-variable = wait_timeout=9600
set-variable = connect_timeout=10
set-variable = max_allowed_packet=16M
set-variable = max_connect_errors=10
 
[mysql.server]
user=mysql
basedir=/usr/local/mysql
 
[safe_mysqld]
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
open_files_limit=8192
 
[mysqldump]
quick
set-variable = max_allowed_packet=16M
 
[myisamchk]
set-variable = key_buffer=64M
set-variable = sort_buffer=64M
set-variable = read_buffer=16M
set-variable = write_buffer=16M
_________________________
...Steven
Hostboard.com
Running UBB v6.1.0.4
Doing what NO other UBB in the world is doing.
The first & only production UBB powered by MySQL.

Top


Moderator:  Deb 
Top Posters Last 30 Days
AllenAyres 12
Gizmo 10
S7ARBVCK 2
blaaskaak 2
Cambridge 1
Murphdog 1
MattUK 1
Who's Online
0 Registered (), 25 Guests and 5 Spiders online.
Key: Admin, Global Mod, Mod
Shout Box

Latest Posts
[7.3.1] add search to showmembers page
by blaaskaak
Today at 04:50 AM
BeyondCompare v3.00
by blaaskaak
09/05/08 02:46 PM
Noob - need help, or a reality check!
by Gizmo
09/04/08 03:21 AM
Here I am! Rock me like a Hurricane!
by AllenAyres
09/02/08 03:05 PM
[7.x] Generic Page Outside of forum directory
by Gizmo
08/30/08 05:43 PM
Team UBBDev Rides Again!
by Gizmo
08/28/08 11:45 PM
Multiple Identity Detector
by MattUK
08/28/08 04:10 PM
New Mods
[7.3.1] add search to showmembers page
by blaaskaak
Today at 04:50 AM
Installing FlashChat with 7.3
by Paug
08/23/08 12:14 AM
[7.3.x] ubb.links
by AllenAyres
06/20/08 11:50 PM
[7.2.1] - Naked shoutbox
by sirdude
08/17/07 10:36 PM
[7.x] Generic Page Outside of forum directory
by
01/14/07 10:58 PM
Newest Members
veedubb8, twentyseven, Claus1, welcomeback1, Paug
13327 Registered Users

 

 

 
fusionbb message board php hacks