Something causes slow performace in specific server actions

[Cedis]

Hey! Gotta get back on this (had a previous topic without real answers).

My issues are:

- MySQL imports are very slow: 1-3 Mb files can take 5-15 minutes (upload time of file excluded)
- CURL/file_get_contents requests are incredibly slow also (hence WP admin is slow and similar CMS interfaces that rely on external content on occation)

These same requests on my regular computer (laptop) where I develope site takes less than a second...
So that's why I must find the reason for my production server having problems in these two areas I've mentioned.

Previously I had only really noted the issue with the SQL imports. But now that I see CURL/file_get_content actions also are very slow there must common cause for these two things being very slow. Probably a simple setting somewhere!

That's where I need your help!

The server is a fairly new (< 1 year) dedicated machine with the following specs:

> Motherboard: 1 x CPU, upp till 32 GB RAM
> CPU: 1 x Intel 2-CORE 2.6 Ghz
> Memory: 8 GB
> RAID: LSI 3Ware 9650SE SATA RAID, 2 ports
> Storage: SATA, 2 x 750GB

The server only have 3 websites or so running on it with low traffic. So it's basically under no load. cPanel reports Load Averages: 0.10 0.04 0.01.

PHP is configured as suPHP, is that possibly the reason?

 

[Cedis]

I can add that I tested FCGI, no difference as far as MySQL import goes or CURL. But a bit faster page loads indeed.

Who can I pay to answer my question(s)? I want an answer on this. Please...!

 

[Cedis]

So for the sake of testing.

Local (my computer): curl.php which reads a URL. Take 0.6 seconds.

Prod: curl.php, takes 6 seconds!

10 times longer... why!?

 

[Cedis]

Hmm! Actually noticed that CURL crawled the URL in the same 0.6 seconds but the command:

die(strlen($output)) is what's taking time to display on screen.

I'm completely lost now.

How can die() be causing such a consistent slow down of the system?

 

[thinkbot]

post the top result

probably you are swapping

 

[Cedis]

