MySQL Performance

Hi,

I just installed MySQL 5.0.45-log Source distribution on my Debian 6.0.6 server.

I installed it under my user home directory like I'm used to doing.

But this time the queries are extremely slow to run.

Running a create table or a database drop takes ages. I can literally watch tv in the meantime.

So I did a profiling of the database drop statement.

mysql> SHOW PROFILES; +----------+--------------+------------------------------+ | Query_ID | Duration | Query | +----------+--------------+------------------------------+ | 1 | 369.54719400 | drop database db_madeintouch | | 2 | 0.00004600 | SELECT DATABASE() | +----------+--------------+------------------------------+ 2 rows in set (0.00 sec) mysql> SHOW PROFILE FOR QUERY 1; +----------------------+-----------+ | Status | Duration | +----------------------+-----------+ | (initialization) | 0.000001 | | checking permissions | 369.54705 | | Opening table | 0.000103 | | System lock | 0.000003 | | Table lock | 0.000018 | | query end | 0.000004 | | freeing items | 0.000004 | | logging slow query | 0.000002 | +----------------------+-----------+ 8 rows in set (0.00 sec)
We can see the time it takes for the checking of permissions is of 369 seconds.

I also did a show status of the InnoDB engine.

mysql> show engine innodb status\G *************************** 1. row *************************** Status: ===================================== 130415 23:11:27 INNODB MONITOR OUTPUT ===================================== Per second averages calculated from the last 9 seconds ---------- SEMAPHORES ---------- OS WAIT ARRAY INFO: reservation count 781, signal count 781 Mutex spin waits 0, rounds 8629, OS waits 231 RW-shared spins 379, OS waits 190; RW-excl spins 380, OS waits 342 ------------ TRANSACTIONS ------------ Trx id counter 0 7599 Purge done for trx's n:o < 0 7430 undo n:o < 0 0 History list length 3 Total number of lock structs in row lock hash table 0 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 0 0, not started, process no 14133, OS thread id 140617364518656 MySQL thread id 16, query id 1305 localhost stephane checking permissions drop database db_madeintouch ---TRANSACTION 0 0, not started, process no 14133, OS thread id 140617364383488 MySQL thread id 13, query id 1307 localhost stephane show engine innodb status ---TRANSACTION 0 7597, COMMITTED IN MEMORY, process no 14133, OS thread id 140617364518656 dropping table COMMITTING , undo log entries 16 MySQL thread id 16, query id 1305 localhost stephane checking permissions drop database db_madeintouch -------- FILE I/O -------- I/O thread 0 state: waiting for i/o request (insert buffer thread) I/O thread 1 state: waiting for i/o request (log thread) I/O thread 2 state: waiting for i/o request (read thread) I/O thread 3 state: waiting for i/o request (write thread) Pending normal aio reads: 0, aio writes: 0, ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0 Pending flushes (fsync) log: 1; buffer pool: 0 174 OS file reads, 3781 OS file writes, 2099 OS fsyncs 0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s ------------------------------------- INSERT BUFFER AND ADAPTIVE HASH INDEX ------------------------------------- Ibuf: size 1, free list len 0, seg size 2, 0 inserts, 0 merged recs, 0 merges Hash table size 17393, used cells 122, node heap has 1 buffer(s) 0.00 hash searches/s, 0.00 non-hash searches/s --- LOG --- Log sequence number 0 7801057 Log flushed up to 0 7798962 Last checkpoint at 0 7798962 1 pending log writes, 0 pending chkp writes 1535 log i/o's done, 0.00 log i/o's/second ---------------------- BUFFER POOL AND MEMORY ---------------------- Total memory allocated 22136914; in additional pool allocated 1048576 Buffer pool size 512 Free buffers 2 Database pages 509 Modified db pages 18 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages read 183, created 1444, written 6980 0.00 reads/s, 0.00 creates/s, 0.00 writes/s No buffer pool page gets since the last printout -------------- ROW OPERATIONS -------------- 0 queries inside InnoDB, 0 queries in queue 1 read views open inside InnoDB Main thread process no. 14133, id 140617334142720, state: waiting for server activity Number of rows inserted 0, updated 0, deleted 0, read 0 0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s ---------------------------- END OF INNODB MONITOR OUTPUT ============================ 1 row in set (0.00 sec)

And here is my environment.

