mysql just not fast enough - need help - will pay

MySQL & MariaDB Other MySQL® Questions
1

I have a website that serves about 130k pages per day from a mysql database. I tried the optimization scripts and they actually slowed down performance. I found after a few days load averages on server would go up to 8 or 10 and eventually 50 and the site would essentially be down it was running so slow. I discovered a trick I have never read about. If I exported the database and then created a new database and imported the data, the site would run great for a few days. I found no talk of this on the internet (very strange). Anyway, so I did that daily, but as site popularity has gone up it is simply not enough.

The above was all one an 8 core high end box with 4 gig of ram.

So I got a separate 16 core computer for running mysql ONLY. It has bought me a little time, but its still browning out in peak times.

I need somebody experienced in php - mysql to optimize it so I can continue. I just don’t get how mysql on a super powerful computer isn’t enough.

If you can help me. Please email me at my gmail acount: doug99collins (not writing email in usual format to prevent getting spammed).

I am willing to pay a fair wage to get someone to optimize this.

Thanks, Doug

2

Is it a particular query that generate the problem ?
Are your indexes are ok ?

Is you MySQL is configured properly to use all the 16 core ressources ?

3

I’ve already looked at this. Large MyISAM table with Fulltext indices which causes locking is the issue.

4

Hum, if it’s a FULLTEXT problem, I think you might find some informations about this in the FULLTEXT forum )

5

Yes I am using fulltext on one field in my biggest and most used tables. I need this feature so I am kind of stuck. I have been told to try InnoDB but I can’t on this table because I need the fulltext features.

6

Usually I’m not a big fan of this kind of question, but:

Are you sure you need FULLTEXT on your biggest field ?
Can you explain your table structure, and the usage your doing ?

7

doug99,

If search over large tables is your workload then take a serious look at Sphinx [URL=“http://www.sphinxsearch.com/”]http://www.sphinxsearch.com/[/URL]

You can use this with MyISAM, InnoDB and pretty much any storage engine I believe. It adds some complexity though so it may not be desirable in every case. A few classified ad customers I know use Sphinx to index their advert data for ease of searching.

Oh, and I would hazard a guess that the reason you get great performance after a re-import is because this normally re-creates fresh indexes. After a few days of live use the table stats are stale. You might benefit from an “optimize tables” job, perhaps automated and weekly? You mention running optimization scripts, are you doing this already? I’ve found that a simple “optimize tables” statement initiated from myisamchk is an easy win.

[URL]http://dev.mysql.com/doc/refman/5.0/en/optimize-table.html[/URL]

8

load average: 0.36, 0.28, 0.26

some additional indices, seperate myisam table for searches, some config file tweaks, and the problem has been solved

9

What tweaks helped you problem ?

10
[B]FMaz wrote on Fri, 06 November 2009 21:10[/B]
What tweaks helped you problem ?
one big table switched to innodb, so larger buffer pool and the key buffer size was still 8 MB