Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations derfloh on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

optimizin mysql 3.23.55

Status
Not open for further replies.

waren23

Programmer
Joined
Jan 25, 2005
Messages
1
Location
HU
hello to all!

i've got some problem with my webserver's mysql database. we operate 2 portals with PHP-MYSQL content management systems on the apache server with mysql 3.23.55. i know it should be upgraded, but we have no time for that at the moment. thus, we should optimize the current one and i need help with that.

so, we've got two portals with two main databases (lets call them A and B) for each portal. we are

publishing news and operating forums.
------- A
this is a website with news and forums, this causes some problems
- 130 tables, but most of them is used rarely except from news,forum and users
- news table with 10.000 rows, 40 new records inserted each day
- forum table with 36.000 rows, 60 records inserted each day
- users table accounts 8000 rows. mysql selects from this table as a joint query with forum table

- over 5.000 users per day read the news mostly between morning time 7.00-11.00, generating 15.000 page

impressions daily

------- problem
in the morning sometimes we publish an article that is read by 2-3.000 users immediately in just an hour or so, around 500 article-reading in the first 10 minutes. and then, the mysql is exhausted.
checking the process, I see 'Copying to tmp table', 'writing to net' and everything slows down

thats why i need help with mysql settings to optimize for that kind of usage. if anyone could help me, i really appreciate it or need some more statistics, i can post them too.

thanks in advance,
waren


------- hardware
vendor_id : GenuineIntel
cpu family : 15
model : 2
model name : Intel(R) Pentium(R) 4 CPU 2.40GHz
stepping : 9
cpu MHz : 2394.213
cache size : 512 KB

total: used: free: shared: buffers: cached:
Mem: 1049579520 991408128 58171392 0 9519104 395718656
Swap: 2582654976 289177600 2293477376
MemTotal: 1024980 kB
MemFree: 56808 kB
MemShared: 0 kB
Buffers: 9296 kB
Cached: 368412 kB
SwapCached: 18032 kB
Active: 499760 kB
Inactive: 401012 kB
HighTotal: 122044 kB
HighFree: 1908 kB
LowTotal: 902936 kB
LowFree: 54900 kB
SwapTotal: 2522124 kB
SwapFree: 2239724 kB


free disk space
mysql_log, mysql_tables 633 Mbyte
mysql_tmp 18 Fbyte

------- /etc/my.cnf
[client]
#password = your_password
port = 3306
socket = /var/lib/mysql/mysql.sock

# The MySQL server
[mysqld]
port = 3306
socket = /var/lib/mysql/mysql.sock
skip-locking

set-variable = max_connections=350
set-variable = key_buffer=16M
set-variable = myisam_sort_buffer_size=64M
set-variable = join_buffer=1M
set-variable = record_buffer=2M
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=15
set-variable = max_allowed_packet=16M
set-variable = max_connect_errors=10
set-variable = thread_concurrency=2
set-variable = tmp_table_size=128000000

log-bin
server-id = 1

# Point the following paths to different dedicated disks
tmpdir = /mnt/sata3/mysql-tmp

# The safe_mysqld script
[safe_mysqld]
err-log=/var/lib/mysql/mysqld.log
#set-variable = ft_min_word_len=3

[mysqldump]
quick
set-variable = max_allowed_packet=16M

[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates

[isamchk]
set-variable = key_buffer=64M
set-variable = sort_buffer=64M
set-variable = read_buffer=16M
set-variable = write_buffer=16M

[myisamchk]
set-variable = key_buffer=64M
set-variable = sort_buffer=64M
set-variable = read_buffer=64M
set-variable = write_buffer=64M

[mysqlhotcopy]
interactive-timeout
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top