mysql> SHOW VARIABLES; > +---------------------------------+--------------------------------------------------------------------+ | Variable_name | Value > | > +---------------------------------+--------------------------------------------------------------------+ | auto_increment_increment | 1 > | | auto_increment_offset | 1 > | | automatic_sp_privileges | ON > | | back_log | 50 > | | basedir | > /home/stephane/programs/mysql-5.0.45/install/ | > | binlog_cache_size | 32768 > | | bulk_insert_buffer_size | 8388608 > | | character_set_client | latin1 > | | character_set_connection | latin1 > | | character_set_database | latin1 > | | character_set_filesystem | binary > | | character_set_results | latin1 > | | character_set_server | latin1 > | | character_set_system | utf8 > | | character_sets_dir | > /home/stephane/programs/mysql-5.0.45/install/share/mysql/charsets/ | > | collation_connection | latin1_swedish_ci > | | collation_database | latin1_swedish_ci > | | collation_server | latin1_swedish_ci > | | completion_type | 0 > | | concurrent_insert | 1 > | | connect_timeout | 5 > | | datadir | > /home/stephane/programs/mysql/install/data/ | > | date_format | %Y-%m-%d > | | datetime_format | %Y-%m-%d %H:%i:%s > | | default_week_format | 0 > | | delay_key_write | ON > | | delayed_insert_limit | 100 > | | delayed_insert_timeout | 300 > | | delayed_queue_size | 1000 > | | div_precision_increment | 4 > | | engine_condition_pushdown | OFF > | | expire_logs_days | 0 > | | flush | OFF > | | flush_time | 0 > | | ft_boolean_syntax | + -><()~*:""&| > | | ft_max_word_len | 84 > | | ft_min_word_len | 4 > | | ft_query_expansion_limit | 20 > | | ft_stopword_file | (built-in) > | | group_concat_max_len | 1024 > | | have_archive | NO > | | have_bdb | NO > | | have_blackhole_engine | NO > | | have_compress | YES > | | have_crypt | YES > | | have_csv | NO > | | have_dynamic_loading | YES > | | have_example_engine | NO > | | have_federated_engine | NO > | | have_geometry | YES > | | have_innodb | YES > | | have_isam | NO > | | have_merge_engine | YES > | | have_ndbcluster | NO > | | have_openssl | NO > | | have_ssl | NO > | | have_query_cache | YES > | | have_raid | NO > | | have_rtree_keys | YES > | | have_symlink | YES > | | hostname | server1 > | | init_connect | > | | init_file | > | | init_slave | > | | innodb_additional_mem_pool_size | 1048576 > | | innodb_autoextend_increment | 8 > | | innodb_buffer_pool_awe_mem_mb | 0 > | | innodb_buffer_pool_size | 8388608 > | | innodb_checksums | ON > | | innodb_commit_concurrency | 0 > | | innodb_concurrency_tickets | 500 > | | innodb_data_file_path | ibdata1:10M:autoextend > | | innodb_data_home_dir | > | | innodb_doublewrite | ON > | | innodb_fast_shutdown | 1 > | | innodb_file_io_threads | 4 > | | innodb_file_per_table | OFF > | | innodb_flush_log_at_trx_commit | 1 > | | innodb_flush_method | > | | innodb_force_recovery | 0 > | | innodb_lock_wait_timeout | 50 > | | innodb_locks_unsafe_for_binlog | OFF > | | innodb_log_arch_dir | > | | innodb_log_archive | OFF > | | innodb_log_buffer_size | 1048576 > | | innodb_log_file_size | 5242880 > | | innodb_log_files_in_group | 2 > | | innodb_log_group_home_dir | ./ > | | innodb_max_dirty_pages_pct | 90 > | | innodb_max_purge_lag | 0 > | | innodb_mirrored_log_groups | 1 > | | innodb_open_files | 300 > | | innodb_rollback_on_timeout | OFF > | | innodb_support_xa | ON > | | innodb_sync_spin_loops | 20 > | | innodb_table_locks | ON > | | innodb_thread_concurrency | 8 > | | innodb_thread_sleep_delay | 10000 > | | interactive_timeout | 28800 > | | join_buffer_size | 131072 > | | key_buffer_size | 16384 > | | key_cache_age_threshold | 300 > | | key_cache_block_size | 1024 > | | key_cache_division_limit | 100 > | | language | > /home/stephane/programs/mysql-5.0.45/install/share/mysql/english/ | > | large_files_support | ON > | | large_page_size | 0 > | | large_pages | OFF > | | lc_time_names | en_US > | | license | GPL > | | local_infile | ON > | | locked_in_memory | OFF > | | log | ON > | | log_bin | OFF > | | log_bin_trust_function_creators | OFF > | | log_error | > /home/stephane/programs/mysql/install/mysql.error.log | > | log_queries_not_using_indexes | OFF > | | log_slave_updates | OFF > | | log_slow_queries | ON > | | log_warnings | 1 > | | long_query_time | 10 > | | low_priority_updates | OFF > | | lower_case_file_system | OFF > | | lower_case_table_names | 0 > | | max_allowed_packet | 1047552 > | | max_binlog_cache_size | 18446744073709551615 > | | max_binlog_size | 1073741824 > | | max_connect_errors | 10 > | | max_connections | 100 > | | max_delayed_threads | 20 > | | max_error_count | 64 > | | max_heap_table_size | 16777216 > | | max_insert_delayed_threads | 20 > | | max_join_size | 18446744073709551615 > | | max_length_for_sort_data | 1024 > | | max_prepared_stmt_count | 16382 > | | max_relay_log_size | 0 > | | max_seeks_for_key | 18446744073709551615 > | | max_sort_length | 1024 > | | max_sp_recursion_depth | 0 > | | max_tmp_tables | 32 > | | max_user_connections | 0 > | | max_write_lock_count | 18446744073709551615 > | | multi_range_count | 256 > | | myisam_data_pointer_size | 6 > | | myisam_max_sort_file_size | 9223372036854775807 > | | myisam_recover_options | OFF > | | myisam_repair_threads | 1 > | | myisam_sort_buffer_size | 8388608 > | | myisam_stats_method | nulls_unequal > | | net_buffer_length | 2048 > | | net_read_timeout | 30 > | | net_retry_count | 10 > | | net_write_timeout | 60 > | | new | OFF > | | old_passwords | OFF > | | open_files_limit | 1024 > | | optimizer_prune_level | 1 > | | optimizer_search_depth | 62 > | | pid_file | > /home/stephane/programs/mysql/install/data/server1.pid | > | port | 3306 > | | preload_buffer_size | 32768 > | | profiling | OFF > | | profiling_history_size | 15 > | | protocol_version | 10 > | | query_alloc_block_size | 8192 > | | query_cache_limit | 1048576 > | | query_cache_min_res_unit | 4096 > | | query_cache_size | 0 > | | query_cache_type | ON > | | query_cache_wlock_invalidate | OFF > | | query_prealloc_size | 8192 > | | range_alloc_block_size | 2048 > | | read_buffer_size | 258048 > | | read_only | OFF > | | read_rnd_buffer_size | 258048 > | | relay_log_purge | ON > | | relay_log_space_limit | 0 > | | rpl_recovery_rank | 0 > | | secure_auth | OFF > | | secure_file_priv | > | | server_id | 1 > | | skip_external_locking | ON > | | skip_networking | OFF > | | skip_show_database | OFF > | | slave_compressed_protocol | OFF > | | slave_load_tmpdir | /tmp/ > | | slave_net_timeout | 3600 > | | slave_skip_errors | OFF > | | slave_transaction_retries | 10 > | | slow_launch_time | 2 > | | socket | /tmp/mysql.sock > | | sort_buffer_size | 65528 > | | sql_big_selects | ON > | | sql_mode | > | | sql_notes | ON > | | sql_warnings | OFF > | | ssl_ca | > | | ssl_capath | > | | ssl_cert | > | | ssl_cipher | > | | ssl_key | > | | storage_engine | MyISAM > | | sync_binlog | 0 > | | sync_frm | ON > | | system_time_zone | MSK > | | table_cache | 4 > | | table_lock_wait_timeout | 50 > | | table_type | MyISAM > | | thread_cache_size | 0 > | | thread_stack | 131072 > | | time_format | %H:%i:%s > | | time_zone | SYSTEM > | | timed_mutexes | OFF > | | tmp_table_size | 33554432 > | | tmpdir | /tmp/ > | | transaction_alloc_block_size | 8192 > | | transaction_prealloc_size | 4096 > | | tx_isolation | REPEATABLE-READ > | | updatable_views_with_limit | YES > | | version | 5.0.45-log > | | version_comment | Source distribution > | | version_compile_machine | x86_64 > | | version_compile_os | unknown-linux-gnu > | | wait_timeout | 28800 > | > +---------------------------------+--------------------------------------------------------------------+ 225 rows in set (43.41 sec)