(i'm a newbie when it comes to running servers so please excuse me if I missunderstand you)

Here's a paste from top:

Tasks: 169 total, 1 running, 168 sleeping, 0 stopped, 0 zombie
Cpu(s): 0.3%us, 0.1%sy, 0.0%ni, 99.0%id, 0.7%wa, 0.0%hi, 0.0%si, 0.0%st
Mem: 8032556k total, 7400720k used, 631836k free, 273820k buffers
Swap: 4194296k total, 16924k used, 4177372k free, 6168084k cached

PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
709 root 20 0 15020 1244 896 R 3.7 0.0 0:00.04 top
1 root 20 0 19328 956 748 S 0.0 0.0 7:01.22 init
2 root 20 0 0 0 0 S 0.0 0.0 0:24.76 kthreadd
3 root RT 0 0 0 0 S 0.0 0.0 0:26.60 migration/0
4 root 20 0 0 0 0 S 0.0 0.0 0:58.25 ksoftirqd/0
5 root RT 0 0 0 0 S 0.0 0.0 0:00.00 migration/0
6 root RT 0 0 0 0 S 0.0 0.0 0:24.18 watchdog/0
7 root RT 0 0 0 0 S 0.0 0.0 0:15.29 migration/1
8 root RT 0 0 0 0 S 0.0 0.0 0:00.00 migration/1
9 root 20 0 0 0 0 S 0.0 0.0 1:33.77 ksoftirqd/1
10 root RT 0 0 0 0 S 0.0 0.0 0:20.02 watchdog/1
11 root RT 0 0 0 0 S 0.0 0.0 0:37.50 migration/2
12 root RT 0 0 0 0 S 0.0 0.0 0:00.00 migration/2
13 root 20 0 0 0 0 S 0.0 0.0 1:39.42 ksoftirqd/2
14 root RT 0 0 0 0 S 0.0 0.0 0:20.43 watchdog/2
15 root RT 0 0 0 0 S 0.0 0.0 0:53.33 migration/3
16 root RT 0 0 0 0 S 0.0 0.0 0:00.00 migration/3

So swap is half of total memory and the usage appear very low, only 17 mb of 4000 in swap.

So it shouldn't be swap then?

Just simply poor disk performance?

 

[Cedis]

Digging further into this I'm looking at the phpmyadmin live mysql connection graph.

This PHP file that have no MySQL connections, appears to be causing mysql connections. Really? Should it?

On my computer with dev server there is no mysql connection what so ever.

There must be something wrong in how PHP is configured if each file calling executes a MySQL connection?!

 

[Cedis]

I'm about to sleep soon but I really hope some here can make a smart guess at what this is all about.

Here's the memory stat:

[email protected] [~/tmp]# free -m -t
total used free shared buffers cached
Mem: 7844 7244 599 0 274 6043
-/+ buffers/cache: 926 6917
Swap: 4095 16 4079
Total: 11940 7261 4678

So looking at the -+ buffer thingy, there is 6917 Mb of ram left out of the 8Gb or so available.

 

[thinkbot]

strlen is quite slow, please post full code that you test
and how much strlen returns ?

 

[Cedis]

Can an actual human being try to make a guess at what the problem is?

 

[thinkbot]

I'm a human being,
In programming, you don't guess, but check

 

[Cedis]

I'm a human being,
In programming, you don't guess, but check

Oh haha sorry. Your name made me wonder.

Anyhow I found the reason for slow php and it had to do with a nameserver ip that stopped working. Switched it out and now all pages load fast.

Nut i still have the problem with mysql being very slow importing SQL files.

What should I monitor to debug the low mysql?

 

[thinkbot]

so you didnt even make sure that
die(strlen($output))

is the problem, uhhh

as I wrote, not guess, but check


you are importing to myisam or innodb ?
what are your my.cnf mysql settings ?

 

[Cedis]

Don't be an jerk dude. I said I was sorry for thinking you were a bot answering weirdly.

I've checked numerous stuff and I've come to the conclusion it's the harddrives. They're extremely slow.

I did these tests here:
How fast is your disk? | LinuxInsight

And compared with what other people have got as result and my disks are fair bit under the worst of the worst result you can find.

So I'm talking with the webhost service appartment and looking at a replacement of the whole server or switching out the disks for SSD:s.

 

[thinkbot]

but did you check and optimized your MySQL settings ?

 

[faisikhan]

you are importing to myisam or innodb ?
what are your my.cnf mysql settings ?

Well Cedis, you need to provide us the settings you are using in your my.cnf file so we can suggest optimization or check the given values. Also please share what's the size of your Database?

 

[Cedis]

Of course I can post the settings.

Here they are.

[mysqld]
collation_server=utf8_swedish_ci
character_set_server=utf8
default-storage-engine=MyISAM
innodb_file_per_table=1

#eget
key_buffer = 72M
max_allowed_packet = 16M
thread_stack = 1M
thread_cache_size = 0 #default
max_connections = 100
tmp_table_size = 168M #tidigare 96M
max_heap_table_size = 168M #tidigare 96M
table_cache = 2500 #tidigare 1500
thread_concurrency = 10
query_cache_limit = 5M
query_cache_size = 10M
query_cache_type = 1
query_prealloc_size = 65536
query_alloc_block_size = 131072

read_rnd_buffer_size = 1M
myisam_sort_buffer_size = 128M
join_buffer_size = 1M
read_buffer_size = 1M
sort_buffer_size = 1M

long_query_time=1
log-slow-queries=/var/lib/mysql/slow.log

open_files_limit=3998
[mysqldump]
quick
quote-names
max_allowed_packet = 16M

[mysql]
#no-auto-rehash # faster start of mysql but no tab completition

[isamchk]
key_buffer = 256M
sort_buffer_size = 256M
read_buffer = 32M
write_buffer = 32M

[myisamchk]
key_buffer = 256M
sort_buffer_size = 256M
read_buffer = 32M
write_buffer = 32M

I've tried different settings but they make no difference.

- - - Updated - - -

Well Cedis, you need to provide us the settings you are using in your my.cnf file so we can suggest optimization or check the given values. Also please share what's the size of your Database?

The databases are only 1-5 mb a piece, so not much to optimize. The performance issue only relates to inserts, and SQL-imports are especially slow.

Either way, a new VPS server I setup did the same SQL import in < 1 second, while the current dedicated server does in it 15!! seconds...! The only difference really between the servers is SSD vs old fashion slow harddrives. Even my synology NAS-servers are faster at importing to MysQL than my dedicated server....

 

[Cedis]

Okay, I still have this problem.

phpmyadmin is really fast mysqldumping and sending for download. Really quick.

But sending the same stuff back upp, 1-2 mb currently takes 15-20 seconds.
any other server I've tested this on does it under 1 sec. From old NAS at home to any other server, or computer.

I need to know why this happens.

Ex test

sudo hdparm -tT /dev/sda

/dev/sda:
 Timing cached reads:   10948 MB in  2.00 seconds = 5478.27 MB/sec
 Timing buffered disk reads: 288 MB in  3.00 seconds =  95.94 MB/sec

 

[Cedis]

Current my.cnf settings

[mysqld]
collation_server=utf8_swedish_ci
character_set_server=utf8
default-storage-engine=MyISAM
#innodb_file_per_table=1

#eget
key_buffer              = 72M
max_allowed_packet	= 16M
thread_stack            = 1M
thread_cache_size	= 0 #default
max_connections        = 100
tmp_table_size         = 168M #tidigare 96M
max_heap_table_size    = 168M #tidigare 96M
table_cache            = 2500 #tidigare 1500
thread_concurrency     = 10
query_cache_limit	= 5M
query_cache_size        = 10M
query_cache_type        = 1
query_prealloc_size     = 65536
query_alloc_block_size = 131072

read_rnd_buffer_size = 1M
myisam_sort_buffer_size = 128M
join_buffer_size = 1M
read_buffer_size = 1M
sort_buffer_size = 1M

#InnoDB
innodb_buffer_pool_size = 6G #should be 70% of RAM
innodb_log_file_size = 256M 
innodb_log_buffer_size = 4M
innodb_flush_log_at_trx_commit = 2 #default = 1
innodb_thread_concurrency = 8
innodb_flush_method = O_DIRECT
innodb_file_per_table = 1 #1 = use