>
>
> 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.
Its already 2024 (I've upped it from 128). What is the maximum
reasonable value on RedHat Linux 6.2 and a 2.2.X kernel. Would upgrading
to RedHat 7.1 and 2.4.x help?
>
> | 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.
Ok. Although 1 out of 346,157 doesn't seem to be significant. After the
server has been up a week or two I can tell if this is significant. What
is the downside of a thread_cache? Why isn't it on by default?
>
> | 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.
This doesn't cover a period of max usage. I need to get some more
information now that I've upgraded the server.
>
> | 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.
Ok.
>
> 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.)
We are running everything through a 100 Mbps switch.
I can certainly take 3 or 4 very query instensive clients and set them
up to use read only clients. Does it make sense to do it that way? Also,
all the queries go through a perl module of mine, so I could distribute
everything, but does it make sense to distribute updates?
>
> 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.
There are some tables that are like this.
>
> 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
---------------------------------------------------------------------
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