Any benefit from switching the db driver from mysql to mysqli on a php app? I've been searching around and I see arguments for both from a performance perspective. Is there a typical clear cut winner?

MySQL 5.6: Detailed Profiling of SQL Activity
http://www.drdobbs.com/database/detailed-profiling-of-sql-activity-in-my/240154959?pgno=1

Hello,

Recently I got a new server and I have migrated everything from old to new (LAMP).
Without any modification, mysql have started to usage too much CPU and the queries are very slow.

The more strange thing it is that the CPU usage is for the %user and it is always about 30%:

# sar 2 5 Linux 2.6.32-5-amd64 (hostname) 05/14/2013 _x86_64_ (24 CPU) 08:11:59 PM CPU %user %nice %system %iowait %steal %idle 08:13:07 PM all 33.11 0.00 0.77 0.00 0.00 66.12 08:13:09 PM all 32.71 0.00 0.73 0.02 0.00 66.54 08:13:11 PM all 34.51 0.00 0.63 0.00 0.00 64.86 08:13:13 PM all 33.39 0.00 0.42 0.04 0.00 66.15 08:13:15 PM all 33.29 0.00 0.44 0.00 0.00 66.26 Average: all 33.39 0.00 0.60 0.01 0.00 66.00
I don't have memory problem, because the disk is not used (as you can see iowait is in 0%).

The only process is usaging the cpu is mysql:
Tasks: 424 total, 1 running, 422 sleeping, 0 stopped, 1 zombie Cpu(s): 27.3%us, 0.4%sy, 0.0%ni, 72.3%id, 0.1%wa, 0.0%hi, 0.0%si, 0.0%st Mem: 32979568k total, 27494980k used, 5484588k free, 932472k buffers Swap: 7999992k total, 6792k used, 7993200k free, 19934596k cached PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND 31390 mysql 20 0 5542m 5.0g 7976 S 708 15.9 1833:52 mysqld 28063 www-data 20 0 261m 14m 5516 S 2 0.0 0:00.08 apache2 28077 root 20 0 19340 1656 1020 R 1 0.0 0:00.20 top 27948 www-data 20 0 245m 13m 5148 S 1 0.0 0:00.05 apache2 27976 www-data 20 0 245m 14m 5284 S 1 0.0 0:00.17 apache2 28032 www-data 20 0 248m 15m 5280 S 1 0.0 0:00.10 apache2
So it should be the queries, but the queries was running fine in old server. Mysql version, apache versión, and debian version is the same, only have changed the hardware, now 24 cpus, 32GB RAM, better than previous.

