| View previous topic :: View next topic |
| Author |
Message |
Paul
CastleCops Founder
Joined: Feb 22, 2002
Posts: 27351
|
 Posted: Sun Feb 24, 2008 1:55 pm Post subject: Executive Summary 24 Feb 2008 |
|
|
Good day, this is an updated topic with respect to CastleCops performance here at the main site. A summary follows:
Initial thread:  /t215785-Optimizing_Performance.html
Initial summary: /t216007-Executive_summary_21_Feb_2008.html
Second: /t216034-Exec_Sum_22_Feb_2008.html
Thread view counts were briefly re-routed to another table, and are now back to its original configuration. I have to yet migrate the interim counts to the correct location.
Forum search is re-activated, including new posts updated via index and favorite forums view.
MySQL has been running over 48 hours now. Here are some details:
| Quote: | Uptime = 2 days 10 hrs 37 min 25 sec
Avg. qps = 2295
Total Questions = 484490495
Threads Connected = 14 |
So before the perf issues started, our average SQL queries per second bordered 2,000. Right now we're above that.
Notice now with things running quicker, the number of slow queries have dropped considerably, but we're still in the 0% range:
| Quote: | SLOW QUERIES
Current long_query_time = 10 sec.
You have 670 out of 484491078 that take longer than 10 sec. to complete
The slow query log is enabled.
Your long_query_time may be too high, I typically set this under 5 sec. |
Seems we can increase our key_buffer_size:
| Quote: | KEY BUFFER
Current MyISAM index space = 7 G
Current key_buffer_size = 1 G
Key cache miss rate is 1 : 755
Key buffer fill ratio = 100.00 %
You could increase key_buffer_size
It is safe to raise this up to 1/4 of total system memory;
assuming this is a dedicated database server. |
One issue we keep having is qcache_lowmem_prunes. Here is a thread about it:
/t215943-MySQL_query_cache_size_improvements.html
In our tuning script run, here are some results:
| Quote: | QUERY CACHE
Query cache is enabled
Current query_cache_size = 128 M
Current query_cache_used = 76 M
Current query_cache_limit = 2 M
Current Query cache Memory fill ratio = 59.69 %
Current query_cache_min_res_unit = 1 K
Query Cache is 23 % fragmented
Run "FLUSH QUERY CACHE" periodically to defragment the query cache memory
If you have many small queries lower 'query_cache_min_res_unit' to reduce fragmentation.
MySQL won't cache query results that are larger than query_cache_limit in size |
Interesting, we're already at the 1K unit size for that value. Now what?
Given the amount of queries run in total, I'm happy with next:
| Quote: | JOINS
Current join_buffer_size = 2.00 M
You have had 12 queries where a join could not use an index properly
You should enable "log-queries-not-using-indexes"
Then look for non indexed joins in the slow query log.
If you are unable to optimize your queries you may want to increase your
join_buffer_size to accommodate larger joins in one pass.
Note! This script will still suggest raising the join_buffer_size when
ANY joins not using indexes are found. |
And I don't think InnoDB is an option for us:
| Quote: | TABLE LOCKING
Current Lock Wait ratio = 1 : 343
You may benefit from selective use of InnoDB. |
However, that ratio I think is better than previous under perf issues.
Another tuner script:
| Code: | [OK] Query cache efficiency: 97.4%
[!!] Query cache prunes per day: 215138
[!!] Connections aborted: 11%
-------- Recommendations -----------------------------------------------------
General recommendations:
Your applications are not closing MySQL connections properly
Variables to adjust:
query_cache_size (> 128M) |
An interesting statistic for folks:
[--] Reads / Writes: 70% / 30%
CastleCops is a far greater Read orientied community.
So what is the current status?
I believe we are awaiting the arrival and replacement of our 3Ware 9000 Series controller card for the main system that is the suspect of our performance woes.
There are other options that may be pursued:
- more RAM
- more CPU
- two new custom systems for this site dedicated to MySQL and Apache/PHP
I am unsure as of right now, but as time progresses I'll learn more. I'm just thankful we're up and running and have the support and love of this wonderful community of people.
Thank you for allowing me to be open.
|
|
| Back to top |
|
 |
Paul
CastleCops Founder
Joined: Feb 22, 2002
Posts: 27351
|
| Posted: Sun Feb 24, 2008 1:58 pm Post subject: |
|
|
P.S.
The majority of static files have been offloaded to another server and lighttpd. Stuff affected:
- site CSS file
- forum stock gallery avatars
- many site wide images
Not affected:
- user uploaded avatars
- Uploads and Forum attachments
In addition, snapshots for PIRT and SIRT are delivered by another server running thttpd.
For a future update, I'd like to include the various diagnostic tools links, etc to save folks time reading the ten page thread.
_________________
Paul Laudanski - http://www.laudanski.com
http://www.linkedin.com/pub/1/49a/17b
|
|
| Back to top |
|
|
Bill_Bright
General
Premium Member
Joined: Jan 16, 2004
Posts: 9046
Location: Nebraska, USA
|
|
| Back to top |
|
|
Ikeb
Special Response Team
Forums Admin
Joined: Apr 20, 2003
Posts: 16543
|
| Posted: Sun Feb 24, 2008 5:50 pm Post subject: |
|
|
| Bill_Bright wrote: | | Quote: | | Thank you for allowing me to be open. |
What kept you? |
Well there is the matter of public disclosure revealing something to crackers.... though "security by obscurity" only works for the short term. The discussions in this forum demonstrates that collective knowledge is a much better approach in the long run. 
|
|
| Back to top |
|
|
Bill_Bright
General
Premium Member
Joined: Jan 16, 2004
Posts: 9046
Location: Nebraska, USA
|
|
| Back to top |
|
|
Ikeb
Special Response Team
Forums Admin
Joined: Apr 20, 2003
Posts: 16543
|
|
| Back to top |
|
|
Bill_Bright
General
Premium Member
Joined: Jan 16, 2004
Posts: 9046
Location: Nebraska, USA
|
|
| Back to top |
|
|
AlphaCentauri
SIRT Handler
Premium Member
Joined: Nov 20, 2003
Posts: 2895
|
| Posted: Mon Feb 25, 2008 12:56 pm Post subject: |
|
|
My snowflakes have morphed into multiple copies of the animated Bugs Bunny gif, all moving in chorus.
|
|
| Back to top |
|
|
Bill_Bright
General
Premium Member
Joined: Jan 16, 2004
Posts: 9046
Location: Nebraska, USA
|
|
| Back to top |
|
|
AlphaCentauri
SIRT Handler
Premium Member
Joined: Nov 20, 2003
Posts: 2895
|
| Posted: Mon Feb 25, 2008 3:06 pm Post subject: |
|
|
Mine is back to snowflakes now, too.
|
|
| Back to top |
|
|
Bill_Bright
General
Premium Member
Joined: Jan 16, 2004
Posts: 9046
Location: Nebraska, USA
|
|
| Back to top |
|
|
Paul
CastleCops Founder
Joined: Feb 22, 2002
Posts: 27351
|
|
| Back to top |
|
|
Bill_Bright
General
Premium Member
Joined: Jan 16, 2004
Posts: 9046
Location: Nebraska, USA
|
|
| Back to top |
|
|
Paul
CastleCops Founder
Joined: Feb 22, 2002
Posts: 27351
|
|
| Back to top |
|
|
|
|
Forum FAQ
Search
Usergroups
Profile
Login to check your private messages
Login
