Performance Issues - Big database?
Posted by: C Cranfield
Date: February 14, 2007 09:06PM

Hi Everyone,

I have inherited a mysql database and am trying to get a handle on things. Here is some relevant information:

Machine:
Mysql 5.0.26
Dual Processor 2.8Ghz
Raid 0+1 Root
Raid 5 storage system for data
4 Gig of Ram

** All transaction are through the web.

Database
50 tables (48 MyISAM and 2 INNODB)
Total Records 4M (1.4M in the INNODB tables)
Total Size 1Gig


Here are the stats for a 5 day Period:
Query statistics: Since its startup, 5,548,005 queries have been sent to the server.
Total ø per hour ø per minute ø per second
5,548 k 39.53 k 658.78 10.98
Query type ø per hour %
admin commands 662 4.72 0.01%
alter db 0 0.00 0.00%
alter table 2 0.01 0.00%
analyze 0 0.00 0.00%
backup table 0 0.00 0.00%
begin 0 0.00 0.00%
change db 15 k 109.20 0.28%
change master 0 0.00 0.00%
check 0 0.00 0.00%
checksum 0 0.00 0.00%
commit 0 0.00 0.00%
create db 0 0.00 0.00%
create function 0 0.00 0.00%
create index 0 0.00 0.00%
create table 7 0.05 0.00%
delete 5,740 40.89 0.10%
delete multi 0 0.00 0.00%
do 0 0.00 0.00%
drop db 0 0.00 0.00%
drop function 0 0.00 0.00%
drop index 0 0.00 0.00%
drop table 1 7.12 m 0.00%
drop user 0 0.00 0.00%
flush 0 0.00 0.00%
grant 0 0.00 0.00%
ha close 0 0.00 0.00%
ha open 0 0.00 0.00%
ha read 0 0.00 0.00%
help 0 0.00 0.00%
insert 648 k 4,616.12 11.73%
insert select 0 0.00 0.00%
kill 0 0.00 0.00%
load 0 0.00 0.00%
load master data 0 0.00 0.00%
load master table 0 0.00 0.00%
lock tables 26 0.19 0.00%
optimize 0 0.00 0.00%
preload keys 0 0.00 0.00%
purge 0 0.00 0.00%
purge before date 0 0.00 0.00%
rename table 0 0.00 0.00%
repair 0 0.00 0.00%
replace 0 0.00 0.00%
replace select 0 0.00 0.00%
reset 0 0.00 0.00%
restore table 0 0.00 0.00%
revoke 0 0.00 0.00%
revoke all 0 0.00 0.00%
rollback 0 0.00 0.00%
Query type ø per hour %
savepoint 0 0.00 0.00%
select 106 k 754.03 1.92%
set option 1,319 9.40 0.02%
show binlog events 0 0.00 0.00%
show binlogs 30 0.21 0.00%
show charsets 162 1.15 0.00%
show collations 162 1.15 0.00%
show column types 0 0.00 0.00%
show create db 30 0.21 0.00%
show create table 774 5.51 0.01%
show databases 50 0.36 0.00%
show errors 0 0.00 0.00%
show fields 840 5.98 0.02%
show grants 61 0.43 0.00%
show innodb status 525 3.74 0.01%
show keys 245 1.75 0.00%
show logs 0 0.00 0.00%
show master status 1 7.12 m 0.00%
show ndb status 0 0.00 0.00%
show new master 0 0.00 0.00%
show open tables 0 0.00 0.00%
show privileges 0 0.00 0.00%
show processlist 2 0.01 0.00%
show slave hosts 24 0.17 0.00%
show slave status 0 0.00 0.00%
show status 529 3.77 0.01%
show storage engines 2 0.01 0.00%
show tables 248 1.77 0.00%
show triggers 553 3.94 0.01%
show variables 372 2.65 0.01%
show warnings 0 0.00 0.00%
slave start 0 0.00 0.00%
slave stop 0 0.00 0.00%
stmt close 0 0.00 0.00%
stmt execute 0 0.00 0.00%
stmt fetch 0 0.00 0.00%
stmt prepare 0 0.00 0.00%
stmt reset 0 0.00 0.00%
stmt send long data 0 0.00 0.00%
truncate 0 0.00 0.00%
unlock tables 25 0.18 0.00%
update 20 k 139.29 0.35%
update multi 0 0.00 0.00%
xa commit 0 0.00 0.00%
xa end 0 0.00 0.00%
xa prepare 0 0.00 0.00%
xa recover 0 0.00 0.00%
xa rollback 0 0.00 0.00%
xa start 0 0.00 0.00%

Would this be classified as a fairly active database? And when does big become big?

With a database doing these types of transactions (tons of inserts and lots of selects) what are the most important things to watch for to insure optimal performance? Does it make sense to change some of the tables to innodb?

I know we are getting some slow queries (we have slow-query-log on) so I am currently trying to nail down whether the issue is hardware, indexes, or just SQL statements.

Thanks for any help/suggestions.
C

Options: ReplyQuote


Subject
Written By
Posted
Performance Issues - Big database?
February 14, 2007 09:06PM


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.