Databases
StackOverflow2013
Postsdbo
POOptimizing mySQL database on large table with frequent writes and fulltext columns?
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POOptimizing mySQL database on large table with frequent writes and fulltext columns?
    text
    copied!<p>My team runs a shopping information site and as we've started to grow, we're starting to experience issues with query response time on our product table impacting display speed.</p> <p>The main issue we experience is when a user "saves" an item triggering an update query as other users are searching on the FULLTEXT indexes that exist as well:</p> <pre><code>UPDATE product SET listed = listed+1 WHERE product_id = XX </code></pre> <p>For example, I just ran the update in 0.01 seconds with no other queries hitting but a few minutes ago, with a large FULLTEXT request also going, the same request took 23 seconds.</p> <p>I assume this is because the table is MYISAM and can't do row-level locks. </p> <p>Our product table contains over 3.5 million records and will double by the end of the month. After that it should level off to 2-5% monthly increases.</p> <pre><code>CREATE TABLE product ( product_id INT UNSIGNED NOT NULL AUTO_INCREMENT, category_id INT UNSIGNED NOT NULL DEFAULT '0', title VARCHAR (100) NOT NULL DEFAULT '', short_desc VARCHAR (255) NOT NULL DEFAULT '', description TEXT NOT NULL, msrp DECIMAL (6,2) NOT NULL DEFAULT '000.00', rating DECIMAL(3,2) NOT NULL DEFAULT '0.0', reviews INT UNSIGNED NOT NULL DEFAULT '0', listed INT UNSIGNED NOT NULL DEFAULT '0', sku VARCHAR(75) NOT NULL DEFAULT '0', upc VARCHAR(20) NOT NULL DEFAULT '0', updateddate DATETIME NOT NULL, PRIMARY KEY (product_id), KEY title (title), KEY category_id (category_id), KEY listed (listed), KEY mfrg_id (mfrg_id), KEY identifier (identifier), FULLTEXT INDEX (title), FULLTEXT INDEX (description) ) ENGINE = MYISAM; </code></pre> <p>The database runs on a dedicated server that only hosts our site. We are planning to move the database into a replication structure with a Dual Proc, 16gb RAM server for the query box [slave] and the current "web" server handling the writes [dual proc, 4gb ram].</p> <p>I'm not DB expert [clearly] and from researching have become warry of running InnoDB at the same time as MYISAM [replication &amp; backup implications?] but it does seem like splitting the product table to house main information [innodb] and the fulltext descriptions [myisam] seperately may help dramatically?</p> <p>If you have an idea and need more info please comment and I will provide back more details.</p> <p>Thank you</p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload