quizzical Posted May 23, 2010 Share Posted May 23, 2010 Hello, I have no great knowledge of mysql and this is my first post here as well, so please bear with me. I've a website running on a apache server of my hosting with php/mysql cms joomla It has 15000 visits a day, 5000 records content, but now there is a cpu issue. What can I do? I tried to do something, modified the table fields type to find the best one, I think I could do something with indexes, but I'm not sure (there are many fulltext indexes should I clear some?) This is my SHOW GLOBAL STATUS of 5 minutes ago (maybe could be useful?) Aborted_clients 30 Aborted_connects 1 Binlog_cache_disk_use 0 Binlog_cache_use 0 Bytes_received 3558625249 Bytes_sent 33791461723 Com_admin_commands 117328 Com_alter_db 6 Com_alter_table 1731 Com_analyze 0 Com_backup_table 0 Com_begin 2532 Com_call_procedure 4 Com_change_db 231729 Com_change_master 0 Com_check 46 Com_checksum 0 Com_commit 2534 Com_create_db 8 Com_create_function 0 Com_create_index 0 Com_create_table 2329 Com_create_user 0 Com_dealloc_sql 19 Com_delete 330211 Com_delete_multi 395 Com_do 0 Com_drop_db 4 Com_drop_function 0 Com_drop_index 0 Com_drop_table 1266 Com_drop_user 0 Com_execute_sql 19 Com_flush 90 Com_grant 129 Com_ha_close 0 Com_ha_open 0 Com_ha_read 0 Com_help 0 Com_insert 116312 Com_insert_select 4291 Com_kill 0 Com_load 0 Com_load_master_data 0 Com_load_master_table 0 Com_lock_tables 13442 Com_optimize 135 Com_preload_keys 0 Com_prepare_sql 19 Com_purge 0 Com_purge_before_date 0 Com_rename_table 0 Com_repair 1 Com_replace 994 Com_replace_select 1 Com_reset 0 Com_restore_table 0 Com_revoke 0 Com_revoke_all 0 Com_rollback 1 Com_savepoint 0 Com_select 17912935 Com_set_option 330201 Com_show_binlog_events 0 Com_show_binlogs 20 Com_show_charsets 7 Com_show_collations 7 Com_show_column_types 0 Com_show_create_db 0 Com_show_create_table 28316 Com_show_databases 3039 Com_show_errors 0 Com_show_fields 32868 Com_show_grants 1862 Com_show_innodb_status 0 Com_show_keys 34 Com_show_logs 0 Com_show_master_status 0 Com_show_ndb_status 0 Com_show_new_master 0 Com_show_open_tables 0 Com_show_privileges 0 Com_show_processlist 236 Com_show_slave_hosts 0 Com_show_slave_status 0 Com_show_status 1 Com_show_storage_engines 0 Com_show_tables 25881 Com_show_triggers 30775 Com_show_variables 2229 Com_show_warnings 2 Com_slave_start 0 Com_slave_stop 0 Com_stmt_close 19 Com_stmt_execute 19 Com_stmt_fetch 0 Com_stmt_prepare 19 Com_stmt_reset 0 Com_stmt_send_long_data 0 Com_truncate 1339 Variable_name Value Com_unlock_tables 13456 Com_update 508008 Com_update_multi 11711 Com_xa_commit 0 Com_xa_end 0 Com_xa_prepare 0 Com_xa_recover 0 Com_xa_rollback 0 Com_xa_start 0 Compression OFF Connections 216954 Created_tmp_disk_tables 293484 Created_tmp_files 24995 Created_tmp_tables 815086 Delayed_errors 0 Delayed_insert_threads 0 Delayed_writes 4 Flush_commands 9 Handler_commit 29258 Handler_delete 194282 Handler_discover 0 Handler_prepare 0 Handler_read_first 1750034 Handler_read_key 113409239 Handler_read_next 310121699 Handler_read_prev 619221 Handler_read_rnd 40776746 Handler_read_rnd_next 551605997 Handler_rollback 2 Handler_savepoint 0 Handler_savepoint_rollback 0 Handler_update 26143205 Handler_write 75185405 Innodb_buffer_pool_pages_data 494 Innodb_buffer_pool_pages_dirty 0 Innodb_buffer_pool_pages_flushed 7557 Innodb_buffer_pool_pages_free 0 Innodb_buffer_pool_pages_misc 18 Innodb_buffer_pool_pages_total 512 Innodb_buffer_pool_read_ahead_rnd 1889 Innodb_buffer_pool_read_ahead_seq 593 Innodb_buffer_pool_read_requests 9386837 Innodb_buffer_pool_reads 57975 Innodb_buffer_pool_wait_free 0 Innodb_buffer_pool_write_requests 921547 Innodb_data_fsyncs 11019 Innodb_data_pending_fsyncs 0 Innodb_data_pending_reads 0 Innodb_data_pending_writes 0 Innodb_data_read 1355386880 Innodb_data_reads 63755 Innodb_data_writes 16198 Innodb_data_written 311533568 Innodb_dblwr_pages_written 7557 Innodb_dblwr_writes 413 Innodb_log_waits 7 Innodb_log_write_requests 146743 Innodb_log_writes 9859 Innodb_os_log_fsyncs 10192 Innodb_os_log_pending_fsyncs 0 Innodb_os_log_pending_writes 0 Innodb_os_log_written 63714816 Innodb_page_size 16384 Innodb_pages_created 954 Innodb_pages_read 82593 Innodb_pages_written 7557 Innodb_row_lock_current_waits 0 Innodb_row_lock_time 0 Innodb_row_lock_time_avg 0 Innodb_row_lock_time_max 0 Innodb_row_lock_waits 0 Innodb_rows_deleted 30283 Innodb_rows_inserted 62721 Innodb_rows_read 2523567 Innodb_rows_updated 46209 Key_blocks_not_flushed 0 Key_blocks_unused 6381 Key_blocks_used 6694 Key_read_requests 445031333 Key_reads 8560895 Key_write_requests 2541748 Key_writes 884854 Last_query_cost 0.000000 Max_used_connections 32 Not_flushed_delayed_rows 0 Open_files 127 Open_streams 0 Open_tables 64 Opened_tables 2199077 Prepared_stmt_count 0 Qcache_free_blocks 0 Qcache_free_memory 0 Qcache_hits 0 Qcache_inserts 0 Qcache_lowmem_prunes 0 Qcache_not_cached 0 Qcache_queries_in_cache 0 Qcache_total_blocks 0 Queries 19958703 Questions 19957727 Variable_name Value Rpl_status NULL Select_full_join 44589 Select_full_range_join 1298 Select_range 633639 Select_range_check 41142 Select_scan 2071447 Slave_open_temp_tables 0 Slave_retried_transactions 0 Slave_running OFF Slow_launch_threads 0 Slow_queries 4 Sort_merge_passes 13380 Sort_range 1562053 Sort_rows 92144621 Sort_scan 971657 Table_locks_immediate 20889474 Table_locks_waited 1510 Tc_log_max_pages_used 0 Tc_log_page_size 0 Tc_log_page_waits 0 Threads_cached 0 Threads_connected 3 Threads_created 216949 Threads_running 1 Uptime 70221 Quote Link to comment Share on other sites More sharing options...
Mchl Posted May 23, 2010 Share Posted May 23, 2010 If you can, try enabling slow_query log and see what kind of queries take most time to execute. Then you can focus on optimising tables/queries involved Quote Link to comment Share on other sites More sharing options...
quizzical Posted May 24, 2010 Author Share Posted May 24, 2010 Thank you Mchl for your reply. This is the table Edit Delete id int(11) unsigned NO PRI NULL auto_increment link varchar(100) NO NULL link2 varchar(100) NO NULL link3 varchar(100) NO NULL link4 varchar(100) NO NULL link5 varchar(100) NO NULL link6 varchar(100) NO NULL link7 varchar(100) NO NULL link8 varchar(100) NO NULL link9 varchar(100) NO NULL title varchar(100) NO NULL alias varchar(100) NO NULL title_alias varchar(100) NO NULL titolo_originale varchar(100) NO NULL genere varchar(30) NO NULL genere_due varchar(30) NO NULL paese varchar(100) NO NULL anno varchar(4) NO NULL durata varchar(3) NO NULL regia varchar(200) NO NULL attori varchar(250) NO NULL altri_attori text NO MUL NULL locandina varchar(100) NO NULL locandina_due varchar(100) NO NULL locandina_tre varchar(100) NO NULL locandina_quattro varchar(100) NO NULL locandina_cinque varchar(100) NO NULL introtext varchar(0) NO NULL fulltext mediumtext NO NULL state tinyint(3) NO MUL 0 sectionid int(11) unsigned NO MUL 0 mask int(11) unsigned NO MUL 0 catid int(11) unsigned NO MUL 0 created datetime NO 0000-00-00 00:00:00 created_by int(11) unsigned NO 0 created_by_alias varchar(100) NO modified datetime NO 0000-00-00 00:00:00 modified_by int(11) unsigned NO 0 checked_out int(11) unsigned NO MUL 0 checked_out_time datetime NO 0000-00-00 00:00:00 publish_up datetime NO 0000-00-00 00:00:00 publish_down datetime NO 0000-00-00 00:00:00 images varchar(0) NO NULL urls varchar(0) NO NULL attribs varchar(250) NO NULL version int(11) unsigned NO 1 parentid int(11) unsigned NO 0 ordering int(11) NO 0 metakey varchar(100) NO NULL metadesc varchar(100) NO NULL access int(11) unsigned NO MUL 0 hits int(11) unsigned NO 0 metadata tinyint(4) NO NULL and indexes arco_content 0 PRIMARY 1 id A 4647 NULL NULL BTREE arco_content 1 idx_section 1 sectionid A 1 NULL NULL BTREE arco_content 1 idx_access 1 access A 1 NULL NULL BTREE arco_content 1 idx_checkout 1 checked_out A 1 NULL NULL BTREE arco_content 1 idx_state 1 state A 1 NULL NULL BTREE arco_content 1 idx_catid 1 catid A 3 NULL NULL BTREE arco_content 1 idx_mask 1 mask A 1 NULL NULL BTREE arco_content 1 altri_attori 1 altri_attori NULL 1 NULL NULL FULLTEXT Do you reckon there are too many fields? Quote Link to comment Share on other sites More sharing options...
Mchl Posted May 24, 2010 Share Posted May 24, 2010 There are certainly some fields with some crazy datatypes selected. VARCHAR(0) ?? What's the point of that? The table could also be further normalised (for example move link* fields to another table) Anyway, without knowing how queries utlise this table, it's hard to give any meaningful advice really. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.