mysql tips for wordpress
TRANSCRIPT
The Original Site…
2
With AdBlock
3
dSero Anti AdBlock is The Solution
4
The Engine Behind this Site is MySQL 5
What WordPress is Made Of?
6
Netw
ork
PH
P
Ap
ach
e
MyS
QL
Lin
ux
Hard
ware
http://www.perforce.com/blog/110607/how-do-they-do-it-googles-one-server-trick
http://kleanpc.com/cms/services/linux-implementations/
http://www.w3resource.com/mysql/mysql-tutorials.php
http://aserverblog.blogspot.co.il/2012/02/apache-releases-v2222-for-apache-http.html
http://www.makemoneyinlife.com/make-money-with-php-scripts-and-programming.html
http://www.glosecgroup.com/index.php?pid=2&menu=134&open=6&item_id=0&div=5
What Our Database is made of?
Name Engine Rows Row (B) Data (KB) Index (KB)wp_commentmeta InnoDB 297 496 147 32wp_comments InnoDB 99 827 81 65wp_links InnoDB 7 2340 16 16wp_options InnoDB 171 12743 2179 16wp_postmeta InnoDB 340 289 98 32wp_posts InnoDB 689 3448 2375 163wp_term_relationships InnoDB 74 221 16 16wp_term_taxonomy InnoDB 38 431 16 32wp_terms InnoDB 38 431 16 32wp_usermeta InnoDB 61 268 16 32wp_users InnoDB 3 5461 16 32
7
What wp_post is made of?
8
post_type # %revision 501 84%attachment 63 11%post 21 4%nav_menu_item 5 1%page 5 1%
#1: Consider Avoiding Revisions
wp-config.php define('WP_POST_REVISIONS', false);
MySQL DELETE
FROM wp_posts WHERE post_type = "revision";
9
#1: Consider Avoiding Revisions
wp-config.php define('WP_POST_REVISIONS', false);
MySQL DELETE
FROM wp_posts WHERE post_type = "revision";
10
#2: Optimize RAM Usage
Preferred: SizeOf(database) < SizeOf(RAM)
innodb_buffer_pool_size 50-70% of your RAM
11
Do We Utilize the Cache?
SHOW STATUS LIKE 'Qcache%';
12
Qcache_free_blocks 718
Qcache_free_memory 13004008
Qcache_hits 780759
Qcache_inserts 56292
Qcache_lowmem_prunes 0
Qcache_not_cached 3711
Qcache_queries_in_cache
1715
Qcache_total_blocks 4344
#3: Cache Queries
query_cache_type = 1 query_cache_limit = 1M query_cache_size = 16M
13
#4: Buffer Optimization
key_reads/key_read_requests < 0.01 If not
Increase Key Buffer
14
#5: CPU Optimization
thread_concurrency = 2 X #(CPU Cores)
15
#7: Run MySQLTuner.pl +Tuning-Premier.sh
16
http://www.farhanfaisal.com/2010/11/fine-tuning-apache-and-mysql-for-performance-and-security/
#8: Enable Slow Query
Regular Joins w/o Indexes
mysql> explain SELECT COUNT(*), post_type FROM wp_posts GROUP BY post_type;+----+-------------+----------+-------+---------------+------------------+---------+------+------+-------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra +----+-------------+----------+-------+---------------+------------------+---------+------+------+-------------+| 1 | SIMPLE | wp_posts | index | NULL | type_status_date | 140 | NULL | 693 | Using index+----+-------------+----------+-------+---------------+------------------+---------+------+------+-------------+1 row in set (0.02 sec)
17
#9: Multiply Database Servers
18
#10: BackUp
Consistent mysqldump Slave Backup XtraDB
19
#11: Security
Root User w/ Permissions to WordPress DB
only Keep FireWall closed:
3306 Only relevant IPs
20