In mysql the processlist:
mysql> show full processlist; +-------+------------+-----------+------------+---------+------+----------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Id | User | Host | db | Command | Time | State | Info | +-------+------------+-----------+------------+---------+------+----------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | 10161 | root | localhost | artigoo_es | Query | 0 | NULL | show full processlist | | 22594 | artigoo_es | localhost | artigoo_es | Query | 4 | Copying to tmp table | SELECT t.tag, t.ftag, COUNT(tag) AS total FROM tags AS t JOIN goos AS g ON t.id = g.idgoo WHERE t.ftag="adrid" and g.idstategoo=1 GROUP BY t.tag ORDER BY total desc LIMIT 0,40 | | 22595 | artigoo_es | localhost | artigoo_es | Query | 3 | Copying to tmp table | SELECT t.tag, t.ftag, COUNT(tag) AS total FROM tags AS t JOIN goos AS g ON t.id = g.idgoo WHERE t.ftag="aduana" and g.idstategoo=1 GROUP BY t.tag ORDER BY total desc LIMIT 0,40 | | 22596 | artigoo_es | localhost | artigoo_es | Query | 3 | Copying to tmp table | SELECT t.tag, t.ftag, COUNT(tag) AS total FROM tags AS t JOIN goos AS g ON t.id = g.idgoo WHERE t.ftag="advertencia" and g.idstategoo=1 GROUP BY t.tag ORDER BY total desc LIMIT 0,40 | | 22597 | artigoo_es | localhost | artigoo_es | Query | 3 | Copying to tmp table | SELECT t.tag, t.ftag, COUNT(tag) AS total FROM tags AS t JOIN goos AS g ON t.id = g.idgoo WHERE t.ftag="aefa" and g.idstategoo=1 GROUP BY t.tag ORDER BY total desc LIMIT 0,40 | | 22598 | artigoo_es | localhost | artigoo_es | Query | 3 | Copying to tmp table | SELECT t.tag, t.ftag, COUNT(tag) AS total FROM tags AS t JOIN goos AS g ON t.id = g.idgoo WHERE t.ftag="ahorra" and g.idstategoo=1 GROUP BY t.tag ORDER BY total desc LIMIT 0,40 | | 22600 | artigoo_es | localhost | artigoo_es | Query | 1 | Copying to tmp table | SELECT t.tag, t.ftag, COUNT(tag) AS total FROM tags AS t JOIN goos AS g ON t.id = g.idgoo WHERE t.ftag="mp3" and g.idstategoo=1 GROUP BY t.tag ORDER BY total desc LIMIT 0,40 | | 22604 | artigoo_es | localhost | artigoo_es | Query | 2 | Copying to tmp table | SELECT t.tag, t.ftag, COUNT(tag) AS total FROM tags AS t JOIN goos AS g ON t.id = g.idgoo WHERE t.ftag="cocina-casera" and g.idstategoo=1 GROUP BY t.tag ORDER BY total desc LIMIT 0,40 | | 22605 | artigoo_es | localhost | artigoo_es | Query | 1 | Copying to tmp table | SELECT t.tag, t.ftag, COUNT(tag) AS total FROM tags AS t JOIN goos AS g ON t.id = g.idgoo WHERE t.ftag="mp3" and g.idstategoo=1 GROUP BY t.tag ORDER BY total desc LIMIT 0,40 | | 22606 | artigoo_es | localhost | artigoo_es | Query | 1 | Copying to tmp table | SELECT t.tag, t.ftag, COUNT(tag) AS total FROM tags AS t JOIN goos AS g ON t.id = g.idgoo WHERE t.ftag="cocina-faci" and g.idstategoo=1 GROUP BY t.tag ORDER BY total desc LIMIT 0,40 | | 22609 | artigoo_es | localhost | artigoo_es | Query | 2 | Copying to tmp table | select g.title, g.url, u.username, g.metadesc from goos as g, users as u, tags as t, goorank as gr where g.idstategoo=1 and u.iduser=g.iduser and g.idgoo=gr.idgoo and t.ftag="cocina-facil" and t.id=g.idgoo group by g.idgoo order by gr.quality desc LIMIT 90, 10 | +-------+------------+-----------+------------+---------+------+----------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 11 rows in set (0.00 sec) That query explain: mysql> explain SELECT t.tag, t.ftag, COUNT(tag) AS total FROM tags AS t JOIN goos AS g ON t.id = g.idgoo WHERE t.ftag="mp3" and g.idstategoo=1 GROUP BY t.tag ORDER BY total desc LIMIT 0,40; +----+-------------+-------+-------+------------------------------+--------------------------+---------+--------------------+-------+-----------------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+------------------------------+--------------------------+---------+--------------------+-------+-----------------------------------------------------------+ | 1 | SIMPLE | g | index | PRIMARY,idx_idgoo_idstategoo | idx_idgoo_idstategoo | 9 | NULL | 44860 | Using where; Using index; Using temporary; Using filesort | | 1 | SIMPLE | t | ref | idx_tag_id_idcontenttype | idx_tag_id_idcontenttype | 5 | artigoo_es.g.idgoo | 2 | Using where | +----+-------------+-------+-------+------------------------------+--------------------------+---------+--------------------+-------+-----------------------------------------------------------+ 2 rows in set (0.00 sec)
That query spent about 6 sec when the server are with high load, but only 0.72 sec when it is normal.
Not always is that query, I already have activated slow queries and i found lot of them. Always same status "Copying to tmp table".

By the way, my system memory usage:
# free total used free shared buffers cached Mem: 32979568 27272216 5707352 0 932860 19774576 -/+ buffers/cache: 6564780 26414788 Swap: 7999992 6788 7993204
As you can see, I have about 25GB free (about 5GB truly free and another 26GB in cache, which can be use if necesary).

Some other stats:

