2

I'm running a pretty big (or maybe I should call it SELECT & INSERT-intensive) database.

Now I've heard here and there that a MEMORY-based table is better for my situation (since I now run on a RAID1 HDD array consisting of 2 HDDs and can't afford SSDs). But I know that using the MEMORY engine is not good in case of a power outage (as the data on there will be lost). And since I'm running the server in my own house without UPS... well... you see where this is going.

So, how can I have 2 tables, 1 on the disk (InnoDB for example) and 1 in the memory, then have the memory table sync with the on-disk table once every 5 minutes so the data won't get lost completely in case of a power outage? (I realise that the data since the last sync will be gone.)

At the moment, I can use about 2GB of RAM for the database (which should not be a big deal because at the moment the table is about 300MB big with InnoDB).

EDIT:
you can find the code of the slowest page here (link to pastebin) and the page where it is running here

2

Big Query Cache == Big Trouble.

  1. It takes a long time to purge the QC of table entries every time you write to the table.

  2. I robs RAM from other useful tasks.

  3. With 1GB for QC and 835 for buffer_pool, you are very probably swapping. Swapping is terrible for performance; MySQL assumes its caches are not swapped!

Do not make the QC bigger than 50M. In most production system it is better to turn off the QC.

With 2GB of RAM, you probably should not set innodb_buffer_pool_size bigger than 500M.

Listing the indexes does not help without seeing the SELECTs. Please also provide SHOW CREATE TABLE.

50 SELECTs/sec -- Swapping and/or poor indexes and/or poor formulation of queries. 5000/sec may be possible with your hardware.

Abandon the discussion of MEMORY until you work through my comments. We may (but probably won't) need to come back to MEMORY.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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