Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I am having problems with on website with 600MB of mysql database. The website is way too slow and i noticed the bigger the mysql database gets the slower it gets. When it was like 5MB website was very fast...when it started getting bigger..it started to get slower and slower and now at 600MB..it is really slow...taking like 10 seconds to load pages...and again...when database was small...website was really fast and had no problem.

Now i check the top processes and it is nothing related to high load or anything. It is not even related to iops as i tested on HDD 7.2k rpm drives and it gave same problem now with testing with Intel 320 SSD drives...so i don't think it is about high queries either.

The website is using wordpress and there are like 9 plugins active..so people said it maybe the plugins...well maybe....but right now i just want to cache the whole database in memory and will ike to get help and direction on where to start and how to do it.

Thanks.

share|improve this question
    
try this: http://www.jetprofiler.com/ to see if you're missing indices in your schema –  Marian Theisen Nov 18 '11 at 5:24
    
I don't know if that's even possible. Are you asking to do it this way because you don't know much about databases and are trying to avoid more practical solutions? –  Brandon Moore Nov 18 '11 at 5:30
    
i have 16GB RAM ...so i mean i just want to do it and actually see if things are fast...and from there i can try to figure out the original problem...so right now i just need help in how to cache to memory –  grant tailor Nov 18 '11 at 5:32
1  
So you tested the performance on a machine with SSDs, it was still slow, and you somehow still think your solution is "more memory"? –  Nick Chammas Nov 20 '11 at 16:53
    
And how do you know your problem is not poorly written queries or poorly designed indexes (or maybe no indexes at all)? –  Nick Chammas Nov 20 '11 at 16:54
add comment

migrated from stackoverflow.com Nov 20 '11 at 16:27

This question came from our site for professional and enthusiast programmers.

2 Answers

If I were you, I would switch all data to InnoDB. Table locking/row locking has long been discussed by many. I would always choose InnoDB hands down. However, there is another profound reason for choosing InnoDB...CACHING.

While most people boast that MyISAM is faster for reads, most people forget that the many cache for MyISAM, which is called the key cache (set by key_buffer_size), only caches index pages from .MYI files. It never caches data pages. It has an official maximum of 4GB in 32-bit Systems. 8GB is best maximum for 64-bit.

The InnoDB Buffer Pool caches the data and index pages. Depending on the server your have, you can cache up to the entire dataset in RAM. You can tune InnoDB for up to 80% RAM and 10% for DB Conenctions, and leave 10% for the OS. This is true even for different operating systems.

I have recommended these things for Drupal customers with marvelous success. It applies to Wordpress just as well. I have provided DB support for clients with WordPress. Same improvements.

You can always configure memory for InnoDB more effectively that you can more MyISAM. There is always a way to tweek InnoDB to suit your performance needs. As your data grows, it will eventually become a requirement.

UPDATE 2011-11-21 11:44 EST

If your complete dataset is small enough, you could execute a SELECT query on every table you have right after mysql starts up.

For all tables that are InnoDB and/or MyISAM, run this query:

SELECT DISTINCT
    CONCAT('SELECT ',ndxcollist,' FROM ',
    db,'.',tb,' ORDER BY ',ndxcollist,';') SelectQueryToLoadCache
FROM (
    SELECT
        engine,table_schema db,table_name tb,index_name,
        GROUP_CONCAT(column_name ORDER BY seq_in_index) ndxcollist
    FROM (
        SELECT
            B.engine,A.table_schema,A.table_name,
            A.index_name,A.column_name,A.seq_in_index
        FROM
            information_schema.statistics A INNER JOIN
            (SELECT engine,table_schema,table_name
            FROM information_schema.tables
            WHERE engine IN ('InnoDB','MyISAM')) B
            USING (table_schema,table_name)
        WHERE
            B.table_schema NOT IN ('information_schema','mysql')
            AND A.index_type <> 'FULLTEXT'
        ORDER BY
            table_schema,table_name,index_name,seq_in_index
        ) A
    GROUP BY
        table_schema,table_name,index_name
) AA
ORDER BY
    engine DESC,db,tb
;

This will output every possible SELECT query you need to run that will summon all indexes to be referenced. Place this query in a file called /root/MakeSelectQueriesToLoad.sql. Run the script and collect the output /root/SelectQueriesToLoad.sql. Finally, run it:

mysql -u... -p... -AN < /root/MakeSelectQueriesToLoad.sql > /root/SelectQueriesToLoad.sql
mysql -u... -p... < /root/SelectQueriesToLoad.sql

This will definitely preload all index pages into the InnoDB Buffer Pool and MyISAM Key Cache. If all your data is InnoDB, make two changes:

  • replace WHERE engine IN ('InnoDB','MyISAM') with WHERE engine='InnoDB'
  • replace CONCAT('SELECT ',ndxcollist,' FROM ', with CONCAT('SELECT * FROM ',

This will also populate more data pages into the InnoDB Buffer Pool.

FINAL NOTE : Make sure the InnoDB Buffer Pool is large enough to hold all your InnoDB Data

share|improve this answer
add comment

You already are caching the whole database in memory. The problem is almost certainly the time it takes to search the database, even in RAM.

share|improve this answer
    
i don't get it...what do you mean by i am already caching the whole database in memory? –  grant tailor Nov 18 '11 at 5:34
1  
Watch your disk I/O stats. You'll probably see that there's just the occasional random bit of disk I/O. The database is in memory. That's not the problem. –  David Schwartz Nov 18 '11 at 5:37
    
how do i check the iostat i tried this iostat and got error -bash: iostat: command not found ... i mean severalpeople told me to cache the entire database in memory...i mean are you saying this is already done? i never did anything like that...so i am confused or you mixing up with something else? –  grant tailor Nov 18 '11 at 5:41
    
tried this also iostat -d -x interval count and got error -bash: iostat: command not found –  grant tailor Nov 18 '11 at 5:55
1  
Did the people who told you that do so after getting any evidence that your entire database wasn't already in memory or that disk I/O was the problem? Otherwise, their advice is the equivalent of a doctor who has never seen or examined you but just heard that your arm hurt telling you to put a cast on it. –  David Schwartz Nov 18 '11 at 6:21
show 9 more comments

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.