1

One of our company's Apache/PHP/MySQL driven sites is being moved to a new server. It is not yet moved to production, so there are only two users or so. Still, loading a page using the database can take several seconds.

As an example: 13.8s at the first page load, and 0.01-0.02s on subsequent page loads. That's on a page with 30 queries.

I suppose some kind of MySQL cache is involved, but my main problem is: why is the initial load so enormously slow? Can anything be diagnosed from this? Especially, there are some large numbers in the MySQL SHOW STATUS output that worry me.

top output:

top - 12:22:33 up 163 days, 22:45,  7 users,  load average: 0.09, 0.16, 0.29
Tasks: 960 total,   1 running, 959 sleeping,   0 stopped,   0 zombie
Cpu(s):  0.3%us,  0.7%sy,  0.0%ni, 99.0%id,  0.0%wa,  0.0%hi,  0.0%si,  0.0%st
Mem:   1025492k total,   700924k used,   324568k free,    21132k buffers
Swap:  1044476k total,   538772k used,   505704k free,   114796k cached

  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND                                                                                      
21571 anna      20   0  3356 1676  852 R    2  0.2   0:00.18 top                                                                                           
 4903 anna      20   0  6176 1824  628 S    0  0.2   0:02.94 screen                                                                                        
    1 root      20   0  3312  688  404 S    0  0.1   1:00.89 init                                                                                          
    2 root      20   0     0    0    0 S    0  0.0   0:00.19 kthreadd                                                                                      
    3 root      20   0     0    0    0 S    0  0.0   0:46.33 ksoftirqd/0                                                                                   
    6 root      RT   0     0    0    0 S    0  0.0   0:00.00 migration/0                                                                                   
    7 root      RT   0     0    0    0 S    0  0.0   0:00.00 migration/1                                                                                   
    9 root      20   0     0    0    0 S    0  0.0   1:49.59 ksoftirqd/1                                                                                   
   11 root       0 -20     0    0    0 S    0  0.0   0:00.00 cpuset         

MySQL SHOW STATUS