mysql> show engine innodb status\G *************************** 1. row *************************** Type: InnoDB Name: Status: ===================================== 130514 20:21:28 INNODB MONITOR OUTPUT ===================================== Per second averages calculated from the last 22 seconds ---------- SEMAPHORES ---------- OS WAIT ARRAY INFO: reservation count 375146125, signal count 358559755 Mutex spin waits 0, rounds 133666397543, OS waits 226613066 RW-shared spins 30516517, OS waits 2475040; RW-excl spins 14667880, OS waits 696823 ------------ TRANSACTIONS ------------ Trx id counter 0 24016085 Purge done for trx's n:o < 0 24016065 undo n:o < 0 0 History list length 1 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 0 24016079, not started, process no 31390, OS thread id 139647571896064 MySQL thread id 22814, query id 716143 localhost artigoo_es Table lock select title, url, date(create_date) as date, (select sum(pageviews) from analytics where idgoo=goos.idgoo) as visits from goos where iduser=1625 and idstategoo=1 order by title ---TRANSACTION 0 24016065, not started, process no 31390, OS thread id 139642324195072 MySQL thread id 22813, query id 716129 localhost artigoo_es Table lock update goos set metakeys="grafeno, grafito, matertial, futuro, l?mina", title="Grafeno el material del futuro", url="grafeno-material-futuro", update_date=now() where idgoo=45215 and iduser=13167 ---TRANSACTION 0 24015879, not started, process no 31390, OS thread id 139642329331456 MySQL thread id 10161, query id 716148 localhost root show engine innodb status ---TRANSACTION 0 24016084, ACTIVE 10 sec, process no 31390, OS thread id 139642327709440 starting index read, thread declared inside InnoDB 453 mysql tables in use 2, locked 0 MySQL thread id 22785, query id 716104 localhost artigoo_es Copying to tmp table select c.category, c.category_url, count(c.category) as total from categories as c, goos as g, tags as t where t.ftag="incidentes-river" and t.id=g.idgoo and g.idcat=c.idcategory group by c.category order by total desc limit 0,10 Trx read view will not see trx with id >= 0 24016085, sees < 0 24016069 ---TRANSACTION 0 24016083, ACTIVE 10 sec, process no 31390, OS thread id 139642325006080 sleeping before joining InnoDB queue mysql tables in use 3, locked 0 MySQL thread id 22819, query id 716124 localhost artigoo_es Copying to tmp table select g.title, g.url, u.username, g.metadesc from goos as g, users as u, tags as t, goorank as gr where g.idstategoo=1 and u.iduser=g.iduser and g.idgoo=gr.idgoo and t.ftag="mejillones-rellenos" and t.id=g.idgoo group by g.idgoo order by gr.quality desc Trx read view will not see trx with id >= 0 24016085, sees < 0 24016069 ---TRANSACTION 0 24016082, ACTIVE 10 sec, process no 31390, OS thread id 139647572166400 starting index read, thread declared inside InnoDB 440 mysql tables in use 2, locked 0 MySQL thread id 22780, query id 716098 localhost artigoo_es Copying to tmp table select c.category, c.category_url, count(c.category) as total from categories as c, goos as g, tags as t where t.ftag="ideas-recomendaciones" and t.id=g.idgoo and g.idcat=c.idcategory group by c.category order by total desc limit 0,10 Trx read view will not see trx with id >= 0 24016085, sees < 0 24016069 ---TRANSACTION 0 24016081, ACTIVE 10 sec, process no 31390, OS thread id 139642325411584 starting index read, thread declared inside InnoDB 159 mysql tables in use 2, locked 0 MySQL thread id 22781, query id 716094 localhost artigoo_es Copying to tmp table select c.category, c.category_url, count(c.category) as total from categories as c, goos as g, tags as t where t.ftag="inaki-pinuel" and t.id=g.idgoo and g.idcat=c.idcategory group by c.category order by total desc limit 0,10 Trx read view will not see trx with id >= 0 24016085, sees < 0 24016069 ---TRANSACTION 0 24016080, ACTIVE 10 sec, process no 31390, OS thread id 139642317977344 waiting in InnoDB queue mysql tables in use 2, locked 0 MySQL thread id 22784, query id 716100 localhost artigoo_es Copying to tmp table select c.category, c.category_url, count(c.category) as total from categories as c, goos as g, tags as t where t.ftag="ideas-decorar" and t.id=g.idgoo and g.idcat=c.idcategory group by c.category order by total desc limit 0,10 Trx read view will not see trx with id >= 0 24016085, sees < 0 24016069 ---TRANSACTION 0 24016078, ACTIVE 10 sec, process no 31390, OS thread id 139642323519232 starting index read, thread declared inside InnoDB 73 mysql tables in use 3, locked 0 MySQL thread id 22811, query id 716127 localhost artigoo_es Copying to tmp table select g.title, g.url, u.username, g.metadesc from goos as g, users as u, tags as t, goorank as gr where g.idstategoo=1 and u.iduser=g.iduser and g.idgoo=gr.idgoo and t.ftag="cocino" and t.id=g.idgoo group by g.idgoo order by gr.quality desc LIMIT 0, 10 Trx read view will not see trx with id >= 0 24016082, sees < 0 24016069 ---TRANSACTION 0 24016077, ACTIVE 10 sec, process no 31390, OS thread id 139647571760896 starting index read, thread declared inside InnoDB 110 mysql tables in use 3, locked 0 MySQL thread id 22818, query id 716125 localhost artigoo_es Copying to tmp table select g.title, g.url, u.username, g.metadesc from goos as g, users as u, tags as t, goorank as gr where g.idstategoo=1 and u.iduser=g.iduser and g.idgoo=gr.idgoo and t.ftag="megadeth" and t.id=g.idgoo group by g.idgoo order by gr.quality desc Trx read view will not see trx with id >= 0 24016082, sees < 0 24016069 ---TRANSACTION 0 24016076, ACTIVE 10 sec, process no 31390, OS thread id 139642329736960 fetching rows, thread declared inside InnoDB 359 mysql tables in use 2, locked 0 MySQL thread id 22783, query id 716103 localhost artigoo_es Copying to tmp table select c.category, c.category_url, count(c.category) as total from categories as c, goos as g, tags as t where t.ftag="inauguracion" and t.id=g.idgoo and g.idcat=c.idcategory group by c.category order by total desc limit 0,10 Trx read view will not see trx with id >= 0 24016085, sees < 0 24016069 ---TRANSACTION 0 24016075, ACTIVE 10 sec, process no 31390, OS thread id 139642328520448 sleeping before joining InnoDB queue mysql tables in use 2, locked 0 MySQL thread id 22782, query id 716095 localhost artigoo_es Copying to tmp table select c.category, c.category_url, count(c.category) as total from categories as c, goos as g, tags as t where t.ftag="inaki" and t.id=g.idgoo and g.idcat=c.idcategory group by c.category order by total desc limit 0,10 Trx read view will not see trx with id >= 0 24016085, sees < 0 24016069 ---TRANSACTION 0 24016074, ACTIVE 10 sec, process no 31390, OS thread id 139642318112512 fetching rows, thread declared inside InnoDB 221 mysql tables in use 2, locked 0 MySQL thread id 22778, query id 716097 localhost artigoo_es Copying to tmp table select c.category, c.category_url, count(c.category) as total from categories as c, goos as g, tags as t where t.ftag="idealizar" and t.id=g.idgoo and g.idcat=c.idcategory group by c.category order by total desc limit 0,10 Trx read view will not see trx with id >= 0 24016085, sees < 0 24016069 ---TRANSACTION 0 24016073, ACTIVE 10 sec, process no 31390, OS thread id 139642321086208 sleeping before joining InnoDB queue mysql tables in use 2, locked 0 MySQL thread id 22776, query id 716096 localhost artigoo_es Copying to tmp table select c.category, c.category_url, count(c.category) as total from categories as c, goos as g, tags as t where t.ftag="idealistaes" and t.id=g.idgoo and g.idcat=c.idcategory group by c.category order by total desc limit 0,10 Trx read view will not see trx with id >= 0 24016085, sees < 0 24016069 ---TRANSACTION 0 24016072, ACTIVE 10 sec, process no 31390, OS thread id 139642319329024 sleeping before joining InnoDB queue mysql tables in use 2, locked 0 MySQL thread id 22777, query id 716101 localhost artigoo_es Copying to tmp table select c.category, c.category_url, count(c.category) as total from categories as c, goos as g, tags as t where t.ftag="idea-despedidas" and t.id=g.idgoo and g.idcat=c.idcategory group by c.category order by total desc limit 0,10 Trx read view will not see trx with id >= 0 24016085, sees < 0 24016069 ---TRANSACTION 0 24016071, ACTIVE 10 sec, process no 31390, OS thread id 139642318247680 sleeping before joining InnoDB queue mysql tables in use 2, locked 0 MySQL thread id 22779, query id 716102 localhost artigoo_es Copying to tmp table select c.category, c.category_url, count(c.category) as total from categories as c, goos as g, tags as t where t.ftag="impresionante" and t.id=g.idgoo and g.idcat=c.idcategory group by c.category order by total desc limit 0,10 Trx read view will not see trx with id >= 0 24016085, sees < 0 24016069 ---TRANSACTION 0 24016070, ACTIVE 10 sec, process no 31390, OS thread id 139642325546752 fetching rows, thread declared inside InnoDB 480 mysql tables in use 3, locked 0 MySQL thread id 22806, query id 716123 localhost artigoo_es Copying to tmp table select g.title, g.url, u.username, g.metadesc from goos as g, users as u, tags as t, goorank as gr where g.idstategoo=1 and u.iduser=g.iduser and g.idgoo=gr.idgoo and t.ftag="inciensos" and t.id=g.idgoo group by g.idgoo order by gr.quality desc LIMIT 0, 10 Trx read view will not see trx with id >= 0 24016074, sees < 0 24016069 ---TRANSACTION 0 24016069, ACTIVE 10 sec, process no 31390, OS thread id 139642326087424 waiting in InnoDB queue mysql tables in use 3, locked 0 MySQL thread id 22812, query id 716126 localhost artigoo_es Copying to tmp table select g.title, g.url, u.username, g.metadesc from goos as g, users as u, tags as t, goorank as gr where g.idstategoo=1 and u.iduser=g.iduser and g.idgoo=gr.idgoo and t.ftag="cizanero" and t.id=g.idgoo group by g.idgoo order by gr.quality desc LIMIT 0, 10 Trx read view will not see trx with id >= 0 24016074, sees < 0 24016070 -------- FILE I/O -------- I/O thread 0 state: waiting for i/o request (insert buffer thread) I/O thread 1 state: waiting for i/o request (log thread) I/O thread 2 state: waiting for i/o request (read thread) I/O thread 3 state: waiting for i/o request (write thread) Pending normal aio reads: 0, aio writes: 0, ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0 Pending flushes (fsync) log: 0; buffer pool: 0 113312 OS file reads, 1308072 OS file writes, 69295 OS fsyncs 0.00 reads/s, 0 avg bytes/read, 1.18 writes/s, 0.59 fsyncs/s ------------------------------------- INSERT BUFFER AND ADAPTIVE HASH INDEX ------------------------------------- Ibuf: size 1, free list len 23, seg size 25, 2354 inserts, 2354 merged recs, 661 merges Hash table size 8850487, node heap has 14181 buffer(s) 275921.14 hash searches/s, 6189.22 non-hash searches/s --- LOG --- Log sequence number 4 462958358 Log flushed up to 4 462958358 Last checkpoint at 4 462958358 0 pending log writes, 0 pending chkp writes 429136 log i/o's done, 0.27 log i/o's/second ---------------------- BUFFER POOL AND MEMORY ---------------------- Total memory allocated 4754045874; in additional pool allocated 1045760 Dictionary memory allocated 720952 Buffer pool size 262144 Free buffers 52225 Database pages 195738 Modified db pages 0 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages read 211942, created 148049, written 1625483 0.00 reads/s, 0.00 creates/s, 0.91 writes/s Buffer pool hit rate 1000 / 1000 -------------- ROW OPERATIONS -------------- 8 queries inside InnoDB, 7 queries in queue 16 read views open inside InnoDB Main thread process no. 31390, id 139642338264832, state: sleeping Number of rows inserted 10078554, updated 402156, deleted 315, read 9406695475 0.23 inserts/s, 0.00 updates/s, 0.23 deletes/s, 286462.98 reads/s ---------------------------- END OF INNODB MONITOR OUTPUT ============================ 1 row in set (0.00 sec)
And here you are the my.cnf:

