MySQL Forums
Forum List  »  Performance

Re: slow queries and high % of wait I/O
Posted by: jawahar Muthukrishnan
Date: January 19, 2009 09:00PM

Increasing BUFFER_POOL_SIZE
a. Unfortunately, we have a 32-bit mysql build installed on a 64-bit machine. So, for the short-term, we are trying to find a solution that will work with the 32-bit version.

Queries in SLOW_QUERY_LOG during PEAK traffic time( the data is spread over 7 hours )
b. Select from History table is by far the most common query present in the log.
======> select * from history = 10534 entries
======> insert into history = 28
======> delete from history = 2
=======> update history = 15
There are 80 entries for other tables in the database


Frequency of the log entries
c. In the peak hour, the slow query is logged ever 3-4 secs.. it isn't consistent though.. sometimes it is a sec, other times it is spread out.

Randomness of Device_id
d. Device_ids for the SELECT queries are random.

No. of rows returned in the query
e. the number of rows returned is usually 25. However, because of a bug in our application, at times we have around 200 or 300 and in some instances, it is even around 2000.

Rows_examined vs Rows_sent
f. The Rows_examined and Rows_sent are the same.


Show variables like 'Innodb%'

mysql> show variables like 'Innodb%';
+---------------------------------+------------------------+
| Variable_name | Value |
+---------------------------------+------------------------+
| innodb_additional_mem_pool_size | 16777216 |
| innodb_autoextend_increment | 8 |
| innodb_buffer_pool_awe_mem_mb | 0 |
| innodb_buffer_pool_size | 2147483648 |
| 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 | 120 |
| innodb_locks_unsafe_for_binlog | OFF |
| innodb_log_arch_dir | |
| innodb_log_archive | OFF |
| innodb_log_buffer_size | 8388608 |
| innodb_log_file_size | 268435456 |
| innodb_log_files_in_group | 3 |
| 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 | 16 |
| innodb_thread_sleep_delay | 10000 |
+---------------------------------+------------------------+
34 rows in set (0.00 sec)


g. Finally, do you think defragmentation will help?

Options: ReplyQuote


Subject
Views
Written By
Posted
2950
January 19, 2009 05:36PM
Re: slow queries and high % of wait I/O
2631
January 19, 2009 09:00PM
2587
January 19, 2009 11:37PM
2411
January 21, 2009 01:22AM
2355
January 20, 2009 01:28AM
2285
January 20, 2009 08:41PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.