[eluser]Jay Logan[/eluser]
I ran that command with Putty on my Windows machine and this was the output:
Code: login as: root
[email protected]'s password:
Last login: Thu Jan 8 13:07:57 2009 from 70.87.80.194
[root@stratus ~]# mysqladmin -u ptentry_dba -p processlist
Enter password:
+--------+-------------+-----------+---------------------+---------+------+----- -----------------+-------------------------------------------------------------- ----------------------------------------+
| Id | User | Host | db | Command | Time | Stat e | Info |
+--------+-------------+-----------+---------------------+---------+------+----- -----------------+-------------------------------------------------------------- ----------------------------------------+
| 103703 | ptentry_dba | localhost | ptentry_entrysystem | Query | 1442 | Copy ing to tmp table | SELECT Meet_Event.*, Event.*, COUNT(Meet_Athlete.id) AS Total , COUNT(Coach.id) AS Coach FROM meet_ev |
| 103805 | ptentry_dba | localhost | ptentry_entrysystem | Query | 1133 | Copy ing to tmp table | SELECT Meet_Event.*, Event.*, COUNT(Meet_Athlete.id) AS Total , COUNT(Coach.id) AS Coach FROM meet_ev |
| 103872 | ptentry_dba | localhost | ptentry_entrysystem | Query | 960 | Copy ing to tmp table | SELECT Meet_Event.*, Event.*, COUNT(Meet_Athlete.id) AS Total , COUNT(Coach.id) AS Coach FROM meet_ev |
| 103878 | ptentry_dba | localhost | ptentry_entrysystem | Query | 941 | Copy ing to tmp table | SELECT Meet_Event.*, Event.*, COUNT(Meet_Athlete.id) AS Total , COUNT(Coach.id) AS Coach FROM meet_ev |
| 103896 | ptentry_dba | localhost | ptentry_entrysystem | Query | 876 | Copy ing to tmp table | SELECT Meet_Event.*, Event.*, COUNT(Meet_Athlete.id) AS Total , COUNT(Coach.id) AS Coach FROM meet_ev |
| 103964 | ptentry_dba | localhost | ptentry_entrysystem | Query | 645 | Copy ing to tmp table | SELECT Meet_Event.*, Event.*, COUNT(Meet_Athlete.id) AS Total , COUNT(Coach.id) AS Coach FROM meet_ev |
| 103990 | ptentry_dba | localhost | ptentry_entrysystem | Query | 581 | Copy ing to tmp table | SELECT Meet_Event.*, Event.*, COUNT(Meet_Athlete.id) AS Total , COUNT(Coach.id) AS Coach FROM meet_ev |
| 104108 | ptentry_dba | localhost | ptentry_entrysystem | Query | 252 | Copy ing to tmp table | SELECT Meet_Event.*, Event.*, COUNT(Meet_Athlete.id) AS Total , COUNT(Coach.id) AS Coach FROM meet_ev |
| 104123 | ptentry_dba | localhost | ptentry_entrysystem | Query | 195 | Copy ing to tmp table | SELECT Meet_Event.*, Event.*, COUNT(Meet_Athlete.id) AS Total , COUNT(Coach.id) AS Coach FROM meet_ev |
| 104130 | ptentry_dba | localhost | ptentry_entrysystem | Query | 182 | Copy ing to tmp table | SELECT Meet_Event.*, Event.*, COUNT(Meet_Athlete.id) AS Total , COUNT(Coach.id) AS Coach FROM meet_ev |
| 104166 | ptentry_dba | localhost | ptentry_entrysystem | Query | 126 | Copy ing to tmp table | SELECT Meet_Event.*, Event.*, COUNT(Meet_Athlete.id) AS Total , COUNT(Coach.id) AS Coach FROM meet_ev |
| 104195 | ptentry_dba | localhost | ptentry_entrysystem | Query | 65 | Copy ing to tmp table | SELECT Meet_Event.*, Event.*, COUNT(Meet_Athlete.id) AS Total , COUNT(Coach.id) AS Coach FROM meet_ev |
| 104215 | ptentry_dba | localhost | ptentry_entrysystem | Query | 38 | Lock ed | UPDATE coaches SET roster = 'men' WHERE id = '1001363' LIMIT 1 |
| 104234 | ptentry_dba | localhost | ptentry_entrysystem | Query | 4 | Lock ed | SELECT *
FROM (`coaches`)
WHERE `id` = '1001363' |
| 104237 | ptentry_dba | localhost | ptentry_entrysystem | Query | 3 | Lock ed | SELECT Meet_Event.*, Event.*, COUNT(Meet_Athlete.id) AS Total , COUNT(Coach.id) AS Coach FROM meet_ev |
| 104239 | ptentry_dba | localhost | | Query | 0 | | show processlist |
+--------+-------------+-----------+---------------------+---------+------+----- -----------------+-------------------------------------------------------------- ----------------------------------------+
[root@stratus ~]#
[eluser]Jay Logan[/eluser]
Here is the info from my phpMyAdmin:
Code: max user connections 30
Wow, it says that I have had 3,390 failed attempts and 342 aborted.
[eluser]Jay Logan[/eluser]
Just checked some things on my backend and I'm looking at my CPU/Memory/MySQL Usage and every day for the past few weeks, the row in the table regarding my MySQL user is RED (that can't be good).
Code: User Domain %CPU %MEM MySQL Processes
ptentry ptentry.com 3.29 16.14 8.5
Top Process %CPU 21.0 /usr/bin/php /home/ptentry/public_html/index.php
Top Process %CPU 19.0 [php]
Top Process %CPU 18.0 [php]
mysql 25.29 6.84 0.0
Apparently, I'm using 25.29% of my CPU with that user. So I'm starting to thing the issue is with my hardware. My server is pretty decent though. I have 1500GB bandwidth, 80 GB hard drive, and 1024 DDR memory. I'm going to contact my server admin. now and see if I can't work this out.
[eluser]Jay Logan[/eluser]
Just finished talking with my host. That was a waste of time. Basically, he told me to optimize my script better. Back to square 1.
[eluser]Hockeychap[/eluser]
Just a quick post before I whizz off for the night:
1. You're max_connection is exceptionally small. Mine (on a shared server of similar capacity) is set to 500. In my cpanel installation the entry is in /etc/my.cnf on the server. There is a line that reads:
Code: set-variable = max_connections=500
If you add this line into your my.cnf and then restart your mysql server that should increase the number of connections your server will handle.
2. More importantly, the process breakdown shows that your mysql server is using temporary tables very heavily. These are primarily used when the group by and sort (order by) buffer is too small for the results of your queries. The parameter to have a look at is Quote:key_buffer_size
as temporary tables are often handled on disk.
TO have a look at the temporary table activity have a look at 3 variables: created_tmp_disk_tables , Created_tmp_files, Created_tmp_tables .
If the value for created_tmp_files is high , you are doing a lot of disk io.
A really good script is available here Tuning script. If you download it and run it on your server it will help identify duff config. * Late edit - argh - doesn;t run so well on Centos as the calculator bc isn't available *
Will have a think on the train tomorrow about how we can reduce your overall temp table use.
[eluser]dmorin[/eluser]
@Hockeychap Great script I hadn't seen that before!
@J-Slim To use, do the following from putty:
1. Download the script
wget http://day32.com/MySQL/tuning-primer.sh
2. Change the permissions for the file
chmod 755 tuning-primer.sh
3. Run it
./tuning-primer.sh
4. Apply the suggestions
From http://www.cpanelconfig.com/optimize-a-c...er-script/
You can certainly benefit from the recommendations of this script, but I'm guessing your biggest problem are inefficient queries and lack of indexes in the correct places. All of those "Copying to tmp table" are not good and look at the Time column. Those are seconds! So the top query has been running for almost 24 minutes. So your mysql server is simply getting overwhelmed and starts lagging which makes future queries execute even more slowly so it's a degrading cycle.
As a test, find the full query that is listed in the info column above. It starts with:
Quote:SELECT Meet_Event.*, Event.*, COUNT(Meet_Athlete.id) AS Total, COUNT(Coach.id) AS Coach FROM meet_ev...
Run the full query in MySQLAdmin. After you run it, there will be an Explain link. Click that and paste the results here. That will tell you how your query is executing. Since it's listed so many times, optimizing this single query is likely to give you a noticeable improvement (but won't fix everything).
EDIT:
Just reading back through and saw you say you think it's your hardware. Throwing hardware at it might help, but it's not the cause. I wouldn't worry about hardware yet.
Also, your max connections is small, but it's not the root cause.
[eluser]Jay Logan[/eluser]
Here are the recommendations from the tuning script.
Code: -- MYSQL PERFORMANCE TUNING PRIMER --
- By: Matthew Montgomery -
MySQL Version 5.0.51a-community i686
Uptime = 3 days 8 hrs 50 min 14 sec
Avg. qps = 4
Total Questions = 1279478
Threads Connected = 1
Server has been running for over 48hrs.
It should be safe to follow these recommendations
To find out more information on how each of these
runtime variables effects performance visit:
http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html
Visit http://www.mysql.com/products/enterprise/advisors.html
for info about MySQL's Enterprise Monitoring and Advisory Service
SLOW QUERIES
The slow query log is NOT enabled.
Current long_query_time = 10 sec.
You have 1867 out of 1279499 that take longer than 10 sec. to complete
Your long_query_time may be too high, I typically set this under 5 sec.
BINARY UPDATE LOG
The binary update log is NOT enabled.
You will not be able to do point in time recovery
See http://dev.mysql.com/doc/refman/5.0/en/point-in-time-recovery.html
WORKER THREADS
Current thread_cache_size = 128
Current threads_cached = 33
Current threads_per_sec = 0
Historic threads_per_sec = 0
Your thread_cache_size is fine
MAX CONNECTIONS
Current max_connections = 500
Current threads_connected = 1
Historic max_used_connections = 34
The number of used connections is 6% of the configured maximum.
You are using less than 10% of your configured max_connections.
Lowering max_connections could help to avoid an over-allocation of memory
See "MEMORY USAGE" section to make sure you are not over-allocating
MEMORY USAGE
Max Memory Ever Allocated : 215 M
Configured Max Per-thread Buffers : 2 G
Configured Max Global Buffers : 74 M
Configured Max Memory Limit : 2 G
Physical Memory : 1011.28 M
Max memory limit exceeds 90% of physical memory
KEY BUFFER
Current MyISAM index space = 6 M
Current key_buffer_size = 32 M
Key cache miss rate is 1 : 4378
Key buffer fill ratio = 5.00 %
Your key_buffer_size seems to be too high.
Perhaps you can use these resources elsewhere
QUERY CACHE
Query cache is enabled
Current query_cache_size = 32 M
Current query_cache_used = 18 M
Current query_cache_limit = 1 M
Current Query cache Memory fill ratio = 58.46 %
Current query_cache_min_res_unit = 4 K
MySQL won't cache query results that are larger than query_cache_limit in size
SORT OPERATIONS
Current sort_buffer_size = 1 M
Current read_rnd_buffer_size = 1020 K
Sort buffer seems to be fine
JOINS
Current join_buffer_size = 1.00 M
You have had 11648 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.
OPEN FILES LIMIT
Current open_files_limit = 2558 files
The open_files_limit should typically be set to at least 2x-3x
that of table_cache if you have heavy MyISAM usage.
Your open_files_limit value seems to be fine
TABLE CACHE
Current table_cache value = 1024 tables
You have a total of 522 tables
You have 980 open tables.
Current table_cache hit rate is 98%, while 95% of your table cache is in use
You should probably increase your table_cache
TEMP TABLES
Current max_heap_table_size = 16 M
Current tmp_table_size = 32 M
Of 42281 temp tables, 0% were created on disk
Effective in-memory tmp_table_size is limited to max_heap_table_size.
Created disk tmp tables ratio seems fine
TABLE SCANS
Current read_buffer_size = 1020 K
Current table scan ratio = 3825 : 1
read_buffer_size seems to be fine
TABLE LOCKING
Current Lock Wait ratio = 1 : 460
You may benefit from selective use of InnoDB.
If you have long running SELECT's against MyISAM tables and perform
frequent updates consider setting 'low_priority_updates=1'
If you have a high concurrency of inserts on Dynamic row-length tables
consider setting 'concurrent_insert=2'.
[eluser]Jay Logan[/eluser]
It appears my max_connections is set to 500. But my max_USER_connections is set to 30. Is this a good setting?
[eluser]dmorin[/eluser]
That setting should be plenty, and could even be trimmed down if you wanted. Probably would have much affect though. The max users is fine since it looks like you only have one user connecting.
Run through the Explain query in my post above and let us know what the results are.
[eluser]Jay Logan[/eluser]
So this is the query I ran. The ID's would change based on the user and meet. I chose one of my bigger meets.
Code: SELECT Meet_Event.*, Event.*, COUNT(Meet_Athlete.id) AS Total, COUNT(Coach.id) AS Coach FROM meet_events AS Meet_Event LEFT JOIN events AS Event ON Meet_Event.event_id = Event.id LEFT JOIN meet_athletes AS Meet_Athlete ON Meet_Event.id = Meet_Athlete.meet_event_id LEFT JOIN athletes AS Athlete ON Meet_Athlete.athlete_id = Athlete.id LEFT JOIN roster AS Roster ON Roster.athlete_id = Athlete.id Left Join coaches AS Coach ON Roster.coach_id = Coach.id AND Coach.id = 1000000 WHERE Meet_Event.meet_id = 815398826 GROUP BY Meet_Event.id ORDER BY Event.type DESC, Event.name ASC, Meet_Event.gender ASC
It took about 5 minutes to process in phpMyAdmin and here are the contents from "Explain".
Code: id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE Meet_Event ALL NULL NULL NULL NULL 4730 Using where; Using temporary; Using filesort
1 SIMPLE Event eq_ref PRIMARY PRIMARY 34 ptentry_entrysystem.Meet_Event.event_id 1
1 SIMPLE Meet_Athlete ALL NULL NULL NULL NULL 6277
1 SIMPLE Athlete eq_ref PRIMARY PRIMARY 8 ptentry_entrysystem.Meet_Athlete.athlete_id 1 Using index
1 SIMPLE Roster ALL NULL NULL NULL NULL 27059
1 SIMPLE Coach const PRIMARY PRIMARY 8 const 1 Using index
|