# # The MySQL database server configuration file. # # You can copy this to one of: # - "/etc/mysql/my.cnf" to set global options, # - "~/.my.cnf" to set user-specific options. # # One can use all long options that the program supports. # Run program with --help to get a list of available options and with # --print-defaults to see which it would actually understand and use. # # For explanations see # http://dev.mysql.com/doc/mysql/en/server-system-variables.html # This will be passed to all mysql clients # It has been reported that passwords should be enclosed with ticks/quotes # escpecially if they contain "#" chars... # Remember to edit /etc/mysql/debian.cnf when changing the socket location. [client] port = 3306 socket = /var/run/mysqld/mysqld.sock # Here is entries for some specific programs # The following values assume you have at least 32M ram # This was formally known as [safe_mysqld]. Both versions are currently parsed. [mysqld_safe] socket = /var/run/mysqld/mysqld.sock nice = 0 [mysqld] # # * Basic Settings # user = mysql pid-file = /var/run/mysqld/mysqld.pid socket = /var/run/mysqld/mysqld.sock port = 3306 basedir = /usr datadir = /var/lib/mysql tmpdir = /tmp language = /usr/share/mysql/english skip-external-locking # # Instead of skip-networking the default is now to listen only on # localhost which is more compatible and is not less secure. #bind-address = 127.0.0.1 # # * Fine Tuning # ############################################# ############### Innodb Tuning ############### ############################################# innodb_buffer_pool_size = 4096M #innodb_log_file_size = 128M innodb_log_buffer_size = 4M innodb_flush_log_at_trx_commit = 2 innodb_thread_concurrency = 8 innodb_flush_method = O_DIRECT innodb_file_per_table transaction-isolation = READ-COMMITTED ############################################# ############### MyIsam Tuning ############### ############################################# key_buffer = 512M #Aumentado de 16M a 512M el 2011/06/26 max_allowed_packet = 16M thread_stack = 128K thread_cache_size = 8 read_buffer_size = 4M # This replaces the startup script and checks MyISAM tables if needed # the first time they are touched myisam-recover = BACKUP max_connections = 1000 table_cache = 512 table_definition_cache = 2048 open_files_limit = 1536 #thread_concurrency = 10 # #Minimo de letras a buscar ft_min_word_len = 2 # * Query Cache Configuration # query_cache_limit = 1M query_cache_size = 16M # # * Logging and Replication # # Both location gets rotated by the cronjob. # Be aware that this log type is a performance killer. #log = /var/log/mysql/mysql.log # # Error logging goes to syslog. This is a Debian improvement :) # # Here you can see queries with especially long duration log_slow_queries = /var/log/mysql/mysql-slow.log long_query_time = 10 #log-queries-not-using-indexes # # The following can be used as easy to replay backup logs or for replication. # note: if you are setting up a replication slave, see README.Debian about # other settings you may need to change. #server-id = 1 #log_bin = /var/log/mysql/mysql-bin.log expire_logs_days = 10 max_binlog_size = 100M #binlog_do_db = include_database_name #binlog_ignore_db = include_database_name # # * BerkeleyDB # # Using BerkeleyDB is now discouraged as its support will cease in 5.1.12. #skip-bdb # # * InnoDB # # InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/. # Read the manual for more InnoDB related options. There are many! # You might want to disable InnoDB to shrink the mysqld process by circa 100MB. #skip-innodb # # * Security Features # # Read the manual, too, if you want chroot! # chroot = /var/lib/mysql/ # # For generating SSL certificates I recommend the OpenSSL GUI "tinyca". # # ssl-ca=/etc/mysql/cacert.pem # ssl-cert=/etc/mysql/server-cert.pem # ssl-key=/etc/mysql/server-key.pem [mysqldump] quick quote-names max_allowed_packet = 16M [mysql] #no-auto-rehash # faster start of mysql but no tab completition [isamchk] key_buffer = 16M # # * NDB Cluster # # See /usr/share/doc/mysql-server-*/README.Debian for more information. # # The following configuration is read by the NDB Data Nodes (ndbd processes) # not from the NDB Management Nodes (ndb_mgmd processes). # # [MYSQL_CLUSTER] # ndb-connectstring=127.0.0.1 # # * IMPORTANT: Additional settings that can override those from this file! # The files must end with '.cnf', otherwise they'll be ignored. # !includedir /etc/mysql/conf.d/
Finally the mysqltuner, there are some recommendations but because mysql wasn't up 24h, they are not very real.

