Not able to upload to Mysql server 5.5.34, it stucks randomly
 

Hello all,

I am facing a problem regarding uploading to our mysql server.
We have a lamp server in cloud.Our problem is that our designer team is uploading various images,text to that serevr constantly,but at random time the server not responds and they are not able to upload. But after some time server becomes normal and they can upload with no problem.
I have used mysqltuner and these are the outputs------

[OK] Currently running supported MySQL version 5.5.34
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MRG_MYISAM
[--] Data in InnoDB tables: 450M (Tables: 379)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
[!!] Total fragmented tables: 379

-------- Security Recommendations -------------------------------------------
[OK] All database users have passwords assigned

-------- Performance Metrics -------------------------------------------------
[--] Up for: 5d 23h 18m 16s (7M q [15.210 qps], 3M conn, TX: 8B, RX: 2B)
[--] Reads / Writes: 99% / 1%
[--] Total buffers: 168.0M global + 2.8M per thread (151 max threads)
[OK] Maximum possible memory usage: 583.2M (14% of installed RAM)
[OK] Slow queries: 0% (19/7M)
[OK] Highest usage of available connections: 19% (30/151)
[OK] Key buffer size / total MyISAM indexes: 8.0M/121.0K
[OK] Key buffer hit rate: 100.0% (14M cached / 74 reads)
[!!] Query cache is disabled
[OK] Sorts requiring temporary tables: 0% (38 temp sorts / 2M sorts)
[!!] Joins performed without indexes: 4651
[OK] Temporary tables created on disk: 0% (7K on disk / 3M total)
[!!] Thread cache is disabled
[!!] Table cache hit rate: 6% (400 open / 5K opened)
[OK] Open file limit used: 1% (16/1K)
[OK] Table locks acquired immediately: 100% (20M immediate / 20M locks)
[!!] InnoDB buffer pool / data size: 128.0M/450.2M
[OK] InnoDB log waits: 0
-------- Recommendations -----------------------------------------------------
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
Adjust your join queries to always utilize indexes
Set thread_cache_size to 4 as a starting value
Increase table_open_cache gradually to avoid file descriptor limits
Read this before increasing table_open_cache over 64: http://bit.ly/1mi7c4C
Variables to adjust:
query_cache_size (>= 8M)
join_buffer_size (> 128.0K, or always use indexes with joins)
thread_cache_size (start at 4)
table_open_cache (> 400)
innodb_buffer_pool_size (>= 450M)

Can anyone explains what are the probable reasons and how should I troubleshoot the problem. Thank you

If your designers upload images then it is likely that sometimes there are problems with the bandwidth in general. Have you checked the mysql logs? Could be in /var/log/mysql.log

Thank you j-ray. Following are my mysqld logs

140801 10:31:35 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended
140801 10:31:35 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
140801 10:31:35 [Note] Plugin 'FEDERATED' is disabled.
140801 10:31:35 InnoDB: The InnoDB memory heap is disabled
140801 10:31:35 InnoDB: Mutexes and rw_locks use GCC atomic builtins
140801 10:31:35 InnoDB: Compressed tables use zlib 1.2.3
140801 10:31:35 InnoDB: Using Linux native AIO
140801 10:31:35 InnoDB: Initializing buffer pool, size = 128.0M
140801 10:31:35 InnoDB: Completed initialization of buffer pool
140801 10:31:35 InnoDB: highest supported file format is Barracuda.
140801 10:31:35 InnoDB: Waiting for the background threads to start
140801 10:31:36 InnoDB: 5.5.34 started; log sequence number 3009579732
140801 10:31:36 [Note] Server hostname (bind-address): '0.0.0.0'; port: 3306
140801 10:31:36 [Note] - '0.0.0.0' resolves to '0.0.0.0';
140801 10:31:36 [Note] Server socket created on IP: '0.0.0.0'.
140801 10:31:36 [Warning] 'user' entry 'root@KRT-VM02' ignored in --skip-name-resolve mode.
140801 10:31:36 [Note] Event Scheduler: Loaded 5 events
140801 10:31:36 [Note] /usr/libexec/mysqld: ready for connections.
Version: '5.5.34' socket: '/var/lib/mysql/mysql.sock' port: 3306 MySQL Community Server (GPL)

I am not able to find any error here. Kindly help.

How do you upload the images to the mysql server?

We use LAMP server...so php is used for uploading.

You can set the maximum upload size in php.ini. If files are bigger than that value they will not arrive at the database. Another critical spot is the max_executiontime in php.ini. If the uploads exceeds this value the script will be stopped. So take a look at these values in php.ini.

The execution time of course depends on the traffic on the server...

I checked in php.ini

upload_max_filesize = 1M
max execution time is 30 secs

but designer team can upload with no problem, randomly it causes problem..they basically upload excell sheets which average is around 100kb to 200 kb. So max upload filesize is not causing problem. Anyway thank you.

1M is extremely small. Are you only uploading thumbnails? On our working machines that are used by some employees we have values like
upload_max_filesize = 100M
max_execution_time= 900 //15 Minutes

But if you think this is not the reason then it must be a bottleneck in the network. At least it is not the mysql it seems.