Hi,
> > 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.
>
> Until we have more of an idea where your system is stressed, it's hard
> to say. Are the CPUs maxed? I/O channels? RAM?
If your system is getting stressed with disk I/O, then a good first step
could be to move to a different RAID configuration. As you're running RAID
1, when writing data, each bit of data has to be written to both drives. If
your environment involves a lot of INSERT queries, then it may be worth
adding another disk and moving up to RAID 5. Alternatively, you might
consider foresaking redundancy and going down to RAID 0. Also, are you
running hardware or software RAID? If software, getting a dedicated RAID
card will lessen the load on your CPUs.
> > 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.
>
> Replication. You can do it with less expensive hardware. You'll get
> good performance and probably be able to scale farther wit it. Of
> course, you'll want to look at #9 before spending any money. And try
> to get an idea of where your contention for resources is today.
Yes, you should definitely look at option #9 first. Here's a few pointers to
some things that immediately spring off the screen at me:
| Open_tables | 1296 |
| Open_files | 2180712 |
| Open_streams | 0 |
| Opened_tables | 1277057 |
| table_cache | 2024 |
Your Opened_tables figure is quite large, which means you are incurring
extra I/O penalties as tables have to be constantly opened and closed. You
should try increasing your table_cache size.
| Slow_launch_threads | 1 |
| Threads_cached | 0 |
| Threads_created | 346157 |
| thread_cache_size | 0 |
Slow_launch_threads should never be more than zero. And, seeing as your
configured slow launch yardstick time is 2 seconds, this indicates you may
be starting to have a bottleneck here. You should trying setting a
thread_cache_size of something like 32 - maybe higher.
| Table_locks_immediate | 27157119 |
| Table_locks_waited | 58498 |
| Key_read_requests | 1535872968 |
| Key_reads | 5560163 |
This is good. Table locks that had to be waited for are less than 1% of
total locks. You don't seem to have too much of a problem with lock
contention. Also, your ratio of key reads/requests is way less than 0.01, so
no general problems with index usage on your queries.
| Created_tmp_disk_tables | 415975 |
| tmp_table_size | 2097144 |
Created_tmp_disk_tables could probably be a little lower. Try increasing
your tmp_table_size memory figure to lessen the number of temp tables
written to disk - 2Mb is probably quite small if you're shuffling large
amounts of data.
As for replication, there could be a couple of sticking points with this
strategy that you may need to overcome. The first is whether your client
applications (be they web scripts, custom apps, whatever) can easily be
re-programmed to support distributing their SQL query load amongst several
servers. Secondly, if you are chucking large amounts of data around and your
servers are replicating it all, your networking may not be up to scratch. If
you go for replication you should make sure you're running at least 100Mbps
between your MySQL servers. (BTW, if in a closed environment, running
100Mbps to the clients might help also.)
Looking at option #8, you may see quite a large performance boost if you go
for InnoDB tables and your query load consists of large quantities of small
queries, as this is where row-level locking, etc. will help most.
Regards,
Basil Hussain
---------------------------------------
Internet Developer, Kodak Weddings
E-Mail: [EMAIL PROTECTED]
---------------------------------------------------------------------
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
