MySQL Problem

NationVoice · #1 (**permalink**) 04-17-08, 02:59 PM

I have a database with about 36 million entries in it and expect that number to grow to 180 million in the next few weeks to months. These are used for live graphs & they have been loading very slow.

I attempted to change the engine type to 'heap' from 'MyISAM'.

This is the error I receive.

Code:

Error

SQL query: Edit

ALTER TABLE `graphs_clients` ENGINE = HEAP

MySQL said: Documentation
#1114 - The table '#sql-1213_156d2fa' is full

if I used the wrong engine what should I be using with this much data?

The machine specs are a Dual Woodcrest 5130, 4gb ram, 4x 174gb 15k SCSI's in Raid10. This server runs nothing but MySQL & offsite backups 4-6 times a day. This is also my sar output.

Code:

12:00:38 AM       CPU     %user     %nice   %system   %iowait    %steal     %idle
12:10:38 AM       all      9.65      0.00      2.10      4.38      0.00     83.86
12:20:38 AM       all     10.58      0.00      2.46      4.78      0.00     82.18
12:30:38 AM       all      8.03      0.00      1.92      4.98      0.00     85.07
12:40:38 AM       all      7.87      0.00      1.61     10.39      0.00     80.13
12:50:38 AM       all     10.49      0.00      1.66      4.75      0.00     83.10
01:00:38 AM       all     12.21      0.00      2.07      5.49      0.00     80.23
01:10:38 AM       all     14.19      0.00      2.63      6.64      0.00     76.54
01:20:38 AM       all     14.22      0.00      2.30      6.20      0.00     77.27
01:30:38 AM       all     11.77      0.00      1.98      6.07      0.00     80.18
01:40:38 AM       all     12.68      0.00      2.33     18.40      0.00     66.58
01:50:38 AM       all     13.82      0.00      2.34      5.48      0.00     78.36
02:00:38 AM       all     11.22      0.00      1.80      4.93      0.00     82.05
02:10:38 AM       all     12.91      0.00      2.16      4.89      0.00     80.04
02:20:38 AM       all     12.69      0.00      1.93      4.95      0.00     80.43
02:30:38 AM       all     11.15      0.00      1.70      5.58      0.00     81.56
02:40:38 AM       all     12.03      0.00      1.78      8.50      0.00     77.69
02:50:38 AM       all     12.58      0.00      1.79      5.02      0.00     80.62
03:00:38 AM       all     10.55      0.00      1.63      5.78      0.00     82.04
03:10:39 AM       all     17.29      0.00      3.45     11.29      0.00     67.96
03:20:39 AM       all     15.52      0.00      2.53      8.42      0.00     73.54
03:30:38 AM       all     10.37      0.00      1.67      6.97      0.00     80.98
03:40:38 AM       all     11.14      0.00      1.69     19.09      0.00     68.07
03:50:38 AM       all     12.41      0.00      1.75      8.67      0.00     77.17
04:00:38 AM       all     10.78      0.00      1.64      5.13      0.00     82.45
04:10:38 AM       all     11.69      0.00      1.93      6.60      0.00     79.77
04:20:38 AM       all     11.99      0.00      1.70      4.90      0.00     81.41
04:30:38 AM       all     10.22      0.00      1.56      5.26      0.00     82.95
04:40:38 AM       all     10.64      0.00      1.59     18.56      0.00     69.21
04:50:38 AM       all     12.32      0.00      1.72     16.53      0.00     69.43
05:00:38 AM       all     10.38      0.00      1.54      4.64      0.00     83.44
05:10:38 AM       all     10.69      0.00      1.66      4.60      0.00     83.05
05:20:38 AM       all     12.42      0.00      1.73      4.42      0.00     81.43
05:30:39 AM       all     10.22      0.00      1.53      4.58      0.00     83.67
05:40:38 AM       all     12.04      0.00      1.85     21.05      0.00     65.06
05:50:38 AM       all     12.17      0.00      1.70     27.19      0.00     58.94
06:00:38 AM       all     10.45      0.00      1.56     13.33      0.00     74.65
06:10:39 AM       all     10.81     20.86      2.33      9.58      0.00     56.42
06:20:38 AM       all     12.12     22.50      2.35      9.13      0.00     53.89
06:30:39 AM       all     10.41     22.94      2.26     10.52      0.00     53.87
06:40:38 AM       all     18.40     11.32     12.69     12.11      0.00     45.48
06:50:39 AM       all     16.72     15.39      7.52     13.20      0.00     47.17
07:00:38 AM       all     10.12     22.10      2.11      8.82      0.00     56.84
07:10:38 AM       all     10.55     12.01      2.25     13.31      0.00     61.87
07:20:38 AM       all     12.07      0.00      1.69      7.87      0.00     78.36
07:30:38 AM       all     10.27      0.00      1.55      7.39      0.00     80.79
07:40:38 AM       all     10.74      0.00      1.63     15.46      0.00     72.17
07:50:38 AM       all     12.01      0.00      1.65      5.45      0.00     80.89
08:00:38 AM       all     15.01      0.00      1.87      4.55      0.00     78.57
08:10:38 AM       all     10.91      0.00      1.64      5.84      0.00     81.61
08:20:38 AM       all     12.19      0.00      1.70      5.86      0.00     80.26
08:30:38 AM       all     10.92      0.00      1.54      5.77      0.00     81.77
08:40:38 AM       all     11.10      0.00      1.63      7.17      0.00     80.10
08:50:38 AM       all     12.07      0.00      1.63      5.20      0.00     81.10
09:00:39 AM       all     10.54      0.00      1.56      5.35      0.00     82.56
09:10:39 AM       all     17.59      0.00      3.43     10.14      0.00     68.84
09:20:38 AM       all     15.42      0.00      2.60      8.35      0.00     73.63
09:30:38 AM       all     10.65      0.00      1.67      6.61      0.00     81.08
09:40:38 AM       all     11.16      0.00      1.65     19.29      0.00     67.90
09:50:38 AM       all     12.30      0.00      1.63      7.57      0.00     78.50
10:00:38 AM       all     10.63      0.00      1.57      4.92      0.00     82.87
10:10:38 AM       all     11.02      0.00      1.68      4.72      0.00     82.58
10:20:38 AM       all     11.36      0.00      1.76      4.71      0.00     82.17
10:30:38 AM       all     11.48      0.00      1.60      6.00      0.00     80.92
10:40:38 AM       all     20.19      0.00      2.53     18.72      0.00     58.56