Variable_name   Value
Aborted_clients 1
Aborted_connects    19
Binlog_cache_disk_use   0
Binlog_cache_use    0
Bytes_received  128
Bytes_sent  163
Compression OFF
Connections 160078
Created_tmp_disk_tables 0
Created_tmp_files   23
Created_tmp_tables  0
Delayed_errors  0
Delayed_insert_threads  0
Delayed_writes  0
Flush_commands  1
Innodb_buffer_pool_pages_data   505
Innodb_buffer_pool_pages_dirty  0
Innodb_buffer_pool_pages_flushed    1969738
Innodb_buffer_pool_pages_free   1
Innodb_buffer_pool_pages_misc   6
Innodb_buffer_pool_pages_total  512
Innodb_buffer_pool_read_ahead_rnd   27256
Innodb_buffer_pool_read_ahead_seq   59242
Innodb_buffer_pool_read_requests    3503466291
Innodb_buffer_pool_reads    325582
Innodb_buffer_pool_wait_free    0
Innodb_buffer_pool_write_requests   214340468
Innodb_data_fsyncs  521160
Innodb_data_pending_fsyncs  0
Innodb_data_pending_reads   0
Innodb_data_pending_writes  0
Innodb_data_read    1133531136
Innodb_data_reads   478093
Innodb_data_writes  1008163
Innodb_data_written 1610111488
Innodb_dblwr_pages_written  1969738
Innodb_dblwr_writes 103107
Innodb_log_waits    1
Innodb_log_write_requests   27797124
Innodb_log_writes   261768
Innodb_os_log_fsyncs    314984
Innodb_os_log_pending_fsyncs    0
Innodb_os_log_pending_writes    0
Innodb_os_log_written   1461624320
Innodb_page_size    16384
Innodb_pages_created    10456
Innodb_pages_read   1379772
Innodb_pages_written    1969738
Innodb_row_lock_current_waits   0
Innodb_row_lock_time    94041908
Innodb_row_lock_time_avg    1324
Innodb_row_lock_time_max    6885
Innodb_row_lock_waits   71018
Innodb_rows_deleted 525
Innodb_rows_inserted    368856
Innodb_rows_read    1557304555
Innodb_rows_updated 104987256
Key_blocks_not_flushed  0
Key_blocks_unused   14495
Key_blocks_used 4
Key_read_requests   52709
Key_reads   15
Key_write_requests  671
Key_writes  94
Last_query_cost 0.000000
Max_used_connections    7
Not_flushed_delayed_rows    0
Open_files  16
Open_streams    0
Open_table_definitions  187
Open_tables 64
Opened_files    582769
Opened_table_definitions    0
Opened_tables   0
Prepared_stmt_count 0
Qcache_free_blocks  1023
Qcache_free_memory  11753872
Qcache_hits 12352457
Qcache_inserts  107355
Qcache_lowmem_prunes    0
Qcache_not_cached   10119746
Qcache_queries_in_cache 2750
Qcache_total_blocks 6655
Queries 23924377
Questions   2
Rpl_status  NULL
Select_full_join    0
Select_full_range_join  0
Select_range    0
Select_range_check  0
Select_scan 0
Slave_open_temp_tables  0
Slave_retried_transactions  0
Slave_running   OFF
Slow_launch_threads 0
Slow_queries    0
Sort_merge_passes   0
Sort_range  0
Sort_rows   0
Sort_scan   0
Ssl_accept_renegotiates 0
Ssl_accepts 0
Ssl_callback_cache_hits 0
Ssl_cipher  
Ssl_cipher_list 
Ssl_client_connects 0
Ssl_connect_renegotiates    0
Ssl_ctx_verify_depth    0
Ssl_ctx_verify_mode 0
Ssl_default_timeout 0
Ssl_finished_accepts    0
Ssl_finished_connects   0
Ssl_session_cache_hits  0
Ssl_session_cache_misses    0
Ssl_session_cache_mode  NONE
Ssl_session_cache_overflows 0
Ssl_session_cache_size  0
Ssl_session_cache_timeouts  0
Ssl_sessions_reused 0
Ssl_used_session_cache_entries  0
Ssl_verify_depth    0
Ssl_verify_mode 0
Ssl_version 
Table_locks_immediate   18960035
Table_locks_waited  520
Tc_log_max_pages_used   0
Tc_log_page_size    0
Tc_log_page_waits   0
Threads_cached  6
Threads_connected   1
Threads_created 7
Threads_running 1
Uptime  13580879
Uptime_since_flush_status   13580879

If no-one can see anything obvious from there, I suppose I'll have to start timing individual queries and portions of the code.

This does not happen on pages that use few database queries, so I'm ruling out network problems.

1 Answer 1

0

First thing to check is that name resolving is disabled, and authentication is done based on IP address, not name. This can introduce huge latency for query operations.

Yes, running a query directly against the data on disk is much, much slower than fetching it from the query cache - a large proportion of your queries are being resolved from the Query cache, but making it any bigger is not going to have a huge impact.

Whether your query performance is the issue will be evident from your slow query log - if you're using anything less than 5.1, upgrade your DB so you can set a slow query threshold of 0 seconds (i.e. everything gets logged) and start using mysqldumpslow to analyse the data.

It looks as if your tables are in innodb - looking at what you've published here, there are problems with your config. Assuming this a dedicated DB server, and not using MyISAM then you need a much bigger innodb_buffer_pool_size (not shown in metrics quoted?)

2
  • Thanks! I didn't know of the slow query log. It simplifies things a lot. Turns out a specific subquery was to blame for the slow-down.
    – Anna
    Aug 17, 2012 at 11:10
  • innodb_buffer_pool_size was set to 8MB. The server is dedicated to Apache and the database, so I'll increase that one a lot. Thanks again.
    – Anna
    Aug 17, 2012 at 11:14

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .