Hi,
I have a difficult question now: in fact one that anybody who helps on a forum will hate because the information I can offer is minimal and not complete.
I have a LAMP setup (Fedora 12, Apache 2, MySql 5 and PHP 5) which is exhibiting a very strange behaviour: MySql response times are very slow. Let me explain this better. The website which is built on this server (and which uses a number of MySql queries at each page load) responds fine - or at least with no perceptible slow down. The repsonse time of MySql gets slow only when I run queries through PhpMyAdmin or through some small PHP scripts.
Here are some further notes:
-
By slow response time I mean that although the query is still run in 0.0002s (as reported by PMA) it takes at least 10 seconds before the query is executed.
-
I have tried emptying the DB and leaving only 10 records in one table, but the problem persists.
-
“SELECT * FROM” queries seem to behave normally.
-
Examples of queries that I know have a problem are:
SELECT * FROM table WHERE id = ‘1’
ALTER TABLE
INSERT
I am starting to think this might be a setup issue, but I have no clue where to look. I also understand this is very limited information, so I will appreciate anybody who will take the time to point me in the right direction.
I have tried activating the cache, turning on slow query loggin (no slow queries) and have tried “skip-name-resolve”, but none of these things have worked.
Here below is my my.cnf file.
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1
# Recommendations from MySqlTuner
max_connections = 150
max_user_connections = 150
key_buffer = 36M
myisam_sort_buffer_size = 64M
join_buffer_size = 2M
read_buffer_size = 2M
sort_buffer_size = 3M
open_files_limit = 2048
table_cache = 1024
wait_timeout = 60
interactive_timeout = 30
query_cache_size = 512M
query_cache_limit = 512M
thread_cache_size = 4
table_cache = 256
log-slow-queries = /var/log/mysql-slow.log
long_query_time = 1
max_allowed_packet = 1M
max_connect_errors = 10
max_heap_table_size = 64M
tmp_table_size = 64M
# Disabling symbolic-links is recommended to prevent assorted security risks;
# to do so, uncomment this line:
# symbolic-links=0
# To allow mysqld to connect to a MySQL Cluster management daemon, uncomment
# these lines and adjust the connectstring as needed.
#ndbcluster
#ndb-connectstring="nodeid=4;host=localhost:1186"
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
[ndbd]
# If you are running a MySQL Cluster storage daemon (ndbd) on this machine,
# adjust its connection to the management daemon here.
# Note: ndbd init script requires this to include nodeid!
connect-string="nodeid=2;host=localhost:1186"
[ndb_mgm]
# connection string for MySQL Cluster management tool
connect-string="host=localhost:1186"
Thank you very much for your help,
Adrien