More RAM never hurts (as long as you're using it, which you might be able to
influence by your other option of optimizing the server variables).
I've always had the opinion, that the faster you service the queries, the
more queries you can handle with existing resources. This might be best
handled by getting more drives, and divying up the tasks of each part of the
database to different disk (RAID) devices. If you're using IDE drives, it
might be a good time to get a decent Ultra SCSI controller off of Ebay (OH,
what do you know, I'm going to be putting up a couple of SCSI RAID cards
for sale on Ebay soon...). Keep your original pair of mirrored drives,
which you can allocate a partition or two off of for the MySQL 'temp' area,
and other stuff (a dedicate 'dump' area ?). Depending on how large each
query 'result' is, you'd need to decide whether just to 'mirror' another
pair of drives for the data partitions, or whether it warranted a 'striped'
RAID of 3 or more drives. If you can get enough drives shoved in your
system, you should dedicate a 'mirrored' pair for indexes and another for
data (allowing for almost simultaneous reads/writes from the indexes and
data portions, especially for concurrent queries).
Hope this helps...
----- Original Message -----
From: "Mike Wexler" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Thursday, July 19, 2001 4:21 PM
Subject: database server upgrade
> 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
>
---------------------------------------------------------------------
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