>> MySQLTuner 1.0.1 - Major Hayden <[email protected]> >> Bug reports, feature requests, and downloads at http://mysqltuner.com/ >> Run with '--help' for additional options and output filtering [OK] Logged in using credentials passed on the command line -------- General Statistics -------------------------------------------------- [--] Skipped version check for MySQLTuner script [OK] Currently running supported MySQL version 5.1.66-0+squeeze1-log [OK] Operating on 64-bit architecture -------- Storage Engine Statistics ------------------------------------------- [--] Status: -Archive -BDB -Federated +InnoDB -ISAM -NDBCluster [--] Data in MyISAM tables: 1G (Tables: 214) [--] Data in InnoDB tables: 2G (Tables: 119) [!!] Total fragmented tables: 46 -------- Performance Metrics ------------------------------------------------- [--] Up for: 17h 40m 11s (718K q [11.299 qps], 23K conn, TX: 4B, RX: 56M) [--] Reads / Writes: 15% / 85% [--] Total buffers: 4.5G global + 6.5M per thread (1000 max threads) [OK] Maximum possible memory usage: 10.9G (34% of installed RAM) [OK] Slow queries: 1% (11K/718K) [OK] Highest usage of available connections: 11% (110/1000) [OK] Key buffer size / total MyISAM indexes: 512.0M/501.4M [OK] Key buffer hit rate: 100.0% (970M cached / 436K reads) [OK] Query cache efficiency: 45.8% (72K cached / 158K selects) [!!] Query cache prunes per day: 18526 [OK] Sorts requiring temporary tables: 0% (178 temp sorts / 1M sorts) [OK] Temporary tables created on disk: 2% (832 on disk / 32K total) [OK] Thread cache hit rate: 88% (2K created / 23K connections) [!!] Table cache hit rate: 16% (512 open / 3K opened) [OK] Open file limit used: 10% (509/5K) [OK] Table locks acquired immediately: 99% (638K immediate / 639K locks) [OK] InnoDB data size / buffer pool: 2.6G/4.0G -------- Recommendations ----------------------------------------------------- General recommendations: Run OPTIMIZE TABLE to defragment tables for better performance MySQL started within last 24 hours - recommendations may be inaccurate Increase table_cache gradually to avoid file descriptor limits Variables to adjust: query_cache_size (> 16M) table_cache (> 512)
I run optimize and repair in more important tables, so I think 46 fragmented table could unused dbs or because many tables are innodb.

