Thanks for all the valuable information Rick!
I converted a single table to use InnoDB, the conversion took 0.09s since it has 8000 rows in it, most of work is done on that specific table I guess, that's the user table.
Just by converting I can say that I notice a performance difference, probably because of the way the two engines perform lockings. But still, 25 concurrent users may cause my web server to respond in more than 12 seconds, that's where it timesout.
Rick James Wrote:
-------------------------------------------------------
>
> * Be sure to shrink key_buffer_size, but not to 0,
> and increase innodb_buffer_pool_size.
>
> * Both MyISAM and InnoDB provide a high level of
> concurrency; the granularity of the locks is
> radically different, but both are so fast that you
> (usually) can't tell the difference.
>
> * I like the slowlog for determining slow queries.
> This indirectly says which tables are being
> pounded. After converting to InnoDB, they will
> still be pounded, but in different ways.
I didn't check my slow log because it was empty, I now know that I should turn it on, I'll do that and see what hints it can give.
> For further analysis, please provide
> * SHOW CREATE TABLE tbl\G -- indexes
> * SHOW TABLE STATUS LIKE 'tbl'\G -- sizes
> * EXPLAIN SELECT ...\G -- clues of inefficiencies
> * SHOW VARIABLES LIKE '%buffer%'; -- cache size
> and surround them with [ code ] and [ / code ]
Create Table: CREATE TABLE `censored_name` (
`id` bigint(20) NOT NULL,
`joined_game` datetime NOT NULL,
`last_login` datetime NOT NULL,
`current_energy` float NOT NULL,
`experience` int(11) NOT NULL,
`money` float NOT NULL,
-- updated using a trigger
`power` float NOT NULL,
`special_points` float NOT NULL,
`level` int(11) NOT NULL,
`character_id` int(11) default NULL,
`wins` int(11) NOT NULL,
`loses` int(11) NOT NULL,
--being updated using a trigger
`nr_payments` int(11) NOT NULL,
`nr_friends` int(11) default NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
mysql> SHOW TABLE STATUS LIKE 'censored_name'\G
*************************** 1. row ***************************
Name: censored_name
Engine: InnoDB
Version: 10
Row_format: Compact
Rows: 7104
Avg_row_length: 221
Data_length: 1572864
Max_data_length: 0
Index_length: 0
Data_free: 0
Auto_increment: NULL
Create_time: 2009-12-01 11:23:40
Update_time: NULL
Check_time: NULL
Collation: latin1_swedish_ci
Checksum: NULL
Create_options:
Comment: InnoDB free: 8192 kB
mysql> SHOW VARIABLES LIKE '%buffer%';
+-------------------------------+----------+
| Variable_name | Value |
+-------------------------------+----------+
| bulk_insert_buffer_size | 8388608 |
| innodb_buffer_pool_awe_mem_mb | 0 |
| innodb_buffer_pool_size | 8388608 |
| innodb_log_buffer_size | 1048576 |
| join_buffer_size | 131072 |
| key_buffer_size | 16777216 |
| myisam_sort_buffer_size | 8388608 |
| net_buffer_length | 16384 |
| preload_buffer_size | 32768 |
| read_buffer_size | 131072 |
| read_rnd_buffer_size | 262144 |
| sort_buffer_size | 2097144 |
+-------------------------------+----------+