10:40:38 AM       CPU     %user     %nice   %system   %iowait    %steal     %idle
10:50:38 AM       all     14.48      0.00      1.88     11.40      0.00     72.23
11:00:38 AM       all     13.17      0.00      1.76      4.42      0.00     80.65
11:10:38 AM       all     17.87      0.00      2.25      5.52      0.00     74.36
11:20:38 AM       all     11.21      0.00      2.20      4.70      0.00     81.89
11:30:38 AM       all     13.38      0.00      1.83      4.77      0.00     80.02
11:40:38 AM       all     11.07      0.00      1.64     11.30      0.00     75.99
11:50:38 AM       all     12.39      0.00      1.64      4.38      0.00     81.60
12:00:38 PM       all     12.71      0.00      1.82      4.35      0.00     81.12
12:10:38 PM       all     23.59      0.00      2.85      9.43      0.00     64.12
12:20:38 PM       all     26.49      0.00      3.05      6.12      0.00     64.33
12:30:38 PM       all     10.59      0.00      1.54      5.70      0.00     82.16
12:40:38 PM       all     11.35      0.00      1.70      6.59      0.00     80.36
12:50:38 PM       all     31.51      0.00      3.02      5.80      0.00     59.67
01:00:38 PM       all     14.32      0.00      2.00      5.67      0.00     78.01
01:10:38 PM       all     13.20      0.00      2.06      5.82      0.00     78.92
01:20:38 PM       all     25.46      0.00      3.03      5.42      0.00     66.09
01:30:38 PM       all     20.95      0.00      2.50      5.35      0.00     71.19
01:40:38 PM       all     29.50      0.00      3.48      6.56      0.00     60.47
01:50:38 PM       all     13.88      0.00      1.98      5.28      0.00     78.87
02:00:38 PM       all     12.03      0.00      1.82      5.32      0.00     80.82
02:10:38 PM       all     12.26      0.00      1.86      5.23      0.00     80.66
02:20:38 PM       all     13.95      0.00      1.94      4.09      0.00     80.02
02:30:38 PM       all     12.00      0.00      1.84      3.69      0.00     82.47
02:40:39 PM       all     13.52      0.00      2.32     11.58      0.00     72.58
02:50:38 PM       all     15.86      0.00      2.87      5.27      0.00     76.00
03:00:39 PM       all     14.08      0.00      2.76      5.65      0.00     77.51
03:10:39 PM       all     19.40      0.00      3.70      8.55      0.00     68.35
03:20:38 PM       all     17.12      0.00      2.92      7.71      0.00     72.24
03:30:38 PM       all     17.40      0.00      2.09      5.36      0.00     75.14
03:40:38 PM       all     26.03      0.00      2.73     10.67      0.00     60.58
03:50:38 PM       all     18.87      0.00      2.44      7.78      0.00     70.91
Average:          all     13.58      1.34      2.21      7.91      0.00     74.96

You can see my server doesn't use as much resources as it should.

Here is my /etc/my.cnf file

Code:

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1
max_connections = 10000
safe-show-database
key_buffer = 1024M
myisam_sort_buffer_size = 128M
join_buffer_size = 32M
read_buffer_size = 32M
sort_buffer_size = 32M
table_cache = 1024
thread_cache_size = 256
wait_timeout = 45
connect_timeout = 30
max_allowed_packet = 64M
max_connect_errors = 15
query_cache_limit = 16M
query_cache_size = 128M
query_cache_type = 1
skip-innodb
skip-name-resolve
old-passwords = 1
     
[mysql.server]
user=mysql
basedir=/var/lib

[mysqld_safe]
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

[isamchk]
key_buffer = 1024M
sort_buffer_size = 1024M
read_buffer = 32M
write_buffer = 32M

[myisamchk]
key_buffer = 1024M
sort_buffer = 1024M
read_buffer = 64M
write_buffer = 64M

Any help on this would be great. I don't see why the queries should be running as slow as they are. I would love to see this perform great.

Thanks,
Daniel Jones

blinn_shade · #2 (**permalink**) 04-25-08, 04:40 PM

Hmmm,

Maybe change to SQL Server?

UnrealEd · #3 (**permalink**) 04-29-08, 05:26 AM

Quote:

Originally Posted by blinn_shade

Hmmm,

Maybe change to SQL Server?

This has nothing to do with the server-type itself, but with the table type (or ENGINE as they call it in MySQL).

As for your problem: have a look at the MySQL manual page on HEAP tables (or MEMORY tables). Scroll down a bit, and you'll find the following lines:

Quote:

The maximum size of MEMORY tables is limited by the max_heap_table_size system variable, which has a default value of 16MB. To have larger MEMORY tables, you must increase the value of this variable. For individual tables, you can also specify a MAX_ROWS table option in the CREATE TABLE statement.

To solve your problem: either raise the value of the max_heap_table_size, or switch to a different table ENGINE, such as InnoDB or MyISAM