In previous server, I had some error like this, but it was because there was different with system timezone and localtime file in etc. When I configured same in both, previous server become stable.
In this server this is not the problem, and I'm a little lost.

Any idea ? If you need some other info, please tell me.

Thanks and best regards.

The biggest performance hit on my site are these queries:

SELECT COUNT (*) FROM `item` WHERE item.item_type_id = ? AND item.has_photo = ?

SELECT item.* FROM `item` WHERE item.item_type_id = ? AND item.has_photo = ? ORDER BY normalized_time DESC LIMIT ?, ?

I'm wondering if a MySQL expert (like most of the people on these forums) were to see these queries do you instantly know they can be improved?

The queries are for Lionseek.com - I hate slow sites and I know the visitors do as well. Need some recommendations on getting the queries improved. Please help!

http://www.lionseek.com/locked.png

How do you decide which items and in what order to place them in a composite index:

- the SELECT has elements where min or max are chosen

- the WHERE clause has multiple elements

- there is a GROUP BY

- there is an ORDER BY which is also one of the min elements of the SELECT

I create a table as follows:
CREATE TABLE IF NOT EXISTS `t` (
   `id` int (11) NOT NULL AUTO_INCREMENT,
   `i` date NOT NULL,
   `o` date NOT NULL,
   PRIMARY KEY (`id`),
   Index0 KEY `` (`i`)

)
to test the performance of my query I do:
explain select * from t o Where> '2013-05-04 'and i <'2013-05-11';
I got the following output on my local computer:

| select_type | table | Type | possible_keys | key | key_len | ref | rows
+ ---- + ------------- + ------- + ------- + -------------- - + ------ + --------- +
| SIMPLE | t | range | a, b ??| b | 3 | NULL | 84 | Using where |
+ ---- + ------------- + ------- + ------- + -------------- - + ------ + --------- +

While on a remote server, I get:

| select_type | table | Type | possible_keys | key | key_len | ref | rows
+ ---- + ------------- + ------- + ------- + -------------- - + ------ + --------- +
| SIMPLE | t | ALL | a, b ??| null | 3 | NULL | 84 | Using where |
+ ---- + ------------- + ------- + ------- + -------------- - + ------ + --------- +

This means that MySQL uses the index locally, but not on the remote server. Why?

Hi,

We are facing a problem. At night we are running several proccesses on a Mysql (Server 5.1.61) and we are getting the exception from jdbc

com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure
The last packet successfully received from the server was 7.200.068 milliseconds ago. The last packet sent successfully to the server was 8.333.893 milliseconds ago.
...
with nested Exception:

Caused by: java.net.SocketException: Connection reset
at java.net.SocketInputStream.read(Unknown Source)
at com.mysql.jdbc.util.ReadAheadInputStream.fill(ReadAheadInputStream.java:114)
at com.mysql.jdbc.util.ReadAheadInputStream.readFromUnderlyingStreamIfNecessary(ReadAheadInputStream.java:161)
at com.mysql.jdbc.util.ReadAheadInputStream.read(ReadAheadInputStream.java:189)
at com.mysql.jdbc.MysqlIO.readFully(MysqlIO.java:2499)
at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3008)


Our wait_timeout is set to 8h which should not be exceeded by the proccesses, because they are stopping after ca 2h.
max_connections is set to 800 which also should not be exceeded.
Is there a logging where i can have a look into, what has happened?

Regards
Stefan

Hi,
Does anyone know of any tools, in which I can plug in a bunch of information about the specs of our drives, information gathered from the Dell DPACK, and the Percona Cacti graphs in order to help me make a informed hardware decision?

I have used some IOPS calculators and they have helped a bit I'm wondering if there are tools more specific to MySQL hardware sizing.

Thanks,
James

Hi,

I have a database with theses privileges:
- an user who have all privileges "user1".
- an user with restricted privileges to some tables or view "user2".

When I send a select request like i.e. "SELECT * FROM film, sales_by_store LIMIT 1;" with an user who have :
GRANT SELECT ON `sakila`.* TO 'user1'@'localhost';
for first time :
1 row in set (0.04 sec)

and then I repeat this sql :
1 row in set (0.00 sec)
1 row in set (0.00 sec)
1 row in set (0.00 sec)

with user2 :
GRANT SELECT ON sakila.sales_by_store TO 'user2'@'localhost';
GRANT SELECT ON sakila.film TO 'user2'@'localhost';

time execution are same :
1 row in set (0.04 sec)
1 row in set (0.04 sec)
1 row in set (0.04 sec)

Why the cache is not used ?

i am using mysql 5.6, i am trying to implement partition (by range) for my existing tables(planning to seperate the data's year wise!),but i am not able do it for the tables which is having primary key as well as foreign key? somehow i need to separate the data's for every year for the purpose of client restriction from old datas.! can anyone help me to fix this? or anyother alternative solution to handle these kind of scenarios,with steps?