MySQL Forums :: Performance :: High load - queries take long time

Hi,
thanks for your reply!

I have 12 Gb of RAM.

what value do you recommend for innodb_log_file_size ?

Here are the info you asked.

mysql> SHOW CREATE TABLE Puntuacio;
+-----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table     | Create Table                                                                                                                                                                                                                            |
+-----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Puntuacio | CREATE TABLE `Puntuacio` (
  `UserID` bigint(20) NOT NULL,
  `Punts` bigint(20) NOT NULL,
  `ultim_acces` date NOT NULL,
  PRIMARY KEY (`UserID`),
  KEY `Punts` (`Punts`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_spanish_ci |
+-----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SHOW CREATE TABLE Puntuacio_a_Tormar;
+--------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table              | Create Table                                                                                                                                                                                                                                                                     |
+--------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Puntuacio_a_Tormar | CREATE TABLE `Puntuacio_a_Tormar` (
  `SenderID` bigint(20) NOT NULL,
  `ReceiverID` bigint(20) NOT NULL,
  `ultim_acces` date NOT NULL,
  PRIMARY KEY (`SenderID`,`ReceiverID`),
  KEY `ReceiverID` (`ReceiverID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_spanish_ci |
+--------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql>  SHOW TABLE STATUS LIKE Puntuacio_a_Tormar;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Puntuacio_a_Tormar' at line 1
mysql>  SHOW TABLE STATUS LIKE 'Puntuacio_a_Tormar';
+--------------------+--------+---------+------------+---------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+
| Name               | Engine | Version | Row_format | Rows    | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time | Check_time | Collation       | Checksum | Create_options | Comment |
+--------------------+--------+---------+------------+---------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+
| Puntuacio_a_Tormar | InnoDB |      10 | Compact    | 2689557 |             71 |   193527808 |               0 |    102449152 |   7340032 |           NULL | 2009-11-05 20:42:40 | NULL        | NULL       | utf8_spanish_ci |     NULL |                |         |
+--------------------+--------+---------+------------+---------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+
1 row in set (0.00 sec)

mysql>  SHOW TABLE STATUS LIKE 'Puntuacio';
+-----------+--------+---------+------------+----------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+
| Name      | Engine | Version | Row_format | Rows     | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time | Check_time | Collation       | Checksum | Create_options | Comment |
+-----------+--------+---------+------------+----------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+
| Puntuacio | InnoDB |      10 | Compact    | 13729419 |             66 |   910163968 |               0 |    496877568 |   5242880 |           NULL | 2009-07-08 13:04:22 | NULL        | NULL       | utf8_spanish_ci |     NULL |                |         |
+-----------+--------+---------+------------+----------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+
1 row in set (0.00 sec)

mysql> EXPLAIN SELECT * FROM Puntuacio_a_Tormar WHERE ReceiverID = 100000241230329 ;
+----+-------------+--------------------+------+---------------+------------+---------+-------+------+-------+
| id | select_type | table              | type | possible_keys | key        | key_len | ref   | rows | Extra |
+----+-------------+--------------------+------+---------------+------------+---------+-------+------+-------+
|  1 | SIMPLE      | Puntuacio_a_Tormar | ref  | ReceiverID    | ReceiverID | 8       | const |    2 |       |
+----+-------------+--------------------+------+---------------+------------+---------+-------+------+-------+
1 row in set (0.00 sec)


mysql> SHOW VARIABLES LIKE '%buffer%';
+-------------------------+------------+
| Variable_name           | Value      |
+-------------------------+------------+
| bulk_insert_buffer_size | 8388608    |
| innodb_buffer_pool_size | 8388608000 |
| innodb_log_buffer_size  | 8388608    |
| join_buffer_size        | 131072     |
| key_buffer_size         | 16777216   |
| myisam_sort_buffer_size | 8388608    |
| net_buffer_length       | 8192       |
| preload_buffer_size     | 32768      |
| read_buffer_size        | 262144     |
| read_rnd_buffer_size    | 524288     |
| sort_buffer_size        | 524288     |
| sql_buffer_result       | OFF        |
+-------------------------+------------+
12 rows in set (0.00 sec)

mysql> SHOW VARIABLES LIKE 'innodb%';
+-----------------------------------------+-------------------------+
| Variable_name                           | Value                   |
+-----------------------------------------+-------------------------+
| innodb_adaptive_hash_index              | ON                      |
| innodb_additional_mem_pool_size         | 2097152                 |
| innodb_autoextend_increment             | 8                       |
| innodb_autoinc_lock_mode                | 1                       |
| innodb_buffer_pool_size                 | 8388608000              |
| innodb_checksums                        | ON                      |
| innodb_commit_concurrency               | 0                       |
| innodb_concurrency_tickets              | 500                     |
| innodb_data_file_path                   | ibdata1:200M:autoextend |
| innodb_data_home_dir                    | /var/lib/mysql/         |
| innodb_doublewrite                      | ON                      |
| innodb_fast_shutdown                    | 1                       |
| innodb_file_io_threads                  | 4                       |
| innodb_file_per_table                   | ON                      |
| innodb_flush_log_at_trx_commit          | 2                       |
| innodb_flush_method                     |                         |
| innodb_force_recovery                   | 0                       |
| innodb_lock_wait_timeout                | 50                      |
| innodb_locks_unsafe_for_binlog          | OFF                     |
| innodb_log_buffer_size                  | 8388608                 |
| innodb_log_file_size                    | 1572864000              |
| innodb_log_files_in_group               | 2                       |
| innodb_log_group_home_dir               | /var/lib/mysql/         |
| 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_stats_on_metadata                | ON                      |
| innodb_support_xa                       | ON                      |
| innodb_sync_spin_loops                  | 20                      |
| innodb_table_locks                      | ON                      |
| innodb_thread_concurrency               | 8                       |
| innodb_thread_sleep_delay               | 10000                   |
| innodb_use_legacy_cardinality_algorithm | ON                      |
+-----------------------------------------+-------------------------+
35 rows in set (0.00 sec)

Edited 1 time(s). Last edit at 11/07/2009 10:04PM by Prova Test.