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
User contributed tags for this post:
learning mysql (257) - mysql optimization (241) - mysql big tables (204) - mysql big database (119) - mysql optimize (86) - mysql big table (81) - mysql optimization tool (79) - mytop mysql 5 (70) - mysql index (61) - mysql optimisation (50) - mysql Allow big tables (44) - MySQL Sort (43) - mysql sort index (43) - mysql fulltext optimization (40) - mysql 5 fulltext (39) - MySQL Optimization tools (39) - mysql bigtable (38) - mysql sort by (38) - learning mysql server (36) - Mysql indexes (34) - optimize mysql 5 (33) - mysql optimize FULLTEXT (32) - mysql optimize tool (32) - mysql million records (29) - mysql overload (27) - mysql big query (27) - MYSQL index optimization (27) - cpanel mysql optimization (24) - mysql hardware sizing (24) - mysql learning (24) - mysql update statistics (24) - database optimization (22) - mysql sizing (22) - mysql fulltext search optimization (22) - optimization in mysql (22) - mysql 3 optimize (22) - mysql big database performance (21) - mysql optimize index (21) - Plesk mysql optimization (20) - mysql optimize sorting (19) - Allow big tables mysql (19) - mysql hardware (18) - plesk mysql optimize (18) - mysql optimizing sort (18) - mysql sort optimization (18) - mysql shard (18) - optimize MySQL cpanel (17) - mysql big databases (17) - big database mysql (17) - mysql optimize big tables (17) - mytop mysql5 (17) - optimize mysql (16) - MySQL big tables GB (16) - mysql 4 optimization (16) - mysql index slow (16) - optimize mysql fulltext (15) - mysql fulltext query optimize (15) - mysql full text performance (15) - mysql sort records (15) - mysql and sort (15) - mysql search query optimization (15) - indexes optimization in mysql (15) - mysql fulltext optimize (15) - mysql optimizing (15) - MYSQL Optimization tricks (15) - mysql fulltext (14) - mysql fulltext slow (14) - mysql fulltext search optimize (14) - plesk 8 mysql 5 (14) - plesk optimize mysql (14) - mysql full text optimization (14) - mysql optimalization (13) - mysql and big databases (13) - mysql full text optimize (13) - mysql big (13) - cpanel optimization (13) - optimize mysql 4 (12) - mysql 5 database records (12) - mysql index trick (12) - mysql index optimizing (12) - mysql index for sort (12) - mysql sorting index (12) - mysql index in ram (12) - mysql optimize tools (12) - mysql big tables optimization (12) - wordpress mysql optimization (11) - mysql big optimization (11) - optimize mysql index (11) - Optimizing MySQL index (11) - mysql sort index sort times (11) - optimize mysql plesk (11) - mysql big database optimization (11) - mysql database search slow (11) - big tables mysql (11) - mysql optimize big table (11) - mysql query optimization tool (11) - mysql full text search optimization (10) - mysql optimize big database (10) - mysql 10 million (10) - cpanel osx (10) - mysql index ram (10) - cpanel optimize mysql (10) - mysql fulltext index search tricks (10) - mysql fulltext sort (10) - google eart 150MB (10) - optimizing Cpanel (10) - mysql 5 big database GB (10) - mysql index slower (10) - plesk mysql 5 (10) - mysql optimization plesk (10) - mysql optimize sort (10) - mysql database overload (9) - mysql full text search optimize manual (9) - sizing mysql (9) - mysql million (9) - mysql fulltext index optimization million (9) - learning DataBase project (9) - mysql 5 optimize (9) - mysql sorting indexes (9) - cpanel mysql optimize (9) - mysql big table optimization (9) - Allow big tables (9) - mysql big-tables (9) - database optimization tricks (8) - cpanel optimize (8) - optimizing mysql 5 (8) - mysql 1000000 records (8) - plesk mysql performance (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 optimice db (8) - mysql and ram (8) - mysql 1 million records (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) - SORT INDEX mysql (8) - mysql osx slow (8) - mysql5 optimize (8) - optimize mysql5 (8) - mysql search optimization (8) - mysql sorting optimization (8) - mysql index sort (8) - mysql optimization for big datbases (8) - mysql big entries (8) - Allow big tables? (8) -
[...] 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.