I have over 1000 posts here at MaisonBisson, but even so, the table with all those posts is under 3MB. Now I’ve got a project with 150,000 posts — yes, 150,000 posts! — and the table is about 500MB. An associated table, structured sort of like WP’s postsmeta, has over 1.5 million records and weighs in at over 100MB (not including the 150MB of indexes).
Up to now I’ve been a “throw more hardware at it” sort of guy — and in a server with only 1GB of RAM, that’s probably the best solution — but I also think it’s time I learned some MySQL optimization tricks. Zach’s been pushing me to get mytop installed for quite a while, and I finally got around to it.
mytop is a console-based (non-gui) tool for monitoring the threads and overall performance of a MySQL 3.22.x, 3.23.x, and 4.x server. It runs on most Unix systems (including Mac OS X) which have Perl, DBI, and Term::ReadKey installed. And with Term::ANSIColor installed you even get color. If you install Time::HiRes, you’ll get good real-time queries/second stats.
mysql, mysql optimization, optimization, server overload, big databases, mytop, server sizing, database optimization, db
Posted January 17, 2006 by Casey Bisson
Categories: Technology. Tags: big databases, database optimization, db, mysql, mysql optimization, mytop, optimization, server overload, server sizing.
2 Comments
Comments RSS
TrackBack Identifier URI
Leave a comment
User contributed tags for this post:
learning mysql (252) - mysql optimization (235) - mysql big tables (198) - mysql big database (109) - mysql optimize (86) - mysql big table (79) - mysql optimization tool (75) - mysql index (61) - mytop mysql 5 (59) - mysql optimisation (50) - MySQL Sort (43) - mysql 5 fulltext (39) - MySQL Optimization tools (39) - mysql bigtable (38) - mysql sort by (38) - mysql Allow big tables (37) - learning mysql server (36) - mysql fulltext optimization (35) - mysql sort index (35) - Mysql indexes (34) - optimize mysql 5 (32) - mysql optimize FULLTEXT (31) - mysql optimize tool (31) - mysql million records (29) - mysql overload (27) - mysql big query (26) - mysql learning (24) - MYSQL index optimization (24) - database optimization (22) - mysql sizing (22) - optimization in mysql (22) - mysql 3 optimize (22) - mysql big database performance (21) - mysql optimize index (21) - cpanel mysql optimization (20) - mysql fulltext search optimization (19) - mysql hardware sizing (19) - mysql hardware (18) - mysql optimizing sort (18) - optimize MySQL cpanel (17) - Allow big tables mysql (17) - optimize mysql (16) - mysql optimize sorting (16) - MySQL big tables GB (16) - mysql 4 optimization (16) - Plesk mysql optimization (16) - mysql shard (16) - mysql fulltext query optimize (15) - mysql full text performance (15) - mysql and sort (15) - mysql search query optimization (15) - big database mysql (15) - indexes optimization in mysql (15) - mysql fulltext optimize (15) - mysql optimizing (15) - mysql fulltext (14) - mysql big databases (14) - mysql fulltext slow (14) - mysql fulltext search optimize (14) - plesk 8 mysql 5 (14) - mysql index slow (14) - mysql optimize big tables (14) - mytop mysql5 (14) - mysql optimalization (13) - optimize mysql fulltext (13) - mysql and big databases (13) - mysql sort records (13) - mysql big (13) - plesk mysql optimize (13) - mysql sort optimization (13) - optimize mysql 4 (12) - mysql full text optimize (12) - mysql 5 database records (12) - mysql index optimizing (12) - mysql index for sort (12) - mysql sorting index (12) - mysql full text optimization (12) - mysql big tables optimization (12) - mysql update statistics (12) - mysql big optimization (11) - optimize mysql index (11) - Optimizing MySQL index (11) - mysql sort index sort times (11) - mysql index trick (11) - mysql database search slow (11) - mysql index in ram (11) - mysql optimize tools (11) - mysql optimize big table (11) - mysql optimize big database (10) - mysql 10 million (10) - cpanel osx (10) - optimize mysql plesk (10) - cpanel optimize mysql (10) - mysql fulltext index search tricks (10) - mysql fulltext sort (10) - google eart 150MB (10) - mysql 5 big database GB (10) - mysql index slower (10) - plesk mysql 5 (10) - plesk optimize mysql (10) - MYSQL Optimization tricks (10) - wordpress mysql optimization (9) - mysql full text search optimize manual (9) - optimizing Cpanel (9) - cpanel optimization (9) - mysql million (9) - mysql fulltext index optimization million (9) - learning DataBase project (9) - mysql 5 optimize (9) - mysql sorting indexes (9) - big tables mysql (9) - mysql big table optimization (9) - mysql query optimization tool (9) - database optimization tricks (8) - mysql database overload (8) - cpanel optimize (8) - optimizing mysql 5 (8) - mysql 1000000 records (8) - mysql fulltext slow reference special (8) - mysql index database (8) - mysql big database optimisation (8) - mysql 5 index optimization (8) - mysql 5 install optimize (8) - mysql big database optimization (8) - mysql optimice db (8) - mysql and ram (8) - mysql 1 million records (8) - sizing mysql (8) - mysql 3 23 migrate 5 (8) - optimization big databases mysql (8) - MySQL millions of records performance (8) - mysql tables over 1 millions records (8) - mysql osx slow (8) - mysql5 optimize (8) - optimize mysql5 (8) - mysql optimization plesk (8) - cpanel mysql optimize (8) - mysql optimization for big datbases (8) - mysql big entries (8) - Allow big tables? (8) - mysql optimize update (8) - optimization (7) - mysql full text search optimization (7) - mysql cpanel optimize (7) - mysql overloading (7) - optimize fulltext search mysql (7) - mysql index ram (7) - mysql fulltext search slow (7) - cpanel mysql 5 (7) - SORT INDEX mysql (7) -
[...] The answer, it seemed, was that I’d have to migrate from Plesk to CPanel to get those features. And now I’ve got a big database project, that’s looking more necessary than ever. [...]
You can upgrade mysql in plesk unless you’re inept with linux/etc. See http://atomicrocketturtle.com for more info.