Essam
Wed 2nd Oct '02, 6:32am
hi
we run a large forum site:
4 GB (the attachment table is about 3.2GB)
we have about 250 user online (average)
here is my server info:
http://www.montada.com/opt/sqlinfo.php
CPU
processor : 0
vendor_id : GenuineIntel
cpu family : 6
model : 11
model name : Intel(R) Pentium(R) III CPU family 1266MHz
stepping : 1
cpu MHz : 1266.731
cache size : 512 KB
fdiv_bug : no
hlt_bug : no
f00f_bug : no
coma_bug : no
fpu : yes
fpu_exception : yes
cpuid level : 2
wp : yes
flags : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 mmx fxsr sse
bogomips : 2529.68
processor : 1
vendor_id : GenuineIntel
cpu family : 6
model : 11
model name : Intel(R) Pentium(R) III CPU family 1266MHz
stepping : 1
cpu MHz : 1266.731
cache size : 512 KB
fdiv_bug : no
hlt_bug : no
f00f_bug : no
coma_bug : no
fpu : yes
fpu_exception : yes
cpuid level : 2
wp : yes
flags : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 mmx fxsr sse
bogomips : 2529.68
SQL Info Script:
2:38am up 6 days, 10:37, 1 user, load average: 1.17, 0.97, 0.73
176 processes: 174 sleeping, 2 running, 0 zombie, 0 stopped
CPU0 states: 42.0% user, 52.0% system, 0.0% nice, 5.0% idle
CPU1 states: 3.0% user, 9.0% system, 0.0% nice, 86.0% idle
Mem: 1028472K av, 1017552K used, 10920K free, 36K shrd, 27104K buff
Swap: 2048952K av, 143912K used, 1905040K free 681632K cached
PID USER PRI NI SIZE RSS SHARE STAT %CPU %MEM TIME COMMAND
10451 mysql 16 0 29516 26M 12460 R 99.9 2.6 0:45 mysqld
Http processes currently running = 66
Mysql processes currently running = 59
Netstat information summary
1 LAST_ACK
1 SYN_RECV
1 SYN_SENT
2 FIN_WAIT1
4 CLOSING
8 ESTABLISHED
17 LISTEN
27 CLOSE_WAIT
199 TIME_WAIT
+---------------------------+-----------------+
| Variable_name | Value |
+---------------------------+-----------------+
| Aborted_clients | 1 |
| Aborted_connects | 0 |
| Bytes_received | 327084873 |
| Bytes_sent | 2453162824 |
| Connections | 2380 |
| Created_tmp_disk_tables | 27 |
| Created_tmp_tables | 50902 |
| Created_tmp_files | 0 |
| Delayed_insert_threads | 0 |
| Delayed_writes | 0 |
| Delayed_errors | 0 |
| Flush_commands | 1 |
| Handler_delete | 38648 |
| Handler_read_first | 29492 |
| Handler_read_key | 20057482 |
| Handler_read_next | 1555516244 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 25341906 |
| Handler_read_rnd_next | 1372688115 |
| Handler_update | 510201 |
| Handler_write | 27349705 |
| Key_blocks_used | 7793 |
| Key_read_requests | 109616782 |
| Key_reads | 483368 |
| Key_write_requests | 284991 |
| Key_writes | 220327 |
| Max_used_connections | 149 |
| Not_flushed_key_blocks | 3 |
| Not_flushed_delayed_rows | 0 |
| Open_tables | 64 | 100% of table_cache in use
| Open_files | 94 |
| Open_streams | 0 |
| Opened_tables | 23544 |
| Questions | 1829868 |
| Select_full_join | 42 |
| Select_full_range_join | 0 |
| Select_range | 245184 |
| Select_range_check | 0 |
| Select_scan | 316997 |
| Slave_running | OFF |
| Slave_open_temp_tables | 0 |
| Slow_launch_threads | 0 |
| Slow_queries | 495 | (execution time > 10 secs)
| Sort_merge_passes | 0 |
| Sort_range | 274004 |
| Sort_rows | 27940775 |
| Sort_scan | 147719 |
| Table_locks_immediate | 2068472 |
| Table_locks_waited | 22618 |
| Threads_cached | 0 |
| Threads_created | 2379 |
| Threads_connected | 51 |
| Threads_running | 3 |
| Uptime | 78759 | 21 hrs 52 mins 39 secs
+---------------------------+-----------------+
Key Reads/Key Read Requests = 0.004410 (Cache hit = 99.99559%)
Key Writes/Key Write Requests = 0.773102
Connections/second = 0.030 (/hour = 108.788)
KB received/second = 4.056 (/hour = 14600.297)
KB sent/second = 26.627 (/hour = 95858.805)
Temporary Tables Created/second = 0.646 (/hour = 2326.683)
Opened Tables/second = 0.299 (/hour = 1076.174)
Slow Queries/second = 0.006 (/hour = 22.626)
% of slow queries = 0.027%
Queries/second = 23.234 (/hour = 83641.550)
My /etc/my.cnf
[mysqld]
skip-networking
skip-locking
set-variable = max_connections=800
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
[mysql.server]
user=mysql
basedir=/var/lib
[safe_mysqld]
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
MEMinfo
total: used: free: shared: buffers: cached:
Mem: 1053155328 1038782464 14372864 167936 29224960 678039552
Swap: 2098126848 2863104 2095263744
MemTotal: 1028472 kB
MemFree: 14036 kB
MemShared: 164 kB
Buffers: 28540 kB
Cached: 659352 kB
SwapCached: 2796 kB
Active: 104748 kB
Inact_dirty: 584852 kB
Inact_clean: 1252 kB
Inact_target: 8120 kB
HighTotal: 131052 kB
HighFree: 2036 kB
LowTotal: 897420 kB
LowFree: 12000 kB
SwapTotal: 2048952 kB
SwapFree: 2046156 kB
NrSwapPages: 511539 pages
i tryed this:
[client]
port = 3306
socket = /var/lib/mysql/mysql.sock
[mysqld]
port = 3306
socket = /var/lib/mysql/mysql.sock
skip-locking
set-variable = max_connections=650
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
[safe_mysqld]
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
after that the load is 3.9 it was 1.9 before..
what is best setings for MySQL with this large DB?
Thank u
we run a large forum site:
4 GB (the attachment table is about 3.2GB)
we have about 250 user online (average)
here is my server info:
http://www.montada.com/opt/sqlinfo.php
CPU
processor : 0
vendor_id : GenuineIntel
cpu family : 6
model : 11
model name : Intel(R) Pentium(R) III CPU family 1266MHz
stepping : 1
cpu MHz : 1266.731
cache size : 512 KB
fdiv_bug : no
hlt_bug : no
f00f_bug : no
coma_bug : no
fpu : yes
fpu_exception : yes
cpuid level : 2
wp : yes
flags : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 mmx fxsr sse
bogomips : 2529.68
processor : 1
vendor_id : GenuineIntel
cpu family : 6
model : 11
model name : Intel(R) Pentium(R) III CPU family 1266MHz
stepping : 1
cpu MHz : 1266.731
cache size : 512 KB
fdiv_bug : no
hlt_bug : no
f00f_bug : no
coma_bug : no
fpu : yes
fpu_exception : yes
cpuid level : 2
wp : yes
flags : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 mmx fxsr sse
bogomips : 2529.68
SQL Info Script:
2:38am up 6 days, 10:37, 1 user, load average: 1.17, 0.97, 0.73
176 processes: 174 sleeping, 2 running, 0 zombie, 0 stopped
CPU0 states: 42.0% user, 52.0% system, 0.0% nice, 5.0% idle
CPU1 states: 3.0% user, 9.0% system, 0.0% nice, 86.0% idle
Mem: 1028472K av, 1017552K used, 10920K free, 36K shrd, 27104K buff
Swap: 2048952K av, 143912K used, 1905040K free 681632K cached
PID USER PRI NI SIZE RSS SHARE STAT %CPU %MEM TIME COMMAND
10451 mysql 16 0 29516 26M 12460 R 99.9 2.6 0:45 mysqld
Http processes currently running = 66
Mysql processes currently running = 59
Netstat information summary
1 LAST_ACK
1 SYN_RECV
1 SYN_SENT
2 FIN_WAIT1
4 CLOSING
8 ESTABLISHED
17 LISTEN
27 CLOSE_WAIT
199 TIME_WAIT
+---------------------------+-----------------+
| Variable_name | Value |
+---------------------------+-----------------+
| Aborted_clients | 1 |
| Aborted_connects | 0 |
| Bytes_received | 327084873 |
| Bytes_sent | 2453162824 |
| Connections | 2380 |
| Created_tmp_disk_tables | 27 |
| Created_tmp_tables | 50902 |
| Created_tmp_files | 0 |
| Delayed_insert_threads | 0 |
| Delayed_writes | 0 |
| Delayed_errors | 0 |
| Flush_commands | 1 |
| Handler_delete | 38648 |
| Handler_read_first | 29492 |
| Handler_read_key | 20057482 |
| Handler_read_next | 1555516244 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 25341906 |
| Handler_read_rnd_next | 1372688115 |
| Handler_update | 510201 |
| Handler_write | 27349705 |
| Key_blocks_used | 7793 |
| Key_read_requests | 109616782 |
| Key_reads | 483368 |
| Key_write_requests | 284991 |
| Key_writes | 220327 |
| Max_used_connections | 149 |
| Not_flushed_key_blocks | 3 |
| Not_flushed_delayed_rows | 0 |
| Open_tables | 64 | 100% of table_cache in use
| Open_files | 94 |
| Open_streams | 0 |
| Opened_tables | 23544 |
| Questions | 1829868 |
| Select_full_join | 42 |
| Select_full_range_join | 0 |
| Select_range | 245184 |
| Select_range_check | 0 |
| Select_scan | 316997 |
| Slave_running | OFF |
| Slave_open_temp_tables | 0 |
| Slow_launch_threads | 0 |
| Slow_queries | 495 | (execution time > 10 secs)
| Sort_merge_passes | 0 |
| Sort_range | 274004 |
| Sort_rows | 27940775 |
| Sort_scan | 147719 |
| Table_locks_immediate | 2068472 |
| Table_locks_waited | 22618 |
| Threads_cached | 0 |
| Threads_created | 2379 |
| Threads_connected | 51 |
| Threads_running | 3 |
| Uptime | 78759 | 21 hrs 52 mins 39 secs
+---------------------------+-----------------+
Key Reads/Key Read Requests = 0.004410 (Cache hit = 99.99559%)
Key Writes/Key Write Requests = 0.773102
Connections/second = 0.030 (/hour = 108.788)
KB received/second = 4.056 (/hour = 14600.297)
KB sent/second = 26.627 (/hour = 95858.805)
Temporary Tables Created/second = 0.646 (/hour = 2326.683)
Opened Tables/second = 0.299 (/hour = 1076.174)
Slow Queries/second = 0.006 (/hour = 22.626)
% of slow queries = 0.027%
Queries/second = 23.234 (/hour = 83641.550)
My /etc/my.cnf
[mysqld]
skip-networking
skip-locking
set-variable = max_connections=800
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
[mysql.server]
user=mysql
basedir=/var/lib
[safe_mysqld]
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
MEMinfo
total: used: free: shared: buffers: cached:
Mem: 1053155328 1038782464 14372864 167936 29224960 678039552
Swap: 2098126848 2863104 2095263744
MemTotal: 1028472 kB
MemFree: 14036 kB
MemShared: 164 kB
Buffers: 28540 kB
Cached: 659352 kB
SwapCached: 2796 kB
Active: 104748 kB
Inact_dirty: 584852 kB
Inact_clean: 1252 kB
Inact_target: 8120 kB
HighTotal: 131052 kB
HighFree: 2036 kB
LowTotal: 897420 kB
LowFree: 12000 kB
SwapTotal: 2048952 kB
SwapFree: 2046156 kB
NrSwapPages: 511539 pages
i tryed this:
[client]
port = 3306
socket = /var/lib/mysql/mysql.sock
[mysqld]
port = 3306
socket = /var/lib/mysql/mysql.sock
skip-locking
set-variable = max_connections=650
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
[safe_mysqld]
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
after that the load is 3.9 it was 1.9 before..
what is best setings for MySQL with this large DB?
Thank u