simple phpmyadmin command causes load to spike

[bmett]

Hi there,

On one of our (older) servers we experience some strange behaviour when using phpMyAdmin.

Every single command (like opening a table, deleting a row, creating a new table, search, etc.) causes the load to spike and the command takes unusally long (up to 5-10 seconds). This is obviously quite a long time to open a table. Mind you the tables we talk about here are not huge or anything.

Strangely enough the websites on the server all load fine and don't seem to have problems accessing the databases.

Hope someone can help.

Cheers

 

[cPanelMichael]

Hello

You can try forcing an update of phpMyAdmin via:

/usr/local/cpanel/bin/updatephpmyadmin --force

However, you should also check to make sure the database you are working with is not corrupted. Try repairing/optimizing it's tables to see if that helps. Also, do you use any custom configuration values in the /etc/my.cnf file? If so, you may want to temporarily disable them, restart MySQL, and see if the issue persists.

Thank you.

 

[bmett]

Hi Michel,

Thanks for the reply.

phpMyAdmin is up-to-date (4.0.5).

I repaired/checked/optimised the tables with no change.

Please see below the my.cnf file. Maybe you can see anything unusual in there:

[mysqld]
innodb_buffer_pool_size=20M
safe-show-database
local-infile=0
#datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
skip-locking
skip-innodb
query_cache_limit=8M
query_cache_size=8M
query_cache_type=1
max_connections=150
max_user_connections=10
interactive_timeout=1200
wait_timeout=1200
connect_timeout=6
thread_cache_size=4
key_buffer=512M
key_buffer_size=512M
join_buffer=1M
max_allowed_packet=100M
table_cache=2200
record_buffer=1M
sort_buffer_size=2M
read_buffer_size=2M
read_rnd_buffer_size=64M
max_connect_errors=10
# Try number of CPU's*2 for thread_concurrency
thread_concurrency=4
myisam_sort_buffer_size=64M
#log-bin
server-id=1
log-error=/var/log/mysql/error.log
log-slow-queries=/var/lib/mysql/slow.log
tmp_table_size=40M

open_files_limit=5000
[mysql.server]
user=mysql
#basedir=/var/lib

[safe_mysqld]
err-log=/var/log/mysqld.log
pid-file=/var/lib/mysql/mysql.pid
log-error=/var/log/mysql/error.log

[mysqldump]
quick
max_allowed_packet=16M

[mysql]
no-auto-rehash
#safe-updates

[isamchk]
key_buffer=32M
sort_buffer=32M
read_buffer=16M
write_buffer=16M

[myisamchk]
key_buffer=32M
sort_buffer=32M
read_buffer=16M
write_buffer=16M

 

[cPanelMichael]

It's difficult to pinpoint the issue to any specific configuration value. I recommend disabling all of them temporarily, and see if the issue persists after restarting MySQL. If the issue is resolved, you can start manually adding the entries back in one at a time to see which one is the culprit.

Thank you.

 

[bmett]

Hi Michael,

I had no luck so far with modifing the my.cnf file.

I noticed though, that the accounts I work on with phpMyAdmin have a lot of core dump file in them. They obviously result from the use of phpMyAdmin.

Any idea what that could mean?

Cheers,

 

[MaestriaNick]

It is only with phpmyadmin ? Are you able to open the tables etc directly connecting to the mysql server ? (i mean, connecting to mysql after ssh ) ? In my opinion mysql have nothing to do with those coredump files.

 

[MatthewM.]

Hello,

I don't quite know whether or not this is a phpmyadmin issue. What I mean is just based by your my.cnf you are using no later than 5.1(MYSQL won't start with a large number of those variables in anything past 5.1). That being said it's more likely you are on an older version of mysql which is also on an old server(old hardware) and that could be causing issues.

When you run your queries in PMA what exactly is causing the load spike? It would be worth a shot to run top/a command from PMA to see if it's PMA or mysql.

Also, PMA has a query function within it, you can actually run the same query you would from the mysql prompt in PMA. You may want to run the same query in both and see what the actual difference in time/load is.

Yours truly,
phpMattadmin
P.S.-That nick name does actually get used...unfortunately