I have a problem with my database having lots of ‘stale’ slow_queries. I think the problem may be because of the following code:
$numresults=mysql_query(“select * from links where catagory=”.$catagory." order by linknum");$numrows=mysql_num_rows($numresults);
I believe this sql statement is doing a full table scan; is this correct?
Would there be anything to gain from coding the sql like this:
select count(*) from links where …
I’ve just realised that catagory (i know it’s spelt incorrectly…) isn’t an index… the links table is the biggest table - do you think this could be a problem?
Also I don’t close the db connection - I’m reading conflicting messages about how good/bad this can be… most of my pages select a subset of data then have links where you can get the next/previous batch which work via post - i’m not sure how a close of each db connection will behave. Any ideas on the best approach?
My db is not very big. It only has six tables and 70k entries in the biggest table…
My index in the biggest table isn’t contiguous because entries get deleted - is this a problem?
Lots of questions! Hope you can help - you’ve probably realised that I’m new to this! …I’ve just created the database to do what I need and didn’t really do any ‘design’ as such.
I’ve now downloaded the tuning-primer.sh script and here follows the output:
– MYSQL PERFORMANCE TUNING PRIMER – - By: Matthew Montgomery -MySQL Version 4.1.22-standard i686Uptime = 0 days 12 hrs 10 min 58 secAvg. qps = 6Total Questions = 275029Threads Connected = 2Warning: Server has not been running for at least 48hrs.It may not be safe to use these recommendationsTo find out more information on how each of theseruntime variables effects performance visit:http://dev.mysql.com/doc/refman/4.1/en/server-system-variables.htmlVisithttp://www.mysql.com/products/enterprise/advisors.htmlfor info about MySQL’s Enterprise Monitoring and Advisory ServiceSLOW QUERIESCurrent long_query_time = 10 sec.You have 22 out of 275041 that take longer than 10 sec. to completeThe slow query log is NOT enabled.Your long_query_time may be too high, I typically set this under 5 sec.WORKER THREADSCurrent thread_cache_size = 128Current threads_cached = 18Current threads_per_sec = 0Historic threads_per_sec = 0Your thread_cache_size is fineMAX CONNECTIONSCurrent max_connections = 500Current threads_connected = 3Historic max_used_connections = 21The number of used connections is 4% 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 memorySee “MEMORY USAGE” section to make sure you are not over-allocatingMEMORY USAGEMax Memory Ever Allocated : 161 MConfigured Max Per-thread Buffers : 2 GConfigured Max Global Buffers : 74 MConfigured Max Memory Limit : 2 GPhysical Memory : 1010.15 MMax memory limit exceeds 90% of physical memoryKEY BUFFERCurrent MyISAM index space = 0 bytesCurrent key_buffer_size = 32 MKey cache miss rate is 1 : 785168Key buffer fill ratio = 3.00 %Your key_buffer_size seems to be too high.Perhaps you can use these resources elsewhereQUERY CACHEQuery cache is enabledCurrent query_cache_size = 32 MCurrent query_cache_used = 3 MCurrent query_cache_limit = 1 MCurrent Query cache Memory fill ratio = 11.07 %Current query_cache_min_res_unit = 4 KQuery Cache is 10 % fragmentedRun “FLUSH QUERY CACHE” periodically to defragment the query cache memoryIf you have many small queries lower ‘query_cache_min_res_unit’ to reduce fragmentation.Your query_cache_size seems to be too high.Perhaps you can use these resources elsewhereMySQL won’t cache query results that are larger than query_cache_limit in sizeSORT OPERATIONSCurrent sort_buffer_size = 1 MCurrent record/read_rnd_buffer_size = 1020 KSort buffer seems to be fineJOINSCurrent join_buffer_size = 1.00 MYou have had 0 queries where a join could not use an index properlyYour joins seem to be using indexes properlyOPEN FILES LIMITCurrent open_files_limit = 2558 filesThe open_files_limit should typically be set to at least 2x-3xthat of table_cache if you have heavy MyISAM usage.Your open_files_limit value seems to be fineTABLE CACHEYou are not ‘101’ or 'root’I am unable to determine the table_count!Current table_cache value = 1024 tablesYou have 85 open tables.The table_cache value seems to be fineTEMP TABLESCurrent max_heap_table_size = 16 MCurrent tmp_table_size = 32 MOf 16414 temp tables, 99% were created on diskEffective in-memory tmp_table_size is limited to max_heap_table_size.Perhaps you should increase your tmp_table_size and/or max_heap_table_sizeto reduce the number of disk-based temporary tablesNote! BLOB and TEXT columns are not allow in memory tables.If you are using these columns raising these values might not impact yourratio of on disk temp tables.TABLE SCANSCurrent read_buffer_size = 1020 KCurrent table scan ratio = 22717 : 1You have a high ratio of sequential access requests to SELECTsYou may benefit from raising read_buffer_size and/or improving your use of indexes.TABLE LOCKINGCurrent Lock Wait ratio = 1 : 23You may benefit from selective use of InnoDB.If you have long running SELECT’s against MyISAM tables and performfrequent updates consider setting ‘low_priority_updates=1’
Does this mean anything to anyone? …I’ve run the flush cache query command that it suggested yesterday but I still had to restart mysql again (
Here’s the output from my mysqladmin status command:
Uptime: 43995 Threads: 1 Questions: 276045 Slow queries: 22 Opens: 160 Flush tables: 1 Open tables: 85 Queries per second avg: 6.274
Looks like the ‘slow queries’ grows throughout the day… I restarted MySQL about 11 hours ago…
I think the problem only occurs when I get more than thousand visitors per hour… so probably a loading issue…
I have just one web user of the database - is this normal practice? Every page initiates a database connection using this same user - should I create different users for different pages? Would this elleviate some contention?
My host told me that they could see ‘locked queries’ how do I view these myself?
select * from links where catagory=“.$catagory.” order by linknum
will if you don’t have any index on it, perform first a table scan and then a sort of the entire table.
Suggestions how to speed things up:
1.
Do you really need all records from this table?
Because if you only want some of them you should use a LIMIT clause at the end to limit the amount of rows sent to PHP.
You should add a index like:
ALTER TABLE links ADD INDEX links_ix_catagory_linknum(catagory, linknum)
that way MySQL can use the index to both find the correct records and use the second column of the index to retrieve the records in the right order as well. And that way also avoiding the sorting of the result set before returning it to your application.
Thanks for the reply - the answer is no I don’t need all the records - I just need a count of how many records there are for the specific category… is this the same thing?
Do you still think I should add the extra index?
In desperation I have now dumped the database and recreated it - it appears a little quicker but only time will tell… The main table grows by about 500 entries per day so I was wondering if it had grown too big (extents?)
Just realised that I’m getting a few of the messages below when I’m running SQL from the command line. Is this indicative of another problem? MySQL processes should be available all the time right? Is there a log file I can check the persistence of the process in?
Welcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 20237 to server version: 4.1.22-standardType ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the buffer.mysql> delete from links where link=11497;ERROR 2006 (HY000): MySQL server has gone awayNo connection. Trying to reconnect…Connection id: 20247Current database: pondlife_wwwaddalinksQuery OK, 0 rows affected (3.63 sec)
[B]pondlife wrote on Sat, 05 January 2008 18:54[/B]
Hi!
Thanks for the reply - the answer is no I don’t need all the records - I just need a count of how many records there are for the specific category… is this the same thing?
Do you still think I should add the extra index?
It is definitely not the same thing!
What you want is:
SELECT COUNT(*) FROM links WHERE catagory=“.$catagory.”
And since you are only counting the records a ORDER BY is not necessary.
Never use a ORDER BY if you don’t need the records in order. It can be a very heavy operation if the result set is large.
Many thanks for your reply. I think you’ve hit the nail on the head with this one - I had to restart MySQL this morning again but since then I’ve not had any slow queries and my ‘queries per second avg’ is now 3.5 which is about half of what it was!
My query now looks like this:
$numresults=mysql_query(“select * from links where catagory=”.$image_details[‘catagory’].“”);$numrows=mysql_num_rows($numresults);
I couldn’t get the count(*) to work - i think this may be a PHP thing…
I’ll keep an eye on how the site runs over the next 24hours.
Then you use the CountOfRows as the column name in your fetch_row_assoc() (don’t remember exactly what it’s called).
Your mysql_num_rows() is a PHP function that counts how many rows that was returned form the DB.
The difference is that in your case the entire result set is copied from the DB into PHP. And copying a lot of data from DB to PHP takes a lot of CPU.
While if you use the COUNT(*) method the count is performed within the DBMS and only the result is passed on to PHP.
If you are not interested in the data itself but only a count then you should go with COUNT(*). But if you are interested in the data also then you should use the mysql_num_rows().
This sounds really strange but I can’t find any examples of using the count(*) with PHP on the web…
Things appear to be running a little better with my site in that it’s up-and-running. But my traffic is now down to less than 1k visitors per day so it’s not hitting the same levels as when I was having issues.
Yep, I realise that but I can’t seem to find any examples on how to capture the count if I use the syntax below. All the PHP examples that are extracting a count from SQL are using the ‘select * from…’ syntax.
I can’t find a way extracting the count value from this (but it’s probably just me being thick):
$numresults=mysql_query(“select count(*) from links where catagory=”.$image_details[‘catagory’].“”);
Anywho, the problem of crashing MySQL is still happening - although it took 3 days for it to happen this time. It appears to be a problem when the ‘queries per second average’ goes up to 6.7 - is this a high figure?
I’m thinking of adding an entry to cron to restart MySQL every night - is this advisable?
How do you usually fetch the data from a SQL query?
Think about that, and read my post 2455 earlier in this thread.
Because the count(*) value will be returned as any other column value from a normal select.
Good luck!
<?php$result = $mysqli->query("SELECT COUNT(*) as TOTALFOUND from table");$row_array=$result->fetch_array(MYSQLI_ASSOC);print($row_array['TOTALFOUND']); ?>
OR
<?//MAKE THE CONNECTION WITH DATABASE$my_table = mysql_query("SELECT COUNT(*) as TOTALFOUND from table", $link); //EXECUTE SQL CODENote: will return the total on TOTALFOUNDprint (mysql_result($my_table,0,"TOTALFOUND")); //use the field camp to get the total from your SQL query!?>
But I’m still interested in any tools that will help me pin down exactly what’s causing the problems with MySQL? (which logs to look in - any analysis tools etc…)
Here’s how you get the result from a count query in PHP.
$numresults=mysql_query(“SELECT COUNT(*) from links where catagory=”.$catagory.“;”);
$numrows=mysql_num_rows($numresults);
echo $dr[0];
You can run the same query in phpMyAdmin and with explain in front and see how slow / fast the query is.
EXPLAIN SELECT COUNT(*) FROM links where catagory=1;
(or whatever the category name / number is.
I can see 2 things abnormal at the mysqltuner report:
Current MyISAM index space = 0 bytes
Of 16414 temp tables, 99% were created on disk
The first one mean you currently have no indexes in your tables. You need to add proper indexes in order to improve your database queries’ speed.
The second is because of the first one, when there’s no indexes on large tables, mysql need to use disk to prepare data.