We currently have a dedicate server for MySQL. The server is a dual
pentium III (1Ghz) with 2GB of RAM in it. It also has 2 18GB 10,000 RPM
drives in it arranged in a Raid 1 configuration (mirror). Sometime in
the next 3-6 months we will be maxing out its capacity. (We were maxed
out a few days ago, but we added 1GB of RAM and cached some query
results). The system is currently running RedHat Linux 6.2.
While there are some non-optimal queries and maybe some variable tuning
that we can and should do, we will need to upgrade at some point and its
not obvious to me what the upgrade path is.
The axes of expansion I see are:
1) CPU speed (2 GHz processors?)
2) # of CPUs (quad processor, 8 processors?)
3) Multiple machines (replication)
4) More memory (current system maxes out at 4GB)
5) Different CPUs (SPARC, Alpha, IBM Power, HP-PA, Itanium)
6) Faster disks (15,000 RPM)
7) More disks (striping, different databases/tables on different disks,
MySQL striping)
8) Switch some high contention tables to InnoDB, BDB or Gemini to avoid
lock contention
9) Optimize server variables
Which approach or combination of approaches is likely to double
(quadruple?) our throughput at the best price performance?
I have attached some info to help characterize our usage.
mysql> show status;
+--------------------------+------------+
| Variable_name | Value |
+--------------------------+------------+
| Aborted_clients | 9356 |
| Aborted_connects | 0 |
| Bytes_received | 483742044 |
| Bytes_sent | 466908215 |
| Connections | 346158 |
| Created_tmp_disk_tables | 415975 |
| Created_tmp_tables | 1908200 |
| Created_tmp_files | 164 |
| Delayed_insert_threads | 0 |
| Delayed_writes | 0 |
| Delayed_errors | 0 |
| Flush_commands | 1 |
| Handler_delete | 56073 |
| Handler_read_first | 743133 |
| Handler_read_key | 388810193 |
| Handler_read_next | 987417906 |
| Handler_read_prev | 319 |
| Handler_read_rnd | 31493284 |
| Handler_read_rnd_next | 365353151 |
| Handler_update | 27173091 |
| Handler_write | 102767056 |
| Key_blocks_used | 15582 |
| Key_read_requests | 1535872968 |
| Key_reads | 5560163 |
| Key_write_requests | 3216153 |
| Key_writes | 573114 |
| Max_used_connections | 177 |
| Not_flushed_key_blocks | 0 |
| Not_flushed_delayed_rows | 0 |
| Open_tables | 1296 |
| Open_files | 2180712 |
| Open_streams | 0 |
| Opened_tables | 1277057 |
| Questions | 20072711 |
| Select_full_join | 255969 |
| Select_full_range_join | 32646 |
| Select_range | 139809 |
| Select_range_check | 0 |
| Select_scan | 3192636 |
| Slave_running | OFF |
| Slave_open_temp_tables | 0 |
| Slow_launch_threads | 1 |
| Slow_queries | 141 |
| Sort_merge_passes | 82 |
| Sort_range | 1130288 |
| Sort_rows | 143848526 |
| Sort_scan | 2073029 |
| Table_locks_immediate | 27157119 |
| Table_locks_waited | 58498 |
| Threads_cached | 0 |
| Threads_created | 346157 |
| Threads_connected | 57 |
| Threads_running | 1 |
| Uptime | 105090 |
+--------------------------+------------+
54 rows in set (0.00 sec)
mysql> show variables;
+---------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Variable_name |
Value
|
+---------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| ansi_mode |
OFF
|
| back_log |
50
|
| basedir |
/
|
| binlog_cache_size |
32768
|
| character_set |
latin1
|
| character_sets | latin1 big5 czech euc_kr gb2312 gbk
sjis tis620 ujis dec8 dos german1 hp8 koi8_ru latin2 swe7 usa7 cp1251
danish hebrew win1251 estonia hungarian koi8_ukr win1251ukr greek
win1250 croat cp1257 latin5 |
| concurrent_insert |
ON
|
| connect_timeout |
5
|
| datadir |
/var/lib/mysql/
|
| delay_key_write |
ON
|
| delayed_insert_limit |
100
|
| delayed_insert_timeout |
300
|
| delayed_queue_size |
1000
|
| flush |
OFF
|
| flush_time |
0
|
| have_bdb |
NO
|
| have_gemini |
NO
|
| have_innodb |
NO
|
| have_isam |
YES
|
| have_raid |
NO
|
| have_ssl |
NO
|
| init_file
|
|
|
|
| interactive_timeout |
28800
|
| join_buffer_size |
131072
|
| key_buffer_size |
16773120
|
| language |
/usr/share/mysql/english/
|
| large_files_support |
ON
|
| locked_in_memory |
OFF
|
| log |
OFF
|
| log_update |
OFF
|
| log_bin |
OFF
|
| log_slave_updates |
OFF
|
| long_query_time |
10
|
| low_priority_updates |
OFF
|
| lower_case_table_names |
0
|
| max_allowed_packet |
1047552
|
| max_binlog_cache_size |
4294967295
|
| max_binlog_size |
1073741824
|
| max_connections |
1024
|
| max_connect_errors |
10
|
| max_delayed_threads |
20
|
| max_heap_table_size |
16777216
|
| max_join_size |
4294967295
|
| max_sort_length |
1024
|
| max_user_connections |
0
|
| max_tmp_tables |
32
|
| max_write_lock_count |
4294967295
|
| myisam_recover_options |
OFF
|
| myisam_max_extra_sort_file_size |
256
|
| myisam_max_sort_file_size |
2047
|
| myisam_sort_buffer_size |
8388608
|
| net_buffer_length |
16384
|
| net_read_timeout |
30
|
| net_retry_count |
10
|
| net_write_timeout |
60
|
| open_files_limit |
0
|
| pid_file |
/var/lib/mysql/spode.pid
|
| port |
3306
|
| protocol_version |
10
|
| record_buffer |
2093056
|
| query_buffer_size |
0
|
| safe_show_database |
OFF
|
| server_id |
0
|
| skip_locking |
ON
|
| skip_networking |
OFF
|
| skip_show_database |
OFF
|
| slow_launch_time |
2
|
| socket |
/var/lib/mysql/mysql.sock
|
| sort_buffer |
4194296
|
| table_cache |
2024
|
| table_type |
MYISAM
|
| thread_cache_size |
0
|
| thread_stack |
65536
|
| transaction_isolation |
READ-COMMITTED
|
| timezone |
PDT
|
| tmp_table_size |
2097144
|
| tmpdir |
/tmp/
|
| version |
3.23.38-log
|
| wait_timeout |
28800
|
+---------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
79 rows in set (0.00 sec)
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
- Re: database server upgrade Mike Wexler
- Re: database server upgrade Steve Brazill
- Re: database server upgrade Mike Wexler
- Re: database server upgrade Jeremy Zawodny
- RE: database server upgrade Basil Hussain
- Re: database server upgrade Mike Wexler
- RE: database server upgrade Basil Hussain
- Re: database server upgrade Mike Wexler
- Re: database server upgrade Mike Wexler
- Re: database server upgrade Jeremy Zawodny
- RE: database server upgrade Basil Hussain
